Switchover to Physical Standby Database in Oracle

Once the standby database is configured and verified to be working properly, we can switch over to the standby database for testing purposes. This helps to minimize downtime for the primary database, which may be required for various maintenance tasks such as OS upgrades, hardware upgrades, and other issues.

During a switchover from the primary database to the standby database, no data is lost. After the primary database maintenance is complete, we can switch back to the primary database.

In this scenario, the primary database is "NOIDA" and the standby database is "RED." We will perform a switchover from the primary database (NOIDA) to the standby database (RED). Before proceeding with the switchover, we need to check certain prerequisites.

Step-by-Step Switchover Procedure

Step 1: Verify Switchover Possibility

1. On the current primary database, check if a switchover is possible by querying the switchover_status column of the V$DATABASE view:

SQL> select switchover_status from v$database;

1. The value TO STANDBY indicates a switchover is possible.
2. If TO STANDBY is not displayed, ensure all log_archive_dest_n parameter values are correctly specified. If SESSIONS ACTIVE or FAILED DESTINATION is displayed, further investigation is needed.

Step 2: Check for Active Users

2. Ensure no active users are connected to the databases:

SQL> select distinct osuser, username from v$session;

Step 3: Switch the Current Online Redo Log File

3. On the primary database, switch the current online redo log file and verify it has been applied:

SQL> alter system switch logfile; 
System altered.

Step 4: Initiate the Switchover

4. Connect to the primary database and initiate the switchover: This command terminates all active sessions, transmits and applies any non-archived redo log files, adds an end-of-redo marker, and converts the current control file into a standby control file.

SQL> sqlplus sys/xxxx@noida as sysdba 

SQL> alter database commit to switchover to physical standby; 
Database altered.

If ORA-01105 occurs, run the following command:

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

Step 5: Restart the Primary Instance (RED)

5. Shut down and restart the primary instance (now standby):

SQL> shutdown immediate; 
SQL> startup mount;

Step 6: Verify Switchover Status

6. Verify the switchover status on both databases:

On the old primary database (NOIDA):

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

On the old standby database (RED):

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

Step 7: Switch the Standby Database to Primary

7. Change the standby database role to primary:

SQL> alter database commit to switchover to primary; 
Database altered. 

SQL> shutdown immediate; 

SQL> startup;

Step 8: Verify and Open the New Primary Database

8. Check the new primary database (RED) and switch the logfile:

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

SQL> alter system switch logfile;
System altered.

Step 9: Open the New Standby Database (NOIDA) in Read-Write Mode

Open the new standby database in read-write mode:

SQL> alter database open;
Database altered.

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

If RECOVERY NEEDED is displayed, start managed recovery:

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name, open_mode from v$database;
NAME   OPEN_MODE
------ -----------
NOIDA  READ ONLY WITH APPLY

Switchover Status Values:

• Not Allowed: Indicates a configuration issue.
• Sessions Active: Active SQL sessions need disconnection.
• Switchover Pending: Switchover request received but not processed.
• Switchover Latent: Switchover was pending but did not complete.
• To Primary: Standby database ready to switch to primary.
• To Standby: Primary database ready to switch to standby.
• Recovery Needed: Standby database needs to process the switchover request.