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：[e-mail]