Setting up detail monitoring for Oracle


Setting up detail monitoring for Oracle
Prev	Setting up detail monitoring for VCS agents for Oracle	Next

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 Cluster Server agent for Oracle. The scripts are available under the directory /opt/VRTSagents/ha/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 agent uses this test table for internal purposes. recommends that you do not perform any other transaction on the test table. 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 example to set up detail monitoring, 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.

To set up detail monitoring for Oracle

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

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.

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

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.