Best Practices for Implementing Data Guard Failover

A fast-start failover is completely automated and requires no user intervention. A manual failover, being user-driven, can be performed using Enterprise Manager, the Data Guard broker command-line interface, or SQL*Plus commands:

■ Fast-start failover: There are no procedural best practices to consider when performing a fast-start failover. However, it is very important to address all of the configuration best practices described in Section 2.4.7.2.2, "Fast-Start Failover Best Practices" on page 2-40.

■ Manual failover: When performing a manual failover, follow the best practices described in Section 4.2.2.2.2, "Using SQL to Fail Over to a Physical Standby Database" on page 4-18 and the configuration best practices outlined in Section 2.4.7.2.3, "Manual Failover Best Practices" on page 2-41:

For manual failovers that involve Real Application Clusters, issue the SHUTDOWN ABORT statement on all secondary RAC instances on the standby database prior to performing a failover.

This section contains these topics:

■ Using Enterprise Manager to Perform a Data Guard Failover

■ Using SQL to Fail Over to a Physical Standby Database

■ Using SQL to Fail Over to a Logical Standby Database

4.2.2.2.1 Using Enterprise Manager to Perform a Data Guard Failover The procedure for Data Guard failover is the same for both physical and logical standby databases. The following screen shots illustrate how to perform a failover using Oracle Enterprise Manager:

In Figure 4–3 the Data Guard Overview page shows the ORA-16625 error status that indicates problems accessing the primary database.

See Also: Oracle Database 10g Release 2 Best Practices: Data Guard Switchover and Failover at

http://www.oracle.com/technology/deploy/availability /htdocs/maa.htm

Figure 4–3 Data Guard Overview Page Showing ORA-16625 Error

To transition DR_Sales into the primary role, select DR_Sales in the Standby Databases table and click Failover.

Figure 4–4 shows the Failover Confirmation page.

Figure 4–4 Failover Confirmation Page

If you determine that a failure occurred on the primary database and there is no possibility of recovering the primary database in a timely manner, you can start the Failover operation. In configurations with both physical and logical standby databases, Oracle recommends using the physical standby database as the failover target because it will allow the logical standby database to continue to function as a logical standby to the new primary database. If the failover is made to the logical

standby, any physical standbys in the configuration will need to be re-created from a backup of the new primary database.

The failover operation enables you to choose one of the following two types of failover operations:

■ Complete

This operation attempts to minimize data loss by applying all available redo on the standby database.

■ Immediate

No additional data is applied on the standby database; data might be lost. This is the fastest type of failover.

Figure 4–5 shows the progress of the failover operation.

Figure 4–5 Failover Progress Page

During the failover, the selected standby database (also referred to as the target standby database) transitions into the primary role. If the failover target is a physical standby database, it is restarted. When completed, the Data Guard Overview page reflects the updated configuration, as shown in Figure 4–6.

Figure 4–6 Data Guard Overview Page After a Failover Completes

In the figure, the Data Guard Status column indicates that the original primary database (North_Sales) is disabled and can no longer be managed through Enterprise Manager until it has been re-enabled as a physical standby database.

4.2.2.2.2 Using SQL to Fail Over to a Physical Standby Database Follow these steps to fail over to a physical standby database:

1. If the standby database is a Real Application Clusters database, then issue a SHUTDOWN ABORT on all additional standby instances.

2. Initiate the failover by issuing the following SQL command on the target standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If the network between the primary and standby sites is unavailable, then the standby RFS processes will wait for the network connections to time out through normal TCP timeout processing before shutting down. While the RFS processes are in this TCP timeout processing, the standby database will not be able to fail over unless you include the FORCE keyword on the RECOVER MANAGED STANDBY DATABASE FINISH statement.

3. Convert the physical standby database to the primary role:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

4. If the standby database was never opened read-only since the last time it was started, then open the new primary database by issuing the following SQL statement:

ALTER DATABASE OPEN;

5. If the standby database has been opened read-only, then restart the new primary database before starting Redo Apply.

4.2.2.2.3 Using SQL to Fail Over to a Logical Standby Database Follow these steps to fail over to a logical standby database:

1. If the standby database is a Real Application Clusters database, then issue a SHUTDOWN ABORT on all additional standby instances.

2. Initiate the failover by issuing the following SQL command on the target standby database:

ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

This statement stops the RFS process, applies any remaining redo data, stops SQL Apply, and activates the logical standby database in the primary role. To avoid waiting for the redo in the standby redo log file to be applied prior to performing the failover, omit the FINISH APPLY clause on the statement.

Although omitting the FINISH APPLY clause will accelerate failover, omitting it will also cause the loss of any unapplied redo data in the standby redo log. To gauge the amount of redo that will be lost, query the V$LOGSTDBY_PROGRESS view. The LATEST_SCN column value indicates the last SCN received from the primary database, and the APPLIED_SCN column value indicates the last SCN applied to the standby database. All SCNs between these two values will be lost.