Performing a Switchover to a Physical Standby Database

Overview steps:

Step:1 Check the db name, open mode, database role of the primary and standby databases.

Step:2 Select switchover status on primary & standby db.

Step:3 Check that there is no active users connected to the databases.

Step:4 Switch the current online redo log file on primary database and verify that it has been applied in the standby database.

Step:5 Connect with primary database and initiate the switchover.

Step:6 Bounce the primary db and check the switchover status.

Step:7 Then convert the physical standby into primary db.(stop the MRP process)

Step:8 Open the new standby db in read only mode.

Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.

Step:1 Check the db name, open mode, database role of the primary and standby databases.

Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine. Keep querying below

sqlplus sys/sys@proddb as sysdba

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

NAME      OPEN_MODE      DB_UNIQUE_NAME         DATABASE_ROLE

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

proddb    READ WRITE     proddb                 PRIMARY

Check primary and standby for any gaps

On primary:

===========

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

On standby:

===========

SQL> select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

Convert primary to standby: We will first convert primary to standby and later standby to primary

On primary:

===========

SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> startup mount;

Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary

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

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

SQL> select SWITCHOVER_STATUS from V$DATABASE;

SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

Check client query: Check the query you executed in step 4 on client, it must get executed

On new standby – Initially your primary database: Start MRP

SQL> alter database recover managed standby database disconnect;

Revert back: Once again follow the above process from top and re-execute steps in proper databases to revert back to original setup.