SAPTechno

Note 1139623 - Using transaction RZ20 to monitor remote Oracle databases

Header
Version / Date 14 / 2011-01-14
Priority Correction with medium priority
Category Program error
Primary Component BC-DB-ORA-CCM CCMS/Database Monitors for Oracle
Secondary Components

Summary
Symptom

As of SAP Basis Release 7.00 Support Package 15, you can use transaction RZ20 to monitor remote Oracle databases. This note provides an overview of this.

Other terms

RZ20, DBA Cockpit, remote, monitoring, Oracle, DBACOCKPIT

Reason and Prerequisites

.

Solution

Prerequisites:
To monitor remote Oracle databases, they must be entered in system management of the DBA cockpit and have the status "Active". This means that at least a secondary data connection must be configured for the relevant system. You can connect both ABAP (4.6C or higher) systems and non-ABAP (Java) systems. You must use at least Version 9 of the Oracle database.
Otherwise, the prerequisites that are also listed in Note 1028624 (Overview of DBA Cockpit for Oracle) apply:

    1. The database user who is to be entered for the database connection must be the relevant schema owner, in other words, SAPR3, SAP<SID>, SAPSR3 or SAPDB<SID>. In MCOD systems, you must select the database user who is the owner of tables SDBAH and SDBAD and in which the results of the BR*Tool actions are saved.
    2. In addition, at least BR*Tools 700 patch level 32 (Oracle client 10) or BR*Tools 640 patch level 42 (Oracle client 9) must be installed on the remote system. If you use BR*Tools 640, you must use sqlplus to create the following synonyms:

              CREATE PUBLIC SYNONYM SAP_DBSTATC FOR < SAPSCHEMAOWNER>.DBSTATC; CREATE PUBLIC SYNONYM SAP_DBCHECKORA FOR <SAPSCHEMAOWNER>.DBCHECKORA;

                   CREATE PUBLIC SYNONYM SAP_DBMSGORA FOR <SAPSCHEMAOWNER>.DBMSGORA;

    3. For the monitoring attribute "Archiver destination full" (in Backup/restore -> Archiving), you also need to configure either an RFC ABAP connection (only allowed for remote ABAP systems) or a connection through the SAP gateway or a remote shell. For more information, see Note 1025707.
    4. In the remote system, the tables DBCHECKORA and DBMSGORA must exist and the table DBCHECKORA must be filled with the relevant contents. In ABAP systems, the tables exist or are filled as standard. On the other hand, for non-ABAP (Java) systems, you must use SQL script to create and fill both of these tables (See the section "Manual steps for non-ABAP (Java) systems").


In addition, you must implement the corrections attached to this note. This will eliminate certain errors in the ABAP source code that will be corrected in SAP Basis Release 7.00 Support Package 16.

Further corrections for the monitoring in transaction RZ20 that occur in Basis 7.00 Support Package 15 and 16 and are corrected only with Support Package 17:

  • The corrections from Note 1168920 (Terminatn in SOREM_GET_TABLESPACES w/ RZ20 Oracle Monitoring) must be implemented.
  • The corrections from Note 1173713 (ORA-00904 in RZ20 Oracle remote monitoring with Version 9.X) must be implemented.


Further corrections for the monitoring in transaction RZ20 that occur in Basis 7.00 Support Package 15, 16, and 17 and are corrected only with Support Package 18:

  • The corrections from Note 1251725 (Error in DBACOCKPIT and RZ20 for dual-stack systems) must be implemented.


Further corrections for the monitoring in transaction RZ20 that occur in Basis 7.00 Support Package 15, 16, 17, and 18 and are corrected only with Support Package 19:

  • The corrections from Note 1272451 (DBA Cockpit: Missing values in remote DB monitoring in RZ20) must be implemented.


Further corrections for the monitoring in transaction RZ20 that occur in Basis 7.00 Support Package 15, 16, 17, 18, and 19 and are corrected only with Support Package 20:

  • Implement the corrections from Note 1339167 (DBA Cockpit: Errors for system IDs with lower case).



