5 Administering Oracle Database

This chapter describes how to administer Oracle Database 11g Release 2 for BS2000/OSD.

Common administration tasks are described in the following sections:

5.1 Using the SQL*Plus Utility

The following topics are described in this section:

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

Note:

  • The editor in the BS2000 environment is always the EDT.

  • The editor vi does not work on blockmode terminals.

  • The editor edtu does not work on xterm terminals.

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.

See Also:

SQL*Plus User's Guide and Reference for more information about customizing SQL*PLUS profiles

5.2 Startup and Parameter Files

Oracle uses two parameter files when starting the database:

  1. The ORAENV file, the environment definition file, which contains BS2000-specific information. In the ORAENV file you identify the database to be started, or shut down. You can use this file to set configuration variables, which adapt the Oracle Database to the local operating system and application environment.

  2. The initialization file INIT.ORA or the server parameter file SPFILE, which exists in all Oracle Database implementations and contains database-specific parameters.

This section describes the following:

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.3 Remote Startup of a Database Instance

This section describes the preparations for a remote startup using SQL*Plus:

  1. Usually, Oracle Database 11g Release 2 listener parameter file (LISTENER.ORA) does not contain a static service registration section (SID_LIST) for a database service. In case of a remote startup you must define this section for the desired database. For example:

    SID_LIST_LISTENER = (SID_LIST =
                         (SID_DESC = 
                          (SID_NAME = ORCL)))
    

    The listener must be running on the computer where the instance is to be started. The listener must statically register the instance. If the listener does not run under the same user ID as the instance you want to start, then you must define the admissions to start a job under the user ID of the instance in the listener's ORAENV file or you must use SECOS, Fujitsu's Security Control System for BS2000/OSD. For more information refer to the Configuring the Network chapter in the Oracle Net Services section of this guide.

  2. Create a password file with the Oracle utility ORAPWD under the user ID of the instance you want to administrate. For more information of how to use ORAPWD, refer to Oracle Database Administrator's Guide. To run the ORAPWD utility on BS2000, use the following command:

    /START-PROGRAM $ORAC1120.ORAPWD
    *file=password_file password=my_password entries=10
    
  3. The name of the password file is taken from the parameter SSSIDPWF. So you must add this parameter to the ORAENV file of the instance you want to start:

    SSSIDPWF = password_file
    
  4. The parameter REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE in the initialization file of the instance.

    REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
    
  5. Execute SQL*Plus on the remote computer and connect as user sys to a server of the instance you want to startup. In the following example, which shows the commands for SQL*Plus on a UNIX client, we use the net service name orcl_on_bs2000 to address the remote instance on the BS2000 computer:

    sqlplus /nolog
    SQL> connect sys@orcl_on_bs2000 as sysdba
    Enter password:
    password
    Connected
    SQL> startup
    ...
    

5.4 Automatic Diagnostic Repository

Automatic Diagnostic Repository (ADR) is a file-based hierarchical data store for depositing diagnostic information produced by diagnostic framework clients. The repository contains data describing incidents, traces, dumps, alert logs, health check records, SQL Trace information, and other information essential for problem diagnosis.

See Also:

"Managing Diagnostic Data" in Oracle Database Administrators Guide for more information about Automatic Diagnostic Repository

This section describes the following:

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.

See Also:

Oracle Database Administrator's Guide for information about the directory structure

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.

See Also:

"Troubleshooting Oracle Net Services" in Oracle Net Services Administrator's Guide for more information about diagnostic information for Oracle Net Services

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.

See Also:

"ADRCI: ADR Command Interpreter" in Oracle Database Utilities for more information about ADRCI

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.

Note:

ADRCI cannot be started in the normal BS2000 environment. ADRCI must be started in the POSIX shell.

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.

Note:

The editor vi does not work on blockmode terminals and the editor edtu does not work on xterm terminals.

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.

5.5 Checking the Integrity of the Physical Data Structure

To check the data-structure integrity of offline databases, use the DBVERIFY command-line utility. To start DBVERIFY enter the following command:

/START-PROGRAM $ORAC1120.DBV

You can now enter the command, for example:

file=ora11.dbs.database1.dbf blocksize=4096 feedback=100

See Also:

Oracle Database Administrator's Guide and Oracle Database Utilities for more information about the DBVERIFY program