Oracle9i Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-01 |
|
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:
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.
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:
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:
See Also:
Section 7.2 for information about switchover 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:
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.
Text description of the illustration roadmap.gif
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:
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:
Carefully plan each switchover operation so that the primary and standby databases involved have as small a transactional lag as possible.
Before starting a switchover operation, verify that:
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 |
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 |
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 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.
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.
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.
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.
Text description of the illustration before.gif
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:
WITH SESSION SHUTDOWN
clause on the SQL statement.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.
Text description of the illustration between.gif
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;
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.
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];
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.
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;
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.
Text description of the illustration switch.gif
See Also:
Chapter 13 and the Oracle9i SQL Reference for more information about the SQL statement syntax |
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.
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.
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 |
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. |
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.
Defer archiving redo logs on the new logical standby database. For example:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
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];
Enable archiving redo logs to the new logical standby database. For example:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
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 |
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.
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:
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 |
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.
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:
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:
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.
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.
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.
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.
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:
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. |
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:
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 processSYNC
- synchronous network transmissionAFFIRM
- synchronous archived log disk I/OREGISTER
- archived log registration on the standby databaseFurthermore, 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 |
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.
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 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
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.
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.
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.
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.
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;
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 |
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;
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.
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.
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:
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:
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
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.
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.
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.
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.
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 processASYNC
- asynchronous network transmissionSERVICE
- standby databaseREGISTER
- archived log registration on the standby databaseUsing 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;
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.
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.
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:
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:
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.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Use an appropriate operating system utility for transferring binary data; for example:
% cp /oracle/arch_dest/*.arc /standby/arch_dest
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';
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
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.
Perform the following steps to fail over to the standby database:
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.
SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
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 |
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. |
|
Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|