[Data Guard]Oracle10g Data Guard學習筆記（三）

9. Performing Switchover and Failover

Choosing the Best Role Transition Operation

Planned role transition-switchover

Primary database can be repaired in as timely manner -repair primary database

Primary database can not be repaired in as timely manner-failover

Switchover-no data loss/no resetting of online redo logs

Performing a Switchover by Using Enterprise Manager

a. A check is made to ensure that the primary database and standby database are not currently

in an error status condition and that broker management of the primary database is enabled

and online.

b. Any active sessions connected to the primary database are automatically closed during the

switchover.

c. The primary database is first changed to the standby role, and then the standby database is

changed to the primary role.

d. If the switchover target is a physical standby database, the target and primary databases are

each restarted.

Performing a Switchover to a Physical Standby by Using SQL

1. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database to verify that it is possible to perform a switchover operation. A TO STANDBY value in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the standby role.

2. To transition the primary database to a physical standby database role, execute the following SQL statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO

PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;

The WAIT option specifies that control is not returned to you until the statement completes.

3. Shut down the instance and restart the database in MOUNT mode:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

4. Verify that the switchover notification has been processed by the standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the standby database. You should see a value of TO_PRIMARY.

5. Execute the following SQL statement on the physical standby database that you want to switch to the primary role: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

6. If the standby database was opened read-only, shut down and restart the new primary database.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

If it was not opened read-only, open the database with the ALTER DATABASE OPEN command.

7. Issue the following statement on the new primary database to start redo transport:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Situations That Prevent a Switchover

• Archived redo log files are unavailable.

• Point-in-time recovery is required.

• Production database is not open and cannot be opened.

Performing a Failover to a Physical Standby Database by Using SQL

1. Identify and resolve any gaps in the archived redo log files.

SQL> SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

2. Repeat step 1 until all gaps are resolved.

3. Copy any other missing archived redo log files.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)

