5 Administering Oracle Database

This chapter describes how to use the SQL*Plus utility to administer Oracle Database 10g release 2 for BS2000/OSD.

Common administration tasks are described in the following sections:

Using the SQL*Plus Utility

The following topics are described in this section:

Invoking the SQL*Plus Utility

To start SQL*Plus, enter the following:


When you are prompted for parameters, enter /NOLOG:


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


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

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.

Running BS2000 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.

  • If you enter the HOST command with a BS2000 command, then the command is executed and you return to SQL*Plus.

Startup and Parameter Files

SQL*Plus uses two parameter files when starting and stopping 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.

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 and by all background jobs. This is ensured by the installation procedures, which create the basic ORAENV file. Refer to Appendix B, "ORAENV Variables" for details of required and optional ORAENV variables.

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 the Oracle Database Administrator's Guide and Oracle Database Reference for full descriptions of these parameters.


Oracle recommends that you always use a question mark (?) to denote the database system-id in initialization files. The "@" character, which is used on other platforms, is the equivalent but is not available on all keyboards and may cause problems in Globalization Support character-set translations.

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:


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

Using the Correct Initialization File

A default initialization file, called $ORAC1020.DEMO.DBS.INIT.ORA, is distributed with Oracle Database. During the Database Installation 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 Installation 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:



  3. sid.DBS.INIT.ORA

If you wish 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:


At the SQL*Plus prompt, enter:


Remote Startup of a Database Instance

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

  1. Usually, the Oracle Database 10g release 2 listener's 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 need to define this section for the desired database. For example:

                         (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 have to define the admissions to start a job under the user ID of the instance in the listener's ORAENV file. 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 the Oracle Database Administrator's Guide. To run the ORAPWD utility on BS2000, use the following command:

    *file=password_file password=my_password entries=10
  3. The name of the password file is taken from the parameter SSSIDPWF. So you have to 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.

  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:
    SQL> startup

Checking the Integrity of the Physical Data Structure

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


You can now enter the command, for example:

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

For more information about the DB_VERIFY program, refer to the Oracle Database Administrator's Guide and the Oracle Database Utilities book.

Customizing SQL*Plus Profiles

The DBA can update the global SQL*Plus profile file, $ORAC1020.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 (refer to SQL*Plus User's Guide and Reference) 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.