Manual data guard switchover in Oracle: In this article we will explain about how to perform Manual Data Guard switchover in Oracle from primary to physical standby database manually.
In a Data Guard environment two types of roles primary database and physical standby database are there we can altered these roles without any data lose or resetting logs called switchover. This switchover activity is a planned activity.
Environment:
Here we perform the Data Guard manual Switchover for our Standalone Data Guard environment. Please check below points for Primary and Physical Standby side details:
Primary:
Platform : Oracle Enterprise Linux Server release 7.9
Server Name : primary.localdomain, IP: 192.168.43.92
DB Version : Oracle 19c Standalone with ASM
File system : +ASM (Automatic Storage Management)
ORACLE_SID : ORCL
Database Name : ORCL
DB_UNIQUE_NAME : ORCL
Oracle Home Path: /u01/app/oracle/product/19c/dbhome_1
GRID Home Path : /u02/app/grid/product/19c/gridhome_1
Standby: (Physical Standby)
Platform : Oracle Enterprise Linux Server release 7.9
Server Name : standby.localdomain, IP: 192.168.43.17
DB Version : Oracle 19c Standalone ASM (Physical Standby)
File system : +ASM (Automatic Storage Management)
ORACLE_SID : ORCL
Database Name : ORCL
DB_UNIQUE_NAME : ORCLDR
Oracle Home Path: /u01/app/oracle/product/19c/dbhome_1
GRID Home Path : /u02/app/grid/product/19c/gridhome_1Prechecks for Switchover:
For data guard switchover prechecks you required to check the db name, open mode, database role, switchover status, database sync status of the primary and physical standby database.
Primary Side:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCL PRIMARY READ WRITE TO STANDBY
SQL> select thread#,max(sequence#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 111
SQL>
Execute below command in primary database for checking your Data Guard is ready for switchover or not. If the output showing any error then, please resolve the issue before the Switchover activity.
SQL> alter database switchover to ORCLDR verify;
Database altered.
SQL> Standby Side:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED NOT ALLOWED
SQL> select thread#,max(sequence#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 111
SQL>
Proceed Switchover process:
Perform switchover on primary Side:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCL PRIMARY READ WRITE TO STANDBY
SQL>
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 805304048 bytes
Fixed Size 9139952 bytes
Variable Size 293601280 bytes
Database Buffers 494927872 bytes
Redo Buffers 7634944 bytes
SQL>
SQL> alter database mount standby database;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCL PHYSICAL STANDBY MOUNTED RECOVERY NEEDED
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCL PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL>Perform switchover on standby Side:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PRIMARY MOUNTED NOT ALLOWED
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PRIMARY READ WRITE TO STANDBY
SQL>
Finally, Check archive sync status on both server:
Check status on current Primary Database:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PRIMARY READ WRITE TO STANDBY
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 113
SQL>
Check status on current Standby Database:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCL PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 113
SQL>
After some logfile switches check sync status:
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
ORCL ORCLDR PRIMARY READ WRITE TO STANDBY
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 113
SQL>
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
Current primary database sync status after some logfile switches:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 116
SQL>
Current standby database sync status after some logfile switches:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 116
SQL>
To perform Manual Data Guard switchback in oracle. (click here)
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.