7 Oracle Database Administration Utilities

7.1 Global Environment Variable File

Environment variables, discussed in the Oracle Database User's Guide for IBM z/OS, are used to control certain aspects of Oracle tools, utilities, and user-written applications. They are named parameters that are supplied locally when the program runs. In POSIX shell environments they can be supplied using native shell mechanisms and in non-POSIX TSO and batch they are supplied using a file identified by an ORA$ENV DD statement or allocation. A client's language, character set, and related locale preferences are among the things controlled by environment variables.

Starting with version 10g, Oracle Database on z/OS supports the use of a global environment variable file. This is a specific data set or HFS file that is read by all Oracle tools, utilities, and client programs running in a POSIX(OFF) environment. The global environment file is not read by Oracle-accessing CICS TS or IMS TM transactions, nor is it read by Oracle database or gateway instances running on z/OS, nor is it read by Oracle tools and utilities running in a POSIX(ON) environment.

Using a global environment file allows you to set default values for selected environment variables for all non-POSIX clients (other than CICS TS and IMS TM) in your system. This can free users from having to supply a local environment variable setting (for example, an ORA$ENV DD) that is widely used in your installation. Global environment file settings only act as defaults: individual jobs or users can override global settings by specifying the same environment variable with a different value in a local ORA$ENV file.

By default, the global environment file has the following filespec:

//'SYS1.ORACLE.ENV'

This means it is a sequential data set named SYS1.ORACLE.ENV. During initialization of Oracle tools and utilities, or during the first Oracle interaction in a user-written application, if a data set of this name exists and can be opened, it is read and processed as environment variable settings. By creating a cataloged data set with this name and placing environment variable settings in it, you activate the global environment variable file feature. For more information on filespecs and environment variables, refer to the Oracle Database User's Guide for IBM z/OS.

If desired, you can change the filespec that Oracle accesses as the global environment variable file. To do this, you must create a loadable module named ORAENVGL and place it where it will be loaded by all Oracle-accessing programs. Depending on how you have installed Oracle client components, this could be in the Oracle CMDLOAD data set or it could be in a system link list library.

The sole content of the ORAENVGL module is the character string filespec to use for the global environment file, ended by a single zero (X'00') byte. The simplest way to create this module is to assemble and link a small source program, as in the following example:

ORAENVGL RSECT
ORAENVGL RMODE ANY
*   Filespec to open for Oracle client global environment
*   variable settings.  Contains single apostrophes, which
*   must be doubled within an assembler character constant.
         DC    C'//''ORACLE.GLOBAL.ENV'''
         DC    X'00'      Required terminator byte
         END   ORAENVGL

This example causes applications to try to use the data set ORACLE.GLOBAL.ENV as the global environment file instead of SYS1.ORACLE.ENV.

Whether you need to use a different filespec or not, you should refer to the Oracle Database User's Guide for IBM z/OS for more information about environment variable files and environment variables. You may also want to talk to the users who are developing or running Oracle database applications on z/OS to determine if global environment settings might be appropriate in your system.

7.2 Recovery Manager (RMAN) on z/OS

RMAN on z/OS supports batch job, TSO, and POSIX shell execution environments. A JCL procedure for batch execution (named ORARMN, by default) is supplied by Oracle and may be installed on your system with the same, or a different, name. In TSO, both CALL and command processor (CP) invocation are supported. For non-POSIX environments, the load module or program object name is RMAN. In a POSIX shell (including OMVS under TSO), use the rman command (in lower case) to invoke this utility.

RMAN has some special processing requirements. It must be able to read the recover.bsq script during its initialization. In batch and TSO environments this script is the RECOVER member of the SQL data set created during Oracle Database installation. RMAN expects a BSQ DD statement or TSO file allocation that specifies the SQL data set but no member name, as in the following examples:

Batch job or TSO logon procedure:

//BSQ DD DISP=SHR,DSN=oracle_hlq.SQL

TSO dynamic allocation:

ALLOCATE FILE(BSQ) DA('oracle_hlq.SQL') SHR

If you use the ORARMNxx procedure, this DD statement is already included. In a POSIX shell this script is $ORACLE_HOME/rdbms/admin/recover.bsq. It is also created during installation. Verifying that the ORACLE_HOME environment variable is set correctly will ensure that the script can be read by RMAN.

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. Refer to the Oracle Net Services Book Set and to Chapter 8, "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=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD DISP=SHR,DSN=ORACLE.V10G.MESG
//BSQ      DD DISP=SHR,DSN=ORACLE.V10G.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 z/OS, RMAN sets return code zero if all input statements are processed without error. If any errors occur, a return code of 8 is produced.

7.3 Oracle Password Utility (ORAPWD) on z/OS

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 9, "Security Considerations" and in the Oracle Database Administrator's Guide.

ORAPWD on z/OS supports batch job, TSO, and POSIX shell execution environments. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object name is ORAPWD. In a POSIX shell (including OMVS under TSO), use the orapwd command (in lower case) to invoke this utility.

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 z/OS Data Sets for the Oracle Database". Refer to the Oracle Database Administrator's Guide for information on sizing this file.

All input to ORAPWD comes from the PARM field or command line parameters. When you specify the FILE= parameter to ORAPWD, use only the data set name of the VSAM LDS. Do not include apostrophes or any "//" prefix. The z/OS userid that is associated with the batch job or 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.

//*----------------------------------------------------------------*
//*                                                                *
//*  JOB DESCRIPTION: Define / create ORAPWD file                  *
//*                                                                *
//*----------------------------------------------------------------*
//*
//DEFINE  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
 DELETE ORACLE.ORA1.ORAPWD
 DEFINE CLUSTER (NAME(ORACLE.ORA1.ORAPWD) LINEAR -
  RECORDS(16))
/*
//ORAPWD  EXEC PGM=ORAPWD,
//  PARM='file=ORACLE.ORA1.ORAPWD password=manager entries=32'
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//ORA@ORA1 DD DUMMY
//SYSIN    DD DUMMY
//*

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

7.4 Offline Database Verification Utility (DBV) on z/OS

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 Oracle Database Backup and Recovery Book Set.

DBV on z/OS supports batch job, TSO, and POSIX shell execution environments. No JCL procedure is supplied but one can easily be created if desired. In TSO, both CALL and command processor (CP) invocation are supported. For the non-POSIX environments, the load module or program object name is DBV. In a POSIX shell (including OMVS under TSO), use the dbv command (in lower case) to invoke this utility.

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 can specify a DD name, as shown in the following example, or a data set name.

//*----------------------------------------------------------------*
//*                                                                *
//*                 ORACLE DBVERIFY BATCH PROCESSOR                *
//*                                                                *
//*----------------------------------------------------------------*
//*
//ORADBV   EXEC PGM=DBV,
//   PARM='/DD/DBFILE START=1 END=50'
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//DBFILE   DD  DISP=SHR,DSN=ORACLE.ORA1.SYSTEM.BKUP.DBF1
//SYSIN    DD DUMMY

On z/OS, 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.