Remote DBA Plans
Remote DBA Service

Changes Under Oracle8

Oracle Tips by Burleson Consulting

Under version 7, SVRMGR was still command-driven. Under Oracle8, the use of pull-down menus and fill-in screens automated many of the more tedious administration tasks, such as the addition of data files, tablespaces, users, dealing with startup/shutdown and with various logs. With Oracle8, SVRMGR and Oracle enterprise monitor (OEM) have come a long way toward being the tools to use to administer a database. The addition of customizable monitor screens under the Oracle performance pack looks to be a big help. Unfortunately, the lack of hard-copy reports, one of the major limitations in SQLRemote DBA under version 7, has not been addressed in Oracle8. However, the SPOOL command capability is still usable under SVRMGR, so some output capability is available. In addition, the UTL_FILE package, new for late Oracle7 and Oracle8, provides direct input and output from PL/SQL to operating system files.

Operation of SVRMGR or SQLPLUS /NOLOG

On Oracle9i, most of the following will apply to a SQLPLUS /NOLOG login as well as SVRMGR.

* To use SVRMGR, the Remote DBA must have issued the .oraenv command on UNIX or Linux to set environmental variables.

* On NT and other Windows-compliant systems, you will be prompted for username, password, and a connect string.

* If the Remote DBA wants to run SVRMGR against a specific instance, the ORAUSER_instance file in the directory specified by the ORA_INSTANCE logical on VMS must be run. Under UNIX, this is taken care of by the .oraenv program.

* The user, for some commands under Oracle8, Oracle8i, and Oracle9i has the OSOPER and OSRemote DBA roles assigned. These roles control the privileges granted, thus allowing limited power to users who must use the CONNECT INTERNAL command under SVRMGR or CONNECT SYS AS SYSRemote DBA under SQLPLUS for shutdown and startup of the database, who but don’t need other privileges. The SVRMGR and SQLPLUS command formats follow.

SVRMGR{L|M} (lowercase on UNIX)

SQLPLUS /NOLOG (lowercase on UNIX)

Once the SVRMGR or SQLPLUS program is running, the Remote DBA uses the CONNECT command to connect to the appropriate instance.

CONNECT username[@db_alias] or

CONNECT username/password[@db_alias [AS SYSRemote DBA|SYSOPER]

The second special form of the CONNECT command can be used by Remote DBAs from accounts with the proper privileges. This form allows the Remote DBA to access all of the structures in the database, including those owned by SYS.

CONNECT INTERNAL [AS SYSOPER|SYSRemote DBA]

The INTERNAL user will have the same password as the SYS user, or the password specified during database creation via the ORAPWRD program at installation. In Oracle9i, the INTERNAL user is no longer supported; you must switch to using SYS. The Remote DBA is allowed certain commands and privileges while in SVRMGR or under the CONNECT…AS SYSRemote DBA umbrella in SQLPLUS. These also depend on database status, which can be one of the following:

Database STATUS	Allowed Commands
CLOSED, not MOUNTED	SHUTDOWN, used for some maintenance activities.
CLOSED, MOUNTED	Used for some maintenance activities.
OPEN, MOUNTED, NORMAL	Normal mode for nonparallel EXCLUSIVE database.
OPEN, MOUNTED, Remote DBA MODE	Used for maintenance.
OPEN, MOUNTED, PARALLEL	Used for parallel instance Oracle.

The Remote DBA tasks include user administration, space administration (physical, such as data files; and virtual, such as tablespace usage), and tools administration. We will cover these tasks in the following sections. In Oracle9i, Svrmgrl is discontinued.

Procedural-Level Standard Query Language (PL/SQL)

The PL/SQL language extensions to SQL make doing complex programming tasks in SQL orders of magnitude easier. PL/SQL adds the capability to use cursors (predefined selects), loops, temporary tables, and a number of other high-level language features not available in standard SQL. Stored functions, procedures, packages, triggers, and methods are all built using PL/SQL. The Oracle-provided packages (DBMS_*, and, UTL*) are also built using PL/SQL and will become your good friends as you use them to explore and master Oracle. In later versions of 8i (8.1), Java is available for trigger, procedure, and package work, as well as PL/SQL. PL/SQL is detailed in the latest release of the PL/SQL documentation

As a Remote DBA, you will have to become very familiar with PL/SQL. There are numerous examples in this book to help; I also suggest you keep handy the PL/SQL User’s Guide and Reference, Release 9.0.1, Part No. A89856-01, June 2001, Oracle Corporation. Another excellent reference is PL/SQL Advanced Programming, by Steven Feuerstein (O’Reilly and Associates, 1996); and for sheer ease of use, nothing beats the RevealNet online references (PL/SQL for Developers, PLVision Lite, and PLVision Professional, available at www.revealnet.com/). Another great reference is Oracle PL/SQL Tips and Techniques by Joseph C. Trezzo, (Oracle Press, Osborne-McGraw Hill, 1999.)

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.