3 Configuring a Database Service and Creating a New Database

3.1 Overview

To create an Oracle database instance under OSDI, you must first define the instance as a service using the OSDI DEFINE SERVICE command. In addition to defining the service, some other items must be set up before the service can be started: a JCL procedure, several parameter files, and possibly security resource definitions.

After you have defined the instance as a service and set up the additional items, you can start the service, which creates one or more z/OS address spaces based on controls that you have specified. After the address spaces are initialized, you must run Oracle SQL*Plus (or a similar tool) to perform the Oracle database startup function. When the startup is complete, you can use the same tool to issue the CREATE DATABASE SQL statement. This statement causes the Oracle server to create the VSAM linear data sets that comprise a database (if you chose not to pre-allocate them) and to initialize their contents. After the database is created, a series of SQL scripts is executed to create the Oracle server's internal database objects (tables, views, stored PL/SQL procedures, and so forth). After the execution of the scripts is complete, your database is ready to use.

A description of the configuration process is included in this chapter.

3.2 Database Service Definition

The OSDI DEFINE SERVICE command is described completely in Appendix A, "OSDI Subsystem Command Reference". Here, we cover DEFINE parameter considerations that are specific to an Oracle database service.

Service Name

The service name for a database can be anything that you want within the content limitations described in Appendix A. By default, OSDI will use the service name as the SID for the service. (The SID is an identifier that end users or application developers must supply to connect an application to a particular database.) The SID can be specified separately, however, and is not required to be the same as the service name.

Although OSDI permits service names up to eight characters long, the name you use for a database service should be seven characters or less due to a length limitation on what is stored in the database control file. The OSDI service name appears in the Oracle data dictionary view V$INSTANCE in column INSTANCE_NAME.

TYPE

The TYPE parameter for a database service must be specified as ORA.

PROC

This parameter specifies the name of a service JCL procedure that you will place in one of your system procedure libraries. The procedure need not exist when DEFINE SERVICE is issued, but it must be in place before the service is started. The procedure name can be anything that you choose or that the naming standards of your installation require. The requirements for this procedure are discussed in section "Database Region JCL".

PARM

The PARM for a database service specifies the name of a z/OS data set containing service initialization parameters. These are z/OS-specific parameters (not the Oracle RDBMS init.ora file startup parameters) and are described in the section "Database Region Parameters". Typically, PARM will specify a member of a PDS (Partitioned Data Set) that is used for various Oracle parameter files. If no member name is included in the PARM string, then the specified data set must be sequential (DSORG=PS).

MAXAS

If you want to exploit the multiple-address-space server features of OSDI, then you should specify the MAXAS parameter on DEFINE SERVICE with a value greater than the default of 1. This sets the maximum number of address spaces for the service, which may be greater than the number started when the service is first brought up. (The number of address spaces to start initially is a database region parameter.) This parameter can be altered with OSDI commands as long as the database service is not active.

JOBNAME

When you run a database service with multiple address spaces, the JOBNAME parameter of DEFINE SERVICE can be used to cause each address space to have a distinct jobname. Although this is not required, it may be desirable if you use z/OS facilities (such as RMF) that distinguish address spaces by jobname. To do this, specify JOBNAME(name), where name is a one-character to five-character jobname prefix followed by an asterisk, as shown. As each address space is started, OSDI substitutes a three-digit address space counter for the asterisk (001, 002, and so on) to produce the final jobname. You can also use JOBNAME to cause the service to run with a jobname different from the service name (which is used by default).

As discussed in the Note in the preceding page, you must specify a JOBNAME parameter if the service name matches any existing subsystem name in your z/OS system.

SID

The SID parameter specifies a unique identifier for the service. It is a critical element in the process that is used by Oracle database applications to specify the instance to which they need to connect. (Inbound network clients specify a SID in the Oracle database network address string that must match the SID that is specified in DEFINE SERVICE. Local z/OS clients connecting via cross-memory specify the SID in any of several ways.) Although the SID can be up to eight characters long, you may want to specify a SID that is four characters or less in order to enable a z/OS-specific feature that local z/OS clients can use to specify a target database. This feature relies on a dummy JCL DD statement (or TSO allocation) whose DD name begins with "ORA@" and ends with a one-character to four-character SID of the target database instance. If you choose a SID longer than four characters (or allow it to default to a service name that is longer than four characters), this feature is not usable.

Although you can issue the OSDI DEFINE SERVICE command via a z/OS system console or similar facility, you should put definition commands for services that you use regularly into the OSDI subsystem parameter file, after the DEFINE SERVICEGROUP command. This ensures that the service is always defined correctly and automatically when the subsystem is initialized (normally at system IPL). In the following sample database DEFINE SERVICE command, the command prefix has been omitted and continuation hyphens have been included as though the command were in the subsystem parameter file:

INIT (ORASSI,ORSS)
DEF SVG SSID(ORSS) DESC('OSDI Oracle 10G Subsystem - ORSS')

DEF SRV ORAS10 PROC(ORA1S10) TYPE(ORA) MAXAS(1) -
 DESC('Oracle V10G RDBMS Service') -
 SID(ORA1) PARM('ORACLE.ORA1.PARMLIB(ORSSPARM)')

DEF SRV ORAN10 PROC(ORA1N10) TYPE(NET) -
 DESC('Oracle V10G Net Service') -
 SID(ORAN) PARM('HPNS PORT(1501) ENCLAVE(SESS)')

SHOW SERVICEGROUP LONG

START  ORAS10
START  ORAN10

3.3 Database Region JCL

Defining a database service requires you to specify a JCL procedure name in a system procedure library. You must create the procedure before you try to start the service, and the procedure must invoke the OSDI database region program with an EXEC statement such as the following:

// EXEC PGM=ORARASC,REGION=0M

REGION=0M is specified to ensure that the server can allocate as much private virtual memory as it needs. Some z/OS systems may prohibit or alter a REGION parameter such as this, so you might want to check with your systems programmer to make sure that the system will accept your REGION parameter.

A z/OS exit called IEFUSI might be installed on your system. The IEFUSI exit prevents started tasks or batch jobs from getting the maximum region size when REGION=0M is specified. If an IEFUSI exit is implemented, it is specified in the SMFPRMxx member of SYS1.PARMLIB that is used during z/OS initialization. To effectively run the Oracle database with an IEFUSI exit installed, ensure that the exit is coded to allow batch jobs or started tasks with the names of your Oracle regions to allocate a large amount of virtual memory above the 16 MB line.

Because Oracle allocates only the amount of memory it needs, you can safely allow Oracle to allocate any amount of memory up to the two gigabyte limit per address space that is imposed by 31-bit addressing conventions.

Note that no other EXEC statement parameters are needed. The PARM parameter of EXEC is not used by the database region program.

Changing a service's JCL procedure after starting one or more address spaces for the service, and then starting another address space (to use the changed JCL), is not supported.

In addition to the EXEC statement, the procedure will need several DD statements, as follows:

ORA$ENV: This DD statement is optional. When used, it specifies a sequential file or PDS member containing environment variable assignment statements. Environment variables are used to supply operating parameters to certain Oracle database product features. Reliance on environment variables and considerations for setting them are discussed in feature-specific chapters of this manual as well as in the Oracle Database User's Guide for IBM z/OS (OS/390). The data specified by ORA$ENV is read only at database service startup. Therefore, in order for changes to the data set to take effect, the service must be stopped and started.

Be aware that the global environment variable file is not read by the Oracle database server. All environment variable settings for the server must be supplied through ORA$ENV. For more information on the global environment variable file, refer to Chapter 7, "Oracle Database Administration Utilities".

