5 Administering Oracle Database

Using the SQL*Plus Utility

The following topics are described in this section:

• Invoking the SQL*Plus Utility

• Calling SQL*Plus from a Procedure

• Running BS2000 Commands from SQL*Plus

/START-PROGRAM $ORAC1020.SQLPLUS

* /NOLOG

SQL> CONNECT / AS SYSDBA

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.

CREATE SPFILE FROM PFILE;

/START-PROGRAM $ORAC1020.SQLPLUS
* /NOLOG

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

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_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 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:

   /START-PROGRAM $ORAC1020.ORAPWD
   *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.

   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:
   *my_password
   Connected
   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:

/START-PROGRAM $ORAC1020.DBV

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.