7
Role Management Services

This chapter describes how to manage the Data Guard environment. Oracle9i Data Guard provides the means to easily manage, manipulate, and change the standby database in many ways.

This chapter contains the following topics:

• Database Roles and Role Transitions
• Database Switchover
• Database Failover

7.1 Database Roles and Role Transitions

A database operates in one of the following mutually exclusive roles: primary or standby. Data Guard allows you to change these roles dynamically as a planned transition called a switchover operation, or in response to a primary database failure, through either a graceful failover or a forced failover operation.

Note: Switchover and failover operations are not invoked automatically. You must initiate switchover or failover operations using a SQL statement or a Data Guard broker interface.

The main difference between a switchover operation and a failover operation is that the switchover operation does not result in data loss. A failover operation may result in data loss, and requires that you shut down and restart the new primary instance and some or all of the standby database instances.

You must consider the different physical aspects of each role. For example, a database in the primary role uses a current control file, while a database in the physical standby role uses a standby control file, and a database in the logical standby role uses a dictionary. Using different control files or a dictionary, depending on the current role of the database, requires careful coordination of the initialization parameter files.

Switchover Operations

A switchover operation transitions the primary database to the standby role and transitions the standby database to the primary role. You initiate a switchover operation on the primary database. Note the following about switchover operations:

• No data is lost.
• If the switchover operation involves a physical standby database, both the primary database and the physical standby database that is switching over to the primary role must be shut down and restarted. However, there is no need to shut down and restart any other standby databases that are not participants of the switchover operation.
• If the switchover operation involves a logical standby database, there is no need to shut down and restart either the primary database or any of the standby databases. Logical standby databases do not need to be shut down and restarted.

Thus, a switchover operation allows the primary database to transition into its role as a standby database. As a result, scheduled maintenance can be performed more frequently with less system downtime.

For many configurations, you can lessen the frequency of failures by using the switchover operation to perform a regular program of preventive maintenance tasks. You can schedule time for hardware and software maintenance such as the following, without interrupting processing:

• Hardware--repairs, cleaning, adjustments, upgrades, installations. Hardware failures repaired or prevented through this approach can extend the apparent reliability of the hardware.
• Software--upgrades, maintenance releases, patch installations, disk defragmentation, cleaning up error logs, verifying status and operation of fault management tools, and collecting data to analyze your needs for additional maintenance can be done during this preventive maintenance time.

  See Also: Section 7.2 for information about switchover operations

Failover Operations

A failover operation changes one of the standby databases into the role of primary database, and discards the original primary database. You initiate the failover operation on the standby database that you want to fail over to the primary role. You should perform a standby database failover only when a software or system failure results in the loss of the primary database. For a failover operation:

• Data loss is possible unless the standby database is running in maximum protection or maximum availability mode.
• The original primary database is presumed to be lost. You must re-create the original primary database using a backup copy of the new primary database.
• Standby databases that are online at the time of the failover operation, but are not involved in the role transition, do not need to be shut down and restarted.

  Note: It is not always necessary to perform a failover operation. In some cases, recovery of the primary database may be faster. See Section 7.3.2.

In a failure of the primary database, such as a system or software failure, you may need to perform a failover operation to change one of the standby databases to the primary role. This unplanned transition may result in the loss of application data. The potential loss of data is dependent on the data protection mode that is set for the configuration. This type of transition often requires you to shut down and restart the original primary database and configure it as a new standby database. There are two types of failover operations: graceful failover or forced failover.

See Also: Section 7.3 for information about graceful and forced failover operations

Figure 7-1 shows a road map of the operations that are discussed in this chapter.

Figure 7-1 Standby Database Road Map

Text description of the illustration roadmap.gif

7.2 Database Switchover

A switchover operation transitions the primary database to the standby role and transitions one of the standby databases to the primary role, without resetting the online redo logs of the new primary database. No data is lost regardless of the protection mode that is set for the configuration. However, the start up and shut down requirements are different for physical and logical standby databases:

• After a switchover operation that involves a logical standby database, all databases continue to run normally; there is no need to shut down and restart the primary database or any of the standby databases.
• After a switchover operation that involves a physical standby database, the primary database and the physical standby database that is involved in the switchover operation must be shut down and restarted. There is no need to restart other standby databases that are not involved in the switchover operation.

A switchover operation is performed in two phases: first, the primary database role is switched to the standby role; then, a standby database is selected to assume the primary database role.

This section contains the following topics:

• Preparing to Perform a Successful Switchover Operation
• Switchover Operations Involving a Physical Standby Database
• Switchover Operations Involving a Logical Standby Database
• Transitioning Multiple Standby Databases to the Primary Role
• Validating the Switchover Transition (Physical Standby Databases Only)

7.2.1 Preparing to Perform a Successful Switchover Operation

Carefully plan each switchover operation so that the primary and standby databases involved have as small a transactional lag as possible.