OVER (PARTITION BY thread#) AS LAST FROM V$ARCHIVED_LOG;

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

4. Initiate the failover operation on the target standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

FINISH FORCE;

5. Convert the physical standby database to the primary role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

6. Complete the transition of the standby database to the primary database role.

7. (Optional) Back up the new primary database.

8. (Optional) Restore the failed primary database.

注：failover不會reset log

Activating a Standby Database

無法進行failover時使用，與failover不同之處在於不會應用所有可用的redo。且failover後會使configuration中的其他standby也不可用

Restoring Databases After a Role Transition

Reinstate-必須開啟flashback database並且能flashback回到failover前的狀態

Re-create

Flashback Through Standby Database Role Transitions

Primary和physical standby經過flashback後保持原有的role；logical standby返回到flashback到的一刻的role

Flashback可用於回退一個activation的動作

Physical Standby Configuration

1. On the new primary database, issue the following query to determine the system change number (SCN) at which the old standby database became the new primary database:

SELECT standby_became_primary_scn FROM v$database;

2. After the old primary database site is available, mount the old primary database.

STARTUP MOUNT;

3. Flash back the old primary database to the “standby became primary” SCN that you determined in step 1:

FLASHBACK DATABASE TO SCN ;

4. On the old primary database, issue the following command to convert the control file to a standby control file:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

5. Shut down the old primary instance.

SHUTDOWN IMMEDIATE;

6. Mount the old primary database. The old primary database is now your new standby database.

STARTUP MOUNT;

7. On the new primary database, enable redo transport to the old primary database (new standby database). Check the status of the archive destinations and enable any that are not enabled.

SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR, SRL FROM V$ARCHIVE_DEST_STATUS;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Then, archive a new log to the new standby by issuing the following command:

ALTER SYSTEM ARCHIVE LOG CURRENT;

8. On the new standby database, start managed standby recovery. The role reversal is now complete:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

If you are using real-time apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

USING CURRENT LOGFILE DISCONNECT;

10. Enabling Fast-Start Failover

Fast-start failover occurs when any of the following conditions occurs:

• Loss of connectivity between both the primary database and the observer, and between the primary database and the fast-start failover target standby database, exceeds the fast-start failover threshold

• Database health-check mechanism determines that the primary database data files are offline

• An instance crash occurs for a single-instance database

• All instances of a Real Application Clusters (RAC) primary database crash

• Shutdown abort of the primary database occurs

Observer-一個客戶端的OCI元件，一般在其他伺服器上啟動

Configuring Fast-Start Failover

1. Specify the target standby database.

EDIT DATABASE edrsr10p1_orcl SET PROPERTY FastStartFailoverTarget = edrsr10p1_site1;

2. Set the FastStartFailoverThreshold property.

EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = threshold-val;

3. Enable fast-start failover.

ENABLE FAST_START FAILOVER;

4. Start the observer.

START OBSERVER [FILE=filename];

5. Verify the configuration.

SHOW CONFIGURATION VERBOSE;

Viewing Information

SELECT fs_failover_status as STATUS, fs_failover_current_target as CURR_TGET,

fs_failover_threshold as THRESHOLD, fs_failover_observer_present as OBS_PRES,

fs_failover_observer_host as OBS_HOST FROM v$database;

Prohibited Operations After Enabling Fast-Start Failover（導致FS Failover失效的操作）

• Change the configuration protection mode

• Change the LogXptMode property for the primary or target standby databases.

• Perform a failover or switchover to a standby database that is not the fast-start failover target.

• Disable the Data Guard broker configuration.

• Delete the Data Guard broker configuration.

• Disable the standby database that is the target of fast-start failover.

• Remove the standby database that is the target of fast-start failover.

• Alter the FastStartFailoverTarget database-level property of either the primary database or the standby database.

• Fail over to an unsynchronized fast-start failover target.

Disabling Fast-Start Failover

DISABLE FAST_START FAILOVER [FORCE];

Configuration中的所有資料庫都將記錄該操作。

在primary和FS target standby失去連線的情況下，可透過連線到FS target standby、primary、與primary連線的第三者三種方式來disable FS Failover。連線到與primary失去連線的第三者上disable FS Failover不起作用。

Using the FORCE Option

• When the fast-start failover environment is synchronized and the primary has lost connectivity to the observer and the target standby database

• To prevent a fast-start failover from occurring on the target standby database

• To conduct a manual failover when the fast-start failover environment is unsynchronized

Stopping the Observer

STOP OBSERVER;-不是立刻停止，broker下次與obserber聯絡時停止。

Performing Role Changes

1. 在configuration synchronized情況下可與FS target standby進行switchover，但不能與其他standby進行切換

2. 在synchronized 的情況下可與FS target standby進行手工failover。如失敗，broker會確保FS failover是suspend狀態以避免observer進行FS failover

3. 進行FS failover，如失敗broker會disable FS failover且observer終止。

Performing a Manual Failover

FAILOVER TO edrsr10p1_site1;

在primary關閉，FS failover處於suspend狀態且synchronized，可以進行manual failover。當原來的主庫開啟時，observer自動進行reinstate，自動保持max availability模式。

在不同步情況下，先disable FS failover，之後可以手工failover到FS target standby或bystander，保護模式變為max performance。

Automatic reinstatement的條件

• The original primary database and the new primary database comprise the same fast-start failover configuration before the failover occurs and after the original primary database restarts.

• In a multi–standby database configuration, you have not performed a subsequent failover or switchover before the original primary database restarting.

• The observer in the fast-start failover configuration can connect to the original primary database.

• The original primary database must be able to connect to the new primary database to complete the reinstatement operation.

Using Enterprise Manager to Enable Fast-Start Failover

Flashback database功能必須開啟

11. Using Data Guard in a Real Application Clusters Configuration

主備資料庫可以是單例項和多例項的任意組合

Configuration Considerations with RAC

LOG_ARCHIVE_FORMAT中必須設定%t或%T，指明是哪一個thread

在maximum protection或maximum availability模式下，任何一個節點與standby dest失去聯絡可能會導致所有節點都不再產生redo

Redo Transport with RAC to RAC

Primary側：每個節點都產生自己的redo，

Standby側：只有一個節點進行redo apply，自動掌握apply的順序。在apply期間，其餘節點不能處於open read only狀態。

Setting Up a Primary Database with RAC

1. On all instances, define the LGWR attribute for the LOG_ARCHIVE_DEST_n parameter to specify that the LGWR process performs the archival operation.

2. Configure each primary instance to send its redo to the standby recovery instance by setting the LOG_ARCHIVE_DEST_n parameter to an appropriate value.

Setting Up a Standby Database with RAC

1. Create the standby redo log files.

In a Real Application Clusters environment, the standby redo log files must reside on disk devices that are shared by all instances.

2. On the recovery instance, define the LOCATION attribute of the LOG_ARCHIVE_DEST_1 initialization parameter to archive locally.

3. Start log apply services on the recovery instance.

Assigning Threads to Standby Redo Log Groups

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'STBY_LOGFILE_1.SRL' SIZE 50M;

指定standby redo log被哪一個thread使用

Apply Instance Failover

當進行redo apply的instance發生故障時，會使得redo transport停止

Broker configuration:

– Set ApplyInstanceTimeout to avoid downtime.

Default is 120 seconds.

Set to 0 (zero) to disable.

– Optionally set PreferredApplyInstance.

如果不設定PreferredApplyInstance，則broker隨機選擇一個instance

Non-broker configuration: Set up a list of destination connect identifiers.

在tnsnames.ora中配置多個地址，如第一個地址不可用，則使用第二個地址。（但必須手工在其他instance上起redo apply服務）

Role Transitions with RAC

• Switchovers: Only one primary instance and one standby instance can be active during a switchover.

• Failovers: Before performing a failover to a RAC standby database, shut down all but one standby instance.

Troubleshooting

• Switchover failure

ORA-01105: mount is incompatible with mounts by other instances

SQL> SELECT * FROM v$active_instances;

• Avoiding downtime during a network outage

將最大保護模式修改為最大可用性模式或最大效能模式以使得在network outage期間減少停機時間

12. Other Considerations for Oracle Data Guard

Offloading Backups to a Physical Standby

• Backups of data files and archived redo logs are fully interchangeable.

• Control file backups are not interchangeable.

• Primary and standby databases must use the same recovery catalog.

• It is not necessary to register the standby database.

Flashback Across Database Changes

• Database RESETLOGS operation

• Database switchover or failover to a physical or logical standby database

• Physical standby database activation

Using Flashback Database After RESETLOGS

Standby的scn處於resetlogs的scn前2個時，standby可越過resetlogs進行apply

Enabling Redo Encryption

在主備資料庫上安裝advanced security元件

使用netmgr進行配置（修改sqlnet.ora檔案）

Activating a Physical Standby Database for Reporting

1. On the physical standby database, perform the following steps:

a. Specify initialization parameters to define the flash recovery area if not already configured.

b. Cancel Redo Apply.

c. Create a guaranteed restore point.

2. On the primary database, perform the following steps to ensure that the physical standby database can be reverted to the restore point:

a. Archive the current log.

b. Defer the log archive destination for the physical standby database.

3. Activate the physical standby database and set protection mode to maximum performance.

4. Perform reporting or testing on the activated database.

5. Revert the activated database to a physical standby database:

a. Flash back to the restore point.

b. Convert it back into a physical standby database.

6. On the primary database, reenable archiving to the physical standby database destination.

7. Allow Data Guard to automatically resynchronize the physical standby database or use RMAN incremental backups from the primary database to resynchronize the physical standby database.

Configuring Cascaded Redo Log Destinations: Physical Standby

• On the primary database, use the LGWR transport.

• On the receiving standby database:

– Configure standby redo logs.

– Configure LOG_ARCHIVE_DEST_n to send redo to the next standby.

Role Transitions with Cascaded Redo Log Destinations

Physical standby: 使用cascaded redo log destination從physical standby接收redo的standby，在failover或switchover時與一般的standby沒有差別，只是切換時間可能會加長；從logical standby處接收redo的無法進行switchover。

[@more@]