5
Administering the Oracle Server

This chapter describes how to use the SQL*Plus utility to administer Oracle9i Release 2 Server for BS2000/OSD.

Common administration tasks are described in the following sections:

• Using the SQL*Plus utility

• Startup and parameter files

• Starting a database instance from a remote machine

• Security considerations

• Customizing your Oracle Server system

Using the SQL*Plus Utility

Invoking the SQL*Plus Utility

To start SQL*Plus, enter the following:

/START-PROGRAM $ORACL920.SQLPLUS

After that you will be prompted for parameters: enter /NOLOG:

* /NOLOG

This prevents SQL*Plus from prompting you for username/password. Then you can connect to the database, e.g.:

SQL> CONNECT / AS SYSDBA
...

For more ways to invoke SQL*Plus see Oracle9i User's Guide, ch. Running SQL*Plus.

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.

Issuing BS2000 Commands from SQL*Plus

The SQL*Plus HOST command allows 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, 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, 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 up, or shut down, and you can set configuration variables which adapt the Oracle Server to your local operating system and application environment.

2. The initialization file (INIT.ORA) or the server parameter file (SPFILE) which exists in all Oracle Server 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 normally ensured by the installation procedures which create the basic ORAENV file. See Appendix B, "ORAENV Variables" for details of required and optional ORAENV variables.

The Initialization File INIT.ORA

SQL*Plus requires the INIT.ORA parameter file which contains a list of specifications for the Oracle Server system. These parameters are used to set up filename assignments and to determine the number of entries stored in the System Global Area for each Oracle Server system resource. The SGA resides in shared memory when your Oracle Server system is running and thus allows fast access to any information it contains. See the Oracle9i Administrator's Guide for full descriptions of these parameters.

The Server Parameter File SPFILE

Starting with Oracle 9i, 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, 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 $ORACL920.DEMO.DBS.INIT.ORA, is distributed with your Oracle Server database. During the Database Installation procedure, this file is copied to your $DBA account 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 your installation parameter file by examinig file names in the following order:

1. sid.DBS.SPFILE.ORA

2. DBS.SPFILE.ORA

3. sid.DBS.INIT.ORA

If you wish to use some other initialization file, 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 $ORACL920.SQLPLUS 
* /NOLOG

At the SQL*Plus prompt, enter:

SQL> CONNECT / AS SYSDBA

SQL> STARTUP PFILE=TEST.INIT.ORA

Remote Startup of a Database Instance

In Oracle9i Release 2 Server for Fujitsu Siemens BS2000/OSD you can use either SQL*Plus or the Oracle Enterprise Manager to perform a remote instance startup.For information of how to configure the Intelligent Agent for the use with the Oracle Enterprise Manager please refer to Chapter 15, "Oracle Intelligent Agent" in this guide.

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

1. Normally the Oracle 9i 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, e.g.:

   SID_LIST_LISTENER = (SID_LIST =
                        (SID_DESC = 
                         (SID_NAME = ORA9)))
   
   

   The listener must be running on the machine where the instance is to be started. The listener must statically register the instance. If the listener does not run under the same userid as the instance you want to start you have to define the admissions to start a job under the userid of the instance in the listener's ORAENV file. For more information see the section "Configuring the Network" in this guide.

2. Creat a password file with the Oracle utility ORAPWD under the userid of the instance you want to administrate. For more information of how to use ORAPWD please refer to the Oracle9i Administrator's Guide. To invoke the ORAPWD utility on BS2000, use the following command:

   /START-PROGRAM $ORACL920.ORAPWD
   *file=password_file password=my_pass 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 initialisation file of the instance.

   REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
   
   

5. Execute SQL*Plus on the remote machine 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
   ora9_on_bs2000 to address the remote instance on the BS2000 machine:

   sqlplus /nolog
   SQL> connect sys@ora9_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 external command-line utility. To start DB_VERIFY enter the following command:

/START-PROGRAM $ORACL920.DBV

You can now enter your command, for example:

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

For more information about the DB_VERIFY program, see the Oracle9i Administrator's Guide and the Oracle9i Utilities book.

Customizing Your Oracle Server System

You can customize your Oracle Server system. The following topics are described in this section:

• updating the global SQL*Plus profile

• adding database files

These procedures are optional and can be performed at any time after the database has been installed. You can perform some or all of them, or decide not to do any of them at all.

Updating the Global SQL*Plus Profile

The DBA can update the global SQL*Plus profile file, $ORACL920.sqlplus.admin.GLOGIN.SQL, which will be executed when a user logs in to SQL*Plus. This file is executed before the user's local LOGIN.SQL (see User's Guide, ch. SQL*Plus) and is provided to allow sites to set up several defaults useful to all users. You can place any SQL and SQL*Plus statement in GLOGIN.SQL.

Note: The procedures in the following sections must be performed under the $DBA userid.

Adding Database Files

You can add additional files to the existing SYSTEM tablespace, or you can create new tablespaces, according to your needs. You do this from SQL*Plus. See your Oracle9i SQL Reference manual for information on the CREATE TABLESPACE, ALTER TABLESPACE and DROP TABLESPACE commands.