Setting Up Detail Monitoring for Oracle

Detail monitoring for an Oracle resource verifies whether a database is ready for transactions by performing an update transaction against a table within the database. The update action is taken by the two scripts, SqlTest.pl and SimpleTest.pl, provided with the VCS enterprise agent for Oracle. The scripts are available under the directory /opt/VRTSvcs/bin/Oracle/. Both scripts update the timestamp to monitor the database.

The SqlTest.pl script checks whether the database is open before updating the timestamp. If the database is found to be in restricted mode, quiesced mode, or suspended mode, the monitor returns success. In such a case, only basic monitoring occurs. The SimpleTest.pl script does not perform database checks but only issues update statements against the table.

Before enabling detail monitoring for Oracle, you must create a test table (with a timestamp) in the Oracle database. The detail monitor script, MonScript, must exist and have execute permission for root. You can use a custom monitor script, or the scripts provided with the agent. In the monitor script, the return code 100 indicates failure. Return codes from 101 to 110 indicate success.

The following example, based on the use of the supplied script, shows how to create and test a table for use by detail monitoring, and how to enable detail monitoring.

1. Make the VCS configuration writable:
      haconf -makerw
2. Freeze the service group to avoid automated actions by VCS caused by an incomplete reconfiguration:
      hagrp -freeze service_group
3. Log on as an Oracle user.
      su - <Owner>
4. Set the environment variables for ORACLE_HOME and ORACLE_SID.
      export ORACLE_HOME=<Home>
      export ORACLE_SID=<Sid>
5. Start the svrmgrl or sqlplus utility to set up a database table:
     $ORACLE_HOME/bin/svrmgrl

       or

   
     $ORACLE_HOME/bin/sqlplus /nolog
6. As the database administrator, issue the following statements at the svrmgrl or sqlplus prompt to create the test table:
      connect / as sysdba
   
      create user <User>
      identified by <Pword> 
      default tablespace USERS
      temporary tablespace TEMP  
      quota 100K on USERS;
   
      grant create session to <User>;
   
      create table <User>.<Table> ( tstamp date );
      insert into <User>.<Table> (tstamp) values (SYSDATE);
7. To test the database table for use, do the following:
      disconnect
      connect <User>/<Pword>
      update <User>.<Table> set ( tstamp ) = SYSDATE;
   
      select TO_CHAR(tstamp, 'MON DD, YYYY HH:MI:SS AM')
      from <User>.<Table>;
      exit
8. Detail monitoring for the Oracle resource can be enabled by using the following VCS commands:
      hares -modify OracleResource User User
      hares -modify OracleResource Pword Pword
      hares -modify OracleResource Table Table
      hares -modify OracleResource MonScript "./bin/Oracle/SqlTest.pl"
      hares -modify OracleResource DetailMonitor 1
      haconf -dump -makero
      hagrp -unfreeze service_group

   ---

     Note    You can also use Cluster Manager (Java Console) to set these attributes.

   ---