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

How to perform Oracle 11g Data Guard Switchover and Switchback