Note: Do not use a switchover operation to perform a rolling upgrade of Oracle software. However, it may be possible to use a switchover operation to perform a hardware-based rolling upgrade. It may be possible to perform an application software rolling upgrade if necessary schema modifications and data updates have been made to the primary database prior to the switchover operation.

Before starting a switchover operation, verify that:

• Initialization parameter files or server parameter files (SPFILEs) are set up properly

  You should maintain two database initialization parameter files or SPFILEs for each database: one should contain initialization parameter settings for when the database is in the primary role, and the other should contain settings for the standby role. Having two initialization parameter files or SPFILEs allows you to easily start the databases after a switchover operation has occurred.

  Although most initialization parameters will be identical for both the primary and standby databases, some initialization parameters (such as CONTROL_FILES, LOCK_NAME_SPACE,LOG_FILE_NAME_CONVERT, and DB_FILE_NAME_CONVERT) may differ.

  See Also: Section 5.6.3 provides sample primary and standby initialization parameter files

• There is network connectivity between the primary and standby databases

  All primary and standby databases in the configuration should have network connectivity to all other databases in the configuration, and for each primary and standby database in the configuration, you should have entries in the tnsnames.ora file to identify the other databases in the configuration. You must also have corresponding entries in the listener.ora file for each database.

  See Also: Oracle9i Net Services Administrator's Guide for information about configuring and administering the listener.ora and tnsnames.ora initialization files

• All but one primary instance and one standby instance in a Real Application Clusters configuration are shut down

  For a database using Real Application Clusters, only one primary instance and one standby instance can perform the switchover operation. Shut down all other instances prior to the switchover operation.

• The primary database instance is open and the standby database instance is mounted

  The standby database that you plan to transition to the primary role must be mounted before you begin the switchover operation. A physical standby database instance must be mounted and in managed recovery mode before you start the switchover operation. If the physical standby database is open for read-only access, the standby switchover operation will take longer. Ideally, the standby database will also be actively recovering archived redo logs when the database roles are switched.

  See Also: Section 6.3.1

• The standby database that will become the new primary database is in ARCHIVELOG mode

For switchover operations involving a physical standby database, see Section 7.2.2. For switchover operation involving a logical standby database, see Section 7.2.3.

7.2.2 Switchover Operations Involving a Physical Standby Database

This section describes how to perform a switchover operation that changes roles between a primary database and a physical standby database. (See Section 7.2.3 for information about switchover operations involving a logical standby database.)

Always initiate the switchover operation on the primary database and complete it on the physical standby database. The following steps describe how to perform a switchover operation.

On the original primary database:

Step 1 Verify it is possible to perform a switchover operation.

Query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify that it is possible to perform a switchover operation. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 SWITCHOVER_STATUS 
 ----------------- 
 TO STANDBY 
 1 row selected 

The TO STANDBY value in the SWITCHOVER_STATUS column indicates it is possible to switch the primary database to the standby role.

Figure 7-2 shows a two-site Data Guard configuration before the roles of the databases are switched.

Figure 7-2 Data Guard Configuration Before a Switchover Operation

Text description of the illustration before.gif

Step 2 Initiate the switchover operation on the primary database.

To transition the primary database to a physical standby database role, use the following SQL statement syntax on the primary database:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [ {WITH | WITHOUT} 
SESSION SHUTDOWN [WAIT | NOWAIT] ];

You can include the optional clauses for this statement as follows:

• The switchover operation requires that only one SQL session be active on the primary instance. To terminate all other SQL sessions, include the WITH SESSION SHUTDOWN clause on the SQL statement.
• By default, the switchover operation must complete before control is returned to the SQL prompt (this is the same as the WAIT clause). To return control before the switchover operation is complete, specify the NOWAIT clause.

The primary database role is converted into a standby database. The current control file will be backed up to the current SQL session trace file prior to the switchover operation; this makes it possible to reconstruct a current control file, if necessary. For example, you may want to reverse the switchover (sometimes referred to as a switchback) and revert the original primary database to the primary role.

Figure 7-3 shows the Data Guard environment after the primary database has been switched over to a standby database and before the original standby database has become the new primary database. At this stage, the Data Guard configuration temporarily has two standby databases.

Figure 7-3 Standby Databases Before Switchover to the New Primary Database

Text description of the illustration between.gif

Step 3 Shut down and restart the primary instance.

Shut down the primary instance and restart it without mounting the database:

SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;

Mount the database as a physical standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

On the original physical standby database:

Step 4 Verify the switchover status in the V$DATABASE view.

After you switch the primary database to the standby role and the switchover notification has been received by the standby database, you should verify whether 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.

Note: Ensure the physical standby database is in managed recovery mode to allow the primary database switchover notification to be processed by the physical standby database.

