7
Oracle9i Utilities

This chapter provides OS/390-specific information for running Oracle utilities that are specific to database administration and are therefore not covered in the Oracle9i Enterprise Edition User's Guide for OS/390.

The following topics are included:

Overview

The Oracle utilities that are specific to database administration include Recovery Manager (RMAN), the Oracle Password utility (ORAPWD), and the Offline Database Verification utility (DBV). In Oracle9i, the functions of Server Manager (SVRMGRL) have been incorporated into SQL*Plus. Refer to the Oracle9i Enterprise Edition User's Guide for OS/390 for OS/390-specific details on running SQL*Plus, Oracle Export and Import, and other utilities that are used by non-administrative users. Certain OS/390 facilities that are common to all Oracle utilities, such as PARM field processing and FNA, are also covered in the Oracle9i Enterprise Edition User's Guide for OS/390.

General Considerations

Each of the Oracle utilities can be invoked as a TSO command, as a batch or started task jobstep, or via TSO CALL. When invoked as a TSO command, parameters are specified on the command line in the usual manner for TSO command processors. For batch or CALL invocation, parameters are supplied within the PARM string. When usage requires more parameter data than will fit in a 100-character PARM, the "++" notation that is described in the Oracle9i Enterprise Edition User's Guide for OS/390 can be used to specify that PARM data be read from a data set.

The syntax with which OS/390 data sets are specified to Oracle utilities differs from the syntax that is used in the Oracle server (described in Chapter 4, "Defining OS/390 Data Sets for the Oracle Database"). The primary differences include the use of "/DD/" and "/DSN/" as prefixes to indicate whether a DD name or data set name is used and the availability in utilities of FNA. FNA is a facility for manipulating simple (single-level) file names so that they are treated as PDS (Partitioned Data Set) member names or as other OS/390-specific identifiers. Refer to the Oracle9i Enterprise Edition User's Guide for OS/390 for a complete discussion of file name syntax and FNA processing.

The Oracle installation process will, optionally, install JCL procedures for commonly-used utilities. To facilitate having multiple different release levels of Oracle software, the installation allows you to specify a distinct 2-character suffix that is appended to the basic name of each such JCL procedure. The detailed utility descriptions on the following pages mention the procedure name for each utility that has one. The name is given with lower-case "xx" on the right-hand end, signifying the optional suffix that is specified at installation time. Refer to your installation records to determine whether these procedures were installed and to determine their exact names.

The following DD statement considerations are common to all Oracle utilities on OS/390:

STEPLIB

Specify a STEPLIB DD in a batch job or TSO command invocation if the Oracle utility that is being invoked is not in a linklist library or OS/390 LPA. Usually STEPLIB designates the Oracle CMDLOAD or AUTHLOAD data set that was created during installation.

ORA$LIB

Specify an ORA$LIB DD statement if your STEPLIB or linklist does not contain the modules that were dynamically loaded by Oracle code, including the Oracle program interface and message and NLS data modules that are used by Oracle localization features.

SYSERR

Required in a non-TSO environment, SYSERR is a sequential output file to which runtime environment error messages are written. Usually this is specified as a SYSOUT data set. In a TSO environment, if no SYSERR DD is allocated to the session, runtime error messages are displayed at the terminal.

SYSOUT

Required in a non-TSO environment, SYSOUT is a sequential output file to which normal tool or utility messages and other output are written. Usually this is specified as a SYSOUT data set. In a TSO environment, if no SYSOUT DD is allocated to the session, then normal output is displayed at the terminal.

SYSIN

Required in a non-TSO environment, SYSIN is a sequential input file which the tool or utility reads for input commands or data. Usually this is specified as an instream (DD *) data set or as a member of a PDS. When creating or editing a PDS member that will be used as SYSIN for an Oracle tool or utility, do not allow the editor to put sequence numbers or other nonblank data into the rightmost positions of each record. In a TSO environment, if no SYSIN DD is allocated to the session, tool, or utility, then input is read from the terminal.

Some utilities receive all of their input from the command line or JCL PARM field. A batch execution of such utilities must include a SYSIN DD statement, but it can be coded as DUMMY.

SQL*Plus on OS/390

In Oracle9i, SQL*Plus is used for most database administration tasks, including Oracle startup and shutdown. All of the functions that were previously provided in line-mode Server Manager are available in SQL*Plus.

A version of SQL*Plus for the OS/390 Unix System Services (USS) environment is also supplied. You can invoke this version of SQL*Plus in a USS shell environment and utilize input and output files that are in the USS Hierarchical File System (HFS).

Considerations for using Oracle tools and utilities, including SQL*Plus, are discussed in the Oracle9i Enterprise Edition User's Guide for OS/390.

Recovery Manager (RMAN) on OS/390

Oracle9i Recovery Manager can be invoked in batch or TSO using the name RMAN. If you installed the Oracle JCL procedures, then you can execute the ORARMNxx procedure to invoke RMAN.

RMAN has some special processing requirements. First, it must be able to read the recover.bsq script during its initialization. (This script is member RECOVER of the SQL data set created during Oracle installation.) RMAN expects a BSQ DD statement that specifies the SQL data set but no member name, as in the following:

//BSQ DD DISP=SHR,DSN=ORAN.ORAV.SQL

If you use the ORARMNxx procedure, this DD statement is already included.

