Oracle 11g Data Guard Role Transitions: Switchover

Role Transitions Involving Physical Standby Databases A database operates in one of the following mutuallyexclusive roles: primary or standby . Data Guard enables you to change theseroles dynamically by issuing the SQL statements described

Role Transitions Involving Physical Standby Databases

 

A database operates in one of the following mutuallyexclusive roles:primary or standby. Data Guard enables you to change theseroles dynamically by issuing the SQL statements described in this chapter, orby using either of the Data Guard broker's interfaces. Oracle Data Guardsupports the following role transitions:

 

• Switchover
  Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.

 

• Failover
  Changes a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected.

 

 

Performing aSwitchover to a Physical Standby Database

 

Step1   Verify that the primary database can be switched to thestandby role.

Query the SWITCHOVER_STATUS column of the V$DATABASEview on the primary database.For example:

 

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME      SWITCHOVER_STATUS

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

READWRITE           MAXIMUM PROTECTION   PRIMARY          prod                 TO STANDBY

 

A value of TO STANDBY or SESSIONS ACTIVE indicatesthat the primary database can be switched to the standby role. If neither ofthese values is returned, a switchover is not possible because redo transportis either misconfigured or is not functioning properly.

 

Step2   Initiate the switchover on the primary database.

 

Issue the following SQL statement on the primarydatabase to switch it to the standby role:

SQL>alter database commit to switchover to physical standby with session shutdown;

 

This statement converts the primary database into aphysical standby database. The current control file is backed up to the currentSQL session trace file before the switchover. This makes it possible toreconstruct a current control file, if necessary.

 

The WITH SESSION SHUTDOWN clause can be omitted fromthe switchover statement if the query performed in the previous step returned avalue of TO STANDBY.

 

Step3   Shut down and then mount the former primary database.

SQL>startup mount;

At this point in the switchover process, the originalprimary database is a physical standby database

 

Step4   Verify that the switchover target is ready to be switched tothe primary role.

Query the SWITCHOVER_STATUS column of the V$DATABASEview on the standby database.

 

SQL>set linesize 200

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

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

READONLY WITH APPLY MAXIMUM PROTECTION  PHYSICAL STANDBY standby                        TO PRIMARY

 

 

A value of TO PRIMARY or SESSIONS ACTIVE indicatesthat the standby database is ready to be switched to the primary role. Ifneither of these values is returned, verify that Redo Apply is active and thatredo transport is configured and working properly. Continue to query thiscolumn until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

 

Step5   Switch the target physical standby database role to theprimary role.

Issue the following SQL statement on the targetphysical standby database:

SQL>alter database commit to switchover to primary with session shutdown;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

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

MOUNTED              MAXIMUM PROTECTION   PRIMARY          standby                        NOT ALLOWED

 

Step6   Open the new primary database.

 

SQL>alter database open;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

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

READWRITE           MAXIMUM PROTECTION   PRIMARY          standby                        TO STANDBY

 

Step7   Start Redo Apply on the new physical standby database.

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

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

MOUNTED              MAXIMUM PROTECTION   PHYSICAL STANDBY prod                           RECOVERY NEEDED

 

SQL>alter database open read only;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

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

READONLY            MAXIMUM PROTECTION   PHYSICAL STANDBY prod                           RECOVERY NEEDED

 

SQL>alter database recover managed standby database using current logfiledisconnect from session;

 

 

Step8   Restart Redo Apply if it has stopped at any of the otherphysical standby databases in your Data Guard configuration.

 

SQL>alter database recover managed standby database using current logfiledisconnect from session;

 

On new primary:

SQL>SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

MAX(SEQUENCE#)    THREAD#

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

            82          1

SQL>SELECT  DESTINATION, STATUS,ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE  STATUS 'DEFERRED'  AND STATUS 'INACTIVE';

 

DESTINATION                    STATUS    ARCHIVED_THREAD# ARCHIVED_SEQ#

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

/u01/arch1/STANDBY             VALID                    1            82

prod                           VALID                    1            82

作者：xiangsir

QQ：444367417

MSN：xiangsir@hotmail.com