For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
----------------- 
SWITCHOVER PENDING 
 1 row selected 

The SWITCHOVER PENDING value of the SWITCHOVER_STATUS column indicates the standby database is about to switch from the standby role to the primary role.

Step 5 Switch the physical standby database role to the primary role.

You can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in managed recovery mode or open for read-only access. It must be mounted in one of these modes so that the primary database switchover operation request can be coordinated.

On the physical standby database that you want to run in the primary role, use the following SQL statement syntax:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [ {WITH | WITHOUT} SESSION 
SHUTDOWN ] [WAIT | NOWAIT];

Note: The COMMIT TO SWITCHOVER TO PRIMARY clause automatically creates online redo logs. This may significantly increase the time required to complete the COMMIT operation. Oracle Corporation recommends that you always manually add the online redo logs to the standby database prior to starting the switchover operation. Also, define the LOG_FILE_NAME_CONVERT initialization parameter to correlate the standby database path names to the online redo logs.

Step 6 Shut down and restart the new primary database.

Shut down the original standby instance and restart it as the new primary database.

SQL> SHUTDOWN;
SQL> STARTUP;

The selected physical standby database is now transitioned to the primary database role.

Note: There is no need to shut down and restart any standby databases that are online at the time of the switchover operation, but are not involved in it. These standby databases will continue to function normally after the switchover completes.

On the new physical standby database:

Step 7 Start managed recovery operations and log apply services.

Issue the following command to begin managed recovery operations on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the new primary database:

Step 8 Begin archiving logs to the physical standby database.

Issue the following statements on the new primary database:

SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE;

When you perform a switchover operation involving a physical standby database, the existing control file type is converted in place. That is, the primary database's current control file is converted into a physical standby control file, and a physical standby control file is converted into a primary control file.

The current primary control file is backed up to the trace file before it is converted to be a standby control file. The current standby control file is not backed up to the trace file before becoming a primary control file.

Figure 7-4 shows a Data Guard environment after a switchover has taken place.

Figure 7-4 Data Guard Environment After Switchover

Text description of the illustration switch.gif

See Also: Chapter 13 and the Oracle9i SQL Reference for more information about the SQL statement syntax

7.2.3 Switchover Operations Involving a Logical Standby Database

This section describes how to perform a switchover operation that changes roles between a primary database and a logical standby database. (See Section 7.2.2 for information about switchover operations involving a physical standby database.)

Always initiate the switchover operation on the primary database and complete it on the logical standby database. The following steps outline the SQL statements to perform the switchover operation on databases located on separate nodes.

On the primary database:

Step 1 Verify it is possible to perform a switchover operation.

Begin by verifying it is possible to perform a switchover operation by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 SWITCHOVER_STATUS 
 ----------------- 
 TO STANDBY 
 1 row selected 

The TO STANDBY value in the SWITCHOVER_STATUS column indicates it is possible to switch the primary database to the standby role.

Step 2 Place the primary database in a quiesced state.

Ask users to log off the primary database or place it in a quiesced state to ensure there is no update activity on the database.

See Also: Oracle9i Database Administrator's Guide for more information on quiescing an Oracle database

Step 3 Switch the primary database to the logical standby database role.

To transition the primary database to a logical standby database role, use the following SQL statement syntax:

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY [WAIT | NOWAIT];

This statement ends current update operations on the primary database and prevents any new users from starting update operations. It also puts a marker in the redo log to provide a synchronization point for logical standby database operations. Executing this statement will also prevent users from making any changes to the data being maintained in the logical standby database.

You can include the optional WAIT or NOWAIT clauses on this statement. By default, the switchover operation must complete before control is returned to the SQL prompt (this is the same as the WAIT clause). To return control before the switchover operation is complete, specify the NOWAIT clause.

The primary database is transitioned to run in the standby database role.

Note: When you transition a primary database to a logical standby database role, you do not have to shut down and restart it.

Step 4 Verify the switchover status in the V$DATABASE view.

After you switch the primary database to the standby role and the switchover notification has been received by the standby database, you should verify whether 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.

For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
----------------- 
SWITCHOVER PENDING 
 1 row selected 

The SWITCHOVER PENDING value of the SWITCHOVER_STATUS column indicates the standby database is about to switch from the standby role to the primary role.

Step 5 Defer archiving redo logs.

Defer archiving redo logs on the new logical standby database. For example:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

On the original logical standby database:

Step 6 Switch the logical standby database to the primary database role.

On the logical standby database that you want to run in the primary role, use the following SQL statement to switch the logical standby database to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WAIT | NOWAIT];

Note: There is no need to shut down and restart any logical standby databases that are in the Data Guard configuration. Logical standby databases continue to function normally after a switchover operation completes.

Step 7 Enable archiving redo logs.

Enable archiving redo logs to the new logical standby database. For example:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

On the new logical standby database:

Step 8 Begin SQL apply operations.

