Oracle 11g Data Guard Role Transitions: Failover

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

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 toStep2.

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 toStep5. 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 inStep3 displays information for the highest gaponly. After resolving a gap, you must repeat the query until no more rows arereturned.

If, after performingStep2 throughStep4, 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 toStep7.

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 inStep3 andStep4, 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 toStep9 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

QQ：444367417

MSN：[e-mail]