5 Administering Oracle Database

5.1.1 Starting the SQL*Plus Utility in BS2000

To start SQL*Plus, enter the following:

/START-PROGRAM $ORAC1120.SQLPLUS

When you are prompted for parameters, enter /NOLOG:

* /NOLOG

This prevents SQL*Plus from prompting you for user name and password. Later you can explicitly connect to the database. For example:

SQL> CONNECT / AS SYSDBA

For more ways to start SQL*Plus, refer to "Running SQL*Plus" in Oracle Database User's Guide for Fujitsu BS2000/OSD.

5.1.2 Calling SQL*Plus from a Procedure

Set Task Switch 1 to on (/MODIFY-JOB-SWITCHES ON=1). This forces SQL*Plus to read in data from the procedure, rather than prompt you at the terminal.

5.1.3 Using SQL*Plus in the POSIX shell

Before starting SQL*Plus in the POSIX shell, you must set the environment variable ORACLE_HOME and extend the POSIX variable PATH by the path to the Oracle bin directory $ORACLE_HOME/bin. For example:

$ ORACLE_HOME=/u01/app/orac1120/product/dbhome_1
$ export ORACLE_HOME
$ PATH=$ORACLE_HOME/bin:$PATH
$ export PATH

Alternatively, you can process the profile oracle_home_path/.profile.oracle, which is created during the installation of the Oracle software under POSIX. This profile sets and expands the most important variables like ORACLE_HOME and PATH. Process the profile as follows:

$ . /u01/app/orac1120/product/dbhome_1/.profile.oracle

If you want to start SQL*Plus for a specific Oracle instance, then you must also set the variable ORACLE_SID. For starting an Oracle server process, the BS2000 variable BGJPAR is required. This variable is not set by running .profile.oracle.

If you want to start a background job with special job parameters, for example, a job should be assigned to a special JOB-CLASS, then ensure that the variable BGJPAR is set in your environment.

$ ORACLE_SID=orcl
$ export ORACLE_SID
$ BGJPAR='START=SOON,CPU-LIMIT=NO,J-C=JCBORA,LOGGING=*NO'
$ export BGJPAR

You can start SQL*Plus in the same way as on other UNIX systems with the following command:

$ sqlplus /nolog
$ SQL> connect / as sysdba

5.1.4 Running System Commands from SQL*Plus

The SQL*Plus HOST command enables you to enter a BS2000 command, while you are logged on to SQL*Plus.

Keep the following points in mind when using the HOST command:

• If you enter the HOST command without any BS2000 command, then it takes you to the command level. To return to SQL*Plus, you must use the RESUME command in BS2000 or the exit command in the POSIX subshell.

• If you enter the HOST command with a system command in the BS2000 environment, then you can only run BS2000 commands. In the POSIX shell, you can run shell commands and BS2000 commands by using the shell command bs2cmd.

5.1.5 Using an Editor in SQL*Plus

Start a text editor in SQL*Plus with the EDIT command, if you want to edit an SQL statement.

If you run SQL*Plus in the native BS2000 environment, then the only editor supported is BS2000 EDT.

If you run SQL*Plus in a POSIX shell, then the default editor depends on the terminal connected with your POSIX session. If the POSIX shell is started on a blockmode terminal, then the default editor in SQL*Plus is set to edtu. If the POSIX shell is started by a remote X-client through rlogin or ssh using a xterm terminal, then the default editor in SQL*Plus is set to vi.

SQL*Plus provides the opportunity to define a preferred text editor with the DEFINE _EDITOR command. In the BS2000 environment, SQL*Plus accepts any editor you specify in the DEFINE _EDITOR command, but the called editor is always the EDT. In the POSIX environment you can define a preferred editor. For example, if you want to define the editor, used by the EDIT command, to be the POSIX editor edtu, then enter the following command in SQL*Plus:

DEFINE _EDITOR = edtu

5.1.6 Customizing SQL*Plus Profiles

The DBA can update the global SQL*Plus profile file, $ORAC1120.SQLPLUS.ADMIN.GLOGIN.SQL, which is run when a user logs in to SQL*Plus. This file is run before the user's local LOGIN.SQL and is provided to enable sites to set up several defaults useful to all users. You can place any SQL and SQL*Plus statement in GLOGIN.SQL.

5.2.1 The Environment Definition File ORAENV

The ORAENV file is identified by sid.P.ORAENV, where SID is the database identifier. The same ORAENV file must be used by SQL*Plus in BS2000 and by all background jobs. This is ensured by the installation procedures, which create the basic ORAENV file. Refer to Appendix B, "Oracle Environment Variables", for details of required and optional ORAENV variables.

If you use SQL*Plus in the POSIX shell, then the requested BS2000 parameters set in the ORAENV file must be specified. You can set the variables in the POSIX environment or use the facility to access the BS2000 ORAENV file. When specifying the SID in the POSIX ORAENV filename, ensure that the SID in the filename and the ORACLE_SID variable use exactly the same case. For example, if you create a POSIX ORAENV file with the SID in the filename in uppercase, then you must set the ORACLE_SID using exactly the same SID.

