Graceful Failover

Database failover changes one of the standby databases into the role of primary database. You should perform a standby database failover only in the event of a software or system failure that results in the loss of the primary database. This section contains the following topics:

No-Data-Loss Failover San Francisco Boston Application Application 0001 0002 Standby database becomes

primary database Online

Redo Logs Archived Redo Logs 0001 0002 0003 Local Archiving Read-Only Reports Read/Write Transactions 0001 0002

Primary Database Online

Redo Logs Archived Redo Logs 0001 0002 0003 Local Archiving

Database Failover

■ Minimal-Data-Loss Failover

■ Switching Database Roles After Graceful Failover ■ Primary Database No-Data-Loss Recovery

5.4.6.1 No-Data-Loss Failover

No-data-loss failover to the standby database can be achieved when the primary database log transport services has been previously set up to provide this capability. The primary database must be using the guaranteed or instant protection mode. These modes require that the primary database have the following archived log destination attributes:

■ LGWR - archived by the LGWR process ■ SYNC - synchronous network transmission

■ SERVICE - standby database

■ AFFIRM - synchronous archived log disk I/O (implied if primary database is in

PROTECTED mode)

■ REGISTER - archived log registration on the standby database ■ MANDATORY - online redo log must be archived to standby

Furthermore, the archived log destinations cannot have the following attribute: ■ DEPENDENCY - archived log destination dependency

In addition, standby databases participating in the no-data-loss environment must have standby redo logs available.

If a failure occurs at the primary site, there will be incomplete standby redo logs that can be salvaged with the RECOVER ... FINISH statement. For example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

When you start managed recovery, specify the FINISH control option to define the mode of the recovery operation. You can also specify the FINISH control option to alter the mode of an ongoing managed recovery operation. If the FINISH control option is being used to alter the mode of an ongoing managed recovery operation, specifying the NOWAIT option allows control to be returned to the foreground process before the recovery operation completes.

Database Failover

When the NOWAIT option is used, the V$MANAGED_STANDBY fixed view may be checked to verify when the managed recovery operation is finished. There should be no rows selected by the following statement after recovery has completed: SQL> SELECT PROCESS FROM V$MANAGED_STANDBY;

no rows selected

5.4.6.2 Minimal-Data-Loss Failover

Minimal-data-loss failover to the standby database can be achieved when the primary database log transport services have been previously set up to provide this capability. Standby databases participating in the minimal-data-loss environment must have the following archived log destination attributes:

■ LGWR - archived by the LGWR process

■ ASYNC - asynchronous network transmission

■ SERVICE - standby database

■ REGISTER - archived log registration on the standby database

Furthermore, the archived log destinations cannot have the following attribute:

■ DEPENDENCY - archived log destination dependency

In addition, standby databases participating in the minimal-data-loss environment must have standby redo logs available.

Using a lower block count for the archived log destination ASYNC attribute minimizes the amount of potential data loss but possibly decreases primary database throughput. The archived log destination AFFIRM attribute can also be used to further reduce potential data loss.

If a failure occurs at the primary site, there will be incomplete standby redo logs on the standby database. The standby redo log contents can be salvaged with the

RECOVER...FINISH statement, for example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

See Also: Section 3.6.1.3 for an overview on minimal-data-loss failover

Database Failover

5.4.6.3 Switching Database Roles After Graceful Failover

After a graceful failover, subsequent redo logs from the original primary database cannot be applied to the standby database. The standby database is "on hold." The standby redo logs have been archived and should be copied to, registered, and recovered on other standby databases derived from the original primary database. You must now change a standby database into the new primary database. Change one of the available standby databases, which may include the original primary database, to the primary role by issuing the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

No reinstantiation of the other standby databases is required. Once the archived standby redo logs have been registered and recovered on all destinations, the other standby databases are ready to receive redo logs from the new primary database. To register the copied archived redo logs, issue the following statement on each standby database:

SQL> ALTER DATABASE REGISTER LOGFILE ’/standby/arch_dest/arch_1_101.arc’; SQL> ALTER DATABASE REGISTER LOGFILE ’/standby/arch_dest/arch_1_102.arc’; The original primary database must be reinstantiated from a backup of the new primary database.

5.4.6.4 Primary Database No-Data-Loss Recovery

It may be faster to recover the primary database, even you are when using a no-data-loss environment.

When the primary database is operating in no-data-loss mode and a system failure occurs, you can restart the primary database without incurring data loss on the corresponding standby databases. The following failure scenarios are automatically recovered by log transport services:

■ Instance recovery, which is only possible in a Real Application Clusters

configuration, occurs in an open primary database when one instance discovers that another instance has failed. A surviving instance automatically uses the online redo log to recover the committed data in the database buffers that was lost when the instance failed. Further, the Oracle database server undoes any

Note: This is the same statement used for no-data-loss recovery. This statement finishes recovery of the standby redo logs, applying as much data to the standby database as possible.

Database Failover

transactions that were in progress on the instance when it failed and then clears any locks held by the failed instance after recovery is complete.

■ Failure recovery occurs when either a single-instance database fails or all instances of a multi-instance primary database fail. In failure recovery, an instance must first open the database and then execute recovery operations. In general, the first instance to open the database after a failure or SHUTDOWN ABORT automatically performs failure recovery.

In both of these scenarios, the standby databases participating in the no-data-loss environment will be automatically resynchronized with the recovered primary database. However, for the resynchronization to occur, the primary database archived log destinations must be properly established to identify the standby databases, and network connectivity must exist between the primary database and the standby databases.