This chapter describes the z/OS-specific details of how to start and stop an Oracle database instance on z/OS. Use this chapter in conjunction with the information in the Oracle Database Administrator's Guide to establish Oracle database operating practices in your installation.
The following topics are included:
As discussed in Oracle Database Administrator's Guide, and in the following section, you make an Oracle database instance available for use by issuing the Oracle database STARTUP
command through Oracle SQL*Plus or a comparable Oracle utility. Before you can do this on z/OS, the OSDI-defined database service must be started. You start a database service using the OSDI START
command documented in Appendix A, "OSDI Subsystem Command Reference." This will create one or more z/OS address spaces according to the INIT_ADR_SPACES
value in the server region parameters. These address spaces are what z/OS calls "system address spaces", and they are similar to z/OS started tasks (STCs). Each address space executes the JCL procedure that you specified through the PROC parameter of DEFINE SERVICE
. The OSDI START
command can be included in the subsystem parameter file so that the service is always started during IPL.
After the service is started, additional OSDI START
commands can be issued to create additional address spaces for the service, up to the MAXAS
limit that was specified in DEFINE SERVICE
. Each additional START
command adds one address space to the service. Added address spaces increase the amount of virtual memory available for database application sessions. Address spaces can be added before or after the Oracle database STARTUP
command is issued. No Oracle database-specific action (such as the STARTUP
command) is needed when adding address spaces.
Other than stopping the database service, which terminates all of the service address spaces, there is no way to reduce the number of address spaces of a running service. Database service address spaces cannot be stopped individually.
When you perform an Oracle database shutdown (by issuing the SHUTDOWN
command through Oracle SQL*Plus), the associated service address spaces continue to run. You can startup and shutdown an Oracle database instance as many times as you want using the same set of service address spaces. The only situations that dictate stopping the service (terminating its address spaces) are to do the following:
Effect a change to one of the service parameter files that is read only at service start: ORA$ENV
, or the main service parameters (data set specified using the OSDI PARM
string)
Effect a fix or upgrade to software modules (Oracle software, or IBM software that is fetched into the address space such as Language Environment)
Resolve a problem that has rendered the address spaces unusable in some way
With this in mind, it may be best to think of the database service address spaces as more or less permanent fixtures. In fact, the OSDI START
command can be included in the subsystem parameter file so that the service is always started during OSDI subsystem initialization (normally during system IPL). This will help to ensure that the service is always ready and available for Oracle database startup processing.
You can stop a database service with the OSDI STOP
command (described in Appendix A, "OSDI Subsystem Command Reference") or the native z/OS STOP
(or P) command. Stopping a service terminates all of its address spaces. The command takes effect immediately regardless of the operating state of the associated Oracle database instances. If you stop a database service without first performing an Oracle database shutdown through SQL*Plus, then active client requests may be abnormally terminated. The subsequent Oracle database startup of a database stopped in this fashion will take longer because of the requirement to read log files and perform recovery for transactions that were in progress at the time of termination.
After the database service is successfully started, you can issue the Oracle database STARTUP command to make the database instance available to applications. Any of several different Oracle database utilities, including SQL*Plus and Recovery Manager, can be used to issue this command. You can execute the utility on the same z/OS system as the database that you are starting, or you can execute it on a different system, even one that is not z/OS. In the latter case, you must have configured and started the Oracle Net network service, and some special security considerations come into play. These special security considerations are discussed in Chapter 9, "Security Considerations." For simplicity, the balance of this section assumes that you are running SQL*Plus on the same z/OS image as the database instance that you are managing.
Most installations will find it convenient to set up started task procedures or operator-startable jobs for executing Oracle database STARTUP
and SHUTDOWN
commands so that these functions are accessible to the system operator. For detailed information about executing SQL*Plus on z/OS, refer to Oracle Database User's Guide for IBM z/OS on System z.
Before the utility can issue STARTUP
, it must establish a connection to the target instance with a CONNECT
statement. Special rules apply to this CONNECT
because it is processed before the Oracle database data dictionary (where Oracle database user ID information resides) is open.
Special security processing is performed on z/OS to authenticate the z/OS job or user who makes the connection. This processing uses a SAF-based (RACROUTE
) test, discussed in Chapter 9, "Security Considerations." If your installation has enabled this processing, then the user ID that runs SQL*Plus must have been granted the proper authority; otherwise, CONNECT / AS SYSDBA
will fail, as will the following STARTUP
command. Refer to Chapter 9 for more details.
You must ensure that the utility CONNECT
statement connects to the correct database service. When you are running the utility on z/OS, several different methods exist to indicate the target service for a CONNECT
, all of which use the SID associated with the service. These methods are documented in the Oracle Database User's Guide for IBM z/OS on System z. In most of our examples here, we use the ORA@
sid
DD statement to specify the target service.
When using multiple server address spaces, the STARTUP
command must be issued from a session that is connected to the first service address space. There is no explicit mechanism for requesting connection to the first address space, but this condition is always met when there are no other users connected to the service, which is normally the case when you are running STARTUP
.
The following is an example Oracle database startup that has been set up as a z/OS batch job using SQL*Plus. The target service has the SID "ORA1", which has been specified using the ORA@
dummy DD statement in the job. The init.ora
parameter file is a member of a PDS. The STARTUP
command has defaulted the MOUNT
and OPEN
options, and the database will therefore be opened and made usable to applications.
//ORASTART JOB 1,'Oracle ORA1 Startup' //PLUS EXEC PGM=SQLPLUS,PARM='/NOLOG' //STEPLIB DD DISP=SHR,DSN=ORACLE.V10G.CMDLOAD //ORA$LIB DD DISP=SHR,DSN=ORACLE.V10G.MESG //SYSERR DD SYSOUT=* //SYSOUT DD SYSOUT=* //SQLLOGIN DD DUMMY //ORA@ORA1 DD DUMMY //INITORA DD DISP=SHR,DSN=ORACLE.ORA1.PARMLIB(ORA1INIT) //SYSIN DD * CONNECT / AS SYSDBA STARTUP PFILE=//DD:INITORA EXIT /*
The database is shut down in the normal Oracle database fashion, by connecting with SQL*Plus and issuing the SHUTDOWN
command. Shutting down the database does not terminate service address spaces.
Oracle does not recommend using SHUTDOWN ABORT
or STARTUP FORCE
with z/OS database instances. These commands attempt to forcibly terminate all processes that are accessing the instance, which usually is not a desirable action. In situations where other forms of Oracle database shutdown do not appear to be working, or when you are unable to connect to the server to issue a shutdown command, the best course of action is to stop and restart the database service, then proceed with STARTUP
.
Each Oracle instance you run produces an alert log, which is a sequential log of text messages pertaining to overall database operations including Oracle startup and shutdown, major database events such as log archiving and tablespace definition, and certain categories of errors. Submitting a copy of your alert log data is required in many problem determination situations. Refer to Oracle Database Administrator's Guide for general information about the alert log.
On z/OS, the alert log is implemented as a sequential data set written with BSAM
. It can be a spool (SYSOUT
) data set or a DSORG=PS
disk data set. When the alert log is a spool data set, you can view and copy an instance's active, in-use alert log data using a facility such as IBM's SDSF
. This is not possible when the alert log is a disk data set: IBM BSAM
does not support concurrent read and write access to a disk data set from distinct tasks or address spaces. If you use a disk data set for the alert log and you need to view or copy the contents (for example, when investigating a problem), then you must cause the current alert log data set to be closed and released (unallocated) by the server.
There are two ways to effect close and release of the alert log. One is to stop the OSDI service hosting the instance (normally after performing an Oracle shutdown). Service termination processing will flush all buffered alert log data to disk and then close and unallocate the data set.
Stopping the OSDI service may be inconvenient in situations where the alert log from a production instance is required. For those circumstances, an alert log "switch" mechanism is provided. When a switch occurs, the current alert log is flushed, closed, and unallocated and a new alert log is allocated and opened. This occurs without affecting application activity in the server or server availability in general.
Two mechanisms are provided for switching the alert log. One is the LOGSWITCH
database service command, issued through the z/OS MODIFY
system command at a system console or comparable facility. This causes an immediate switch of the alert log, freeing the current log for viewing or copying. Besides on-demand use, installations might consider issuing this command automatically on a periodic basis through a system command scheduling facility, to "spin off" accumulated alert log data on a regular basis such as daily or weekly. The database region parameter ALERT_MIN
can be used to set a minimum alert log size below which a normal LOGSWITCH
command is ignored. A FORCE
option on the LOGSWITCH
command can be used to override this minimum. Details on the LOGSWITCH
command are in the following section.
You also can configure a database service to switch the alert log automatically when it reaches a given size. The OSDI database region parameter ALERT_MAX
, described in Chapter 3, sets a size threshold at which the log is switched automatically. As alert log records are written the number of bytes in each logical record is tallied; when the instance issues an alert log message that would exceed the threshold size, the current log is flushed, closed, and unallocated and a new log is opened (and the tally zeroed) before the new record is written.
The alert log also switches automatically if any errors occur when writing to the current log, including the case where BSAM WRITE
incurs a System x37 ABEND due to data set or disk volume space being exhausted. Unlike most other Oracle platforms, the alert log is not automatically closed and opened at Oracle shutdown and startup, respectively.
Regardless of which alert log switch mechanism is used, switches occur without regard to the sequence or interrelationship of messages being issued. Oracle could be issuing multiple related messages at the moment a switch occurs, and those messages may end up "split" across the old and new log data sets. However, alert log messages are never lost or discarded. If allocation or open of a new alert log fails and anything other than the default is being used, an attempt is made to allocate and open a default alert log (see below). If allocation of a default alert log fails, alert log messages are issued to the z/OS system log instead. Once alert log messages start going to the system log they continue to go there until the OSDI service is stopped or a LOGSWITCH
command is issued and processed successfully, starting a new alert log file.
By default, the alert log switch allocates a new alert log as a spool data set in the default output class of the server, that is, SYSOUT=*
. You can use the ALERT_DSNAME
database region parameter, described in Chapter 3, "Configuring a Database Service and Creating a New Database", to change this. The value given for this parameter can be either a SYSOUT
filespec (including output class, form name, and destination) or it can be a data set name filespec, indicating that a sequential disk data set is desired. When you use a data set name filespec, the filespec must include imbedded system symbol references so that each use of the filespec produces a unique data set name.
Typically, z/OS symbols for the current date and time (&LDATE
and <IME
) are used. You can also use OSDI service-specific symbols such as &ORASRVN
in the filespec, but OSDI session-level symbols such as &ORASESST
cannot be used. An example of this parameter with a data set name filespec is as follows:
ALERT_DSNAME(&ORAPREFD..&ORASRVN..ALERT.D&LDATE..T<IME)
An example of the parameter with a SYSOUT
filespec is as follows:
ADSN(//S:*,AN01,MHQPRT12)
Refer to Oracle Database User's Guide for IBM z/OS on System z for additional information about filespecs and filespec syntax.
Allocation details for new alert logs are controlled using server file management parameters in the ORA$FPS
DD, described in Chapter 4, "Defining z/OS Data Sets for the Oracle Database." The file group name DBAT
supplies particulars for alert log allocation. If your ORA$FPS
does not include an entry for group DBAT
, the default group (DFLT
) is used. File management parameters are meaningful only when the alert log is a disk data set (as opposed to SYSOUT
), and only the parameters UNIT
, VOLUMES
, STORCLAS
, DATACLAS
, MGMTCLAS
, and disk space-related parameters are honored when allocating an alert log data set.
In addition to the commands described previously, three other commands can be issued to a running database service: DISPLAY SESSION
, DUMP SESSION
, and REFRESH FPS
. These commands use the z/OS MODIFY
(or F) command mechanism. To issue these commands, enter the following z/OS system command, where id
is the service jobname or identifier, and command
is the command image:
F id,command
The DISPLAY SESSION
command displays information for active sessions within a database service. All keywords and values are required.
DISPLAY SESSION JOBNAME(job_filter)
Abbreviations: D, SESS, JOB
job_filter:
Specify up to eight characters. The value may be suffixed with an '*' or may consist of only an '*' to indicate wildcarding.
Examples:
F MYORA8,DISPLAY SESSION JOBNAME(JAOTT3)
The above command displays information for sessions initiated by clients with a job name of 'JAOTT3
' within the MYORA8
database service.
F MYORA8,D SESS JOB(JAOTT*)
The above command displays information for sessions initiated by clients with a job name beginning with 'JAOTT
' within the MYORA8
database service.
The DISPLAY VERSION
command displays the version and linkedit date of the database service or Net service (OSDI listener) executables.
DISPLAY VERSION
Abbreviations: D, VER
Examples:
F ORA1,DISPLAY VERSION
The above command displays the version information for the ORA1
database service.
F NET1,DISPLAY VERSION
The above command displays the version information for the NET1
Net service.
The DUMP SESSION
command creates a system-readable dump of the address space, or spaces, that are associated with a given session within a database service. All keywords and values are required.
DUMP SESSION(sessid) DSN(dataset_name)
Abbreviations: SESS
, DA
sessid:
The Session ID is an identifier of the relevant session in the form of eight hexadecimal characters. Session IDs may be discovered with the DISPLAY SESSION
command.
dataset_name:
This identifier is the fully qualified name of the data set that will contain the dump, and it may be up to 44 characters in length. The data set cannot exist when the DUMP
command is entered.
Example:
F MYORA8,DUMP SESS(00010010) DSN(OSDI.SESSION.DUMP)
The above command dumps the address spaces associated with session '00010010' to a data set of the name 'OSDI.SESSION.DUMP
' within the MYORA8
database service.
The KILL SESSION
command terminates active sessions within a database service. All keywords and values are required.
KILL SESSION(sessid)
Abbreviations: SESS
sessid:
The Session ID is an identifier of the relevant session in the form of eight hexadecimal characters. Session IDs may be discovered with the DISPLAY SESSION
command.
Example:
F MYORA,KILL SESSION(00130010)
The above command terminates the session with the session id (or SPID
) of '00130010' within the MYORA
database service.
Network client sessions (sessions on an enclave SRB) may not be terminated immediately if they are executing in the database server. Therefore, it may be necessary to use the SQL statement ALTER SYSTEM SESSION KILL
to terminate them.
The LOGSWITCH
command is used to cause the current Oracle alert log file to be flushed, closed, and unallocated followed by allocation and open of a new alert log according to the ALERT_DSNAME
region parameter.
LOGSWITCH [FORCE]
Abbreviations: LOGSW
The FORCE
option indicates that the switch is to be done regardless of any minimum threshold specified with the ALERT_MIN
region parameter. If FORCE
is omitted and the total size in data bytes of the current alert log is less than ALERT_MIN
, no switch is done and message MIR0612I
is issued.
Example:
F WFMORA1,LOGSW
This command causes an alert log switch if the alert log is at least ALERT_MIN
bytes in size within the WFMORA1
database service.
The REFRESH FPS
command is used to reload the server file management parameters defined "Server File Management Parameters" in Chapter 4, "Defining z/OS Data Sets for the Oracle Database." As during database service startup, the file specified by the ORA$FPS
DD statement is read to obtain the parameters. Unlike service startup, any error encountered while processing the file contents will cancel the attempted refresh.
REFRESH FPS
Abbreviations: REFR
Example:
F MYORA8,REFR FPS
This command reloads the server file management parameters within the MYORA8
database service.