Sunday 30 October 2011

Oracle 11gR2 Dataguard Switchover to Physical Standby Database

Overview:
This article is a comprehensive guide for the switchover of primary database to physical standby datbase in a Dataguard configuration with Database 11g Release 2 (11.2.0.1.0) x64 on the Oracle Enterprise Linux 5 (OEL5) x64 Operating System Environment.
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official dataguard configuration guide for Oracle Database 11g.

 
 
Dataguard Configuration:
For configuration of Dataguard, please visit the below mentioned link:
www.sensehaze.com

 For the screenshot of the steps for switchover activity, please visit the link::  www.sensehaze.com


 Switchover to Physical Standby Database:
Connect to the primary database (DG1) and fire the below mentioned query:
SQL> select name , database_role from v$database ;

Similarly connect to the standby database (DG2) and fire the below mentioned query:
SQL> select name , database_role from v$database ;

Then query "V$DATABASE" for finding whether the primary database (DG1) is capable of performing a switchover. Query "V$DATABASE" for the value of the "SWITCHOVER_STATUS" column. As shown below:
SQL> select switchover_status from v$database ;

If the "SWITCHOVER_STATUS" column’s value is anything other than "TO STANDBY", it is not possible to perform the switchover. If the column’s value is "SESSIONS ACTIVE", we should terminate active user sessions.
If the "SWITCHOVER_STATUS" column's value is "TO STANDBY", then we can initiate its transition to the physical standby database role with the following command:
SQL> alter database commit to switchover to physical standby;

If switchover is successful then after few moments the sql prompt will be returned to us without any error.
Then query "V$DATABASE" of the standby database (DG2). Query "V$DATABASE" for the value of the "SWITCHOVER_STATUS" column. As shown below:
SQL> select switchover_status from v$database ;


For performing switchover of the standby database to primary, fire the below mentioned command:
SQL> alter database commit to switchover to primary ;
As shown in the below screenshot:

For checking the database role (DG2), fire the below mentioned command:
SQL> select name , database_role from v$database ;
As shown in the below screenshot:

To open the new Primary database(DG2), fire the below mentioned command:
SQL> alter database open ;


Shutdown the Standby Database (Prevoius Primary (DG1)) and start it in mount mode and place it in recovery mode. For doing so, fire the below mentioned command:
SQL> shutdown immediate ;
SQL> startup nomount
SQL> alter database mount standby database ;
SQL> alter database recover managed standby database disconnect ;

To check the new standby database(DG1) "current log sequence", fire the below mentioned command:
SQL> archive log list ;


For the screenshot of the steps for switchover activity, please visit the link::  www.sensehaze.com




No comments:

Post a Comment