ِData Guard Physical Standby Switchover using the Broker

1. Verify there are no Gaps:

Primary: 
select thread#, max(sequence#) "Last Primary Seq Generated"
from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

 

Physical Stby:
select thread#, max(sequence#) "Last Standby Seq Applied"
from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES')
group by thread# order by 1;

 

Primary: 
select a.db_unique_name , a.dest_name ,a.archived_seq# ,  a.applied_seq#, a.gap_status 
  from v$archive_dest_status a
 where a.db_unique_name in ('BASE1');

 

Physical Stby:

select thread#, low_sequence#, high_sequence# from v$archive_gap;

 

2. Verify all datafiles are online On Target Standby:

SQL> select name from v$datafile where status='OFFLINE';

 

If there are any OFFLINE datafiles, and these are needed after switchover, bring them ONLINE:

SQL> alter database datafile '<DATAFILE_NAME>' online;

 

3. Verify Primary and Standby tempfiles match:

select tmp.name filename, bytes, ts.name tablespace
  from v$tempfile tmp, v$tablespace ts
 where tmp.ts#=ts.ts#;

 

If the queries do not match, then you can correct the mismatch now or immediately after the open of the new primary database;

 

4. Stop Jobs in Primary

SQL> select * from dba_jobs_running;

Depending on what the running job is, be ready to terminate the job if necessary.

 

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> show parameter job_queue_processes

SQL> alter system set job_queue_processes=0 scope=spfile;

 

5. Create Guaranteed Restore Points

 Physical Stby:

SQL> alter database recover managed standby database cancel;

SQL> create restore point SWITCHOVER_START_GRP guarantee flashback database;

SQL> alter database recover managed standby database using current logfile nodelay disconnect from session parallel 10;

 

Primary: 

SQL> create restore point SWITCHOVER_START_GRP guarantee flashback database;

 

6. Check the primary database:

DGMGRL> show database verbose 'primary_dbunique';

 

7. Check the standby database that is the target of the switchover:

DGMGRL> show database verbose 'standby_dbunique';

 

8. Issue the switchover command:

DGMGRL> switchover to 'standby_dbunique';

Switchover always originates from Primary database. On the request of switchover sql statement "alter database commit to switchover to physical standby with session shutdown", Primary will generate special marker called EOR (end-of-redo) that is placed in the header of online redo log sequence. So this online redo log sequence will be archived locally and sent to all standby databases. Only upon receiving and applying EOR (end-of-redo), v$database.switchover_status will change from "not allowed" to "to primary" or "sessions active". At this stage Physical standby is ready to assume Primary database role.

 

9. Show the configuration:

DGMGRL> show configuration;

 

10. Check Primary and Standby:

SQL> select db_unique_name,total_gap,open_mode,database_role from v$database;

 

11. Drop any Switchover Guaranteed Restore Points on all databases.

DGMGRL> drop restore point SWITCHOVER_START_GRP;

 

 

Document:

https://docs.oracle.com/cd/E11882_01/server.112/e40771/sofo.htm#DGBKR330

Doc ID 1305019.1