8
Managing a Physical Standby Database

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

This chapter contains the following topics:

• Backing Up the Primary Database Using the Standby Database
• Monitoring Events That Affect the Standby Database
• Responding to Events That Affect the Standby Database
• Standby Databases in an Oracle Real Application Clusters Configuration

8.1 Backing Up the Primary Database Using the Standby Database

You can use the Recovery Manager utility (RMAN) to back up the primary database at the standby site. This allows the standby database to off-load 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 10.1.10 for a relevant scenario

8.2 Monitoring Events That Affect the Standby Database

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 thus require no user intervention. Nevertheless, some changes to a primary database require manual intervention at the standby site.

This section contains the following topics:

• Dynamic Performance Views (Fixed Views)
• Monitoring the Primary and Standby Databases
• Determining Which Logs Have Been Applied to the Standby Database
• Determining Which Logs Have Not Been Received by the Standby Site

8.2.1 Dynamic Performance Views (Fixed Views)

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 on a standby database. 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 system by viewing the applied field.

• 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 and standby database protection mode.

• 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 re-created.

  See Also: Table 10-1 and Section 10.1.3.2 for information on renaming standby datafiles when re-creating a standby database

• V$DATAGUARD_STATUS

  Displays and logs events related to Data Guard since the instance was started.

• 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 static 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 on the standby system.

• 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 14, "Views" and the Oracle9i Database Reference for additional information on view columns

8.2.2 Monitoring the Primary and Standby Databases

Table 8-1 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.

Table 8-1  Troubleshooting Primary Database Events

Primary Database Event	Primary Site Problem Report Location	Standby Site Problem Report Location	Recommended Response
Archiving errors	ERROR column of V$ARCHIVE_DEST view Alert log ARCHIVED column of V$LOG view Archiving trace files	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 V$THREAD view	Alert log	Thread events are automatically propagated through archived logs, so no extra action is necessary.
Redo log changes	Alert log V$LOG view and STATUS column of V$LOGFILE view	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 3.3. Clears the logs on the standby database with the ALTER DATABASE CLEAR LOGFILE statement. See Section 8.3.9.
Issue a CREATE CONTROLFILE statement	Alert log	Database functions normally until it encounters redo depending on any parameter changes.	Re-create the standby control file. See Section 8.3.8. Re-create the standby database if the primary database is opened with the RESETLOGS option. See Section 3.3.
Managed recovery performed	Alert log	Alert log	Re-create the standby database if the RESETLOGS option is utilized. See Section 3.3.
Tablespace status changes (made read/write or read-only, placed online or offline)	DBA_TABLESPACES view Alert log	Verify that all datafiles are online. V$RECOVER_FILE view	Status changes are automatically propagated, so no response is necessary. Datafiles remain online.
Add datafile or create tablespace	DBA_DATA_FILES view Alert log	ORA-283, ORA-1670, ORA-1157, ORA-1110 Standby recovery stops.	If you have not set the STANDBY_FILE_MANAGEMENT initialization parameter to auto, you must re-create the control file on the standby database. See Section 8.3.8.
Drop tablespace	DBA_DATA_FILES view Alert log	Alert log	If you have not set the STANDBY_FILE_MANAGEMENT initialization parameter to auto, you must refresh the control file on the standby database. See Section 8.3.8.
Tablespace or datafile taken offline, or datafile is deleted offline	V$RECOVER_FILE view Alert log The tablespace or datafile requires recovery when you attempt to bring it online.	Verify that all datafiles are online. V$RECOVER_FILE view	Datafiles remain online. The tablespace or datafile is fine after standby database activation.
Rename datafile	Alert log	Alert log	See Section 8.3.2.
Unlogged or unrecoverable operations	Direct loader invalidates block range redo entry in online redo log. Check V$DATAFILE view. V$DATABASE view	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 8.3.7 on how to apply these changes.
Recovery progress	V$ARCHIVE_DEST_STATUS view Alert log	V$ARCHIVED_LOG view V$LOG_HISTORY view V$MANAGED_STANDBY view Alert log	Check the views on the primary site and the standby site for recovery progress. See Section 6.4.
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 OPEN RESETLOGS or CLEAR UNARCHIVED LOGFILES statements	Alert log	Standby database is invalidated.	Rebuild the standby database. See Section 3.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 11, "Initialization Parameters".

8.2.3 Determining Which Logs Have Been Applied to the Standby Database

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.

8.2.4 Determining Which Logs Have Not Been Received by the Standby Site

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.

8.3 Responding to Events That Affect the Standby Database

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 data:

