| Oracle® Database System Administration Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B25398-01 |
|
|
View PDF |
| Oracle® Database System Administration Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B25398-01 |
|
|
View PDF |
The IBM System Management Facility (SMF) provides a facility for users to collect and record a variety of system and job-related information. SMF formats the information into a number of different records. By creating analysis and report routines, installations can use the information in SMF records to track system usage.
The Oracle server uses the standard SMF interface to write user records to the SMF data sets. These user records contain Oracle server accounting and Oracle auditing information allowing Oracle installation sites to charge individual users for the resources they use.
The following topics are included:
Oracle accounting SMF recording is activated by updating the SMFPRMxx member of SYS1.PARMLIB using the SYS or SUBSYS option to allow recording of the Oracle accounting user record type. If the SUBSYS option is used, then the SUBSYS name must match the OSDI subsystem name that hosts the database service. Refer to the IBM SMF documentation for information on implementing SMF.
The default Oracle user record type is 0 (zero). A zero for this parameter indicates that no SMF accounting statistics record is to be written. You can override the default to any value between 128 and 255 by adding the SMF_STAT_RECNO (abbreviation is SMFSTRCN) to the OSDI database region parameter file. The SMF record number that is chosen must not be the same as the number that is used by any other z/OS software.
Oracle Corporation recommends using SMF record number 204, but any available record number between 128 and 255 may be used.
If this parameter is not specified, or if zero is specified, then no SMF accounting 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, and the in-line overhead is mostly just moving data into SMF buffers).
The OSDI SMF_STAT_RECNO parameter is defined as follows:
SMF_STAT_RECNO | SMFSTRCN
SMF_STAT_RECNO can be added as an OSDI parameter to the OSDI database region parameter data set to override the default record number 0. In the following example, 204 is the new SMF record type:
SMF_STAT_RECNO(204)
In addition, make sure the OSUSER parameter is specified in the Oracle Net service definition. This ensures that the operating system Userid and Program Name fields are included in the OSDI BIND parameters passed to the database service. For information on the OSUSER parameter keyword, refer to the section "PARM"in Chapter 8, "Oracle Net".
SMF recording of Oracle accounting information starts automatically at startup if SMF is activated and if the Oracle record number is specified.
Because the standard system default record types activated for SMF are 128 through 255, and because the recommended number for Oracle accounting (204) is within this range, many sites automatically begin SMF recording of Oracle accounting records when Oracle is installed, and the SMF_STAT_RECNO parameter is supplied.
The service must be stopped and restarted for this parameter to take effect.
The OSDI SMF_STAT_RECNO parameter can be used to stop SMF accounting recording for Oracle. To stop SMF recording for Oracle regardless of what your system tables specify, use:
SMF_STAT_RECNO (0)
or take the default of 0. The service must be stopped and restarted for this parameter to take effect.
After SMF accounting recording is turned on, an SMF record is written each time a user logs off (normal termination or SMFINV=SMFNORM), provided SMF was activated when the user logged on.
SMF records are also written on an abend or cancellation of a job if SMFINV=SMFABORT.
If the z/OS system crashes, then SMF records are not written, and the information is lost.
When Oracle Access Manager for CICS TS transactions are used to access data on your local Oracle Database for z/OS server, a single thread can be shared by many CICS TS users. When SMF recording is activated, an SMF record is written for a single thread when the thread is dropped. However, SMF recording is not supported when these transactions are used to access data on a remote Oracle server. If a thread is defined with PROTECT set to NO, then the thread is dropped after being idle for a specified time (CINTERVL) in the thread table.
If a thread is defined with PROTECT set to YES, then the thread is dropped when Oracle Access Manager for CICS TS is stopped with the STOP command. Refer to Chapter 11, "Oracle Access Manager for CICS TS", for more information on the STOP command.
For Oracle Access Manager for CICS TS, SMF accounting information is based on the Oracle userid for the CICS TS transaction.
The following is a sample thread definition table:
ORACICS TYPE=THREAD, AUTH=TRANSID, PROTECT=NO, TRANSAC=(PGM1,PGM2)
If the above thread definition is used, then a sample session is as follows:
PGM1 PGM2 PGM1
The thread is dropped after being idle for the number of seconds specified in the CINTERVL parameter, and two SMF records are written. One of the records summarizes statistics for all PGM1 transactions, and the other record summarizes statistics for all PGM2 transactions.
Refer to Chapter 11, "Oracle Access Manager for CICS TS" for instructions for configuring thread definition tables in Oracle Access Manager for CICS TS.
To interpret an Oracle accounting SMF record, you first need to dump the SMF data set to a sequential data set. You can then write a program that does all of the following:
Reads the sequential data set
Selects only records with the Oracle record number
Accesses the Oracle SMF record fields using the provided DSECT
Prints the statistics
A sample program named ORAFMTO is provided in the SRCLIB library that you can customize for your installation. Refer to "Sample Formatting Program for SMF Accounting Records" in Chapter 10, "Oracle SMF Data".
The Assembler copy file, ORASMFO, contains DSECTS that map and document the Oracle SMF record fields. The ORASMFO data set member resides in the Oracle SRCLIB library.
The ORASMFO file is divided into these sections:
Standard record header section, which contains offsets and lengths of the Net and accounting sections
Correlation section
OSDI section
Database engine section
Net section (if applicable), which contains information about the network origin of clients on an Oracle Net for z/OS TCP/IP protocol network (IBM or SNS/TCPaccess)
z/OS accounting section (if applicable)
Not all sections are present in all SMF records. For example, the z/OS accounting section is present only in SMF records for batch and TSO users. When a section is present, the SMF record header contains the correct length for that section, which might be release dependent. The length field for non-existent sections contains 0 (zero).
Table 10-1 contains brief descriptions for the labels in the SMF header section. For a complete layout of the contents of the SMF header section, refer to the DSECT.
Table 10-1 Contents of the SMF Header Section
| ORASMF0 Label | Description |
|---|---|
|
SMFHDR |
Standard SMF header |
|
SMFHLEN |
Total length of SMF record |
|
SMFHSEG |
Segment descriptor = 0 |
|
SMFHSIN |
SYS IND = X'80' Subsystem info to follow SYS IND = x'40' Subtype format record |
|
SMFHREC |
Record type recommended = 204 (decimal) |
|
SMFHTIM |
Timestamp, time binary (0.01 seconds since midnight) |
|
SMFHDAT |
Timestamp, date (0cyyydddf) c=0 for 19xx. c=1 for 20xx |
|
SMFHSYS |
System id |
|
SMFHSSI |
OSDI Subsystem id |
|
SMFHSUB |
Record subtype; 1 = accounting record |
|
SMFSRVC |
OSDI service name |
|
SMFSESID |
OSDI session id |
|
SMFHRSV1 |
Reserved |
|
SMFNETO |
Offset to Net section |
|
SMFACTO |
Offset to z/OS accounting section |
|
SMFHRV2 |
Reserved |
|
SMFNETL |
Length of Net section |
|
SMFACTL |
Length of z/OS accounting section |
|
SMFHRV3 |
Reserved |
Table 10-2 contains brief descriptions for the labels in the SMF correlation section.
Table 10-2 Contents of the SMF Correlation Section
| ORASMF0 Label | Description |
|---|---|
|
SMFAUTH |
Authorization id = TSO logon id Batch user id on jobcard CICS USERID, TERM-ID,TRANS-ID, PROGRAM-ID, or OPID |
|
SMFCORI |
Correlation id = TSO logon id Batch jobname CICS jobname Not valid for Oracle Net |
|
SMFCONN |
Connection type (TSO,BATCH,CICS,VTAM,TCP/IP, IMS) |
|
SMFASID |
Users address space id (not valid for Oracle Net |
|
SMFOUSR |
Oracle logon id |
|
SMFTNAME |
Originating terminal id (if available) |
|
SMFPNAME |
Originating program name (if available) |
|
SMFGRPN |
RACF group name (if available) |
|
SMFJBID |
JES job identifier |
|
SMFENTRY |
RDR jobcard entry time (batch and TSO only). This field is equivalent to the SMF5RST field in the SMF job termination (type 5) record. |
|
SMFEDATE |
RDR jobcard entry date (batch and TSO only). This field is equivalent to the SMF5RSD field in the SMF job termination (type 5) record. |
Table 10-3 contains brief descriptions for the labels in the SMF OSDI data section.
Table 10-3 Contents of the SMF OSDI Data Section
| ORASMF0 Label | Description |
|---|---|
|
SMFTIM |
Beginning timestamp, time binary (0.01 second since midnight) |
|
SMFDAT |
Beginning timestamp, date (0cyyydddf), ending time and date in header c=0 for 19xx. c=1 for 20xx |
|
SMFDTAI |
Data in |
|
SMFDTAO |
Data out |
|
SMFXMCPU |
Cross memory CPU time (TOD format) |
|
SMFRPCS |
RPC count |
|
SMFHWST |
High-water mark of storage used |
|
SMFINV |
Reason for invocation |
|
SMFNORM |
Normal termination |
|
SMFABORT |
Clean up done |
Table 10-4 contains brief descriptions for the labels in the SMF database engine section.
Table 10-5 contains brief descriptions for the labels in the SMF Net data section.
Table 10-5 Contents of the SMF Oracle Net Data Section
| ORASMF0 Label | Description |
|---|---|
|
SMFNET |
Net section header |
|
SMFNETL |
Length of Net NIV information. The information contained in this section is specific to the Net driver in use. This information is variable length. |
|
SMFNETA |
Start of variable length information. Refer to next section, "Oracle Net Network Information Vector Overview". |
Oracle Net constructs a Network Information Vector (NIV) list containing information about the network origin of an incoming client connection. This information is available to the Logon User Exit and is also written out in the SMF record for each user.
The vector list is preceded by a 2-byte length field indicating the length of the entire list including the length field itself. The individual vectors in the list consist of a 1-byte length field indicating the length of the vector, a 1-byte vector ID field identifying the vector, and a variable number of vector-specific data bytes. The first NIV in the list is always a protocol identification NIV, which will identify the Net protocol being used as well as the network location of the client.
Currently, only the protocol identification NIV is built. Other NIVs may be added in the future as required. The following tables describe the NIV list and the individual NIV formats.
Table 10-8 lists and describes the Net TCP/IP identification NIV format:
Table 10-9 lists and describes the labels in the SMF z/OS accounting data section:
Table 10-9 Contents of the SMF z/OS Accounting Data Section
| ORASMF0 Label | Description |
|---|---|
|
SMFACT |
z/OS accounting section header |
|
SMACTNF |
Number of accounting fields. This field is equivalent to the SMF5ACTF field in the SMF job termination (type 5) record |
|
SMFACTA |
z/OS accounting information. This field is equivalent to the SMF5JSAF field in the SMF job termination (type 5) record |
|
SMFACLN |
Length of z/OS accounting section |
A sample program, ORAFMTO, is provided with the Oracle SMF interface to format Oracle accounting SMF records. ORAFMTO is an Assembler program that reads and formats SMF accounting records with the default Oracle type of 204. It reads records from a variable-blocked sequential data set and writes the formatted records to a fixed-block sequential data set with a logical record length of 133.
The sample ORAFMTO program is in the Oracle SRCLIB library. The following members are included:
Table 10-11 lists and describes the SMF record values:
Table 10-11 SMF Record Values
| Value | Description |
|---|---|
|
SSN |
OSDI Subsystem name |
|
SERVICE |
OSDI Service name |
|
SMFAUTH |
Authorization id = TSO logon id Batch user id on jobcard CICS USERID, TERM-ID,TRANS-ID, PROGRAM-ID, or OPID |
|
SMFCONN |
Connection type (TSO,BATCH,CICS,VTAM,TCP/IP, IMS) |
|
Oracle ID |
Oracle user id. (This field is blank if the connection is not associated with a user id.) |
|
DATE |
Start date of Oracle session |
|
TIME |
Start time of Oracle session |
|
CPU SECONDS |
Total CPU seconds used in the Oracle address space (SMFXMCPU) |
|
LOG READS |
Count of logical reads |
|
PHY READS |
Count of physical reads |
|
LOG WRITES |
Count of logical writes |
|
DMC |
Data Manipulation Language Commits |
|
DMR |
Data Manipulation Language Rollbacks |
|
DED |
Deadlocks |
|
HI STG |
High-water mark of main storage used by the session |
If any of the values are too large for the precision their column allows, then they are shown as a series of asterisks.
The following is sample output from the ORAFMTO program:
SSN SERVICE SMFAUTH SMFCONN ORACLE ID DATE TIME CPU SECONDS LOG READS PHY READS LOG WRITES DMC DMR DED HI STG ---- -------- -------- -------- -------------- ------ ----------- -------------- ---------- ---------- ---------- --- --- --- ------ ORA1 ORA1O8 MJJONES BATCH SYS 00.259 08:06:25.82 1.258847 5396 220 33 0 0 0 955K ORA1 ORA1O8 MJJONES TSO SCOTT 00.259 08:07:16.43 1.604269 1611 1037 8 0 0 0 673K ORA1 ORA1O8 MJJONES TSO SYSTEM 00.259 08:10:35.49 .318189 614 17 97 3 1 0 675K ORA1 ORA1O8 MJJONES BATCH SCOTT 00.259 10:12:33.04 .580421 3006 96 23 0 0 0 772K
Oracle Database for z/OS allows system-wide audit records to be written to operating system audit trails. Oracle Database for z/OS uses the System Management Facility (SMF) as its operating system audit trail. This section describes z/OS-specific considerations for defining and using an operating system audit trail. Refer to Oracle Database Concepts and Oracle Database Administrator's Guide for additional information on auditing.
Two steps must be performed before SMF audit recording can take place:
Two INITORA parameters must be specified.
SMF recording by the Oracle database instance must be enabled.
Until both steps are completed no SMF auditing will occur.
Step 1: Specify INITORA Parameters AUDIT_TRAIL. and AUDIT_FILE_DEST
AUDIT_TRAIL=OS is required to inform Oracle Database for z/OS that operating system auditing is desired. AUDIT_FILE_DEST is required to indicate the desired SMF record type.
When specifying a record type, you must select a user SMF record type that does not conflict with any other user record types. This includes the Oracle Database for z/OS accounting record type described in "Specifying the Oracle Record Type", earlier in this chapter. For example, specifying AUDIT_FILE_DEST=205 causes Oracle Database for z/OS audit records to be written to SMF record type 205.
Step 2: Enable SMF Recording by the Oracle Database for z/OS Instance
There are two ways to accomplish this:
You can use the SMFPRMxx member of SYS1.PARMLIB; or
You can use the z/OS SETSMF command.
For example, issuing the following SETSMF command would cause subsystem ORA1 to begin writing SMF records to user type 205.
SETSMF SUBSYS(ORA1,TYPE(205))
Continuing the example, if you decide also to write Oracle Database for z/OS accounting records to SMF and use the recommended SMF record type of 204 for those records, then the following SETSMF command activates SMF recording for both record types.
SETSMF SUBSYS(ORA1,TYPE(204,205))
To interpret Oracle audit SMF records, you first need to dump the SMF data set to a sequential data set. You can then write a program to read the sequential data set and extract the desired records from it. A sample program to extract and print the audit records is provided in member ORAFMTAO of the installed Oracle Database for z/OS SRCLIB data set. It refers to the copy file ORASMFAO, containing DSECTS that map and document the Oracle audit SMF record fields. The contents of the Oracle audit data are defined by Oracle.
You can customize ORAFMTAO for use in your installation. If you select an SMF record type other than 205, then update the value of the ORAREC constant to match your chosen SMF record type and reassemble the program. SRCLIB member ORAFTACL contains sample JCL to assemble and link ORAFMTAO. Sample JCL to run ORAFMTAO is provided in SRCLIB member ORAFTAGO.
