ORACLE DATAGUARD SWITCHOVER OPERATION

At this article I’ll present Data Guard Switch OVER. During the run time system if you prefer to  change role of your primary Database to Standby Database you’d need Swicth OVER the Databases without lost of data or resetting of redo logs.

I tested the Switchover first on my testing systems before working on Production.

Verify the primary database instance is open and the standby database instance is mounted.

Make sure that there are no active users connected to the databases.

ON THE PRIMARY QUERY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Make sure you move the all log file. Force a log switch to archive the current online redo log file on primary

SQL> ALTER system switch logfile;

Verify the new redo data was archived on the standby database.

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Make sure (non-broker) on the standby

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

And Make sure there is no GAP

SQL> select status, gap_status

  2  from v$archive_dest_status

  3  where dest_id = 2;

STATUS    GAP_STATUS

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

VALID     NO GAP

Now Switch the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

Shutdown the database.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

Start up the database no mount mode;

SQL> startup nomount;

 

Now mount the database as standby.

SQL> alter database mount standby database;

Database altered.

Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_2=defer;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SWITCHOVER PENDING

ON THE STANDBY QUERY

Switch the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

Shutdown the database.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

….

…

..

Database mounted.

Database opened.

 Now enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable;

So, At last I'm going to start managed recover on the new standby database:

SQL> recover managed standby database disconnect; 

Media recovery complete.