Enter the following statement to begin SQL apply operations on the new logical standby database.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY dblink;

See Also: Chapter 13 and the Oracle9i SQL Reference for more information about the SQL statement syntax

7.2.4 Transitioning Multiple Standby Databases to the Primary Role

Typically, you choose only one standby database to become the new primary database. That is, you switch the primary database to the standby role and choose one standby database to be switched to the primary role.

In some cases, however, you may choose to switch multiple databases from the standby role to the primary role. If you transition multiple standby databases to run in a primary role, each will become a separate and distinct primary database. Any remaining standby databases can be connected to a single primary database only. They cannot be changed later to another primary database.

7.2.5 Validating the Switchover Transition (Physical Standby Databases Only)

After the SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement has been issued on the primary database, the resulting physical standby database is automatically placed in managed recovery mode. This is necessary to ensure that all unapplied redo logs have been applied to the new standby database. Upon completion of the recovery operation, the physical standby database instance is dismounted.

Examine the new standby alert log to ensure that recovery has finished on the new standby database. Subsequent standby database switchover operations cannot take place until this happens and the original standby database acknowledges that it has received all available archived logs.

The following example shows a successful managed recovery operation in the standby alert log:

MRP0: Media Recovery Complete: End-Of-REDO 
Resetting standby activation ID 3830496333 (0xe450bc4d) 
MRP0: Background Media Recovery process shutdown

If the primary database finished archiving the last online redo log, you can query the SEQUENCE# column in the V$ARCHIVED_LOG view to see if the last archived log has been applied on the physical standby database. If the last log has not been applied, you can manually copy the archived log from the primary database to the physical standby database and register it with the SQL ALTER DATABASE REGISTER LOGFILE filespec statement. If you then start up the managed recovery process, the archived log will be applied automatically. Query the SWITCHOVER_STATUS column in the V$DATABASE view. The TO PRIMARY value in the SWITCHOVER_STATUS column verifies that switchover to the primary role is now possible.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
----------------- 
TO PRIMARY 
1 row selected 

In situations where an error has occurred, it may still be possible to revert the new physical standby database to the original primary database using the following steps:

1. When the switchover procedure to change the role from primary to standby was initiated, a trace file was written in the log directory. This trace file contains the SQL statements to re-create the original primary control file. Locate the trace file and extract the SQL statements into a temporary file. Execute the temporary file from SQL*Plus. This will convert the new physical standby database back to the original primary role.
2. Create a new physical standby control file. This is necessary to resynchronize the primary and physical standby databases. Copy the standby control file to the physical standby sites.
3. Shut down the original physical standby instance and restart it.

If this procedure is successful and archive gap management is enabled, the FAL processes will start and re-archive any missing archived logs to the physical standby database. Force a log switch on the primary database and examine the alert logs on both the primary and physical standby databases to ensure that the archived log sequence numbers are correct.

See Also: Section 6.5 for information about archive gap management and Section 6.4.8 for information about locating the trace files

7.3 Database Failover

You invoke a failover operation when a catastrophic failure occurs on the primary database, and there is no possibility of recovering the primary database in a timely manner. During a failover operation, the incapacitated primary database is removed from the Data Guard environment and a standby database assumes the primary database role. You invoke the failover operation on the standby database that you want to fail over to the primary role.

Figure 7-5 shows the result of a failover operation from a primary database in San Francisco to a physical standby database in Boston.

Figure 7-5 Failover to a Standby Database

Text description of the illustration failover.gif

You should not fail over to a standby database except in an emergency, because the failover operation is an unplanned transition that may result in the loss of application data. Once you perform a failover operation, there is no going back. This is because the original primary database is incapacitated and the standby database that you fail over to the primary role is no longer capable of returning to being a standby database in the original configuration.

Most failures can be resolved at a primary site within a reasonable amount of time without failover.

Note: It is not always necessary to fail over to the standby database. In some cases, recovery of the primary database may be faster. See Section 7.3.2.

However, a database that needs complete restoration and recovery (or a database that is partially restored and recovered) can be resolved quickly by failing over to the standby database. Failures such as the following are good reasons to fail over to the standby database:

• Loss of the current online redo log
• Loss of system or rollback tablespace
• Loss of hardware and the database
• Inadvertent database operations that cannot be recovered from quickly, such as dropping a tablespace

There are two types of failover operations: graceful failover and forced failover. Which to use depends on whether a physical or a logical standby database is participating in the failover operation, and if standby redo logs are used.

This section contains the following topics:

• Planning for Database Failover
• Primary Database No-Data-Loss Recovery
• Graceful Failover
• Forced Failover

7.3.1 Planning for Database Failover

It is possible for the standby database to have lost transactions committed on the original primary database. This occurs because the current log of the primary database contains database modifications that may not be available to the standby database. Therefore, if the primary database current log is unavailable, data loss may be possible on the standby database.