ORA$FPS: This DD statement specifies a sequential file or PDS member containing z/OS-specific parameters that control data set processing in the Oracle server. These parameters are organized by type of file (such as tablespace, control, online log, and so forth), and they primarily pertain to creation processing when the Oracle server invokes the IDCAMS utility or dynamic allocation to create a z/OS data set. Considerations and syntax rules for the ORA$FPS parameter file are covered in "Server File Management Parameters". The ORA$FPS DD is optional. If you omit it, then server file creation operations may fail unless your installation has DF/SMS ACS routines that supply defaults for data set creation parameters. At database service startup, data specified by ORA$FPS is read and checked. Any errors are reported and ignored. Valid entries are loaded as server file management parameters. After database service startup, a new set of server file management parameters can be loaded from the updated ORA$FPS specification by using the REFRESH FPS command, as described in Chapter 5, "Operating a Database Service".

ORA$LIB: This DD statement specifies a non-authorized load library from which non-executable (data) modules are fetched. The modules contain NLS data objects and messages that are associated with Oracle NLS internationalization features. Normally these modules are installed in the OSDI MESG data set, for example ORACLE.V10G.MESG. The ORA$LIB DD statement is optional: if you omit it, then the Oracle server attempts to fetch messages and NLS data objects modules from STEPLIB. Do not concatenate a non-APF-authorized MESG data set to STEPLIB in lieu of specifying ORA$LIB.

ORAPASSW:  This DD statement is optional. It specifies a VSAM linear data set that has been initialized with the ORAPWD utility. This file contains encrypted passwords and is used only to authenticate a client who is connecting as SYSDBA or SYSOPER. The use of this file is described in "Security Considerations" in the section "Controlling Access to Database SYSDBA and SYSOPER Privileges" and the ORAPWD utility is discussed in Chapter 7, "Oracle Database Administration Utilities".

SNAPCF: This DD statement is optional. When used, it specifies a VSAM linear data set that contains a copy of the database control file. The considerations for this file are discussed in Chapter 6, "Database Backup and Recovery".

SQLBSQ: This DD statement specifies an input file containing the Oracle database "bootstrap" SQL script. It is read only during an Oracle database cold start (CREATE DATABASE SQL statement) and is therefore required only when a cold start is planned. When specified, it usually designates the SQLBSQ member of a partitioned data set dedicated to SQL scripts. This data set was created during Oracle product installation.

SQLNET: This DD statement specifies an input file containing Oracle Net parameters. It is required if the Oracle instance uses any of the following:

• Network data encryption

• Network activity tracing

• Altering of default Oracle Net file names

• Outbound database links whose Oracle Net addressing requires access to an Oracle database Names server

Refer to Chapter 8, "Oracle Net", for additional information.

STEPLIB: This DD statement must specify the APF-authorized Oracle AUTHLOAD library that was populated during installation. The IBM LE/370 runtime library must be concatenated to it unless your installation has put LE/370 runtime into the system linklist. A typical name for the LE/370 runtime library is SYS1.SCEERUN, but it may have a different name in your system.

SYSPRINT: This DD statement is optional. When used, the Oracle database instance alert log is written to it. The alert log is a sequential text file containing status messages that are related to the operation of the database instance, including startup and shutdown information, log file switches, archive operations, and certain types of error condition. The alert log is also used to log some z/OS-specific events, including IDCAMS utility output associated with database file creation and deletion. Regardless of the number of server address spaces, an Oracle database instance has only one alert log, which is opened by the first server address space (AS1). Alerts that are generated by sessions in other address spaces are routed to AS1.

You can specify a sequential (DSORG=PS) disk data set or a spool file (SYSOUT) for this DD. If you omit the SYSPRINT DD, the alert log is dynamically allocated as a disk data set or spool file according to the ALERT_DSNAME region parameter, discussed in "Database Region Parameters" in Chapter 3, "Configuring a Database Service and Creating a New Database".

If you specify a disk data set for SYSPRINT and an error occurs while it is being written (including an out of space condition), an alert log switchoccurs. Refer to Chapter 5, "Operating a Database Service" Managing the Alert Log for additional information on Oracle alert log switching.

TNSNAMES: This DD statement specifies an input sequential file or PDS member containing Oracle Net name/address assignments. It is required if the Oracle instance uses database links (connections to other Oracle database instances) whose USING clause specifies an Oracle Net service name rather than an explicit Oracle Net address. If you are using external routines or shared servers, refer to "Step 2: Edit the Server Sqlnet.ora File" in Chapter 8, "Oracle Net" to add the correct entries.

Sample Database Region JCL Procedure The following is an example of a JCL procedure for a database region:

//ORA1S10 PROC
//*----------------------------------------------------------------*
//*--   ORACLE DATABASE SERVICE PROCEDURE                        --*
//*--                                                            --*
//*----------------------------------------------------------------*
//IEFPROC  EXEC PGM=ORARASC,REGION=0M
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.AUTHLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//*
//ORA$FPS  DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1FPS)
//ORA$ENV  DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1ENV)
//TNSNAMES DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(TNSNAMES)
//TNS@ORAN DD DUMMY

3.4 Database Region Parameters

OSDI database region parameters are supplied in a data set whose name is specified as the PARM string in the service definition. This will typically be a member of a PDS. Because the data set name is supplied via the service PARM mechanism, no DD statement is coded in the region JCL. The data set is dynamically allocated, opened, and read when the service is started. Changing parameters in the data set has no effect until the service is stopped and restarted.

Region parameters are read independently by each address space of a multi-address space server. Adding, removing, or changing parameters between the starting of one address space and later starting of another is not supported.

The OSDI database region parameters consist of a parameter name followed by the parameter value in parentheses. Each parameter has a long descriptive name and a shorter name of eight characters or less. Each record may contain only one parameter. No continuation is allowed. Records beginning with an asterisk (*) are treated as comments and are ignored. Embedded spaces and all characters after the closing parenthesis are ignored.

ALERT_DSNAME | ADSN

ALERT_DSNAME specifies a filespec for the Oracle database alert log for the purpose of alert log switching. The format for this parameter is as follows:

ALERT_DSNAME ( filespec )

The filespec value can be a SYSOUT type or a data set name type with embedded system symbols that will guarantee a unique data set name on each use. Using date and time system symbols is recommended in a data set name filespec.

Examples:

ALERT_DSNAME(ORACLE.&ORASRVN..ALERT.D&LDATE..T&LTIME)
  ADSN(//S:Z,,DBOPS01)

If you omit this parameter, alert log switches use a default SYSOUT specification //SYSOUT:*. For more information on alert log switching, refer to "Managing the Alert Log" in Chapter 5, "Operating a Database Service".

ALERT_MAX | AMAX

ALERT_MAX specifies:

ALERT_MAX ( size )

The size value is the number of data bytes (sum of logical record lengths) written to the alert log. This value can be specified as a number, n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576). The writing of an alert log record that would exceed this size causes an automatic alert log switch before the new record is written. This happens without regard for the sequence or interrelationship of alert log messages; for example, the switch can occur between a pair of related messages.

The default value is 0. If you specify 0, no automatic switching is done. Specifying a value less than 65536 (64 KB) is not recommended for this parameter.

ALERT_MIN | AMIN

ALERT_MIN specifies:

ALERT_MIN ( size )

The size value is the number of data bytes (sum of logical record lengths) written to the alert log. This value can be specified as n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576). If an alert log switch is requested (for example, using a LOGSWITCH service command without the FORCE option), the request is honored only if the size of the current alert log exceeds this value.

The default value is 0. If you specify 0, no minimum size checking is done, and all alert log switch requests are carried out.

CLUSTER_ENABLE | CLUSTER

