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