Skip Headers
Oracle® Database System Administration Guide
10g Release 2 (10.2) for IBM z/OS (OS/390)

Part Number B25398-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 Operating a Database Service

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 server operating practices in your installation.

The following topics are included:

5.1 Starting and Stopping the Database Service

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 via 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 via 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 via 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:

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 via SQL*Plus, then active client requests may be abnormally terminated. The subsequent Oracle database server 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.

5.2 Oracle Database Instance Startup and Shutdown

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 on executing SQL*Plus on z/OS, refer to the Oracle Database User's Guide for IBM z/OS (OS/390).

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 userid 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 userid 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 utilize the SID associated with the service. These methods are documented in the Oracle Database User's Guide for IBM z/OS (OS/390). 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 Corporation 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.

5.3 Managing the Alert Log

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 the Oracle Database Administrator's Guide for general information on 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), you must cause the current alert log data set to be closed and released (unallocated) by the server.

5.3.1 Closing and Releasing the Alert Log

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 via 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 via 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, i.e. 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 &LTIME) are used. You can also use OSDI service-specific symbols such as &ORASRVN in the filespec, but OSDI session-level symbols such as &ORASESST can not be used. An example of this parameter with a data set name filespec is as follows:

ALERT_DSNAME(&ORAPREFD..&ORASRVN..ALERT.D&LDATE..T&LTIME)

An example of the parameter with a SYSOUT filespec is as follows:

ADSN(//S:*,AN01,MHQPRT12)

Refer to the Oracle Database User's Guide for IBM z/OS for additional information on 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.

5.4 Other Database Service Commands

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

5.4.1 DISPLAY SESSION

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.

5.4.2 DISPLAY VERSION

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.

5.4.3 DUMP SESSION

The DUMP SESSION command creates a machine-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 at the time that 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.

5.4.4 KILL SESSION

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.

5.4.5 LOGSWITCH

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.

5.4.6 REFRESH FPS

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.