• Taking a primary tablespace offline or bringing it online
• Changing tablespace status from read/write to read-only and the reverse
• Renaming a datafile
• Dropping a tablespace

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, or perform unlogged DML operations.

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 Tablespaces and Adding or Deleting Datafiles in the Primary Database
• Renaming Datafiles on the Primary Database
• Adding or Deleting Redo Logs on the Primary Database
• Resetting or Clearing Unarchived Redo Logs on the Primary Database
• Altering the Primary Database Control File
• Taking Datafiles in the Standby Database Offline
• Detecting Unlogged or Unrecoverable Operations
• Refreshing the Standby Database Control File
• Clearing Online Redo Logs

8.3.1 Adding or Dropping Tablespaces and Adding or Deleting Datafiles in the Primary Database

Adding or dropping a tablespace 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 10.1.3.1 for more information on adding datafiles and tablespaces and Section 10.1.3.3 for more information on deleting datafiles and dropping tablespaces

8.3.2 Renaming Datafiles on the Primary Database

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.

See Also: Section 10.1.3.2 for a scenario that explains renaming a datafile on the primary database

8.3.3 Adding or Deleting Redo Logs on the Primary Database

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 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 failover to 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.

See Also: Section 8.3.8 for instructions on refreshing the standby database control file

8.3.4 Resetting or Clearing Unarchived Redo Logs on the 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.

See Also: Section 3.3 and Section 10.1.8 for information on re-creating the standby database

8.3.5 Altering the Primary Database Control File

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:

• Change the maximum number of redo log file groups or members
• Change the maximum number of instances that can concurrently mount and open the 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 8.3.8.

8.3.6 Taking Datafiles in the Standby Database Offline

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.

8.3.7 Detecting Unlogged or Unrecoverable Operations

Caution: Unlogged or unrecoverable operations invalidate the standby database and may require substantial DBA administrative activities.

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):

• Direct path load
• CREATE TABLE through subquery
• CREATE INDEX on the primary database

8.3.7.1 Propagating Unrecoverable Operations Manually

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:

• Take the affected datafiles offline in the standby database and drop the tablespace after failover. See Section 8.3.6.
• Re-create the standby database from a new database backup. See Section 3.3.
• Back up the affected tablespace and archive the current logs in the primary database, copy the datafiles to the standby database, and resume standby recovery. This is the same procedure that you would perform to guarantee ordinary database recoverability after an unrecoverable operation.

  See Also: Section 10.1.4

8.3.7.2 Determining Whether a Backup Is Required After Unrecoverable Operations

If you have performed unrecoverable operations on your primary database, determine whether a new backup is required.

To determine whether a new backup is necessary:

1. Query the 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.
2. Issue the following SQL statement on the primary database to determine whether you need to perform another backup:

   SELECT UNRECOVERABLE_CHANGE#, 
          TO_CHAR(UNRECOVERABLE_TIME, 'mm-dd-yyyy hh:mi:ss') 
   FROM   V$DATAFILE;
   

3. If the query in the previous step reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.

   See Also: V$DATAFILE in Chapter 14, "Views" and the Oracle9i Database Reference for more information about the V$DATAFILE view

8.3.8 Refreshing the Standby Database Control File

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.

Caution: Do this only if all archived logs have been applied to the standby database. Otherwise, the archived logs are lost when you create the new standby control file.

To refresh the standby database control file:

1. Start a SQL session on the standby database and issue the CANCEL statement on the standby database to halt its recovery process.

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   

2. Shut down the standby database:

   SQL> SHUTDOWN IMMEDIATE;
   

3. Start a SQL session on the primary database and create the control file for the standby database:

   SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
   

4. Copy the standby control file and archived log files to the standby site using an operating system utility appropriate for binary files.
5. Connect to the standby database and mount (but do not open) the standby database:

   SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
   

6. Restart the recovery process on the standby database:

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

8.3.9 Clearing Online Redo Logs

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.

8.4 Standby Databases in an Oracle Real Application Clusters Configuration

You can configure 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 8-1 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.

Figure 8-1 Archiving Redo Logs from a Multi-instance Primary Database

Text description of the illustration sbr81088.gif

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 nonrecoverable instances should be shut down, although they can also be mounted.

See Also: Oracle9i Real Application Clusters Setup and Configuration for information about configuring a database for Real Application Clusters

This section contains the following topic:

• Setting Up a Cross-Instance Archival Database Environment

8.4.1 Setting Up a Cross-Instance Archival Database Environment

It is possible to set up a cross-instance archival database environment. Within a Real Application Clusters 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 8-1 shows how to set up the LOG_ARCHIVE_DEST_n initialization parameter for archiving redo logs across instances. Execute this example on all instances except the recovery instance.

Example 8-1 Setting Destinations for Cross-Instance Archiving

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 archiving is available using the ARCn process only. Using the LGWR process for cross-instance archiving results in the RFS process failing and the archive log destination being placed in the Error state.

See Also: Appendix D, "Standby Database Real Application Clusters Support"