Data Guard Physical Standby Switchover using SQLPlus

1. Verify Managed Recovery is running on the standby:

On Standby:

SQL> select process from v$managed_standby where process like 'MRP%';

PROCESS

-------------

MRP0

 

On Primary:

SQL> select recovery_mode from v$archive_dest_status where dest_id=2;

RECOVERY_MODE

-------------------------------------

MANAGED REAL TIME APPLY

 

2. Verify there are no Gaps:

On 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 ('stby_dbunique');

 

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;

 

On Standby:
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;

 

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

 

3. 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;

 

4. 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;

 

5. 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;

 

6. Create Guaranteed Restore Points

On Standby:

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;

 

On Primary: 

SQL> create restore point SWITCHOVER_START_GRP guarantee flashback database;

 

7. Verify that the primary database can be switched to the standby role.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

-----------------------------

SESSIONS ACTIVE

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role.

 

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.

8. If The Primary is a RAC, then shutdown all secondary primary instances
9. Switchover the Primary to a standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

 

10. Verify that the standby database can be switched to the primary role.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role.

 

11. Switchover the Standby to a primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> ALTER DATABASE OPEN;

 

12. Restart the new standby

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> alter database open;

If the database is a RAC, then start all secondary instances on the new standby.

 

13. Check Primary and New Standby:

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

 

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

DGMGRL> drop restore point SWITCHOVER_START_GRP;

 

15. Reset Jobs

Set the job queue processes to its original value on the new standby.

SQL> alter system set job_queue_processes=<value> scope=both sid='*';

SQL> show parameter job_queue_processes

 

Document:

Doc ID 1304939.1