Depending on the log transport services destination attributes, a graceful failover may provide no data loss or minimal data loss. A forced failover will result in intentional data loss. The database role transition options and requirements are summarized in Table 7-1.

7.3.1.1 No-Data-Loss Failover

On a standby database, no-data-loss failover is possible only when the corresponding primary database is operating in a data protection mode that provides no-data-loss semantics, such as the maximum protection and maximum availability modes. This means that all primary database archived redo logs necessary for no-data-loss failover are available on the standby system. However, it is possible that the archived redo logs have not yet been applied to the standby database. No-data-loss failover requires that all archived redo logs are first applied. If the archived redo logs are not applied, failover to a standby database will result in data loss relative to the primary database.

7.3.1.2 Minimal-Data-Loss Failover

Standby database minimal-data-loss failover occurs when primary database modifications have not all been applied, or when the corresponding primary database is operating in a data protection mode that allows data-divergence semantics, such as maximum performance mode. This results in standby database data loss relative to the primary database. The amount of data loss can be controlled by primary database archived log destination attributes and the availability of standby redo logs at the standby database.

7.3.1.3 Physical Standby Databases and Standby Redo Logs

The presence of standby redo logs on a physical standby database determines whether a graceful failover with no data loss is possible. To avoid or minimize data loss during a database failover operation involving a physical standby database, you should always use standby redo logs. (See Section 5.8.4 for information about creating standby redo logs.) If the primary database is lost, the use of standby redo logs allows primary database modifications to be automatically recovered on the physical standby database. Depending on the log transport services options used, this may provide no-data-loss or minimal-data-loss failover.

Note: You should not fail over to a physical standby database to test whether it is being updated correctly. Instead, open the standby database in read-only mode.

If the physical standby database does not use standby redo logs, database failover will almost always result in data loss. However, if the primary database is not completely lost, it may be possible to manually recover the primary database online redo logs and apply them on the standby database. (Section 7.3.2 describes no-data-loss recovery without performing a failover operation.) In both cases, the original primary database is incompatible with the resulting new primary database.

Table 7-1 summarizes the database role transition options and requirements. Also, see Chapter 5 for more information about setting up log transport services.

Table 7-1  Summary of Role Transitions

Summary of Role Transitions	Database Switchover	Graceful Database Failover - No Data Loss	Graceful Database Failover - Minimal Data Loss	Forced Database Failover
Data Loss Potential	No data loss	No data loss	Minimal data loss	Probable data loss
Data Divergence Potential	Not applicable	No divergence if in maximum protection mode Possible divergence if in maximum performance mode	Possible divergence	Probable divergence
Requirements to Shut Down and Restart Database	For switchovers with a physical standby database: the participating physical standby database and the original primary database For switchovers with a logical standby database: None	New primary database	New primary database	New primary database and all other standby databases that were not the target of the failover operation
Required Archived Log Destination Attributes	SERVICE REGISTER	SERVICE LGWR SYNC AFFIRM REGISTER MANDATORY	SERVICE LGWR ASYNC REGISTER	SERVICE
Standby Redo Logs	Optional	Required for physical standby databases	Required for physical standby databases	Optional for physical standby databases
SQL Statement Issued on the Primary Database	ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY	Not applicable	Not applicable	Not applicable
SQL Statement Issued on the Standby Database	ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY	Physical standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH Logical standby: ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE Physical and Logical: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY	Physical standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH Logical standby: ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE Physical and Logical: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY	ALTER DATABASE ACTIVATE STANDBY DATABASE

7.3.2 Primary Database No-Data-Loss Recovery

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

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

Note: When the primary database is running in a mode other than no-data-loss, the archiver (ARCn) process is responsible for performing resynchronization activities to the primary database.

7.3.3 Graceful Failover

Graceful database failover automatically recovers some (minimal data loss) or all (no data loss) of the original primary database application data. There is also graceful failover for unavoidable data loss for physical standby databases whose standby redo logs either are not available or contain corruption. The data protection modes that you set act as failure policies that dictate how to manage the primary database if a standby database becomes inaccessible. Depending on whether you have defined the maximum protection, maximum availability, or maximum performance protection mode for the configuration and what attributes you have chosen for log transport services, it may be possible to automatically recover some or all of the primary database modifications.

One of the consequences of a graceful database failover is that you must shut down and restart the new primary database. However, standby databases not participating in the failover operation do not need to be shut down and restarted.

This section contains the following topics:

• Graceful Failover with No Data Loss
• Graceful Failover with Unavoidable Data Loss
• Graceful Failover with Minimal Data Loss

7.3.3.1 Graceful Failover with No Data Loss

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 configured with the maximum protection (physical standby databases only) or maximum availability mode. These modes require that the primary database have the following archived log destination attributes on the LOG_ARCHIVE_DEST_n initialization parameter:

