Oracle Data Guard Switchover and Switchback

 

Here’s a step-by-step guide for Oracle Data Guard Switchover and Switchback (role reversal) operations in a 19c environment, using SQL (manual) method. A switchover changes roles between the primary and standby database with no data loss, while a switchback restores them to original roles after maintenance.

🔹 Prerequisites for Switchover

1. Ensure both databases are in sync.
2. Verify redo transport and apply services are working.
3. Confirm no archive log gaps between primary and standby.

🔸 Step 1: Check Database Roles and Readiness

On both databases (Primary and Standby):

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

• On primary: SWITCHOVER_STATUS should be TO STANDBY
• On standby: SWITCHOVER_STATUS should be TO PRIMARY

If not, sync redo logs using:

SQL> select thread#, max(sequence#) from v$log_history group by thread#;
SQL> select sequence#, applied from v$archived_log order by sequence#;

🔸 Step 2: Verify Switchover Readiness

On Primary:

SQL> alter database switchover to STANDBY_DB_UNIQUE_NAME verify;

If no error appears, proceed.

🔸 Step 3: Perform Switchover (Primary → Standby)

On Primary:

SQL> alter database switchover to STANDBY_DB_UNIQUE_NAME;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;

🔸 Step 4: Activate Standby as New Primary

On Standby:

SQL> alter database open;

(If using multitenant, open PDBs too:)

SQL> alter pluggable database all open;

Verify:

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

Now this instance is PRIMARY.

🔸 Step 5: Confirm Role Change

On both databases:

On new PRIMARY:

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

→ Should show READ WRITE and PRIMARY

On former PRIMARY:

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

→ Should show MOUNTED and PHYSICAL STANDBY

🔹 Switchback (Restoring to Original Roles)

Once maintenance is complete, repeat the same procedure but reverse the database names.

Step 1: On current Primary

SQL> alter database switchover to ORIGINAL_PRIMARY_DB_NAME verify;
SQL> alter database switchover to ORIGINAL_PRIMARY_DB_NAME;
SQL> shutdown immediate;

Then start instance as standby:

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

Step 2: On current Standby (the former Primary)

SQL> alter database open;
SQL> alter pluggable database all open;

🔸 Step 3: Validate Final Roles

On both databases:

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

Expected results:

Database	OPEN_MODE	DATABASE_ROLE
PRIMARY	READ WRITE	PRIMARY
STANDBY	MOUNTED	PHYSICAL STANDBY

These are the manual SQL-based commands. If you use Data Guard Broker (DGMGRL), the process is even simpler:

DGMGRL> show configuration;
DGMGRL> validate database mydb;
DGMGRL> switchover to standby_db;
-- Later, to switch back:
DGMGRL> switchover to primary_db;
DGMGRL> show configuration;