Activating monitoring
You can use the checkbox 'Collect Alert Data' in system management of the DBA cockpit to activate or deactivate monitoring for individual systems. (Note: This checkbox is deactivated if the tables DBCHECKORA and DBMSGORA do not exist on the remote system or if the table DBCHECKORA is not filled (See point 4 in prerequisites).
In addition, when you select the checkbox, the system starts to structure the monitor tree in transaction RZ20 for the affected system or the system deletes the tree. Alternatively, you can use transaction SE38 to call the ABAP program RSDBMON0 or to restart the application server. If you restart the application server, the system automatically runs the report RSDBMON0.

NOTE: If you activate the remote monitoring for many systems,
the size of the shared memory segment used by RZ20 may not be sufficient. As a result, the monitor trees of additional systems are not structured completely in RZ20, and the checkbox selection is removed for these systems. In this case, you must use the profile parameter alert/MONI_SEGM_SIZE to create a larger segment (as described in Note 135503) and then reactivate the remote monitoring for the relevant systems using the checkbox.

CCMS_OnAlert_Email is defined for the individual alerts as the default auto-reaction method. You can change this default value as described in Note 1331861.

You can display the monitor trees for all remote Oracle databases by calling transaction RZ20 and choosing SAP CCMS Monitor Templates -> Remote Databases -> Oracle.

In addition, you can display the monitor tree in the DBA cockpit for the selected system by choosing 'Alerts' -> 'Alert Monitor'.

The structure of the tree is basically the same as the tree that is used to monitor the local database at SAP CCMS Monitor Templates -> Database -> Oracle. Therefore, the properties that are monitored are contained in the subtrees:

Space management
Performance
Backup/restore
Health

  In addition, the tree 'Connection <System>' also exists for remote databases.

Further differences compared to the monitor tree that is for the local database:

  • The tree 'SAP consistency' is not available for remote databases.
  • If LMTS is set for the SYSTEM tablespace, the system no longer displays the following monitor tree elements in Space managements -> Segments:

                       "Segments w. too few allocatable extents"
"Fewest allocatable extents for a segment"
"Segments approaching max_extents"
"Fewest extents left before max_extents"

  • In performance, the following new subtrees exist:

                       1. Active session waits
Here, an alert is triggered if more than 10 ("yellow")
or 20 ("red") Oracle sessions exist at the same time in one of the following wait
states:
Enqueue
Latch
Row cache
Library cache
Log File switch
Cursor

                       2. Reads per user call
If the ratio in "session logical reads"/"user calls" (both values from V$SYSSTAT) exceeds a threshold value, the system issues an alert: > 20: "yellow" or more than 30: "red". Possible solution: SQL statement tuning (Note 766349).

                       3. Wait Events (only for Oracle Version 10 or higher)
This subtree contains certain selected "wait events":

                       3.1 Db File sequential read
Average "wait time":  greater than 10 milliseconds: "yellow" alert; greater than 15 milliseconds: "red" alert
Possible solution: I/O tuning (Note 793113)

                       3.2 Db File seq.+scat.+parallel read
The sum of all three "wait events" is responsible for X percent of the total database time: Greater than 70%: "yellow" alert; greater than 85%: "red" alert.
Possible solution: I/O tuning (Note 793113), SQL statement tuning (Note 766349), enlarging the buffer pool (Note 789011)

                       3.3 Log File file sync
Average "wait time":  Greater than 15 milliseconds: "yellow" alert; greater than 25 milliseconds: "red" alert.
Possible solution: I/O tuning with a focus on LGWR (Note 793113)

                       3.4 latch:
Latch event is responsible for X percent of the total database time: Greater than 5%: "yellow" alert; greater than 10%: "red" alert.
Possible solution: Latch optimization (Note 767414)

                       3.5 enq:
Enqueue event is responsible for X percent of the total database time: Greater than 5%: "yellow" alert; greater than 10%: "red" alert.
Possible solution: Enqueue optimization (Note 745639)

                       3.6 Free buffer waits
Each time this "wait event" occurs, the system issues a "red" alert.
Possible solution: I/O tuning with a focus on DBWR (Note 793113)

                       3.7 Log file switch (checkpoint incomplete)
Each time this "wait event" occurs, the system issues a "red" alert.
Possible solution: Optimizing the redo log layout (Note 79341), I/O tuning with a focus on DBWR (Note 793113).

                       Remark: You can view the threshold values for the performance attributes mentioned above in 1, 2, and 3.1 to 3.5 as guide values. If required, you can choose "Properties" -> "Change" in transaction RZ20 to adjust the relevant attribute in the subtree.


Manual steps for non-ABAP (Java) systems
As mentioned above in point 4 of the "Prerequisites" section, for non-ABAP (Java) systems, you must create the tables DBCHECKORA and DBMSGORA (step 1) and fill the table DBCHECKORA with the check conditions (step 2). In addition, you must also use brconnect to create the synonyms on both tables (step 3).
To do this, download the file scripts_1139623.sar from the attachment and use SAPCAR to unpack it.

To create the tables, use the script rz20_nonabap.sql and then, to fill the table DBCHECKORA, use the script dbcheckora9.sqlf or Oracle 9i, the script dbcheckora10_oltp.sql for Oracle 10g, or the script dbcheckora11_oltp.sql for Oracle 11g.

To run the *.sql scripts, use Oracle SQL*Plus.

Step 1: Create tables DBCHECKORA and DBMSGORA:

  sqlplus sap<schema_id>/<pwd>
  SQL> @rz20_nonabap.sql

Remark: The script rz20_nonabap.sql also creates the tables DBSTATC, DBSTAIHORA, DBSTATHORA, DBSTATIORA, and DBSTATTORA. These tables exist as standard in SAP ABAP systems. These tables are not currently required to monitor a database remotely using transaction RZ20 but they can be used for additional control functions in connection with the BR*Tool brconnect for generating statistics.

Step 2: Fill the table DBCHECKORA:

  sqlplus sap<schema_id>/<pwd>
  SQL> @dbcheckora9
or
  SQL> @dbcheckora10_oltp
or
  SQL> @dbcheckora11_oltp

Step 3: Create the synonyms (as user ora<sid>:

brconnect -u / -c -f crsyn -o sap<schema_id>

Affected Releases
Software Component Release From Release To Release And subsequent
SAP_BASIS70700702
SAP_BASIS71710711
SAP_BASIS71730730

Correction delivered in Support Package
Support Packages Release Package Name
SAP_BASIS700SAPKB70016
SAP_BASIS700SAPKB70023
SAP_BASIS701SAPKB70108
SAP_BASIS702SAPKB70207
SAP_BASIS710SAPKB71008
SAP_BASIS710SAPKB71012
SAP_BASIS711SAPKB71107
SAP_BASIS730SAPKB73002

Related Notes
892294Enhanced support for non-ABAP database in BRCONNECT 7.00
134592Importing the SAPDBA role (sapdba_role.sql)