• LGWR - archived by the LGWR process
• SYNC - synchronous network transmission
• AFFIRM - synchronous archived log disk I/O
• REGISTER - archived log registration on the standby database

Furthermore, the archived log destinations cannot have the DEPENDENCY attribute defined, which provides for archived log destination dependencies. In addition, physical standby databases participating in the no-data-loss environment must have standby redo logs available.

See Also: Section 5.8.4 for an overview of standby redo logs

Graceful Database Failover for Physical Standby Databases

Depending on how you have defined the protection mode for the configuration and the attributes for log transport services, it may be possible to automatically recover some or all of the primary database modifications.

Step 1 Initiate the graceful failover operation on the standby database.

If a failure occurs at the primary site, a graceful failover allows you to salvage incomplete standby redo logs using the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Note: To issue the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH statement, you must set the COMPATIBLE initialization parameter to 9.0.1.0.0 (or higher) in the initialization parameter file. For example, add the following line to the initialization parameter file: COMPATIBLE=9.0.1.0.0 Once you set the COMPATIBLE initialization parameter to 9.0.1.0.0 (or higher), its value must remain at this release number from this point forward. The only way to revert to an 8.0 or 8.1 release is to cancel the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH statement before the standby redo logs have been applied.

Note that when you start managed recovery, you can specify the FINISH control option to define the mode of the recovery operation or 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.

When the NOWAIT option is used, you can query the V$MANAGED_STANDBY fixed view 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

Step 2 Convert the physical standby database to the primary role.

Once the RECOVER...FINISH statement has successfully completed, you must issue the COMMIT TO SWITCHOVER TO PRIMARY statement to convert the physical standby database to the primary database role. You can no longer use this database as a standby database that is compatible with the old primary database.

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. This will happen automatically if the standby destinations are defined on the new primary database.

You must now change the standby database into the new primary database by issuing the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

There is no need to shut down and restart other standby databases in the configuration that were not participants in the failover operation.

Step 3 Register the missing redo logs.

Once the archived standby redo logs have been received 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';

To use the original primary database, you must re-create it from a backup copy of the new primary database.

Graceful Database Failover for Logical Standby Databases.

When a failover occurs, perform the following steps to stop SQL apply operations on the logical standby database, register missing redo logs, and switch the logical standby database to the primary database role.

Step 1 Copy redo logs to the logical standby site.

If redo logs exist on the primary database that have not yet been applied on the logical standby database, manually copy the redo logs to that standby database.

Step 2 Register the missing redo logs.

Specify the REGISTER LOGFILE clause from the standby database to register log files from the failed primary database. This operation is required unless missing log files from the failed primary database have been copied to the directory specified in the STANDBY_ARCH_DEST initialization parameter.

Specify the following statements on the logical standby database to register the missing redo logs that you copied manually from the original primary database:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 'filespec';
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Step 3 Ensure that all redo logs have been applied to the new primary database.

On the new primary database, ensure that the remaining redo logs have been applied by checking the V$LOGSTDBY view for idle processes.

See Also: Chapter 14 for information about the V$LOGSTDBY view

Step 4 Activate the new primary database.

Execute the following statements on the new primary database to stop SQL apply operations and activate the database in the primary database role:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;

Step 5 Prepare the new logical standby database.

Prepare the new logical standby database to apply all remaining redo logs from the old primary database and begin applying redo logs from the primary database.

On the new logical standby database, apply the remaining redo logs from the old primary database and begin SQL apply operations from the new primary database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY dblink;

When this statement completes, all remaining archived redo logs will have been applied. Depending on the volume of work to be done, this operation may take some time to complete. A database link to an account with access to the logical standby system views is necessary.

Step 6 Shut down and restart the original primary database.

To return the system to its original database configuration, re-create the original primary database as a logical standby database, add it to the new Data Guard configuration, and perform a switchover operation to return it to the primary database role.

7.3.3.2 Graceful Failover with Unavoidable Data Loss

For physical standby databases only, you can perform a graceful failover operation that allows you to fail over to a physical standby database whose standby redo logs either are not available or contain corruption. This type of graceful failover operation, called unavoidable-data-loss failover, is performed using the FINISH SKIP STANDBY LOGFILE clause of the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE SQL statement.

Unavoidable-data-loss failover allows you to transition a physical standby database to the primary role even though some data loss may occur. The FINISH SKIP STANDBY LOGFILE clause directs managed recovery operations to:

• Ignore the contents of the standby redo logs, even if they are available.
• Apply as much archived redo as possible before ending the recovery operation.

  When you issue the FINISH SKIP STANDBY LOGFILE clause, log apply services apply archived redo logs until the first unarchived redo log is encountered. All archived redo logs beyond this point are not recovered and are unavoidably lost. In a Real Application Clusters environment, this may result in greater data loss due to the dependencies on the redo data by multiple instances.

