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:
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