$ echo '$ORADATA.ORCL.P.ORAENV' > $ORACLE_HOME/dbs/oraenvORCL
$ ORACLE_SID=ORCL
$ export ORACLE_SID
$ sqlplus /nolog
$ SQL> connect / as sysdba

Refer to "Starting Oracle Utilities in the POSIX environment" in Oracle Database User's Guide for Fujitsu BS2000/OSD, for more information about how to set POSIX environment variables.

5.2.2 The Initialization File INIT.ORA

Startup requires the INIT.ORA parameter file, which contains a list of specifications for the Oracle database. These generic, that is, platform independent parameters, are used to setup the instance. Refer to Oracle Database Administrator's Guide and Oracle Database Reference for full descriptions of these parameters.

5.2.3 The Server Parameter File SPFILE

You can choose to maintain initialization parameters in a binary server parameter file. A server parameter file is initially built from a traditional text initialization parameter file using the CREATE SPFILE command. If you enter the following command:

CREATE SPFILE FROM PFILE;

where neither SPFILE name nor PFILE name is specified, then Oracle looks for a text initialization file sid.DBS.INIT.ORA and creates a server parameter file sid.DBS.SPFILE.ORA.

5.2.4 Using the Correct Initialization File

A default initialization file, called $ORAC1120.DEMO.DBS.INIT.ORA, is distributed with Oracle Database. During the database creation procedure, this file is copied to the DBA User ID and renamed, sid.DBS.INIT.ORA, where sid is the 1 to 4 character database ID you specified at the beginning of the database creation procedure.

Oracle determines the value of SID by retrieving the ORASID environment variable defined in the ORAENV file for the database. When you issue the STARTUP command with no PFILE clause, Oracle locates the initialization parameter file by examining file names in the following order:

1. sid.DBS.SPFILE.ORA

2. DBS.SPFILE.ORA

3. sid.DBS.INIT.ORA

If you want to use some other initialization file, then use the argument PFILE. For example, to bring up a previously created database using an initialization file called TEST.INIT.ORA, enter the following:

/START-PROGRAM $ORAC1120.SQLPLUS
* /NOLOG

At the SQL*Plus prompt, enter:

SQL> CONNECT / AS SYSDBA
SQL> STARTUP PFILE=TEST.INIT.ORA

5.4.1 Automatic Diagnostic Repository Directories and Files

Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down.

The directories and files of the Automatic Diagnostic Repository are stored in the POSIX file system.

The ADR root directory is known as ADR base. Its location is set by the DIAGNOSTIC_DEST initialization parameter. For example:

DIAGNOSTIC_DEST=/u01/app/orac1120/oradata/adr

If this parameter is omitted or left null, then the database sets DIAGNOSTIC_DEST upon startup as follows:

• If environment variable ORACLE_BASE is set, then DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

• If environment variable ORACLE_BASE is not set, then DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

Within ADR base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data, such as traces, dumps, the alert log, and so on, for a particular instance of a particular Oracle product or component.

Oracle Net Services also store diagnostic data in the ADR. The location for diagnostic information for Oracle Net Services is set by the parameters ADR_BASE and ADR_BASE_listener_name. These are set in the Oracle Net Services parameter files sqlnet.ora and listener.ora.

You can read the text files of the Automatic Diagnostic Repository with text editors such as vi, edtu, or with POSIX shell commands such as cat, more.

You can investigate the ADR with the utility ADRCI.

5.4.2 ADR Command Interpreter

ADR Command Interpreter (ADRCI) is a utility that enables you to investigate problems, view health check reports, and package first-failure diagnostic data within a command-line environment. You can then upload the package to Oracle Support. ADRCI also enables you to view the names of the trace files in the ADR, and to view the alert log with XML tags stripped, with and without content filtering.

Start the command-line utility ADRCI in the POSIX shell. Before starting ADRCI, set the environment variable ORACLE_HOME and extend the environment variable PATH by the path name of the Oracle directory oracle_home_path/bin. Additional environment variables, such as ORACLE_SID, are not required. Refer to "Starting Oracle Utilities in the POSIX environment" in Oracle Database User's Guide for Fujitsu BS2000/OSD for more information about Oracle Database utilities.

After setting these environment variables, start ADRCI in the POSIX shell by entering adrci after the POSIX shell command prompt.

Display the current ADR base with the ADRCI show base command. The current ADR home can be displayed with the show homes command.

Set ADR base with the set base command. ADR home can be set with the set home command.

The command show alert shows the contents of the alert log in a text editor.

If you use a blockmode terminal, then the default editor for ADRCI on Fujitsu BS2000/OSD is edtu. If you use an xterm terminal after having logged in to POSIX through rlogin or ssh, then the default editor for ADRCI is vi.

You can either select the preferred text editor by setting the environment variable EDITOR before starting ADRCI or specify your preferred text editor within ADRCI with the ADRCI set editor command.

With ADRCI you can invoke Incident Packaging Service (IPS) to create packages for incidents with the commands ips create package and ips generate package. The resulting zip file can be uploaded to Oracle Support.