To perform an unavoidable-data-loss graceful failover operation, perform these steps:

Step 1 Set attributes for the LOG_ARCHIVE_DEST_n parameter.

For a standby database to be eligible for graceful failover, it must be a physical standby database and the following attributes must be set on the LOG_ARCHIVE_DEST_n initialization parameter:

• SERVICE=net_service_name

  Specifies a valid Oracle Net service name identifying the standby database

• REGISTER

  Indicates that the location of the archived redo log is to be recorded at the corresponding destination

Step 2 Begin the graceful failover and managed recovery operations.

Issue the following SQL statements to perform a graceful failover to a standby database without standby redo logs:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP 
  2> STANDBY LOGFILE;

After the graceful failover operation completes successfully, archived redo logs containing the failover information are automatically archived to the destinations defined by the LOG_ARCHIVE_DEST_n initialization parameter on the physical standby database that will be transitioned to the primary database role. These archived log destinations were defined when the database was still running in the standby database role, in anticipation of the standby database assuming the primary database role.

Step 3 Optionally, copy archived redo logs to the other standby destinations.

If the standby database does not define archived log destinations, you may need to manually copy and register the resulting failover archived redo logs on each of the remaining physical standby databases in the configuration. Use the ALTER DATABASE REGISTER LOGFILE statement to register a copied archived redo log at a standby destination.

Step 4 Switch the physical standby database to the primary role.

Once the RECOVER...FINISH SKIP STANDBY LOGFILE statement has successfully completed, you must issue the COMMIT TO SWITCHOVER TO PRIMARY statement to transition the physical standby database to run in the primary database role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Until you issue the COMMIT TO SWITCHOVER TO PRIMARY statement, the database is on hold. While on hold, the database is not usable as a standby database. After you issue the COMMIT TO SWITCHOVER TO PRIMARY statement, the database must be shut down and restarted in the primary role.

Step 5 Drop old standby redo logs and create a standby database from the new primary database.

There is no need to shut down and restart other standby databases in the configuration. Once the archived redo logs containing the failover information have been received and recovered on all destinations, the other standby databases are ready to receive redo logs from the new primary database.

Note: Some standby redo logs may be created during a graceful failover operation. Thus, you may want to consider dropping them after completion of the failover operation.

A graceful failover assumes that the primary database is incapacitated; therefore, performing a graceful failover transitions the standby database to the primary role and eliminates the original primary database from participating in the standby configuration. To reuse the old primary database in the new configuration, you must create it as a standby database, using a backup copy of the new primary database.

7.3.3.3 Graceful Failover with Minimal Data Loss

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 on the LOG_ARCHIVE_DEST_n initialization parameter:

• LGWR - archived by the LGWR process
• ASYNC - asynchronous network transmission
• SERVICE - standby database
• REGISTER - archived log registration on the standby database

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. Furthermore, the archived log destinations cannot have the DEPENDENCY attribute defined, which provides for archived log destination dependencies.

In addition, it is recommended that physical standby databases participating in the minimal-data-loss environment should have standby redo logs available. If a failure occurs at the primary site, there will be incomplete standby redo logs on the physical 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;

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

Once the RECOVER...FINISH statement has successfully completed, you must issue the COMMIT TO SWITCHOVER TO PRIMARY statement to transition the physical standby database to run in the primary database role. You can no longer use this database as a standby database.

After a graceful failover, subsequent redo logs from the original primary database cannot be applied to the physical 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 physical standby databases derived from the original primary database. This will happen automatically if the standby destinations are defined on the new primary database.

You must now change the standby database to be the new primary database by issuing the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

There is no need to shut down and restart other standby databases in the configuration that were not participants in the failover operation. 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';

The original primary database must be re-created from a backup copy of the new primary database.

7.3.4 Forced Failover

Forced database failover forces a standby database into the primary role, possibly resulting in lost application data. (This is true even when standby redo logs are configured on the physical standby databases.) Do not perform a forced failover operation unless a software or system failure results in the loss of the primary database. Perform a graceful failover whenever possible.

The syntax for physical and logical standby database environments is the same. You invoke a forced failover using the SQL ALTER DATABASE ACTIVATE {PHYSICAL | LOGICAL} STANDBY DATABASE statement. This changes the state of a standby database to an active database in preparation for it to become the primary database.

One consequence of a forced database failover is that you must re-create the old primary database and other standby databases from a copy of the new primary database. Another consequence is that application data may be lost unless the standby database is running in maximum protection or maximum availability mode at the time of the primary database failure. Depending on the nature of the emergency, you may not have access to your primary database files. If you do have access, you should attempt to archive the current online redo log on the primary database manually, and then copy and apply all available archived redo logs to the standby database that you plan to change to the primary database role.

