Dev's Weblog

Reading time: 2 - 4 minutes

Its really necessary to have dataguard instances started upon reboot of machine but the problem is that how system will decide which one is primary and which one is standby.

Oracle does not have any solution for so far.

Here is the procedure to have DG instances started across reboot automatically.

• First we need to enable the auto start for both instance like we do for standalone database.

Create script called /etc/init.d/dbora with following contents.

#! /bin/bash
#
# oracle Start/Stop the Databases...
#
# chkconfig: 345 99 10
# description: DG auto start
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid

# Source function library.
. /etc/init.d/functions

RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/10.2.0/db_1/"

# See how we were called.

prog="oracle"

start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/dbora

return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"

}

restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?

• chmod 755 /etc/init.d/dbora ( on both machine )
• chkconfig --add dbora ( on both machine )
• Modify /etc/oratab file for both instance to Y at the end.

For example :

chicago:/u01/app/oracle/product/10.2.0/db_1:Y ( Primary DB )

boston:/u01/app/oracle/product/10.2.0/db_1:Y  ( Standby DB )

• Login to Primary DB and make sure its in sync with Standby DB

Create trigger on primary DB which will be automatically propagated on standby database.

CREATE OR REPLACE TRIGGER SYS.DB_ROLE
AFTER STARTUP ON DATABASE
DECLARE
CTL varchar(10);
BEGIN
   SELECT CONTROLFILE_TYPE INTO CTL FROM V$DATABASE;
IF CTL = 'STANDBY' THEN
execute immediate 'shutdown immediate';
execute immediate 'startup mount';
execute immediate 'alter database recover managed standby database using current logfile disconnect from session';
END IF ;
END DB_ROLE;

• Reboot both node and you will see that your standby db will started in Read Only mode first then it will bounced and restarted in Mount mode with Managed Recovery on.