Cluster Server 7.3.1 Agent for Oracle Installation and Configuration Guide - Solaris

To set up detail monitoring for Oracle

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 sqlplus utility to set up a database table:

   $ORACLE_HOME/bin/sqlplus /nolog

6. As the database administrator, issue the following statements at the sqlplus prompt to create the test table.

   For traditional or container databases:

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

   Note:

   For a CDB resource, the user name should be prefixed with C##.

   To enable detail monitoring for PDB, issue the following statements at the sqlplus prompt to create the test table:

   $ORACLE_HOME/bin/sqlplus /nolog
   connect / as sysdba
   

   Create common user for CDB if not already created.

   create user <User>
   identified by <Pword>
   default tablespace USERS
   temporary tablespace TEMP
   quota 100K on USERS;
   grant connect to <User> container=all;
   alter session set container=PDBName
   create table <User>.<Table> ( tstamp date );
   insert into <User>.<Table> (tstamp) values (SYSDATE);

   Note:

   The VCS Agent for Oracle requires a common user for both CDB and PDB detail monitoring, else detail monitoring will not work for PDB.

7. To test the database table for use, do the following for CDB or traditional databases:

   $ORACLE_HOME/bin/sqlplus /nolog
   connect / as sysdba
   connect  <User>/<Pword>
   update <User>.<Table> set(tstamp)=SYSDATE;
   select TO_CHAR(tstamp, 'MON DD, YYYY HH:MI:SS AM')
   from <User>.<Table>;

   For PDB

   alter session set container=PDBName;
   update <User>.<Table> set (  tstamp ) = SYSDATE;
   select TO_CHAR(tstamp, 'MON DD, YYYY HH:MI:SS  AM') 
   from <User>.<Table>;
   exit
   

8. Enable the detail monitoring for the Oracle resource 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 -override OracleResource LevelTwoMonitorFreq
   # hares -modify OracleResource LevelTwoMonitorFreq 1

   # haconf -dump  -makero

   # hagrp -unfreeze service_group

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