How to perform Oracle 11g Data Guard Switchover and Switchback

 How to perform Oracle 11g Data Guard Switchover and Switchback 

Switchover:

Before performing switchover, please  verify the state of the data guard on both the instances by following SQL queries:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

 SQL> SELECT sequence#, first_time, next_time, applied

 FROM v$archived_log

 ORDER BY sequence#;

 

 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

 DEST_NAME

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

 STATUS ERROR

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

 LOG_ARCHIVE_DEST_2

 VALID

 SQL> select message from v$dataguard_status;

Note: This command will give you an appropriate message about the data guard current status.

Kick-Off switchover activity by issuing the following  commands:

On Primary database:

Step-1

Connect to Primary database and convert primary database to standby.

 [oracle@funoracle ~]$ sqlplus / as sysdba

 SQL> alter database commit to switchover to standby;

 Database altered.

Step-2

Shutdown primary database:

SQL> shutdown immediate;

Step-3

Startup nomount old primary database as new standby database:

 SQL> startup nomount

 ORACLE instance started.

 

 

 SQL> alter database mount standby database;

 Database altered.

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

 Database altered.

Verify database role on old primary database:

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

 NAME OPEN_MODE DATABASE_ROLE

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

 PROD MOUNTED PHYSICAL STANDBY

 

On Standby database:

Step-4

On the original standby database, Convert the old standby database to the primary database:

 SQL> alter database commit to switchover to primary;

 Database altered.

Step-5

Convert old standby database as primary and shutdown database:

[oracle@funoracledr ~]$ sqlplus / as sysdba

 SQL> shutdown immediate;

 ORA-01109: database not open

 Database dismounted.

 ORACLE instance shut down.

Step-6

Startup old standby database as the primary database:

 SQL> startup

 ORACLE instance started.

 

Verify database role on old standby database:

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

 NAME OPEN_MODE DATABASE_ROLE

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

 PROD READ WRITE PRIMARY

Switch overactivity have completed successfully, our old primary database has become standby and old standby database has become the primary database.

Note

We can test the above switchover activity, by generating multiple archive logs on the primary database and verify those archive logs being transferred on the standby database.

Switchback:

To switchback, we  have to follow the same above-mentioned steps