Thursday, August 23, 2012

DataGuard and Oracle Restart - how to make it work

If you are going to implement Oracle Data Guard together with Oracle Restart you should be aware that there is a configuration problem in version (and probably in other 11.2. as well). Oracle Restart is not checking what is a current database role and it is going to start standby instance in OPEN mode. This can end up with license issue if your Data Guard Broker will start applying process on standby database. If you don't have license to use Active Data Guard you just broken your license agreement.

There is couple of possible solutions:
  • manually set up "mount" state as start up mode for standby database and change it after every switch- or fail-over
  • disable MRP functionality on standby database and keep it open in read only mode 
  • add your own script to open primary database only and keep standby in "mount" state
I want to describe last solution and share script which I have created for it.
  1. First of all you have to change your Oracle Restart configuration to open both databases (standby and primary) in "mount" state using following command.  
    [oracle@testdb1 ~] srvctl modify database -d database_name -s MOUNT
  2. In next step user script has to be created - you can use this one as an template. I have based it on CRS demo script.Script has been save in /tmp directory on both server using name "opendb"
    # These messages goes into the CRSD agent log file.
    echo " *******   `date` ********** "
    echo "Action script '$_CRS_ACTION_SCRIPT' for resource[$_CRS_NAME] called for action $1"
    #setup database home
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
    #setup database SID
    export ORACLE_SID=testa
    # check database role and return following values
    # OPENED if DB is open and it is primary
    # PRIMARY if DB is mounted and DB role is primary
    # PHYSICAL is DB is mounted and DB role is standby
    function getrole() {
    $ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
         spool /tmp/getrole.tmp
         set echo off feedback off head off
         select case when OPEN_MODE = 'READ WRITE' then 'OPENED' when OPEN_MODE='MOUNTED' and DATABASE_ROLE='PRIMARY' then 'PRIMARY' when OPEN_MODE='MOUNTED' and DATABASE_ROLE='PHYSICAL STANDBY' then 'PHYSICAL' end from v\$database;
    DBROLE=`cat /tmp/getrole.tmp | sed 's/[ \t]*$//' | sed 's/^[ \t]*//' | tail -1`
    case "$1" in
         echo "START entry point has been called.."
         # check role and do following actions
         case $DBROLE in
           'PHYSICAL')   echo "This is standby - do nothing" ;;
         $ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
         alter database open;
         echo ;;
         exit 0
         echo "STOP entry point has been called.."
         exit 0
        echo "CHECK entry point has been called.."
        if [ "$DBROLE" == 'OPENED' ] || [ "$DBROLE" == 'PHYSICAL' ]; then
            echo "Check -- SUCCESS"
            exit 0
            echo "Check -- FAILED"
            exit 1
         echo "CLEAN entry point has been called.."
         exit 0
  3. Register user script in Oracle Restart and set up dependencies using following command. Ora.testa.db is my database resource name in CRS - please change to your database resource name. New resource is called "ora.opendb" and it has to be registered on both servers and started.There is hard dependency between my resource and database resource so my script will be started only when database has been started as well.
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl add resource ora.opendb -type cluster_resource \
    > ,START_DEPENDENCIES=hard(intermediate:ora.testa.db)"
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl start resource ora.opendb
  4. Check status of resources.
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl status res -t
    NAME           TARGET  STATE        SERVER                   STATE_DETAILS
    Local Resources
                   ONLINE  ONLINE       testdb1
                   OFFLINE OFFLINE      testdb1
    Cluster Resources
          1        OFFLINE OFFLINE
          1        OFFLINE OFFLINE
          1        ONLINE  ONLINE       testdb1
          1        ONLINE  ONLINE       testdb1
          1        ONLINE  ONLINE       testdb1                  Open

When everything is deployed is it running in following way: Oracle Restart is starting both data bases in mount state and then next resource (user script) is started. Script is checking current database role and it is opening primary database and doesn't do any action on standby database. 

Please check script in your development and test environment before you deploy it and change anything in production environment. This script has been created for my personal tests and there is no guarantee that it is bug free.