Before you can execute a forced failover, Oracle Corporation recommends that you try to perform a graceful failover operation. If you must execute a forced failover operation, you must first stop the primary database archiving operations to the standby database.

Note: You cannot perform a forced failover operation to a physical standby database on which standby redo logs are present unless you indicate that it is acceptable to skip applying the contents of the standby redo log with the FINISH SKIP STANDBY LOGFILE keywords of the RECOVER MANAGED STANDBY DATABASE clause of the SQL ALTER DATABASE statement.

See Section 5.3.1.2 for information about disabling archived log destinations using the DEFER attribute of the LOG_ARCHIVE_DEST_STATE_n initialization parameter.

This section contains the following topics:

• Recovering Primary Database Modifications
• Standby Database Failover
• Intentional Data Loss (Physical Standby Databases Only)

7.3.4.1 Recovering Primary Database Modifications

Prior to performing the standby database failover operation, you should transfer as much of the missing primary database contents as possible to the standby database. The more database modifications that can be transferred to the standby site, the closer the new primary database will match the original primary database.

To move modifications from the primary database to the standby database, take the following steps:

1. If possible, archive the current log group of the primary database locally; for example:

   SQL> ALTER SYSTEM LOG CURRENT;
   

   In a true database failover situation, this is seldom possible. However, if you are able to sufficiently recover the primary database to permit archiving to online redo logs, then you can probably recover the entire primary database.

2. For physical standby databases running in maximum protection mode only: By default, you cannot fail over to a physical standby database whose control file was created from a primary database operating in the maximum protection mode, even if the primary database is no longer available. To perform a forced failover operation, you must put the standby database in maximum performance mode by issuing the following statement on the physical standby database:

   SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
   
   

3. If possible, manually copy all primary database archived log groups to the standby database.

   Use an appropriate operating system utility for transferring binary data; for example:

   % cp /oracle/arch_dest/*.arc /standby/arch_dest
   
   

4. Register the copied archived logs with the standby database using the SQL ALTER DATABASE REGISTER LOGFILE statement. Each archived log must be registered individually, for example:

   SQL> ALTER DATABASE REGISTER LOGFILE '/standby/arch_dest/arch_1_101.arc';
   SQL> ALTER DATABASE REGISTER LOGFILE '/standby/arch_dest/arch_1_102.arc';
   
   

5. Apply all available archived logs to the standby database; for example:
   • On a physical standby database, use the following statement to apply archived redo logs:

     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
     
     

     Note: Do not use the RECOVER...FINISH clause when applying manually copied archived logs to the standby database.

   • On a logical standby database, specify the following statements to register the missing redo logs that you copied manually from the original primary database:

     SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
     SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 'filespec';
     SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
     
     

Once you complete all of these steps, the standby database is rolled forward as close to the time of the primary database failure as possible.

7.3.4.2 Standby Database Failover

Perform the following steps to fail over to the standby database:

1. Ensure that your standby database is mounted in EXCLUSIVE mode by executing the following query:

   SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME='cluster_database';
   NAME                                 VALUE                                                                           
   -------------------------  ---------------------------------------
   cluster_database                FALSE                                                                           
   1 row selected.
   
   

   If the value is true, then the database is not mounted exclusively and you must shut down, set the parameter to false, and restart the instance. If the value is false, then the database is mounted exclusively.

2. Fail over to the standby database:

   Note: This statement resets the online redo logs.

   SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
   
   

   Note: The ACTIVATE STANDBY DATABASE clause automatically creates online redo logs. This may significantly increase the time required to complete the failover operation. Oracle Corporation recommends that you always manually add the online redo logs to the standby database prior to starting the failover operation. Also, define the LOG_FILE_NAME_CONVERT initialization parameter to correlate the standby database path names to the online redo logs.

3. Shut down the standby instance:

   SQL> SHUTDOWN IMMEDIATE;
   
   

4. At this point, the former standby database is now your primary database. Back up your new primary database. This task, while not required, is a recommended safety measure, because you cannot recover changes made after failover without a backup.
5. Start the new primary instance in read/write mode:

   SQL> STARTUP PFILE=initPRIMARY.ora
   
   

   Now that you have switched the database role, use the proper initialization parameter file.

   See Also: Section 5.6.3 for information about setting up initialization parameter files for the standby and primary roles

7.3.4.3 Intentional Data Loss (Physical Standby Databases Only)

If the physical standby database has standby redo logs, you should always attempt a graceful failover operation. However, this may result in undesirable modifications being applied to the physical standby database. The following example shows the error produced if you attempt to fail over a physical standby database without applying all received archived logs:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; 
ERROR at line 1: 
ORA-16140: standby online logs have not been recovered 

You can force the failover to the physical standby database, which will result in lost data. For example:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE 
  2> SKIP STANDBY LOGFILE; 

Note: Oracle Corporation recommends that you always apply all data to the standby database prior to the failover operation.