10 Oracle SMF Data

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:

Preparing to Record Oracle Accounting SMF Information
Events that Generate SMF Accounting Records
SMF Accounting Recording under CICS TS
Interpreting an Oracle Accounting SMF Record
Oracle Net Network Information Vector Overview
Sample Formatting Program for SMF Accounting Records
Auditing Database Use

10.1 Preparing to Record Oracle Accounting SMF Information

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.

10.1.1 Specifying the Oracle Record Type

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".

10.1.2 Starting SMF Recording of Oracle Records

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.

10.1.3 Stopping SMF Recording of Oracle Records

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.

10.2 Events that Generate SMF Accounting Records

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.

10.3 SMF Accounting Recording under CICS TS

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.

10.4 Interpreting an Oracle Accounting SMF Record

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).

10.4.1 Contents of the SMF Header Section

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

10.4.2 Contents of the SMF Correlation Section

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.

10.4.3 Contents of the SMF OSDI Data Section

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

10.4.4 Contents of the SMF Database Engine Data Section

Table 10-4 contains brief descriptions for the labels in the SMF database engine section.

Table 10-4 Contents of the SMF Database Engine Section

ORASMF0 Label	Description
SMFLRC	Logical read count
SMFPRC	Physical read count
SMFLWC	Logical writes
SMFDMC	DML COMMITs
SMFDMR	DML ROLLBACKs
SMFDED	DEADLOCKs
SMFHDLN	Length of SMF header

10.4.5 Contents of the SMF Net Data 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".

10.5 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.

10.5.1 General NIV Format

Table 10-6 lists and describes the general NIV format:

Table 10-6 General NIV Format

Byte	Contents
0	Total length of NIV including byte 0
1	ID of NIV
2 through p	NIV data

10.5.2 NIV List Format

Table 10-7 lists and describes the NIV list format:

Table 10-7 NIV List Format

Byte	Contents
0 through 1	Total length of NIV list including bytes 0 and 1
2 through m	First NIV
m+1 through n	Second NIV
n+1 through ...	......
.. .-x	......
x+1 through y	LSast NIV

10.5.3 Net TCP/IP Identification NIV Format

Table 10-8 lists and describes the Net TCP/IP identification NIV format:

Table 10-8 Net TCP/IP Identification NIV Format

Byte	Contents
0	NIV length = x'08'
1	NIV ID = x'03'
2 through 3	TCP port number from which client originated
4 through 7	Internet address on which client resides (hex format)

10.5.4 Contents of the SMF z/OS Accounting Data Section

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

10.6 Sample Formatting Program for SMF Accounting Records

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-10 Members in Sample ORAFMTO Program

Member	Description
ORAFMTCL	contains sample JCL to assemble and link ORAFMTO.
ORAFMTGO	contains sample JCL to run ORAFMTO.
ORAFMTO	extracts and prints values from the Oracle SMF records.

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

10.7 Auditing Database Use

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.

10.7.1 Preparing To Record Oracle Database for z/OS Audit information

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)) 
```

10.7.2 Interpreting Oracle Database for z/OS Audit Records

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.