The CLUSTER_ENABLE parameter specifies whether to enable or disable the Oracle Real Application Clusters option in the Oracle kernel. The format is as follows:

CLUSTER_ENABLE ( YES | NO )

The default value is NO. Setting CLUSTER_ENABLE to YES causes the ORARASC module to activate a subtask that handles the IBM/XCF group management calls required by Oracle Real Application Clusters.

DSN_PREFIX_DB | ORAPREFD

The DSN_PREFIX_DB parameter supplies a constant string that is associated with the &ORAPREFD system symbol. The &ORAPREFD system symbol can be used to form the high-level (leftmost) qualifier of z/OS data set names generated by the Oracle server. The format is as follows:

DSN_PREFIX_DB ( dsn_prefix )

The dsn_prefix value is a valid one-character to eight-character data set name qualifier that conforms to your installation's requirements. In most cases, this will be the qualifier that is used for all Oracle database files associated with this instance. For example:

DSN_PREFIX_DB(ORADB01)

DSN_PREFIX_DB has no default value. If you omit this parameter, certain situations in which the Oracle server generates "default" filenames will produce errors. Refer to Chapter 4, "Defining z/OS Data Sets for the Oracle Database", for more information.

IDLE_TIMEOUT | ITIMEOUT

The IDLE_TIMEOU parameter parameter sets a timeout value for idle sessions. Sessions that are idle for a period longer than the interval set are terminated and all resources released. The format is as follows:

IDLE_TIMEOUT ( time_interval )

The time_interval value is the timeout value specified as nnn or nnnS for seconds or nnnM for minutes. The default is no timeout value. The maximum value is 604,800 seconds or one week. The timeout value set is a minimum approximation and a session may be idle for some additional seconds or minutes before it is terminated. When a session is using a dedicated TCB, as is the case with the Transparent Gateway products, the task is terminated with an S222 completion code. Clients with connections to timed-out sessions may see a variety of errors if they attempt to continue.

INIT_ADR_SPACES | INTADSPC

INIT_ADR_SPACES controls how many auxiliary address spaces are started. The format is as follows:

INIT_ADR_SPACES ( number_of_address_spaces )

The number_of_address_spaces value is the number of address spaces to start. The default is 1, which starts only the control address space (AS1). The maximum is the number that was specified for MAXAS on the associated DEFINE SERVICE command for the database service.

INIT_STACK_SIZE | INTSTKSZ

INIT_STACK_SIZE controls the size of the C stack that is allocated for each session. The format is as follows:

INIT_STACK_SIZE ( init_size )

The init_size value determines the initial size of the C stack. This value can be specified as n or nK. The default is 128K. For more information on INIT_STACK_SIZE, refer to "User Stack Area in z/OS" in Chapter 15, "Oracle Database Performance".

If the RDBMS Java system will be initialized, and if Java stored procedures will be used, then the value of init_size should be at least 256K.

LOGON_AUTH | LGNAUTH

LOGON_AUTH specifies how the Oracle server interacts with a SAF-based external security product when processing Oracle logons for users defined as IDENTIFIED EXTERNALLY. The format is as follows:

LOGON_AUTH ( auth )

The auth value can be specified as follows:

If exitname is specified, then it must reside in the system linklist, or in an APF-authorized library that is part of the server region STEPLIB concatenation. The default is NONE.

Examples:

LOGON_AUTH(NONE) 
LOGON_AUTH(RACFSMPO)

For more information about Oracle logon authorization, refer to Chapter 9, "Security Considerations".

MAX_SESSION_MEM | MAXSMEM

The MAX_SESSION_MEM parameter specifies a hard limit on the amount of virtual memory that a single database session can allocate. The format is as follows:

MAX_SESSION_MEM ( session_memory )

The session_memory value is the maximum amount of virtual memory that a single database session can allocate. This value can be specified as n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576). The default is zero (0), which means no session limit is imposed.

This parameter is useful for stopping a "runaway" session that is allocating excessive amounts of memory due, perhaps, to problems with application design. This pertains only to session-private C stack and "heap" memory allocated during Oracle server processing. It does not include SGA (System Global Area) memory used by a session nor internal memory allocations done by the implementation.

Care must be taken in choosing a limit, particularly where certain database administration operations might be affected. The "catalog build" step of new database creation requires as much as 96 MB of session memory and may fail if this parameter is set to a lower value. Omit this parameter or set it to a higher value during new database creation; you can change it to a lower value afterward if desired. In the current product release, a normal database startup requires up to 16 MB of session memory, so do not set this parameter to a value less than 16MB.

MAX_SESSIONS | MAXSESS

The MAX_SESSIONS parameter limits the number of sessions that can be scheduled in an address space. The format is as follows:

MAX_SESSIONS ( number_of_sessions )

The number_of_sessions value is the maximum number of sessions per address space. This value can be specified as n or nK. The default is 1024. The number of sessions that can be supported in an address space depends on the complexity of the work. Limiting the number of sessions per address space reduces the chances of session failure due to exhaustion of virtual storage. Refer to "Database Server Address Space Configuration" in Chapter 15, "Oracle Database Performance" for more information.

REGION_MEM_RESERVE | REGMRES

The REGION_MEM_RESERVE parameter specifies the amount of private area memory in the server address space to be "reserved" for implementation and z/OS use (not available for the SGA and Oracle session-private purposes). The format is as follows:

REGION_MEM_RESERVE ( region_memory )

The region_memory value is the amount of private area memory reserved. This value can be specified as n, nK (denoting a multiplier of 1024), or nM (denoting a multiplier of 1,048,576).

During initialization, each server address space calculates the total available private area memory and subtracts the reserve amount from it. The result is the aggregate limit for the SGA and for all session memory requests in that address space.

The default is zero (0), which means that no aggregate limit applies. In this case, it is possible for SGA and session memory requests to exhaust the available private area of the address space, leading to unpredictable failures.

Thus, the reserve amount must be sufficient to accommodate internal implementation memory requrements as well as memory required by z/OS services used by Oracle, particularly Local System Queue Area (LSQA) memory, which is used by all database I/O operations. Because it is difficult to predict this amount for any given workload, the best strategy is to specify a relatively large reserve amount, such as 50M or more. This has the effect of reducing slightly the number of sessions that can be accommodated in a server address space. However, additional address spaces can be started, if necessary.

SERVER_LOADMOD | SRVRLMOD

SERVER_LOADMOD specifies the name of the service load module. The format is as follows:

SERVER_LOADMOD ( loadmod )

The loadmod value is the name of the load module to load. For the Oracle RDBMS, this is usually ORACLE. This parameter is required.

SMF_STAT_RECNO | SMFSTRCN

SMF_STAT_RECNO specifies the SMF record number to use. The format is as follows:

SMF_STAT_RECNO ( record_number )

The record_number value is the number of the desired record of Oracle SMF statistics. The default is zero (0). Otherwise, the value must be specified between 128 and 255 for this parameter. Example:

SMF_STAT_RECNO(204)

The collection and writing of Oracle SMF statistics records is controlled by this single parameter in the OSDI service parameter file. A zero (0) for this parameter indicates that no SMF statistics record is to be written. The SMF record number that is chosen must not be the same as the number that is used by any other z/OS software.

If this parameter is not specified, or if zero is specified, then no SMF statistics collection or recording is done. This saves some CPU overhead and saves the overhead of the SMF write itself (which is mostly asynchronous work done by the SMF address space, the in-line overhead is mainly just moving data into SMF buffers). For more information about SMF, refer to Chapter 10, "Oracle SMF Data".

TRACE_DSNAME | TDSN

