Oracle 11g Data Guard Switchover and Switchback – Active Data Guard Part-II

Published3 years agobyJignesh Jethwa

In my PrevIoUs article we have coveredHow to configured oracle 11g Data Guard on CentOS 6.5,Now we will look into switchover and switch back activity:

After configuring data guard,databases would be either primary and standby database role,and we can altered these roles without loss of data or without resetting logs. called switchover and switch back.

Switchover:

Before performing switchover,kindly verify the state of 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#;
 ...
 ...
 59 08-OCT-14 08-OCT-14 YES
 60 08-OCT-14 08-OCT-14 YES
 61 08-OCT-14 08-OCT-14 YES

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 appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity,we can instantiate switchover activity by issuing following sort of commands:

On Primary database:

Step-1>>
Connect to Primary database and convert primary database to standby.

[oracle@pr ~]$ 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.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 805307128 bytes
 Database Buffers 159383552 bytes
 Redo Buffers 5988352 bytes

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
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

On Standby database:

Step-4>>
On original standby database,Convert old standby database to primary database:

[oracle@dr ~]$ sqlplus / as sysdba

Step-5>>

Convert old standby database as primary and shutdown database:

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

sql> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

Step-6>>

Startup old standby database as primary database:

sql> startup
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 717226744 bytes
 Database Buffers 247463936 bytes
 Redo Buffers 5988352 bytes
 Database mounted.
 Database opened.

Verify database role on old standby database:

sql> select name,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Switch over activity havecompleted successfully,our old primary database has become standby and old standby database has become primary database.

Note

To test above switchover activity,generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

Switchback:
To switchback,you have to follow same above mentioned 6 steps.

Stay Tune.

Share this:

• Tweet
• Share on Tumblr
• Email
• Print

Like this:

Loading...

Related

Steps to configure Manual Data Guard - Oracle 11g Manual Data Guard Part-I

April 27,2016

In "Oracle"

How to open physical standby database in read only mode - Active Data Guard Part -III

October 15,2014

In "Oracle 11g"

Switch over and switch back - Oracle 11g Manual Data Guard Part-II

May 12,2016

In "Oracle"

This entry was posted inOracle 11g,Oracle Replicationand taggedData Guard.

Post navigation

←ORA-16038-log 4 sequence# 10702 cannot be archived-ORA-19809: limit exceeded for recovery files-ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit

How to open physical standby database in read only mode – Active Data Guard Part -III→

6 THOUGHTS ON “ORACLE 11G DATA GUARD SWITCHOVER AND SWITCHBACK – ACTIVE DATA GUARD PART-II”

1. Shrikanton November 14,2014

   Very nice help for Switchover and Switchback

   reply

2. Jignesh Jethwaon November 15,2014

   Thank you Shrikant for writing… Stay Tune.

   reply

3. Sudheeron August 11,2015

   Very helpful steps. Is it going to be the same in a actual DR scenario when the Primary went down unexpectedly?

   reply

   • Jignesh Jethwaon August 11,2015

     Thank you Sudheer for writing!
     Yes,These mentioned steps are used in Actual DR scenario’s.

     Switch over and switchback are the planned activity(like h/w upgrade on primary or any maintenance task) its not unexpectedly happens.
     In case of Primary went down unexpectedly,you need to Failover to the DR database and make DR database as a primary database. Still it is depend on depth of the worst situation on primary database. If Primary database recoverable within RTO than it is okay else you need to Failover.
     Kindly visit onhttp://dbatricksworld.com/oracle-11g-physical-standby-data-guard-failover-steps-data-guard-part-v/to know more about Failover.

     Stay Tune.

     reply

4. IgnatIoUs Enason December 17,2016

   Dear Jignesh,

   Your article is great !!!

   Now i have my standby database in read-only with apply mode.
   Can i still use the same procedure for switchover and failover ? Or should be bring the Standby DB to mount state fist before starting ?

   Thanks in advance.
   IgnatIoUs Enas

   reply

   • Jignesh Jethwaon December 20,2016

     Thank you IgnatIoUs for writing!
     You can choose either way,but i would suggest to go with mount state first before starting.
     Stay Tune.

     reply