Release 1 (9.0.1)
Part Number A88808-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Data Guard Concepts and Administration Release 1 (9.0.1) Part Number A88808-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 can be in one of two mutually exclusive roles: primary or standby. You can change these roles dynamically as a planned transition or you can change as a result of a database failure. This is known as role transition.
For example, a primary database can change to the standby role so that one of its corresponding standby databases can change to the primary role. This planned transition occurs without having to reinstantiate either database. This is known as a switchover operation.
In a failure of the primary database, such as a system or software failure, you may need to change one of its corresponding 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 user-defined parameters of the primary database and on the configuration of the standby databases. This type of transition often requires you to instantiate a new standby database from the newly activated primary database. This is known as a failover operation.
The active role of a database is more than conceptual, because there are physical aspects of the role that you must take into consideration. For example, a database in the primary role uses a current control file, while a database in the standby role uses a standby control file. Using different control files, depending on which database role is active, requires careful coordination of the initialization parameter file.
Table 5-1 describes four methods for changing database roles.
Role transition requires all instances whose roles have changed to be shut down and restarted before the new role become effective.
Figure 5-1 is a road map of the operations that are discussed in the following sections of this chapter.
This section contains the following topics:
You can switch a database role from primary to standby, as well as from standby to primary, without resetting the online redo logs of the new primary database. This is known as a database switchover operation, instead of a failover operation, because there is no loss of application data, and there is no need to reinstantiate the standby databases, including the other standby databases not involved in the switchover operation.
On the primary database, the SQL ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY statement is used to prepare the current primary database for switchover to the standby role. On one of the standby databases, the SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY statement is used to prepare the standby database for switchover to the primary role.
This is a database switchover, performed in reverse, which results in the original primary database becoming a new primary database. There is no actual database switchback operation. Use the switchover procedure instead.
Database failover changes one of the standby databases into the role of primary database. You should perform a standby database failover only in the event of a software or system failure that results in the loss of the primary database.
One of the consequences of a graceful database failover is that the original primary database must be reinstantiated. However, the other standby databases in the configuration do not need to be reinstantiated.
Depending on the log transport services attributes, it may be possible to automatically recover some or all of the primary database modifications. The SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH statement is used to finalize the recovery of the archived logs on the standby database.
To issue the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH statement, you must set compatibility to 9.0.1.0.0 in the initialization parameter file. Furthermore, when you apply the standby redo logs, compatibility must remain at 9.0.1.0.0 from this point forward. Add the following line to the initialization parameter file:
compatible=9.0.1.0.0
If the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH statement is canceled before the standby redo logs have been applied, you can return to an 8.0 or 8.1 release.
Database failover changes one of the standby databases into the role of primary database. You should perform a standby database failover only in the event of a software or system failure that results in the loss of the primary database.
One of the consequences of a forced database failover is that the original primary database and all other standby databases must be reinstantiated. Another consequence is that there may be lost application data unless the standby and primary databases have been configured to run in guaranteed protection mode.
The SQL ALTER DATABASE ACTIVATE STANDBY DATABASE statement changes the state of a standby database to an active database and prepares it to become the primary database.
You can switch a database role from primary to standby, as well as from standby to primary. Figure 5-2 shows a typical two-site Data Guard configuration before the roles of the databases are switched.
The 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.
Switchover is always initiated from the primary database and is always completed on a standby database.
When you perform a switchover operation, the existing control file type is converted in place. That is, the primary database current control file is converted into a standby control file, and a standby control file is converted into a primary control file.
This section contains the following topics:
The act of switching database roles should be a well-planned activity. The primary and standby databases involved in the switchover operation should have as small a transactional lag as possible.
Oracle Corporation highly recommends that you consider performing a full, consistent backup of the primary database prior to starting the switchover procedure.
You cannot 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.
This section describes how to set up log transport services on the standby database in preparation for switchover. Before starting a switchover operation, verify that:
Oracle Corporation suggests that you maintain two database initialization parameter files at both the primary and standby databases. This will allow you to easily change the databases from the primary role to the standby role or from the standby role to the primary role.
Most initialization parameters in the initialization parameter files of the primary and standby databases should be identical, although some initialization parameters such as CONTROL_FILES, LOCK_NAME_SPACE, and DB_FILE_NAME_CONVERT may differ.
To run a standby database in a Data Guard environment, you must configure an Oracle Net connection between the primary and standby databases so that you can archive the redo logs to the standby site, and so that the FAL client can fetch missing log files from the FAL server. Furthermore, all primary and standby databases in the configuration should have network connectivity to all other databases in the configuration.
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 |
When your database is using Real Application Clusters, only one instance is allowed to perform the switchover operation. Shut down all other instances prior to performing the switchover operation.
The switchover operation requires that only one session be active on the instance performing the switchover. Manually terminate all other sessions prior to the switchover operation. Active sessions may include processes other than SQL sessions.
The standby database must be mounted and ideally should be in managed recovery mode before you start a primary database switchover operation.
You can switch a database from the primary role to the standby role when the instance is either mounted or open.
At least one standby database must be active in the Data Guard configuration. The standby database must be mounted and have proper network connectivity. Ideally, the standby database that has been targeted to become the new primary database will be mounted and actively recovering when the database roles are switched. If the standby database is open for read-only access, the standby switchover operation will take longer.
The database that will become the new primary database should be in ARCHIVELOG mode.
For a database using Real Application Clusters, only one primary instance is allowed to perform the switchover operation. Shut down all other instances prior to the switchover operation.
The switchover operation requires that only one SQL session be active on the primary instance. Terminate all other SQL sessions prior to switchover.
To verify whether or not it is possible to perform a switchover operation, refer to 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 of the SWITCHOVER_STATUS column indicates you are allowed to switch the database from the primary role to the standby role.
|
See Also:
V$DATABASE in Chapter 10, "Fixed Views" for possible values of the |
To perform the primary-to-standby role switchover operation, issue the following SQL statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
The primary database role will now be 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.
|
Note: Should you wish to convert the original primary database back, you must use the standby switchback procedure. See Section 5.3.4. |
Following completion of the switchover operation, you must shut down the instance and restart it as a standby database.
Figure 5-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. Therefore, you temporarily have two standby databases.
You can switch a database from the standby role to the primary role when the instance is either mounted or open for read-only access. You can also use this procedure to revert a primary database to its original role.
For a database using Real Application Clusters, only one standby instance is allowed to perform the switchover operation. Shut down all other instances prior to the switchover operation.
The switchover operation requires that only one SQL session be active on the standby instance. Terminate all other SQL sessions prior to switchover.
The primary database must have previously switched roles to the standby database. Also, the standby database must have applied the switchover notification from the primary database.
The standby database must be in managed recovery mode prior to starting a switchover operation, so that the primary database switchover operation request can be coordinated. If managed recovery is not active, or the primary database switchover notification cannot be processed, the switchover operation cannot proceed.
To verify whether it is possible to perform a switchover operation, query 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 ----------------- TO PRIMARY 1 row selected
The TO PRIMARY value of the SWITCHOVER_STATUS column indicates you are allowed to switch the database from the standby role to the primary role.
After you switch the primary database to the standby role and the switchover notification has been received by the standby database, you should then manually place the standby database in managed recovery mode. This allows the primary database switchover notification to be processed by the standby database. To verify whether the switchover notification has been processed by the standby database, query 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.
|
See Also:
V$DATABASE in Chapter 10, "Fixed Views" for possible values of the |
To perform the primary-to-standby-role operation, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
To perform the standby-to-primary role switchover operation, issue the following SQL statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
The selected standby database will now be converted into a primary database. The current standby control file will not be backed up to the current trace file. You can always reconstruct the standby control file from the original primary database which is a new standby database that has a copy of the control file.
Following completion of this operation, you must shut down the instance and restart it as a primary database. Figure 5-4 shows a Data Guard environment after a switchover has taken place.
Normally, you choose only one standby database to become the new primary database. 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 switch over multiple standby databases, each will become a separate and distinct primary database. Any remaining standby databases can only be connected to a single primary database.
There is no need to reinstantiate any other standby databases not involved in the switchover operation. These standby databases will continue to function normally.
When your database is using Real Application Clusters, active instances prevent a switchover from being performed. When other instances are active, an attempt to switch over fails with the following error message:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY * ORA-01105: mount is incompatible with mounts by other instances
Should this error occur, query the GV$INSTANCE view as follows to determine which instances are causing the problem:
SQL> SELECT INSTANCE_NAME, HOST_NAME FROM GV$INSTANCE 2> WHERE INST_ID <> (SELECT INST_ID FROM V$INSTANCE); INSTANCE_NAME HOST_NAME ------------- --------- INST2 standby2
In the previous example, the identified instance must be manually shut down before the switchover can proceed. You can connect to the identified instance from your instance and issue the SHUTDOWN statement remotely, for example:
SQL> CONNECT sys/change_on_install@standby2 AS SYSDBA SQL> SHUTDOWN; SQL> EXIT
Active SQL sessions prevent a switchover from being processed. Active SQL sessions may include other Oracle processes.
When sessions are active, an attempt to switch over fails with the following error message:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY * ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Should this occur, query the V$SESSION view to determine which processes are causing the error. For example:
SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION 2> WHERE TYPE = 'USER' 3> AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT); SID PROCESS PROGRAM --------- -------- ------------------------------------------------ 7 3537 oracle@nhclone2 (CJQ0) 10 14 16 19 21 6 rows selected.
In the previous example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry. Because the job queue process is a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by the job queue controller.
Verify that the JOB_QUEUE_PROCESSES parameter is set using the following SQL statement:
SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; NAME TYPE VALUE ------------------------------ ------- -------------------- job_queue_processes integer 5
Then set the parameter to 0. For example:
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; Statement processed.
Because JOB_QUEUE_PROCESSES is a dynamic parameter, you can change the value and have the change take effect immediately without having to restart the instance. You can now retry the switchover procedure.
You should not modify the parameter in your initialization parameter file. After you shut down the instance and restart it after switchover has completed, the parameter will be reset to the original value. This applies to both primary and standby databases.
Table 5-2 summarizes the common process that prevent switchover and what corrective action you need to take.
After the SQL ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY statement has been issued on the primary database, the resulting 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 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 is an example of a successful managed recovery operation as shown in the standby alert log:
MRP0: Media Recovery Complete: End-Of-REDO Resetting standby activation ID 3830496333 (0xe450bc4d) MRP0: Background Media Recovery process is now terminated
If the last primary database online redo log is not archived to the original standby database, or if the SQL ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY statement is issued before the archived log has been applied to the original standby database, you will have two standby databases and no primary database.
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 standby database. If not, you can manually copy the archived log from the primary database to the 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 automatically applied. Query the SWITCHOVER_STATUS column in the V$DATABASE view to verify that switchover to the primary role is now possible.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO PRIMARY 1 row selected
In some situations where an error has occurred, it may still be possible to revert the new standby database back 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 rearchive any missing archived logs to the standby database. Force a log switch on the primary database and examine the alert logs on both the primary and standby databases to ensure that the archived log sequence numbers are correct.
|
See Also:
Section 4.5 for information about archive gap management and Section 4.9.5 for information about locating the trace files |
A failover operation is typically used when a catastrophic failure occurs on the primary database, and there is no possibility of recovering the primary database in a timely manner. The primary database is discarded and the standby database assumes the primary database role.
You should not fail over to a standby database except in an emergency. After failover, the standby database becomes an ordinary primary database and loses its previous standby database capability. That is, you cannot return the database to a standby mode that is compatible with the original primary database.
There are two major differences between a database failover operation and a database switchover operation:
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 5.4.6.4.
Note:
This section contains the following topics:
Data divergence is the temporary state of the primary database that occurs when a standby database becomes inaccessible. While in the data divergence state, the primary database is able to commit transactions whose data modifications are not immediately available on the standby database. Data divergence also occurs when you use the LGWR or ARCn process for asynchronous standby database archival operations.
Data loss occurs when you fail over to a standby database whose corresponding primary database is in the data divergence state. To prevent primary database data divergence if network connectivity fails, use the guaranteed protection mode.
The primary database protection modes are failure policies that dictate how to manage the primary database if a standby database becomes inaccessible. There are four primary database protection modes:
Guaranteed protection mode dictates that the primary database modifications must always be available on at least one standby database; data divergence is prohibited by terminating the primary instance if it loses network connectivity to the last standby database.
Instant protection mode dictates that the primary database modifications always be available on at least one standby database. Unlike guaranteed protection mode, data divergence is not prohibited by the instant protection mode. Data divergence exists for the duration that standby database connectivity is unavailable, and can be resolved when network connectivity to the primary database is reestablished.
Rapid protection mode indicates that primary database modifications are available to the standby database as soon as possible with minimal effect on primary database performance.
Delayed protection mode indicates that primary database modifications will ultimately be available on the standby site, as long as the network is active. Both the rapid and delayed protection modes allow the primary database to diverge from all standby databases, even when network connectivity is available. The degree of data divergence is equivalent to the data contained in the unarchived primary database online redo logs.
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-divergence semantics, such as guaranteed and instant protection mode. This means that all primary database archived redo logs necessary for no-data-loss failover are available. 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 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 rapid and delayed protection modes. 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.
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.
To avoid or minimize data loss during a database failover operation, always use standby redo logs. See Section 3.6.3.4 for information on 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 standby database. Depending on the log transport services options used, this may provide no-data-loss or minimal-data-loss failover.
If the 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.
In both cases, the original primary database is incompatible with the resulting new primary database.
Figure 5-5 shows the result of a failover operation from a primary database in San Francisco to a standby database in Boston.
The presence of standby redo logs on the standby database determines whether a graceful failover is possible. 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. These two types of failover are discussed in Section 5.4.6 and Section 5.4.7.
Database failover changes one of the standby databases into the role of primary database. You should perform a standby database failover only in the event of a software or system failure that results in the loss of the primary database.
This section contains the following topics:
No-data-loss failover to the standby database can be achieved when the primary database log transport services has been previously set up to provide this capability. The primary database must be using the guaranteed or instant protection mode. These modes require that the primary database have the following archived log destination attributes:
LGWR - archived by the LGWR process
SYNC - synchronous network transmission
SERVICE - standby database
AFFIRM - synchronous archived log disk I/O (implied if primary database is in PROTECTED mode)
REGISTER - archived log registration on the standby database
MANDATORY - online redo log must be archived to standby
Furthermore, the archived log destinations cannot have the following attribute:
In addition, standby databases participating in the no-data-loss environment must have standby redo logs available.
If a failure occurs at the primary site, there will be incomplete standby redo logs that can be salvaged with the RECOVER ... FINISH statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
When you start managed recovery, specify the FINISH control option to define the mode of the recovery operation. You can also specify the FINISH control option to alter the mode of an ongoing managed recovery operation. If the FINISH control option is being used to alter the mode of an ongoing managed recovery operation, specifying the NOWAIT option allows control to be returned to the foreground process before the recovery operation completes.
When the NOWAIT option is used, the V$MANAGED_STANDBY fixed view may be checked to verify when the managed recovery operation is finished. There should be no rows selected by the following statement after recovery has completed:
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY;no rows selected
Minimal-data-loss failover to the standby database can be achieved when the primary database log transport services have been previously set up to provide this capability. Standby databases participating in the minimal-data-loss environment must have the following archived log destination attributes:
LGWR - archived by the LGWR process
ASYNC - asynchronous network transmission
SERVICE - standby database
REGISTER - archived log registration on the standby database
Furthermore, the archived log destinations cannot have the following attribute:
In addition, standby databases participating in the minimal-data-loss environment must have standby redo logs available.
Using a lower block count for the archived log destination ASYNC attribute minimizes the amount of potential data loss but possibly decreases primary database throughput. The archived log destination AFFIRM attribute can also be used to further reduce potential data loss.
If a failure occurs at the primary site, there will be incomplete standby redo logs on the standby database. The standby redo log contents can be salvaged with the RECOVER...FINISH statement, for example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
After a graceful failover, subsequent redo logs from the original primary database cannot be applied to the standby database. The standby database is "on hold." The standby redo logs have been archived and should be copied to, registered, and recovered on other standby databases derived from the original primary database.
You must now change a standby database into the new primary database. Change one of the available standby databases, which may include the original primary database, to the primary role by issuing the following statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
No reinstantiation of the other standby databases is required. Once the archived standby redo logs have been registered and recovered on all destinations, the other standby databases are ready to receive redo logs from the new primary database. To register the copied archived redo logs, issue the following statement on each standby database:
SQL> ALTER DATABASE REGISTER LOGFILE '/standby/arch_dest/arch_1_101.arc'; SQL> ALTER DATABASE REGISTER LOGFILE '/standby/arch_dest/arch_1_102.arc';
The original primary database must be reinstantiated from a backup of the new primary database.
It may be faster to recover the primary database, even you are when using a no-data-loss environment.
When the primary database is operating in no-data-loss mode and a system failure occurs, you can restart the primary database without incurring data loss on the corresponding standby databases. The following failure scenarios are automatically recovered by log transport services:
SHUTDOWN ABORT automatically performs failure recovery.
In both of these scenarios, the standby databases participating in the no-data-loss environment will be automatically resynchronized with the recovered primary database. However, for the resynchronization to occur, the primary database archived log destinations must be properly established to identify the standby databases, and network connectivity must exist between the primary database and the standby databases.
Most failures can be resolved at a primary site within a reasonable amount of time. 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 switch to the standby database:
There are two methods to perform a failover operation. Which to use depends on whether the standby database contains standby redo logs. If standby redo logs are available, you must either perform a graceful failover operation or disregard the standby redo logs intentionally and perform a forced failover operation. If standby redo logs are not available, your only method is to activate the standby database, which is a forced failover operation.
You should not perform a forced failover to the standby database except in an emergency.
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 on changing to the primary database role.
Before you can execute a forced failover, you must stop the primary database archival operations to the standby database. See Section 3.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 always attempt to transfer as much of the missing primary database contents to the standby database. The more database modifications that can be transferred to the standby site, the closer the new primary database will match to the original primary database.
Four steps are necessary to move modifications from the primary database to the standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
However, in a true database failover situation, this is seldom possible. If you are able to sufficiently recover the primary database to permit online redo log archival, then you can probably recover the entire primary database.
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
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;
This procedure rolls the standby database forward as close to the time of the primary database failure as possible.
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 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 3.5.3 for information about setting up initialization parameter files for the standby and primary roles |
If the 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 standby database. The following shows the error produced if you attempt to activate a 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 standby database, which will result in lost data. For example:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE 2> SKIP STANDBY LOGFILE;
Table 5-3 summarizes the database role transition options and requirements.
You can use the Recovery Manager utility (RMAN) to back up the primary database at the standby site. This allows the standby database to offload the task of database backup from the primary database. Using RMAN at the standby site, you can back up the datafiles and the archived redo logs while the standby database is in managed recovery mode. These backups can be later restored to the primary database with RMAN.
The primary control file, however, still must be backed up at the primary site. This is not a problem, because the control file is significantly smaller than the datafiles.
|
See Also:
Oracle9i Recovery Manager User's Guide for more details about RMAN backup and recovery of a primary database using a standby database and Section 6.12 for a relevant scenario |
To prevent possible problems, you should be aware of events that affect a standby database and learn how to monitor them. Most changes to a primary database are automatically propagated to a standby database through archived redo logs and so require no user intervention. Nevertheless, some changes to a primary database require manual intervention at the standby site.
This section contains the following topics:
The Oracle database server contains a set of underlying views that are maintained by the server. These views are often called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. These views are also called fixed views, because they cannot be altered or removed by the database administrator.
These view names are prefixed with either V$ or GV$, for example, V$ARCHIVE_DEST or GV$ARCHIVE_DEST.
Standard dynamic performance views (V$ fixed views) store information on the local instance. In contrast, global dynamic performance views (GV$ fixed views), store information on all open instances. Each V$ fixed view has a corresponding GV$ fixed view.
The following fixed views contain useful information for monitoring the Data Guard environment:
V$ARCHIVE_DEST
Describes the archived redo log destinations associated with the current instance on the primary site. You can use this view to find out the current settings of your archived redo log destinations.
V$ARCHIVE_DEST_STATUS
This view, an extension of the V$ARCHIVE_DEST view, displays runtime and configuration information for the archived redo log destinations. You can use this view to determine the progress of archiving to each destination. It also shows the current status of the archive destination.
V$ARCHIVE_GAP
Provides information about archive gaps. You can use this view to find out the current archive gap that is blocking recovery.
V$ARCHIVED_LOG
Displays archived redo log information from the control file, including archived log names. This view gives you information on which log has been archived to where on your primary database. On the primary database, this view describes the logs archived to both the local and remote destinations. On a standby database, this view provides information about the logs archived to this standby database. You can use this fixed view to help you to track archiving progress to the standby site.
V$DATABASE
Contains database information from the control file. You can use this view to quickly find out if your database is a primary or a standby database, as well as its switchover status.
V$DATAFILE
Contains datafile information from the control file. You can query this fixed view to verify that the standby datafiles are correctly renamed after your standby database is instantiated.
|
See Also:
Table 6-1 and Section 6.3.2 for information on renaming standby datafiles when instantiating a standby database |
V$LOG
Contains log file information from the online redo logs. You can use the information about the current online log on the primary database from this view as a reference point to determine how far behind your standby database is in receiving and applying logs.
V$LOGFILE
Contains information about the online redo logs and standby redo logs.
V$LOG_HISTORY
Contains log history information from the control file, including a record of the latest archived log that was applied.
V$MANAGED_STANDBY
Displays current and status information for some Oracle database server processes related to Data Guard. This view can show both foreground and background processes. You can use this view to monitor the various Data Guard recovery and archiving processes.
V$STANDBY_LOG
Provides information about the standby redo logs. Standby redo logs are similar to online redo logs, but they are only used on a standby database receiving logs from the primary database using the log writer process.
|
See Also:
Chapter 10, "Fixed Views" and the Oracle9i Database Reference for additional information on view columns |
Table 5-4 indicates whether a primary database event is automatically administered by log transport services and log apply services or requires additional intervention by the database administrator (DBA) to be propagated to the standby database. It also describes how to respond to these events.
| Primary Database Event | Primary Site Problem Report Location | Standby Site Problem Report Location | Recommended Response |
|---|---|---|---|
|
Archiving errors |
Remote file server (RFS) process trace file |
Fix the problem reported in the alert log file or trace file and resume archiving to the destination. If the problem persists or archiving performance is degraded, you can create scripts to send the archived redo logs. |
|
|
Thread events |
Alert log |
Thread events are automatically propagated through archived logs, so no extra action is necessary. |
|
|
Redo log changes |
|
Alert log |
Redo log changes do not affect the standby database unless a redo log is cleared or lost. In these cases, you must rebuild the standby database. See Section 2.3.
Pre-clear the logs on the standby database with the |
|
Issue |
Alert log |
Database functions normally until it encounters redo depending on any parameter changes. |
Re-create the standby control file. See Section 5.8.8.
Re-create the standby database if the primary database is opened with the |
|
Managed recovery performed |
Alert log |
Alert log |
Re-create the standby database if the |
|
Tablespace status changes (made read/write or read-only, placed online or offline) |
Status changes are automatically propagated, so no response is necessary. Datafiles remain online. |
||
|
Add datafile or create tablespace |
If you have not set the |
||
|
Drop tablespace |
Alert log |
If you have not set the |
|
|
Tablespace or datafile taken offline, or datafile is deleted offline |
The tablespace or datafile requires recovery when you attempt to bring it online. |
Datafiles remain online. The tablespace or datafile is fine after standby database activation. |
|
|
Rename datafile |
Alert log |
Alert log |
See Section 5.8.2. |
|
Unlogged or unrecoverable operations |
Alert log. File blocks are invalidated unless they are in the future redo, in which case they are not touched. |
Unlogged changes are not propagated to the standby database. See Section 5.8.7 on how to apply these changes. |
|
|
Recovery progress |
Check the views on the primary site and the standby site for recovery progress. See Section 4.9. |
||
|
Autoextend a datafile |
Alert log |
May cause operation to fail on standby database because it lacks disk space. |
Ensure that there is enough disk space for the expanded datafile. |
|
Issue |
Alert log |
Standby database is invalidated. |
Rebuild the standby database. See Section 2.3. |
|
Change initialization parameter |
Alert log |
May cause failure because of redo depending on the changed parameter. |
Dynamically change the standby parameter or shut down the standby database and edit the initialization parameter file. See Chapter 7, "Initialization Parameters". |
Query the V$LOG_HISTORY view on the standby database, which records the latest log sequence number that has been applied. For example, issue the following query:
SQL> SELECT thread#, max(sequence#) AS "LAST_APPLIED_LOG" 2> FROM v$log_history 3> GROUP BY thread#; THREAD# LAST_APPLIED_LOG ------- ---------------- 1 967
In this example, the archived redo log with log sequence number 967 is the most recently applied log.
You can also use the APPLIED column in the V$ARCHIVED_LOG fixed view on the standby database to find out which log is applied on the standby database. The column displays YES for the log that has been applied. For example:
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG; THREAD# SEQUENCE# APP ---------- ---------- --- 1 2 YES 1 3 YES 1 4 YES 1 5 YES 1 6 YES 1 7 YES 1 8 YES 1 9 YES 1 10 YES 1 11 NO 10 rows selected.
Each archive destination has a destination ID assigned to it. You can query the DEST_ID column in the V$ARCHIVE_DEST fixed view to find out your destination ID. You can then use this destination ID in a query on the primary database to discover logs that have not been sent to a particular standby site.
For example, assume the current local archive destination ID on your primary database is 1, and the destination ID of one of your remote standby databases is 2. To find out which logs have not been received by this standby destination, issue the following query on the primary database:
SQL> SELECT local.thread#, local.sequence# from 2> (select thread#, sequence# from v$archived_log where dest_id=1) local 3> where 4> local.sequence# not in 5> (select sequence# from v$archived_log where dest_id=2 and 6> thread# = local.thread#); THREAD# SEQUENCE# ---------- ---------- 1 12 1 13 1 14
The preceding example shows the logs that have not yet been received by standby destination 2.
Changes to the primary database structure always affect a standby database. In cases such as the following, the standby database is updated automatically through applied redo:
However, you might need to perform maintenance on the standby database when you add a datafile to the primary database or create a tablespace on the primary database.
In addition, you must re-create the standby database entirely when you restore a backup control file on the primary database or open the primary database with the RESETLOGS option.
This section contains the following topics:
Adding or dropping a tablespace and/or adding or deleting a datafile in the primary database generates redo logs that, when applied to the standby database, automatically add or delete the datafile name or add or drop the tablespace in the standby control file. If the standby database locates the file with the filename specified in the control file, then recovery continues. If the standby database is unable to locate a file with the filename specified in the control file, then recovery terminates.
Data Guard automatically updates tablespaces and datafiles on the standby site if you use the STANDBY_FILE_MANAGEMENT parameter with the auto option. For example:
STANDBY_FILE_MANAGEMENT=auto
|
See Also:
Section 6.3.1 for more information on adding datafiles and tablespaces and Section 6.3.3 for more information on deleting datafiles and dropping tablespaces |
If you rename a datafile on your primary database, the new name does not take effect at the standby database until you refresh the standby database control file. To keep the datafiles at the primary and standby databases synchronized when you rename primary database datafiles, you must update the standby database control file.
You can add redo log file groups or members to the primary database without affecting the standby database. Similarly, you can drop log file groups or members from the primary database without affecting your standby database. Enabling and disabling of threads at the primary database has no effect on the standby database.
Consider whether to keep the online redo log configuration the same at the primary and standby databases. Although differences in the online redo log configuration between the primary and standby databases do not affect the standby database functionality, they do affect the performance of the standby database after activation. For example, if the primary database has 10 redo logs and the standby database has 2, and you then activate the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database.
If you clear log files at the primary database by issuing the ALTER DATABASE CLEAR UNARCHIVED LOGFILE statement, or open the primary database using the RESETLOGS option, you invalidate the standby database. Because both of these operations reset the primary log sequence number to 1, you must re-create the standby database in order to be able to apply archived logs generated by the primary database.
If you use the CREATE CONTROLFILE statement at the primary database to perform any of the following operations, you may invalidate the control file for the standby database:
Using the CREATE CONTROLFILE statement with the RESETLOGS option on your primary database will force the next open of the primary database to reset the online logs, thereby invalidating the standby database.
If you have invalidated the control file for the standby database, re-create the file using the procedures in Section 5.8.8.
You can take standby database datafiles offline as a means to support a subset of your primary database's datafiles. For example, to skip recovery of datafiles that were not copied to the standby database, take the missing datafiles offline using the following statement on the standby database:
SQL> ALTER DATABASE DATAFILE 'MISSING00004' OFFLINE DROP;
If you execute this statement, then you must drop the tablespace containing the offline files after opening the standby database.
When you perform a direct load originating from any of the following, the performance improvement applies only to the primary database (there is no corresponding recovery process performance improvement on the standby database):
Primary database operations using the UNRECOVERABLE option are not propagated to the standby database because these operations do not appear in the archived redo logs. If you perform an unrecoverable operation at the primary database and then recover the standby database, you do not receive error messages during recovery; instead, the Oracle database server writes error messages in the standby database alert log file. The following error message is displayed:
Errors in file /oracle/rdbms/log/rcv12_ora_150.trc: ORA-01578: ORACLE data block corrupted (file # 1, block # 36031) ORA-01110: data file 1: '/oracle/dbs/s1_t_db1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
To correct your standby database following an unrecoverable operation, you will need to perform one or more of the following tasks:
If you have performed unrecoverable operations on your primary database, determine whether a new backup is required.
V$DATAFILE view on the primary database to determine the system change number (SCN) or time at which the Oracle database server generated the most recent invalidation redo data.
SELECT unrecoverable_change#, to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss') FROM v$datafile;
V$DATAFILE in Chapter 10, "Fixed Views" and the Oracle9i Database Reference for more information about the
See Also:
V$DATAFILE view
The following steps describe how to refresh, or create a copy, of changes you have made to the primary database control file. Refresh the standby database control file after making major structural changes to the primary database, such as adding or deleting files.
CANCEL statement on the standby database to halt its recovery process.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.dbf';
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
After creating the standby database, you can clear online redo logs on the standby site to optimize performance by issuing the following statement, where 2 is the number of the log group:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
This statement optimizes failover to the standby database because it is no longer necessary for the Oracle database server to clear the logs before failover. Clearing involves writing zeros to the entire contents of the redo log and then setting a new header to make the redo log look like it was when it was created.
If you clear the logs manually, the Oracle database server realizes at activation that the logs already have zeros and skips the clearing step. This optimization is important because it can take a long time to write zeros into all of the online logs. If you prefer not to perform this operation during maintenance, the Oracle database server clears the online logs automatically during failover.
You can use a standby database to protect a primary database using Real Application Clusters. The following table describes the possible combinations of instances in the primary and standby databases:
| Instance Combinations | Single-Instance Standby Database | Multi-Instance Standby Database |
|---|---|---|
| Single-Instance Primary Database |
Yes |
Yes (for read-only queries) |
| Multi-Instance Primary Database |
Yes |
Yes |
In each scenario, each instance of the primary database archives its own online redo logs to the standby database. For example, Figure 5-6 illustrates a Real Application Clusters database with two primary database instances (a multi-instance primary database) archiving redo logs to a single-instance standby database.
In this case, Instance 1 of the primary database transmits logs 1, 2, 3, 4, 5 while Instance 2 transmits logs 32, 33, 34, 35, 36. If the standby database is in managed recovery mode, it automatically determines the correct order in which to apply the archived redo logs.
If both your primary and standby databases are in a Real Application Clusters configuration, and the standby database is in managed recovery mode, then a single instance of the standby database applies all sets of logs transmitted by the primary instances. In this case, the standby instances that are not applying redo cannot be in read-only mode while managed recovery is in progress; in most cases, the non-recovery instances should be shut down, although they can also be mounted.
|
See Also:
Oracle9i Real Application Clusters Installation and Configuration for information about configuring a database for Real Application Clusters |
This section contains the following topic:
It is possible to set up a cross-instance archival database environment. Within a Real Application Cluster configuration, each instance directs its archived redo logs to a single instance of the cluster. This instance is called the recovery instance and is typically the instance where managed recovery is performed. This instance typically has a tape drive available for RMAN backup and restore support. Example 5-1 shows how to set up the LOG_ARCHIVE_DEST_n initialization parameters for archiving redo logs across instances. Execute this example on all instances except the recovery instance.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = `LOCATION=archivelog MANDATORY REOPEN=120'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = enable; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = `SERVICE=prmy1 MANDATORY REOPEN=300'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = enable;
Destination 1 is the repository containing the local archived redo logs required for instance recovery. This is a mandatory destination. Because the expected cause of failure is lack of adequate disk space, the retry interval is 2 minutes. This should be adequate to allow the DBA to purge unnecessary archived redo logs. Notification of destination failure is accomplished by manually searching the primary database alert log.
Destination 2 is the primary database on the instance where RMAN is used to back up the archived redo logs from local disk storage to tape. This is a mandatory destination, with a reconnect threshold of 5 minutes. This is the time needed to fix any network-related failures. Notification of destination failure is accomplished by manually searching the primary or standby database alert log.
Cross-instance archival is available using the ARCn process only. Using the LGWR process for cross-instance archival results in the RFS process failing and the archive log destination being placed in the Error state.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|