Depending on how it is used, RMAN may need to connect to as many as three distinct Oracle database instances: one for its catalog (the "catalog instance"), one for the database that is being backed up or recovered (the "target instance"), and, during certain types of point-in-time recovery, an "auxiliary instance" that participates in recovery processing.

The requirement to connect to multiple instances indicates that you cannot rely entirely on one of the singular mechanisms (the ORA@sid DD statement or the ORACLE_SID or TWO_TASK environment variables) to specify the instance. You can use one of those mechanisms for any one of your RMAN connections, but the other connection(s), if required, must use a tnsnames.ora file or explicit Oracle Net address strings. Oracle Corporation recommends using a tnsnames.ora file, which is specified via a TNSNAMES DD statement on OS/390. Refer to the Oracle9i Net Services Book Set and to Chapter 10, "Oracle Net", for a discussion of this file.

The RMAN CONNECT statements that do not rely on ORA@sid, ORACLE_SID, or TWO_TASK will need to supply the tnsnames.ora identifier for the instance. In the example batch RMAN job which follows, we have used ORA@sid to access the catalog instance at SID 'ORMC' and have used a tnsnames.ora identifier to access the target instance at SID 'ORA1'. Only the RMAN CONNECT statements are shown.

//ORARMAN  JOB 1,'Oracle Recovery Mgr'
//RMAN     EXEC PGM=RMAN
//STEPLIB  DD DISP=SHR,DSN=ORAN.ORAV.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORAN.ORAV.MESG
//BSQ      DD DISP=SHR,DSN=ORAN.ORAV.SQL
//SYSERR   DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//ORA@ORMC DD DUMMY
//TNSNAMES DD *
DBORA1=(DESCRIPTION=(ADDRESS=(PROTOCOL=XM)(SID=ORA1)))
/*
//SYSIN    DD *
connect catalog rman/rman
connect target /@DBORA1
...
/*

On OS/390, RMAN sets return code zero if all input statements are processed without error. If any errors occur, a return code of 8 is produced.

Oracle Password Utility (ORAPWD) on OS/390

The Oracle password utility, ORAPWD, is used to initialize a password file that the database server uses to validate certain types of Oracle logon. Usage considerations for a password file (which is optional) are discussed in Chapter 8, "Security Considerations" and in the Oracle9i Database Administrator's Guide.

ORAPWD can be run in batch or as a TSO command. Because it is used infrequently, no JCL procedure is provided. The password file must be pre-allocated as a VSAM LDS prior to executing ORAPWD. The IDCAMS DEFINE CLUSTER considerations for this file are exactly the same as those for Oracle database files, discussed in Chapter 4, "Defining OS/390 Data Sets for the Oracle Database". Refer to the Oracle9i Database Administrator's Guide for information on sizing this file.

All input to ORAPWD comes from the PARM field or TSO command line parameters. A SYSIN DD statement must be supplied, but it can be coded as DUMMY. When you specify the FILE= parameter to ORAPWD, use only the data set name of the VSAM LDS. Do not include apostrophes or any "/DSN/" prefix. The OS/390 userid that is associated with the batch job or TSO session must have update authority on the data set. Following is an example batch job that creates the password data set using IDCAMS and then initializes the password data set using ORAPWD.

//ORAPW    JOB 1,'Oracle Administration'
//AMS      EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
 DEFINE CLUSTER(NAME(ORAPROD.ORADB1.PWD) LINEAR -
  RECORDS(16) STORAGECLASS(PRODSC1) MANAGEMENTCLASS(PRODMC1))
/*
//OPW      EXEC PGM=ORAPWD,COND=(0,NE),
//  PARM='FILE=ORAPROD.ORADB1.PWD PASSWORD=SECRET3 ENTRIES=32'
//STEPLIB  DD DISP=SHR,DSN=ORAN.ORAV.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORAN.ORAV.MESG
//SYSERR   DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSIN    DD DUMMY
//

On OS/390, ORAPWD sets return code zero if the file is initialized without errors. If any errors occur, a nonzero return code is produced.

Offline Database Verification Utility (DBV) on OS/390

DBV (Database Verification Utility) examines the physical and logical structure of an offline Oracle database file or a (backup) copy of a database file. General considerations for using DBV are discussed in the Oracle9i User-Managed Backup and Recovery Guide.

DBV can be run as a TSO command or as a batch job. Because it is used infrequently, no JCL procedure is provided. All input to the utility is via command line parameters or the PARM field. A SYSIN DD statement is required, but it can be coded as DUMMY. The FILE= parameter for DBV must be specified as a DD name with a "/DD/" prefix, as shown in the following example. Do not use the BLOCKSIZE and FEEDBACK parameters on OS/390.

//ORADBV  JOB 1,'Oracle DBVerify'
//DBV     EXEC PGM=DBV,
//  PARM='FILE=/DD/DBFILE START=1 END=50'
//STEPLIB  DD DISP=SHR,DSN=ORAN.ORAV.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORAN.ORAV.MESG
//DBFILE   DD DISP=SHR,DSN=ORAPROD.ORADB1.SYSTEM.BKUP.DBF1
//SYSERR   DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSIN    DD DUMMY
//

On OS/390, DBV produces a zero return code if processing was successful and if no logical or physical errors were detected. Otherwise, return code 8 is produced.

7 Oracle9i Utilities