Role TransitionsInvolving 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 a Failoverto a Physical Standby Database

?

Fault Simulation

?

Original Primary:

SQL>set linesize 200

SQL>? selectOPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUS fromv$database;

?

OPEN_MODE??????????? PROTECTION_MODE????? DATABASE_ROLE??? DB_UNIQUE_NAME???????????????? SWITCHOVER_STATUS

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

READWRITE?????????? MAXIMUM AVAILABILITYPRIMARY????????? prod?????????????????????????? TO STANDBY

?

SQL>exit

Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@prod~]$ ps -ef | grep smon

oracle??? 3915????1? 0 16:20 ???????? 00:00:01 ora_smon_prod

oracle??? 4584?3323? 0 16:57 pts/1??? 00:00:00 grep --color=auto smon

[oracle@prod~]$ kill -9 3915

?

Step1???Flush any unsent redo from the primary database to thetarget standby database.

?

If the primary database can be mounted , it may be possible to flush any unsent archived andcurrent redo from the primary database to the standby database. If thisoperation is successful, a zero data loss failover is possible even if theprimary database is not in a zero data loss data protection mode.

?

Ensure that Redo Apply is active at the targetstandby database.

Standby:

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

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

READ ONLY WITH APPLY MAXIMUM AVAILABILITY PHYSICALSTANDBY standby??????????????????????? TOPRIMARY

?

Primary:

Mount, but do not open the primarydatabase. If the primary database cannot be mounted, go to Step2 .

Issue the following SQL statement at the primarydatabase:

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area? 263639040 bytes

FixedSize????????????????? 1344312 bytes

VariableSize???????????? 230689992 bytes

DatabaseBuffers?????????? 25165824 bytes

RedoBuffers??????????????? 6438912 bytes

Databasemounted.

SQL>alter system flush redo to 'standby';

Systemaltered.

?

?ALTER SYSTEMFLUSH REDO TO target_db_name .

For target_db_name, specify the DB_UNIQUE_NAME of thestandby database that is to receive the redo flushed from the primary database.

This statement flushes any unsent redo from theprimary database to the standby database, and waits for that redo to be appliedto the standby database.

If this statement completes without anyerrors, go to Step5 . If the statement completes with any error,or if it must be stopped because you cannot wait any longer for the statementto complete, continue with Step2 .

?

Step2???Verify that the standby database has the most recentlyarchived redo log file for each primary database redo thread.

Query the V$ARCHIVED_LOG view on the target standbydatabase to obtain the highest log sequence number for each redo thread.

?

Primary and Standby

SQL>SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) ASLAST from V$ARCHIVED_LOG;

?

??? THREAD??????LAST

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

???????? 1???????? 98

standby

If possible, copy the most recently archived redo logfile for each primary database redo thread to the standby database if it doesnot exist there, and register it. This must be done for each redo thread.

?

ALTERDATABASE REGISTER PHYSICAL LOGFILE 'redo_logfile';

?

Step3???Identify and resolve any archived redo log gaps.

?

Query the V$ARCHIVE_GAP view on the target standbydatabase to determine if there are any redo gaps on the target standbydatabase.

SQL>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

norows selected

?

THREAD#???LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
???????? 1??????????? 90???????????? 92

In this example the gap comprises archived redo logfiles with sequence numbers 90, 91, and 92 for thread 1.

If possible, copy any missing archived redo log filesto the target standby database from the primary database and register them atthe target standby database. This must be done for each redo thread.

?

SQL>ALTER DATABASE REGISTER PHYSICAL LOGFILE 'redo_logfile';

?

?

Step4???Repeat Step 3 until all gaps are resolved.

The query executed in Step3 displays information for the highest gaponly. After resolving a gap, you must repeat the query until no more rows arereturned.

If, after performing Step2 through Step4 , you are not able to resolve all gaps in thearchived redo log files (for example, because you do not have access to thesystem that hosted the failed primary database), some data loss will occurduring the failover.

?

Step5???Stop Redo Apply.

Issue the following SQL statement on the targetstandby database:

?

SQL>alter database recover managed standby database cancel;

Databasealtered.

?

Step6???Finish applying all received redo data.

Issue the following SQL statement on the targetstandby database:

?

SQL>alter database recover managed standby database finish;

Databasealtered.

?

If this statement completes without anyerrors, proceed to Step7 .

If an error occurs, some received redo data was notapplied. Try to resolve the cause of the error and re-issue the statementbefore proceeding to the next step.

Note that if there is a redo gap thatwas not resolved in Step3 and Step4 , you will receive an error stating that thereis a redo gap.

If the error condition cannot be resolved, a failovercan still be performed (with some data loss) by issuing the following SQLstatement on the target standby database:

?

SQL>alter database activate physical standby database;

Databasealtered.

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 AVAILABILITY PRIMARY????????? standby??????????????????????? NOT ALLOWED

?

Proceed to Step9 when the ACTIVATE statement completes.

?

Step7???Verify that the target standby database is ready to become aprimary database.

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

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

?

SWITCHOVER_STATUS

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

NOTALLOWED

?

A value of either TO PRIMARY or SESSIONS ACTIVEindicates that the standby database is ready to be switched to the primaryrole. If neither of these values is returned, verify that Redo Apply is activeand continue to query this view until either TO PRIMARY or SESSIONS ACTIVE isreturned.

?

Step8???Switch the physical standby database to the primary role.

Issue the following SQL statement on the targetstandby database:

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

?

Note:

The WITH SESSION SHUTDOWN clause can be omitted fromthe switchover statement if the query of the SWITCHOVER_STATUS column performedin the previous step returned a value of TO PRIMARY.

?

Step9???Open the new primary database.

SQL>alter database open;

Databasealtered.

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 AVAILABILITYPRIMARY????????? standby??????????????????????? FAILED DESTINATION

?

Step10???Back up the new primary database.

Oracle recommends that a full backup be taken of thenew primary database.

?

Step11???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;

?

Step12???Optionally, restore the failed primary database.

After a failover, the original primary database canbe converted into a physical standby database of the new primary database usingthe method

As following:

Flashing Back a FailedPrimary Database into a Physical Standby Database

?

轉載請注明作者出處及原文鏈接，否則將追究法律責任：

作者：xiangsir

原文鏈接：http://blog.csdn.net/xiangsir/article/details/9107501

QQ：444367417

MSN：xiangsir@hotmail.com

?

?

[置頂] Oracle 11g Data Guard Role Transitions: Failover