Data Guard graceful switchover and switchback

Assume, that INSTP is the primary and INSTS is the standby Instance and DG_INSTP and DG_INSTS are the related DG-Broker:

Prerequisites

• Primary database is open
• Standby database is mounted
• Data guard configuration is available (including configuration of listener.ora and tnsnames.ora on both primary and standby database)
• Transport and takeover of redo log from primary to standby data guard is working
• Flashback is enabled
• Database startup trigger is defined to set database service name
• File tnsnames.ora on clients site is configured correctly

Check appropriate configuration of data guard

Start data guard management tool dgmgrl:

$ dgmgrl
DGMGRL> connect sys/<password>@DG_INSTP
DGMGRL> show configuration verbose;

Make sure, that the configuration is enabled, the protection mode is "MaxPerformance", settings of primary and physical standby database are correct.

Test manageability and function of physical standby database (make sure that redo apply is working)

Connect to database (sqlplus, toad or any other db-tool):

$ sqlplus sys/<password>@INSTP
sql> select max(sequence#) sequence from v$archived_log;

On server of standby database start sqlplus:

$ sqlplus sys/<password>@INSTS
sql> select max(sequence#) sequence from v$archived_log;

Verify that the sequence number on the standby database has the same or higher value than the sequence number of the primary database (regard: the application is still running).

Stop scheduler and jobs

Connect to database (sqlplus, toad or any other db-tool):

$ sqlplus sys/<password>@INSTP
sql> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
sql> select value from v$parameter where name = 'job_queue_processes';
sql> alter system set job_queue_processes=0;

Attention: notice the value of job_queue_processes and keep it well!

Switch roles in data guard

Force log file switch

$ sqlplus sys/<password>@INSTP
sql> alter system switch logfile; (3x)
sql> select max(sequence#) sequence from v$log;

Notice the sequence.

Verify that the latest logfile has arrived the standby database (perhaps wait some time and repeat)

$ sqlplus sys/<password>@INSTS
sql> select max(sequence#) sequence from v$log;

Sequence must be the same like the one above.

Start data guard management tool dgmgrl:

$ dgmgrl
DGMGRL> connect sys/<password>@DG_INSTP
DGMGRL> shutdown immediate;
DGMGRL> connect sys/<password>@DG_INSTS
DGMGRL> failover to INSTS;

Reinstate old configuration

Start data guard management tool dgmgrl:

$ dgmgrl
DGMGRL> connect sys/<password>@DG_INSTP
DGMGRL> startup mount
DGMGRL> connect sys/<password>@DG_INSTS
DGMGRL> reinstate database INSTP;
DGMGRL> switchover to INSTP;

Start scheduler and jobs

Connect to database (sqlplus, toad or any other db-tool):

$ sqlplus sys/<password>@INSTP
sql> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
sql> alter system set job_queue_processes=<value>;