After you have created an OSDI subsystem, you can configure and initialize one or more Oracle databases to run under that subsystem. This chapter describes how to set up OSDI definitions, JCL procedures, parameter files, and other z/OS-specific items required by an Oracle database instance. The three chapters, Chapter 4, "Defining z/OS Data Sets for the Oracle Database", Chapter 5, "Operating a Database Service", and Chapter 6, "Database Backup and Recovery", provide additional details on Oracle database files, database service operation, and database backup and recovery. Oracle suggests that you read these chapters before configuring a new database service, and that you review Chapter 9, "Security Considerations", for information about z/OS security features that affect an Oracle database service.
If you are migrating from a previous release of Oracle database to Oracle Database 10g for z/OS, you will not be creating a new database as described in this chapter. Refer to Chapter 17, "Migration and Upgrade Considerations" for details on migrating your existing database.
The following topics are included:
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 preallocate 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.
The OSDI DEFINE SERVICE
command is described completely in Appendix A, "OSDI Subsystem Command Reference." This appendix covers the DEFINE
parameter considerations that are specific to an Oracle database service.
The service name for a database can be anything that you want within the content limitations described in Appendix A. By default, OSDI uses 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
.
JOBNAME
parameter that is not the same as any existing subsystem. If you do not use unique names, then OSDI starts the service using the service name as the job identifier. When z/OS processes a start for an address space whose job name or job identifier matches a known subsystem, the job runs under control of the master subsystem instead of under control of JES.Caution:
Running OSDI services under the master subsystem is not supported. This situation must be avoided by making sure that the service runs with a job name or a job identifier that is not the same as any subsystem name.The TYPE
parameter for a database service must be specified as ORA.
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".
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
specifies 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
).
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 if the database service is not active.
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 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.
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 must 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 through 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 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 through 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
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 ensure 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 SMFPRM
xx
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 and in the Oracle Database User's Guide for IBM z/OS on System z. 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 about the global environment variable file, refer to Chapter 7, "Oracle Database Administration Utilities."
Note:
TheORACLE_HOME
environment variable (referring to the ORACLE_HOME
directory name under HFS, specified during installation) is required for components that run in a UNIX System Services shell environment, such as Oracle JVM, Oracle Text, and the time zone feature.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."
Note:
When this DD statement is omitted, an IEC130I message may appear in the system log during service address space initialization. This is normal.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
.
Note:
When this DD statement is omitted, an IEC130I message may appear in the system log during service address space initialization. This is normal.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. IBM z/OS Language Environment runtime library routines are required by the Oracle database and can usually be loaded from the system linklist. If this is not the case at your installation, then you must add the LE libraries to the STEPLIB. The IBM default data set names are CEE.SCEERUN
and CEE.SCEERUN2
.
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".
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 switch occurs. Refer to Chapter 5, "Operating a Database Service" Managing the Alert Log for additional information about 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)
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 through 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.
The ALERT_DSNAME
parameter 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<IME) ADSN(//S:Z,,DBOPS01)
If you omit this parameter, alert log switches use a default SYSOUT
specification //SYSOUT:*
. For more information about alert log switching, refer to "Managing the Alert Log" in Chapter 5, "Operating a Database Service."
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
, n
K
(denoting a multiplier of 1024), or n
M
(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 ( 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
, n
K
(denoting a multiplier of 1024), or n
M
(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.
The ALERT_ROUTCDE
parameter specifies that alert log messages should also be written to the operator console and provides a list of routing codes.
The format of this parameter is as follows:
ALERT_ROUTCDE(routcde[,routcde,...])
The routcde
value specifies a numeric routing code in the range 1 - 29 and 65 - 128. Multiple values may be specified, separated by commas. Refer to the WTO
macro in the MVS Assembler Services Reference for a description of each routing code.
If you omit this parameter, database messages are written only to the alert log. For example:
ALERT_ROUTCDE(11)
The AS1_METRIC_BIAS
parameter adjusts the virtual memory availability metric of the control region, relative to the auxiliary regions. Its purpose is to reserve the control region for background processes in a multi-address space configuration. Depending on the value chosen, some, or all connections from users will be directed to one of the auxiliary regions. This parameter has no effect, if the control region is the only address space that is active.
The format of this parameter is as follows:
AS1_METRIC_BIAS(memory_bias)
The memory_bias
value specifies the amount of memory to be subtracted from the memory availability metric, when the control region is started. This value can be specified as n
, nK
(denoting a multiplier of 1024), or nM
(denoting a multiplier of 1,048,576). The maximum value is 1024M. The default is zero (0), meaning that no adjustment is made to the initial value of the metric. For example:
AS1_METRIC_BIAS(416M) ECO1 DIS ECS1 LONG MIS0308I Service ECS1 is ACTIVE , 002 address spaces ASID 0077: ACTIVE , 00000 binds, 65FFFFFF metric ASID 008A: ACTIVE , 00000 binds, 7FFFFC17 metric
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.
The DETACHED_PROCESS
parameter specifies whether non-fatal detached (background) processes (for example, Pnnn
, Qnnn
, Jnnn
) may run in an auxiliary region or only in the control region. The purpose of this parameter is to reserve the control region for critical background processes in a multi-address space configuration. This parameter has no effect when used with database releases before 10.2, which did not have a process spawner process (PSP0), or if the control region is the only active address space.
The format of this parameter is as follows:
DETACHED_PROCESS(AS1|ANY)
The parameter value specifies the address spaces that are eligible to run non-fatal detached processes. The value ANY
specifies that these tasks will be scheduled to the region with the highest storage availability metric. The value AS1
reverts to the previous behavior and limits non-fatal detached processes to the control region. The default value is ANY
.
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.
The IDLE_TIMEOU
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 nnn
S for seconds or nnn
M 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.
The INIT_ADR_SPACES
parameter 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.
The INIT_STACK_SIZE
parameter 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 about 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.
The LOGON_AUTH
parameter 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:
Value | Description |
---|---|
NONE |
IDENTIFIED EXTERNALLY not allowed (no interaction) |
SAF |
perform built-in SAF RACROUTE verification |
exitname |
call an installation-supplied logon exit; exitname is the one-character to eight-character load module name of the exit |
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."
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.
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.
The MIN_SESSION_MEM
parameter specifies the minimum amount of virtual memory that must be available, before a new session will be connected. This parameter has no effect, unless a region-wide memory limit is established through the REGION_MEM_RESERVE
parameter.
The format of this parameter is as follows:
MIN_SESSION_MEM(session_memory)
The session_memory value is the minimum amount of virtual memory that a single database session requires. 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), meaning that connection processing will not impose a minimum memory requirement. For example:
MIN_SESSION_MEM(10M)
The ORACLE_SID
parameter specifies the internal SID
and is equivalent to the environment variable of the same name on other platforms. It is used in Oracle RAC fail-over environments to avoid ORA-00304
and may be displayed through the INSTANCE_NAME
column of dynamic view V$INSTANCE
.
The format of this parameter is as follows:
ORACLE_SID(SVCNAME|sid)
Specifies the value SVCNAME
, meaning the OSDI
service name, or a 1 to 8 character instance identifier.
If this parameter is omitted, then the internal SID
is the same as the external SID
in the OSDI
service definition. For example:
ORACLE_SID(US04_R1)
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 requirements and 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.
The SERVER_LOADMOD
parameter 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.
The SMF_STAT_RECNO
parameter 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."
The TRACE_DSNAME
parameter specifies the destination for Oracle RDBMS trace files. This includes normal traces requested by setting the session SQL_TRACE
option to TRUE
, and 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.
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 user ID 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 about creating an SPFILE
on z/OS, refer to "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 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 user ID 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" in Appendix B, "Operating System Dependent Variables."
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."
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.
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 preallocating 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'"
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 about database logical blocksize, refer to "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 Oracle Database Administrator's Guide for more information about 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 about the standby database feature can be found in Oracle Data Guard Concepts and Administration.
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.
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 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.T
nnn
.S
nnn
.R
nnn
and ORA5.ARCHLOG2.T
nnn
.S
nnn
.R
nnn
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
.
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.
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 Oracle Database User's Guide for IBM z/OS on System z for information about ambiguous and unambiguous filespecs.
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 about 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 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.'
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.
You can allow the Oracle server to create the database VSAM clusters during CREATE DATABASE
processing, or you can preallocate 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 by using the server ORA$FPS
DD statement as described in Chapter 4, "Defining z/OS Data Sets for the Oracle Database."
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 use 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 about database service interaction with z/OS security.
Shared servers on Oracle Database for z/OS are supported by the generic listener running in a UNIX System Services shell environment. For information about configuring the generic listener for shared servers, refer to Chapter 8, "Oracle Net". For more information about shared servers, refer to Oracle Net Services Administrator's Guide.
When the OSDI service has been defined, all required JCL procedures and parameter files have been prepared, and any database files that you want preallocated 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 ensure 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 Oracle Database User's Guide for IBM z/OS on System z.
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 user ID:
CONNECT / AS SYSDBA
This command connects the tool as Oracle user ID SYS. If you configured SAF-based OSDI bind authorization as discussed in Chapter 9, "Security Considerations," the z/OS user ID 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 user ID 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 Oracle Database User's Guide for IBM z/OS on System z. 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
, then you must supply the PFILE
parameter on your STARTUP
command, specifying the text file containing your instance parameters. For more information about SPFILE
and PFILE
processing, refer to "Oracle Initialization Parameter Considerations" and to 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 preallocated 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 re-creating 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 through a SQLBSQ
DD statement in the service JCL procedure as described in "Database Region JCL". 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 preallocated 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 through 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 user ID 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";
After the CREATE DATABASE
completes successfully, your database is mounted and open. Before you can create application tables, users, and so forth, you must create the Oracle dictionary tables, stored procedures, and other internal structures. SQL files for this purpose reside in a UNIX System Services shell under the ORACLE_HOME
/rdbms/admin
directory. As with database creation, you can use SQL*Plus to process these scripts against your new database.
For a new database, script files catalog.sql
and catproc.sql
must be run in sequence. Both can be done in a single tool execution as in the following example. As with our previous example, the target database service is identified by an ORA@
sid
DD statement. We are using the same CONNECT
statement as the create job.
//*----------------------------------------------------------------* //* * //* 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 @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql /*
The "@
" symbols that are used in SYSIN
in the example above are SQL*Plus shorthand notation for reading an alternate input file of commands or SQL statements. Similarly, the "?" is replaced by the value of the ORACLE_HOME environment variable that is defined in the ORA$ENV
data set. Following each "@" is a file path in the UNIX System Services hierarchical file system.
These two scripts are large and will therefore run for quite a while. Because the scripts create and load data into tables, log file data is generated as they execute. If you specified ARCHIVELOG
in your CREATE DATABASE
statement (as in our example), logs may fill and require archiving while catalog.sql
and catproc.sql
run. You may want to avoid this complication because you are not likely to attempt a recovery of a brand new database. If there are problems, then rerun both catalog.sql
and catproc.sql
. To avoid log archiving during catalog creation, specify NOARCHIVELOG
in your CREATE DATABASE
, and then use ALTER DATABASE
to switch to ARCHIVELOG
mode later.
Be sure to check the output of the catalog build job carefully. Because the scripts are designed to be rerunnable, they contain DROP
statements that produce errors the first time they are run. These errors are normal. Other errors must be investigated and resolved to complete initialization of the database.
Note:
The database session which executes the catalog build requires up to 96M of session-private memory. If you have limited session memory to less than 96M with the database regionMAX_SESSION_MEM
parameter, catalog build may fail with an ORA-04030
error, an LE/370 U4088 ABEN
D, or other errors.The following example initializes the Java VM:
//*----------------------------------------------------------------* //* * //* 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 @?/javavm/install/initjvm.sql @?/xdk/admin/initxml.sql @?/rdbms/admin/catjava.sql @?/rdbms/admin/utlrp.sql /*
Depending on other Oracle products or features that you may use, you must run additional scripts against your new database to enable those products or features. Refer to the product-specific documentation for more information.
After catalog.sql
and catproc.sql
have run, your database is ready for use. If you created your database with NOARCHIVELOG
to avoid archiving logs during catalog build, then return it to ARCHIVELOG
mode by shutting down Oracle, starting it back up with the MOUNT
and EXCLUSIVE
options, and issuing ALTER DATABASE ARCHIVELOG
then ALTER DATABASE OPEN
. This also is a good time to change the passwords of the Oracle user IDs SYS
and SYSTEM
, which are set up with default passwords during CREATE DATABASE
. (This should be done regardless of whether you are changing ARCHIVELOG
mode.) Both of these actions are shown in the following example.
//*----------------------------------------------------------------* //* * //* 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.ORA1.PARMLIB(INITORA)' mount alter database open; alter user sys identified by SECRET1; alter user system identified by SECRET2; alter user dbsnmp identified by SECRET3;
You can now proceed with creating ids for Oracle users, adding tablespaces and tables for applications, and so forth.
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, then you must complete those tasks before continuing. For more information, refer to Oracle Database Installation Guide for IBM z/OS on System z.
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 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 qualifier for each.
The high-level qualifier 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 qualifier 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 the 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 must be modified to create the database instance.
All JCL procedures (or batch jobs) must be reviewed carefully to ensure that they are valid. The JOBCARD
job provided is only a default used by Oracle and must 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 SQL
sidn
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.
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 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 sid
PARM
. 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 must be issued to define and activate the subsystem definition file.
The sid
FPS
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, then defining 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 init
sid
.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 sid
ENV
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 sid
FNA
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 PROC
s into a system PROCLIB
library.
Step 4: Define Initial Database Files
This step defines the basic instance VSAM files required for an Oracle database. Review the DEFINE
JCL procedure. Verify that the IDCAMS
definitions are valid for your site and that the volumes specified have the necessary space on them. Then run this procedure and ensure that all database files are defined correctly. If any modification to names, types, and sizes of files is required, then the corresponding change must 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 must 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 (SQLsid
1)
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 installation on the UNIX platform.
For batch, a database administrator must run the job SQL
sid
1
. 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 must run SQL*Plus and invoke the commands specified in the CREATE
member.
Step 7: Run Catalog and Catproc SQL Scripts (SQLsid
2).
In this step, you run the catalog
and catproc
SQL scripts for the database instance. This is done by submitting batch job SQL
sid
2
. The job is fairly slow and can take a significant amount of time. The output from this step must be carefully reviewed to ensure that there are no Oracle error messages. The SQL files used for this step reside in a UNIX System Services shell under 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 (SQLsid
3)
This step can be done by running SQL
sid
3
. 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 ensure that 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 (SQLsid
4)
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.
To run this step, submit batch job SQL
sid
4
or change the working directory to ORACLE_HOME/sqlplus/admin
and run pupbld.sql
and hlpbld.sql
scripts.
Step 10: Create Sample Users (SQLsid
5)
This step is optional and creates the Oracle sample user SCOTT
. This job creates the user and demonstration tables referred to in the Oracle product documentation: the SCOTT
user ID 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 SQL
sid
5
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 (SQLsid
6)
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 SQL
sid
6
, 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 (SQLsid
7)
This job sets up the spatial data option. It should only be run by installations requiring this feature. To set up the spatial data option, run SQL
sid
7
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 (SQLsid
8)
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. 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 SQL
sid
8
.
Step 14: Change All Oracle Default Passwords
This is critical for security purposes. For installation purposes, Oracle uses a number of default passwords. These must be altered once the installation has completed so that there is no security exposure.