Switch over:

It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.

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.

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

NAME              OPEN_MODE     DATABASE_ROLE
--------- -------------------- ----------------
CANADA            READ WRITE       PRIMARY

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

NAME             OPEN_MODE      DATABASE_ROLE
--------- -------------------- ----------------
CANADA           READ ONLY     PHYSICAL STANDBY

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

SQL>select osuser, username from v$session;

       OSUSER                                 USERNAME
--------------------------------    -------------------------
       oracle                                  oracle
                                               sys

Step:4 Check the log sequence number in primary and standby db(before switchover).

Step:3 In this step primary db is converted into standby by giving the following command.

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

Database altered

SQL>alter database mount standby database;

Database altered

Step:5 Check name, open_mode, database_role of new standby database.

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

NAME      OPEN_MODE            DATABASE_ROLE    
--------- -------------------- ---------------- 
CANADA   READ ONLY             PHYSICAL STANDBY

Step:6 Then start to apply the redo log (MRP process) on primary(current standby).

SQL>alter database recover managed standby database disconnect from session;

Database altered

Step:7 Check the log sequence number in the current standby.

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            10

Step:8 Now we have to stop the MRP process in old standby.

SQL> alter database recover managed standby database cancel;

Database altered.

Step:9 Conversion of standby to primary db and also check the name, open mode, database role of standby db.

alter database commit to switchover to primary with session shutdown;

Database altered.

Step:10 Check name, open_mode, database_role of new primary database.

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

NAME      OPEN_MODE            DATABASE_ROLE    
--------- -------------------- ---------------- 
CANADA   READ WRITE             PRIMARY

Switch over activity has been completed successfully…..!!!

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8