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 11.2.0.3 (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"
   

   #!/bin/sh
   # 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"
   #env
   #
   
   #setup database home
   export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
   #setup database SID
   export ORACLE_SID=testa
   DBROLE=''
   
   
   # 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;
        exit
   EOF
   DBROLE=`cat /tmp/getrole.tmp | sed 's/[ \t]*$//' | sed 's/^[ \t]*//' | tail -1`
   
   }
   
   
   case "$1" in
     'start')
        echo "START entry point has been called.."
        getrole
        # check role and do following actions
        case $DBROLE in
          'PHYSICAL')   echo "This is standby - do nothing" ;;
          'PRIMARY')
        $ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
        alter database open;
        exit
   EOF
        echo ;;
        esac
        exit 0
        ;;
   
     'stop')
        echo "STOP entry point has been called.."
        exit 0
        ;;
   
     'check')
       echo "CHECK entry point has been called.."
       getrole
       if [ "$DBROLE" == 'OPENED' ] || [ "$DBROLE" == 'PHYSICAL' ]; then
           echo "Check -- SUCCESS"
           exit 0
       else
           echo "Check -- FAILED"
           exit 1
       fi
       ;;
   
     'clean')
        echo "CLEAN entry point has been called.."
        exit 0
        ;;
   
   esac
   

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 \
   > -attr "ACTION_SCRIPT=/tmp/opendb,CHECK_INTERVAL=30,RESTART_ATTEMPTS=2 \
   > ,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
   --------------------------------------------------------------------------------
   ora.LISTENER.lsnr
                  ONLINE  ONLINE       testdb1
   ora.ons
                  OFFLINE OFFLINE      testdb1
   --------------------------------------------------------------------------------
   Cluster Resources
   --------------------------------------------------------------------------------
   ora.cssd
         1        OFFLINE OFFLINE
   ora.diskmon
         1        OFFLINE OFFLINE
   ora.evmd
         1        ONLINE  ONLINE       testdb1
   ora.opendb
         1        ONLINE  ONLINE       testdb1
   ora.testa.db
         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. 

Disclamer:
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.

regards,
Marcin