TRACE_DSNAME specifies the destination for Oracle RDBMS trace files. This includes normal traces requested by setting the session SQL_TRACE option to TRUE, as well as diagnostic traces generated automatically in certain error situations. The format is as follows:

TRACE_DSNAME ( filespec )

The filespec value is either a SYSOUT specification (including class, form, and JES destination) or a data set name.

A SYSOUT specification is of the form:

//SYSOUT:class,form,dest

as described in "Server File Name Syntax" in Chapter 4. When this is used, trace files are dynamically allocated SYSOUT data sets. In a multi-address space service, the trace file for a given database session is allocated in the address space that hosts the session. Thus, SYSOUT trace files can appear in all server address spaces. For example, traces written to SYSOUT class X, form AA01, would be written as:

TRACE_DSNAME(//SYSOUT:X,AA01)

As an alternative to a SYSOUT specification, you can specify a data set name. Because each trace file created as a data set must have a unique data set name, the supplied value must include system symbols that guarantee uniqueness. Refer to Appendix C, "Oracle Database for z/OS System Symbols" for more information.

To guarantee uniqueness, use some combination of the session identifier (&ORASESST) system symbol, date (&LYYMMDD), and time (&LHHMMSS). Also use high-level qualifier(s) that are appropriate for your installation. This will avoid the possibility of duplicating trace data set names generated in other Oracle instances you run. All components of the string must resolve to produce a name that is valid for a z/OS sequential data set. For example:

TRACE_DSNAME(ORA3A.TRACE.D&LYYMMDD..T&LHHMMSS..&ORASESST)

The allocation parameters for Oracle trace data sets are obtained from the DBTR file group of the server file management parameters, discussed in Chapter 4, "Defining z/OS Data Sets for the Oracle Database".

If this parameter is omitted or fails to produce a valid, unique data set name, all Oracle trace files are written to the default SYSOUT class associated with the server region.

3.5 Oracle Initialization Parameter Considerations

Oracle server initialization parameters, or init.ora file parameters, can be supplied in several ways. The oldest mechanism, still supported for backward compatibility, is to use parameters in a text file that is read by the utility doing the startup (usually SQL*Plus, but possibly RMAN or others).

This occurs when you issue "STARTUP PFILE=filespec...". The filespec you supply on this command can be a sequential data set, a PDS member, an instream (DD *) data set, or a file in the POSIX HFS; the z/OS userid that is running the utility must be authorized to open it.

If you omit PFILE= from the STARTUP command, no file is opened in the utility session, and STARTUP processing in the Oracle database server address space attempts to open a parameter file. The server first tries to open an SPFILE (server parameter file), which is a database-type file, a VSAM linear data set (LDS), containing current parameter settings. You can create an SPFILE using the CREATE SPFILE SQL statement. One advantage to using an SPFILE is that the server can update it when you change parameters with ALTER SYSTEM, for example, saving you from having to remember to make such changes to a text PFILE. For more information on creating an SPFILE on z/OS, refer to the section "SPFILE".

If the server cannot locate or open an SPFILE, it attempts to open a regular PFILE instead. The default filespec for this file on z/OS is //DD:INITORA, so in order to read a PFILE the server region JCL must contain an INITORA DD statement. A PFILE read by the server must be a sequential data set or a member of a partitioned data set. The z/OS userid associated with the server address space must be authorized to read this data set.

Considerations for most of the initialization parameters in this file are the same regardless of the operating system on which the associated Oracle database instance runs. However, some have z/OS-specific considerations, discussed in this section.

Use this section together with the other z/OS-specific documentation when choosing initialization parameters for an Oracle server on z/OS.

For a complete list of initialization parameters with z/OS- specific defaults or limits, refer to Table B-1, "Initialization Parameters with z/OS-Specific Defaults or Limits" inAppendix B, "Operating System Dependent Variables".

AUDIT_FILE_DEST

The AUDIT_FILE_DEST parameter specifies the desired SMF record type to which audit records will be written when the AUDIT_TRAIL initialization parameter is set to OS. For example, specifying AUDIT_FILE_DEST=205 causes Oracle Database for z/OS audit records to be written to SMF record type 205. The default for this parameter on z/OS is 0, which means no SMF records will be written.

When specifying a record type, you must select a user SMF record type that does not conflict with any other user record types. For more information, refer to Chapter 10, "Oracle SMF Data".

AUDIT_TRAIL

The AUDIT_TRAIL parameter enables or disables database auditing. Setting AUDIT_TRAIL=OS is required to inform Oracle Database for z/OS that operating system auditing is desired.

CONTROL_FILES

The CONTROL_FILES parameter specifies the name, or names, of one or more database control files that are specified using the file name syntax discussed in Chapter 4, "Defining z/OS Data Sets for the Oracle Database". When you are first creating a database, if you choose to let the Oracle server allocate your control files (instead of pre-allocating them yourself), this parameter will specify the VSAM linear data set names that do not yet exist but will be created during processing of the CREATE DATABASE command.

The following is a sample CONTROL_FILES parameter for z/OS using the full file name syntax:

CONTROL_FILES = "//'ORAPROD.ORADB1.CTL1'", "//'ORAPROD.ORADB1.CTL2'"

DB_BLOCK_SIZE

The DB_BLOCK_SIZE parameter is used as the default logical database blocksize for all tablespaces that do not request a different logical blocksize with the BLOCKSIZE option of CREATE TABLESPACE. DB_BLOCK_SIZE can be specified as 4096 (4 KB), 8192 (8 KB), 16384 (16 KB), or 32768 (32 KB).

The default for this parameter on z/OS is 4096. Regardless of what logical blocksize you use, Oracle database files on z/OS always have a physical blocksize of 4096 bytes. For more information on database logical blocksize, refer to the section "Pre-Allocating Database Files".

DB_CREATE_FILE_DEST; DB_CREATE_ONLINE_LOG_DEST_n

These parameters are associated with Oracle Managed Files (OMF). OMF simplifies database administration by making the Oracle server responsible for naming, creating, and deleting the VSAM linear data sets comprising a database. Refer to "Oracle Database Files" in Chapter 4, "Defining z/OS Data Sets for the Oracle Database" and the Oracle Database Administrator's Guide for more information on OMF.

On z/OS, these parameters supply character strings that are used as the left-hand portion of the data set names generated by the server when an OMF file is created. Valid z/OS and Oracle-specific system symbols can be included. The maximum length permitted after any system symbols are resolved is 23 characters for DB_CREATE_FILE_DEST and 29 characters for DB_CREATE_ONLINE_LOG_DEST_n. To be usable for z/OS data set name generation these strings must end with a period after any symbol substitution has been done. The following are some examples:

DB_CREATE_FILE_DEST = "ORACLE.ORADB01."
DB_CREATE_ONLINE_LOG_DEST_1 = "&ORAPREFD..&ORASRVN..M1."
DB_CREATE_ONLINE_LOG_DEST_2 = "&ORAPREFD..&ORASRVN..M2."

DB_FILE_NAME_CONVERT; LOG_FILE_NAME_CONVERT

These parameters are used in conjunction with the standby database availability feature and in certain point-in-time recovery situations. They cause the server to convert database and log file names that are read from the control file by replacing a portion of the original file name with another value.

To use the standby feature and the DB_FILE_NAME_CONVERT parameters, your database data file data set names must share a common naming convention. The easiest way to meet this requirement is to use the same high-level qualifier (or qualifiers) for all database data file names. The same logic applies to the LOG_FILE_NAME_CONVERT for the redo log files.

For example, if all of your primary database data files have data set names beginning with "ORA5.DBPRIM." and you choose "ORA5.DBSTNDBY." as the prefix for database files in your standby database, then to effect the name conversion for the standby database, you would specify:

DB_FILE_NAME_CONVERT=(ORA5.DBPRIM,ORA5.DBSTNDBY)

Information on the standby database feature can be found in Oracle Data Guard Concepts and Administration.

LOCK_SGA

The LOCK_SGA parameter is ignored on Oracle Database for z/OS. Buffers in the SGA are pagefixed during I/O operations, only; otherwise, the SGA on z/OS is pageable.

LOG_ARCHIVE_

The parameters whose names begin with "LOG_ARCHIVE_" control Oracle database processing of filled database log files when the database runs in ARCHIVELOG mode. These parameters changed in Oracle 8.1 to allow more than two copies of archived logs and to support transmitting logs to a remote standby database. The old parameter forms are still supported in Oracle Database 10g, but the new forms must be used to allow for more than two log destinations or to use remote standby logging.

With Oracle Database for z/OS, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST (the old parameters) or the LOCATION component of each LOG_ARCHIVE_DEST_n parameter are used to specify only the left-hand portion of a full z/OS data set name. This will be the high-level data set name qualifier, or qualifiers, for all logs archived to this destination. The remainder of the data set name is specified using LOG_ARCHIVE_FORMAT, which should contain the log thread and sequence number substitution indicators (%T and %S) to ensure that each archived log data set name is unique. The destination string must end with a period, or the format string must begin with one in order to form a proper z/OS data set name. The following example uses the newer parameters:

LOG_ARCHIVE_FORMAT = "T%T.S%S.R%R"
LOG_ARCHIVE_MIN_SUCCEED_DEST=1
LOG_ARCHIVE_DEST_1='LOCATION=ORA5.ARCHLOG1. MANDATORY REOPEN=5'
LOG_ARCHIVE_DEST_2='LOCATION=ORA5.ARCHLOG2. MANDATORY REOPEN=5'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable

With these example settings, archived logs will have data set names of the form ORA5.ARCHLOG1.Tnnn.Snnn.Rnnn and ORA5.ARCHLOG2.Tnnn.Snnn.Rnnn with the nnn parts replaced by log thread, sequence numbers, and reset logs ID, respectively.

When archiving to a standby database, use the SERVICE keyword to specify a valid net service name from the tnsnames.ora file, as in the following example:

LOG_ARCHIVE_DEST_4='SERVICE=standby1'
LOG_ARCHIVE_DEST_STATE_4=enable

The specified service name must use PROTOCOL=TCP.

SGA_MAX_SIZE

This parameter should not be specified on Oracle Database for z/OS. It will default to the actual SGA size. An incorrect value will cause the database to fail to start.

SPFILE

The SPFILE (server parameter file) parameter refers to the VSAM linear data set managed by the instance. The default name of the SPFILE data set is the following concatenation: &ORAPREFD..&ORASRVN..SPFILE.ORA.

When creating an SPFILE, the text parameter file you specify with the PFILE parameter is read by the database server, not by the program that is issuing the CREATE statement (for example, SQL*Plus). The filespec you give for the PFILE can be a sequential data set, a PDS member, a DD filespec, or the path and name of a file in the POSIX HFS. If you use a DD filespec, the server region JCL must contain the appropriate DD statement and it must specify a sequential data set or a member of a PDS. If you are using an HFS file, the filespec must be unambiguous in order for the server to recognize it as an HFS file. Refer to the Oracle Database User's Guide for IBM z/OS (OS/390) for information on ambiguous and unambiguous filespecs.

STANDBY_ARCHIVE_DEST

This parameter is used in conjunction with the standby database availability feature to perform database recovery. It is used along with the LOG_ARCHIVE_FORMAT parameter to generate the fully qualified standby database log filenames, which are then stored in the standby database control file. For more information on the standby database availability feature, refer to Oracle Data Guard Concepts and Administration.

With Oracle Database for z/OS, the STANDBY_ARCHIVE_DEST parameter is used to specify only the left-hand portion of a full z/OS data set name. This will be the high-level data set name qualifier, or qualifiers, for all logs archived to this destination. The remainder of the data set name is specified using LOG_ARCHIVE_FORMAT, which should contain the log thread, sequence number, and reset logs ID substitution indicators (%T, %S, and %R) to ensure that each archived log data set name is unique. The destination string must end with a period, or the format string must begin with one in order to form a proper z/OS data set name. The following is an example:

LOG_ARCHIVE_FORMAT = "T%T.S%S.R%R"
STANDBY_ARCHIVE_DEST='ORA5.ARCHLOG.'

TRACEFILE_IDENTIFIER

This parameter is not used to form part of the trace data set name, but with ALTER SESSION, will cause a new trace data set to be allocated.

3.6 Pre-Allocating Database Files

You can allow the Oracle server to create the database VSAM clusters during CREATE DATABASE processing, or you can pre-allocate them yourself using z/OS IDCAMS. If you choose to use z/OS IDCAMS, now is the time to do it. Considerations for defining database files are covered in Chapter 4, "Defining z/OS Data Sets for the Oracle Database". If you are going to let the server create any files, then be sure to provide creation parameters for the associated file types via the server ORA$FPS DD statement as described in Chapter 4, "Defining z/OS Data Sets for the Oracle Database".

3.7 Configuring z/OS Security

If you plan to use any of the SAF-based security features discussed in Chapter 9, "Security Considerations", you may want to configure them now. OSDI subsystem command security affects the service START command that you will issue to start the database service address spaces. Both OSDI bind security and protection of the database SYSDBA or SYSOPER privilege affect the connection that you will make to startup Oracle Database for z/OS and create the database.

Even if you do not utilize any of these features, you must ensure that the database service address space is authorized to access resources (such as data sets) that might be protected by default in your system. Refer to Chapter 9 for information on database service interaction with z/OS security.

3.8 Configuring for Shared Servers

Shared servers on Oracle Database for z/OS are supported by the generic listener running in a UNIX System Services shell environment. For information on configuring the generic listener for shared servers, refer to Chapter 8, "Oracle Net". For more information on shared servers, refer to the Oracle Net Services Administrator's Guide.

3.9 Creating the Database

When the OSDI service has been defined, all required JCL procedures and parameter files have been prepared, and any database files that you want pre-allocated have been defined, then you are ready to create the Oracle database.

First, start the OSDI service with an OSDI START command. This is described in Chapter 5, "Operating a Database Service", and in Appendix A, "OSDI Subsystem Command Reference". Check the z/OS system log to make sure that the service starts successfully, as indicated by message MIR0002I. If problems occur, the service address space(s) will terminate. In this case, correct the problems and issue another OSDI START command.

After the database service is started, you can use SQL*Plus or a similar tool to perform Oracle startup and database creation. z/OS considerations for running SQL*Plus are covered in the Oracle Database User's Guide for IBM z/OS (OS/390).

Before issuing STARTUP or CREATE DATABASE, the tool must connect to the running database service. The form of CONNECT command that is used when starting the database is special and does not specify an Oracle userid:

CONNECT / AS SYSDBA

This command connects the tool as Oracle userid SYS. If you configured SAF-based OSDI bind authorization as discussed in Chapter 9, "Security Considerations", the z/OS userid associated with the SQL*Plus session must be authorized for the UBIND resource associated with the database service. If you configured SAF-based protection of the SYSDBA and SYSOPER privileges as discussed in Chapter 9, "Security Considerations", the userid must also be authorized for the DBA or OPER resource associated with the database service.

When SQL*Plus processes the CONNECT, it must determine the target database service. On z/OS, you can specify the target service in several ways, all based on the SID that is specified in DEFINE SERVICE. These methods of specification are described in detail in the Oracle Database User's Guide for IBM z/OS (OS/390). In our examples, we have used the dummy ORA@sid DD statement to specify the target service.

If CONNECT fails, the subsequent STARTUP and CREATE statements will fail as well. This does not affect the running service. You can simply correct the CONNECT problem and try again.

After a successful CONNECT, you are ready to issue STARTUP with the NOMOUNT option, which causes the server instance to initialize without trying to mount and open a database. Before doing so, you have the option to create an SPFILE (a server parameter file), which is a VSAM LDS containing a binary representation of your Oracle database instance init.ora file parameters. If you do not create the SPFILE, you will need to supply the PFILE parameter on your STARTUP command, specifying the text file containing your instance parameters. For more information on SPFILE and PFILE processing, refer to the section"Oracle Initialization Parameter Considerations" and to the Oracle Database Administrator's Guide and Oracle Database Reference.

If STARTUP fails (because of an error in your init.ora file, for example), the subsequent CREATE will fail as well. In addition to any messages displayed by the tool, messages might be in the instance alert log (usually, though not necessarily, a SYSOUT file). In general, you can correct errors and retry the whole sequence without stopping and restarting the service.

After STARTUP is successful, you can issue CREATE DATABASE. This is a SQL statement and is therefore documented in Oracle Database SQL Reference. (CONNECT and STARTUP are commands, not SQL statements, and are described in the SQL*Plus tool documentation.) There are few z/OS-specific considerations for CREATE DATABASE. The syntax conventions for file names that are specified to the server are covered in Chapter 4, "Defining z/OS Data Sets for the Oracle Database".

If you pre-allocated any of the database files, be sure to specify REUSE in the appropriate clauses. For files that you want the server to create, omit REUSE and, except for the control files, specify SIZE. Oracle automatically calculates SIZE for the control files based on other parameters in CREATE DATABASE. With Oracle Database for z/OS, you can take advantage of the Oracle Managed Files feature and allow the server to generate names for data sets created during CREATE DATABASE. Pay careful attention to the CREATE DATABASE parameters whose names begin with "MAX", because these parameters specify limits that cannot be changed later without recreating the control file.

As CREATE DATABASE is processing in the server, it reads the Oracle bootstrap SQL file, usually referred to as sql.bsq. On z/OS, this file must be specified via a SQLBSQ DD statement in the service JCL procedure as described in "Database Region JCL" in Chapter 3, "Configuring a Database Service and Creating a New Database". This file is read only during processing of a CREATE DATABASE, and the DD statement can therefore be removed from the procedure if desired. It does no harm to leave it in, however.

After CREATE DATABASE, you must run Oracle-supplied SQL scripts to build the Oracle dictionary, stored PL/SQL procedures, and related structures. Although this can be done immediately after CREATE DATABASE (in the same tool session), we chose to run it separately in our examples. CREATE DATABASE is therefore the last statement in this part of the example.

Our example of z/OS database creation is presented as a batch job that uses SQL*Plus. We have pre-allocated four log files and a single file for the SYSTEM tablespace, but we are going to let the server create the control files (whose names are specified in the init.ora parameter file, which is supplied via the INITORA DD in this job). The SID of the target instance is ORA1. The /NOLOG in the SQL*Plus PARM prevents SQL*Plus prompting for an Oracle userid and password. The batch job is shown in the following example:

//*----------------------------------------------------------------*
//*                                                                *
//*  JOB DESCRIPTION: Create Oracle database                       *
//*                                                                *
//*----------------------------------------------------------------*
//*
//SQLPLUS  EXEC PGM=SQLPLUS,PARM='/NOLOG',REGION=0M
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//*  REQUIRES //ORA@SID  DD DUMMY STATEMENT (ORACLE INSTANCE).
//ORA@ORA1 DD DUMMY
//SQLBSQ   DD  DISP=SHR,DSN=ORACLE.V10G.SQL(SQLBSQ)
//ORA$ENV  DD  DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1ENV)
//SQLLOGIN DD DUMMY
//SYSIN    DD *
SET ECHO ON
CONNECT / as SYSDBA
@'//ORACLE.ORA1.PARMLIB(CREATE)'

The job may run for a while depending on the number and sizes of the files that are specified. Oracle formats all of the primary space for all control, log, and data files.

The PARMLIB CREATE member contains the following:

STARTUP PFILE='ORACLE.ORA1.PARMLIB(INITORA)' NOMOUNT
CREATE DATABASE ORA1
  MAXDATAFILES 255
  MAXLOGFILES  255
  CONTROLFILE REUSE
  LOGFILE 'ORACLE.ORA1.LOG1' REUSE,
          'ORACLE.ORA1.LOG2' REUSE
  DATAFILE 'ORACLE.ORA1.SYSTEM.DB1' REUSE,
           'ORACLE.ORA1.SYSTEM.DB2' REUSE
  SYSAUX DATAFILE 'ORACLE.ORA1.SYSAUX.DB1' REUSE
  UNDO TABLESPACE "UNDOTBS"
       DATAFILE 'ORACLE.ORA1.SYSTEM.UNDO.DB1',
                'ORACLE.ORA1.SYSTEM.UNDO.DB2'
  DEFAULT TABLESPACE USERS
          DATAFILE 'ORACLE.ORA1.USER.DB1',
                   'ORACLE.ORA1.USER.DB2'
  DEFAULT TEMPORARY TABLESPACE "TEMP"
          TEMPFILE 'ORACLE.ORA1.TEMP.DB1'
  CHARACTER SET "WE8EBCDIC1047";

//*----------------------------------------------------------------*
//*                                                                *
//*  JOB DESCRIPTION: run catalog and catproc                      *
//*                                                                *
//*----------------------------------------------------------------*
//*
//SQLPLUS  EXEC PGM=SQLPLUS,PARM='/NOLOG',REGION=0M
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//*  REQUIRES //ORA@SID  DD DUMMY STATEMENT (ORACLE INSTANCE).
//ORA@ORA1 DD DUMMY
//ORA$ENV  DD  DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1ENV)
//SQLLOGIN DD DUMMY
//SYSIN    DD *
SET ECHO ON
CONNECT / as SYSDBA
@/oracle/v10102/rdbms/admin/catalog.sql
@/oracle/v10102/rdbms/admin/catproc.sql
/*

//*----------------------------------------------------------------*
//*                                                                *
//*  JOB DESCRIPTION:  Initialize Java VM.                         *
//*                                                                *
//*----------------------------------------------------------------*
//*
//SQLPLUS  EXEC PGM=SQLPLUS,PARM='/NOLOG',REGION=0M
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//*  REQUIRES //ORA@SID  DD DUMMY STATEMENT (ORACLE INSTANCE).
//ORA@ORA1 DD DUMMY
//ORA$ENV  DD  DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1ENV)
//SQLLOGIN DD DUMMY
//SYSIN    DD *
SET ECHO ON
CONNECT / as SYSDBA
@/oracle/v10102/javavm/install/initjvm.sql
/*

//*----------------------------------------------------------------*
//*                                                                *
//*  JOB DESCRIPTION: Change database passwords                    *
//*  The passwords SECRET* need tailoring before running this job  *
//*----------------------------------------------------------------*
//*
//SQLPLUS  EXEC PGM=SQLPLUS,PARM='/NOLOG',REGION=0M
//STEPLIB  DD  DISP=SHR,DSN=ORACLE.V10G.CMDLOAD
//ORA$LIB  DD  DISP=SHR,DSN=ORACLE.V10G.MESG
//*  REQUIRES //ORA@SID  DD DUMMY STATEMENT (ORACLE INSTANCE).
//ORA@ORA1 DD DUMMY
//ORA$ENV  DD  DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1ENV)
//SQLLOGIN DD DUMMY
//SYSIN    DD *
SET ECHO ON
CONNECT / as SYSDBA
startup pfile='ORACLE.V10G.PARMLIB(INITORA)' mount
alter database open;
alter user sys identified by SECRET1;
alter user system identified by SECRET2;
alter user dbsnmp identified by SECRET3;

3.10 Creating an Oracle Database Instance

After installing Oracle Database for z/OS, you use the Oracle Universal Installer configuration utility to create one or more Oracle database instances. The configuration utility creates INSTLIB and PARMLIB libraries customized to your environment. Each database instance will have its own set of INSTLIB and PARMLIB libraries. These libraries contain the JCL procedures needed to create the database instance.

Before using the configuration utility, determine the following information for the database instance to be created:

• Subsystem name of the database

• Names of the JCL procedures for the subsystem

• Database SID

• High-level qualifier for all PDS and VSAM data sets

• Volumes on which PDS and VSAM data sets will reside

This information should be determined in advance, because it is used multiple times in creating the database members and can be more complicated to change later. For all other information, you can accept the defaults and change them manually, later.

The following steps provide guidelines for creating a database instance using the configuration utility. They assume that you have already installed Oracle Database for z/OS, performed the APF authorization, and put the necessary files in the linklist. If not, you will need to complete those tasks before continuing. For more information, refer to the Oracle Database Installation Guide for IBM z/OS (OS/390).

Step 1:  Run the Configuration Utility

Start Oracle Universal Installer and select the Oracle z/OS Database and Subsystem Configuration option. This starts the configuration utility which prompts for the following information:

• High-level qualifiers. Specify the high-level qualifiers for the the location of the Oracle executable code and the database. Although you can use one high-level qualifier for both, it is recommended that you use a separate high-level qualifer for each.

  The high-level qualifer for the location of the Oracle executable code (oracle_hlq). For this high-level qualifier, you should include the version information, for example ORACLE.V10G.

  The high-level qualifier for the Oracle database (db_hlq) is used to identify the PDS data set files for the database instance. This high-level qualifer should be labeled with the database name or a similar name, for example ORACLE.ORA1. It is recommended that you do not include version information in the database high-level qualifier, as this is likely to change over time.

• INSTLIB and PARMLIB libraries. Specify where to create the new INSTLIB and PARMLIB libraries. This can be done using IBM SMS (Storage Management Subsystem) or by manually specifying a volume and unit.

• Subsystem Definition Parameters. Specify the subsystem name, database SID, net SID, port on which the Oracle database should listen for remote connection attempts,and names of the JCL procedures for the database instance.

• Server Parameters. Specify the basic OSDI parameter file definitions for hte database service.

• ORA$FPS Control File Definitions. Specify the information to create an ORA$FPS file. This file is used to create various database files of a particular size. You can enter new values for your installation or enter default values and later modify the ORA$FPS file that is created. The VOLSER (volume serial number) used for control files and database files is also used in the DEFINE JCL procedure.

The configuration utility generates two PDS data set files for the INSTLIB and PARMLIB libraries, db_hlq.INSTLIB and db_hlq.PARMLIB.

The INSTLIB library contains all the sample JCL required to create the database instance and perform simple tasks like starting and stopping the database.

The PARMLIB library contains all the required parameter files needed to create the database instance.

Step 2:  Customize JCL Procedures and Parameter Files

The degree to which the information provided in Step 1 is accurate will determine how much the JCL procedures and parameter files need to be modified in order to create the database instance.

JCL Procedures

All JCL procedures (or batch jobs) need to be reviewed carefully to ensure that they are valid. The JOBCARD job provided is only a default used by Oracle and will need to be tailored to your environment.

The batch jobs can be divided into the following categories:

• Two sample PROCS that define the Oracle address space and the Net address space, and a procedure to copy them into a system installation library.

  The PROCS are named in step 1 of the configuration process, and are cross-referenced to the OSDI parameter file. Each procedure must be defined to RACF as a started task and associated with a user. The following are examples of RACF commands required to perform these tasks:

  The batch job used to copy the two PROCS into a system PROCLIB library job is called COPYPROC and must be customized to point to the system PROCLIB library.

• Sample PROCs which are used on a system-wide basis for Oracle utilities. These usually begin with the name ORA*, and should be customized and copied into system PROCLIB libraries before the new database goes into production. Initially, they are not needed for the basic installation.

• JCL procedures required to define, start and set up the database. These all begin with SQLsidn where sid is the SID name chosen for the instance, and n is a number from one to eight. These procedures perform specific tasks related to creating the database. The other batch jobs associated with this are called DEFINE and STRTSRVC. The DEFINE job is used to define the database files and the STRTSRVC job is used to define and activate the subsystem. Another set of PROCS provide basic functions like starting and stopping the database and starting the subsystem services.

Parameter Files

The parameter files are located in db_hlq.PARMLIB. They are the core definition files for the database. You should review these files for accuracy before you create the database instance. The parameter files can be divided into the following categories:

• OSDI parameter files. Of this group of files, the the subsystem definition file is the core file. This file is called only by the OSDI subsystem name. This file has a major impact the other JCL batch jobs and parameter files and should be modified with care. The following is an example of the subsystem definition file:

  INIT (ORASSI,SSN1)                                            
  DEF SVG SSID(SSN1) DESC('OSDI Oracle 10G Subsystem - SSN1')   
                                                                
  DEF SRV ORAS10 PROC(ORA1S10) TYPE(ORA) MAXAS(1) -             
   DESC('Oracle V10G RDBMS Service') -                          
   SID(ORA1) PARM('ORACLE.ORA1.PARMLIB(SSN1PARM)')            
                                                                
  DEF SRV ORAN10 PROC(ORA1N10) TYPE(NET) -                      
   DESC('Oracle V10G Net Service') -                            
   SID(ORAN) PARM('HPNS PORT(1501) ENCLAVE(SESS)')              
                                                                
  SHOW SERVICEGROUP LONG                                        
                                                                
  START  ORAS10                                            
  START  ORAN10
  
  

  The file related to this is called sidPARM. This contains all the OSDI specific parameters. These parameters are documented in detail in this guide. Most default parameters are acceptable for most basic installations. The following is an example fo this file:

  * SSN1 OSDI SUBSYSTEM PARAMETER FILE.
  * USED BY SUBSYSTEM SSN1 SERVICE ORAS10 PROC ORA1S10
  * LOAD MODULE TO USE.
  SERVER_LOADMOD(ORACLE)
  * NUMBER OF ADDRESS SPACES TO START. VALUES ARE 1-256
  INIT_ADR_SPACES(1)
  * MAXIMUM NUMBER OF SESSIONS ALLOWED FOR THIS ADDRESS SPACE
  MAX_SESSIONS(500)
  * MAX MEMORY ALLOWED PER SESS. VALUES ARE NNNN {K|M}.
  MAX_SESSION_MEM(100M)
  * INITIAL STACK SIZE. VALUES ARE NNNN {K|M}.
  INIT_STACK_SIZE(256K)                                                 
  * TURN ON SMF RECORDING. VALUES 0 AND 128 THROUGH 255.                
  SMF_STAT_RECNO(0)                                                     
  * EXTERNAL AUTHENTICATION                                             
  LOGON_AUTH(NONE)                                                      
  * STORAGE CUSHION. VALUES ARE NNNN {K|M}.                             
  REGION_MEM_RESERVE(10M)                                               
  * DATABASE DATASET NAME PREFIX.                                       
  DSN_PREFIX_DB(ORACLE)                                                 
  * TRACE DATASET NAME PREFIX.                                          
  TRACE_DSNAME(ORACLE.ORA1.TRACE.&ORASESST..T&HHMMSS)                   
  * ALERT DATASET NAME PREFIX.                                          
  ALERT_DSNAME(ORACLE.ORA1.ALERT.&ORASESST..T&HHMMSS)                   
  * MINIMUM SIZE OF ALERT FILE. VALUES ARE NNNN {K|M}.                  
  ALERT_MIN(10M)                                                        
  * MAXIMUM SIZE OF ALERT FILE. VALUES ARE NNNN {K|M}.                  
  ALERT_MAX(20M)
  
  

  The third file in this section is called SUBSYS. It provides the command which needs to be issued in order to define and activeate the subsystem definition file.

• The sidFPS parameter file contains all the default FPS parameters for creating the database files. Remove any which are not required. It contains the six most common data set types. If this is defined correctly, thendefining additional database files becomes easier. The minimum that is recommended is DFLT. Refer to this guide for details about what to code for this parameter file. An example is as follows

  * Default parameters                     
  FILE_GROUP(DFLT)                         
    RECALL(NONE)                           
    MOUNT(NO)                              
    DEFAULT_SPACE(10000 10000)            
    UNIT(SYSDA)
  
  

• Instance-specific files

  These are files which relate to the Oracle instance itself. They are the INITORA which is the standard initsid.ora file found on other platforms with default parameters for z/OS. This file should be modified to match any installation requirements. The installation utility does not ask many questions about tailoring this parameter file . It prompts only for the SID and number of processes because most parameters can be allowed to take the default for most installations.

  The CREATE file which contains the SQL required to create a new Oracle database instance. This file along with the DEFINE JCL defines the basic layout of an Oracle instance. The type of instance it builds is a small, multi-purpose instance and follows the basic rules of Oracle database design. For example, it is highly recommended that there are at least two control files in any instance, the system data goes into the system tablespace and all user data goes elsewhere.

  The sidENV contains the environment variables required for the Oracle database. The most important is the ORACLE_HOME variable which should point to the ORACLE_HOME for the installation. The sidFNA is not required and is primarily used for compatibility reasons. It provides default naming conventions for SQL files.

  Two other files which can be used are the TNSNAMES file and the SQLNET file. The TNSNAMES file provides the default TNSNAMES entries for users to access this instance both through cross memory and through TCP/IP.

Step 3:   Copy the Subsystem PROCs to a System PROCLIB

When the COPYPROC JCL has been customized correctly and the PROC names defined for the database are valid, run the COPYPROC JCL. It copies the two subsystem PROCs into a system PROCLIB library.

Step 4:  Define Initial Database Files

This step defines the basic instance VSAM files requried for an Oracle database. Review the DEFINE JCL procedure. Verify that the IDCAMS definitions are valid for your site and that the volumes specificed have the necessary space on them. Then run this procedure and make certain that all database files are defined correctly. If any modification to names, types sizes of files is requried then the corresponding change needs to be done in the CREATE member of PARMLIB.

Step 5:  Define and Start OSDI Services

This can be done by running the STRTSRVC member. This uses the SETSSI command to define the OSDI subsystem. If the user is not able to issue this command then the define service needs to be done by a system programmer. A sample of the command to issue is provided by the SUBSYS file in the PARMLIB.

At the end of this step, you should have an RDBMS and a Net subsystem defined and active.

Step 6:  Create the Database Instance (SQLsid1)

There are two ways the database instance can be created. This guide will only consider the method involving JCL and batch jobs. The second method is to use SQL commands from the UNIX System Services shell environment. This method is similar to a manual installationon the UNIX platform.

For batch, a database administrator needs to run the job SQLsid1. This job takes the CREATE member of PARMLIB and creates an ORACLE instance in the address space started in the previous step. Using a UNIX System Services shell, a database administrator would need to run SQL*Plus and invoke the commands specified in the CREATE member.

Step 7:  Run Catalog and Catproc SQL Scripts (SQLsid2).

In this step, you run the catalog and catproc SQL scripts for the database instance. This is done by submitting batch job SQLsid2. The job is fairly slow and can take a significant amount of time. The output from this step needs to be carefully reviewed to make certain there are no Oracle error messages. The SQL files used for this step reside in a UNIX System Services shell under in the ORACLE_HOME/rdbms/admin directory. In a UNIX System Services shell, the same step can be implemented by changing the directory to ORACLE_HOME/rdbms/admin, connecting as SYSDBA and then running the catalog and catproc scripts.

This step must complete successfully because it will have an impact on every aspect of the database instance. No other jobs should be run against the database instance while this job is running.

Step 8:  Initialize Java (SQLsid3)

This step can be done by running SQLsid3. In a UNIX System Services shell, you would change the working directory to javavm/admin, connect as SYSDBA and run the initjvm.sql script. This step creates the Java virtual machine in the Oracle instance. The output from this step should be reviewed carefully to make certain there are no errors.

After this step has completed it is recommended that the instance be shut down and restarted. This is the final step for any installation.

The following steps must be implemented while the instance is mounted and open.

Step 9:  Load SQL*Plus Help Data (SQLsid4)

This job loads the SQL*Plus help data into the database. The database must be running when this job is run. You can omit this job if you do not want online help or if you have limited database space.

You might receive messages indicating that a synonym, table, or view does not exist. These messages are normal and do not indicate an error condition. Examine the output for other error messages.

In order to run this step, submit batch job SQLsid4 or change the working directory to ORACLE_HOME/sqlplus/admin and run pupbld.sql and hlpbld.sql scripts.

Step 10:  Create Sample Users (SQLsid5)

This step is optional and creates the Oracle sample user SCOTT. This job creates the user and demonstration tables referrred to in the Oracle product documentation: the SCOTT userid and the EMP, DEPT, SALGRADE, and BONUS tables. The database must be running when this job is run. You can omit this job if you do not want the demonstration tables installed or if you have limited database space. To install this job, run SQLsid5 or, from a UNIX System Services shell, change the working directory to ORACLE_HOME/rdbms/admin, run SQL*Plus and connect as SYSTEM, and run the utlsampl.sql script.

You might receive messages indicating that a synonym, table, or view does not exist. These messages are normal and do not indicate an error condition. Examine the output for other error messages.

Step 11:  Install Oracle Intermedia Text (SQLsid6)

This job sets up the database for use with Oracle Text. It creates the user CTXSYS and the database objects that are required by Oracle Text. User CTXSYS is created with the password CTXSYS, which you can change after this job completes by using the SQL command ALTER USER. The database must be running when this job is run. This job is optional and should be run only by installations requiring Intermedia text.

To install Intermedia Text run the batch job SQLsid6, or, from a z/OS UNIX System Services shell, change the working directory to ctx/admin, run SQL*Plus and connect as SYSDBA, and run the catctx.sql script.

Step 12:  Install Spatial Data Option (SQLsid7)

This job sets up the spatial data option. It should only be run by installations requiring this feature. In order to set up the spatial data option, run SQLsid7 or, from a UNIX System Services shell, change the working directory to md/admin, run SQL*Plus and connect as SYSDBA, and run the catmd.sql script.

Step 13:  Turn on Archiving for the instance (SQLsid8)

It is recommended that you turn on archiving for any database in which the data is of critical importance. It greatly enhances recoverability of the database. Without it, the only real database recovery that can be performed is to restore from the last cold backup. In order to turn on archiving, modify the init.ora parameter file to uncomment and customize the ARCHIVE_LOG parameters, then shut down the instance and run SQLsid8.

Step 14:  Change All Oracle Default Passwords

This is critical for security purposes. For installation purposes, Oracle uses a number of default passwords. These need to be altered once the installation has completed so that there is no security exposure.