Standby Database

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

Monitoring the Primary and Standby Databases

This table indicates whether a command is normally propagated or requires extra administrative efforts to be fully propagated. It also describes how to respond to these events.

Determining Which Archived Logs Have Been Received by the Standby Site

The simplest way to determine the most recent archived log received by the standby site is to query the V$ARCHIVED_LOG view. This view is only useful after the standby site has started receiving logs, because before that time the view is populated by old archived log records generated from the primary control file. For example, you can execute the following script (sample output included):

col name format a20
col thread# format 999
col sequence# format 999
col first_change# format 999999
col next_change# format 999999

SELECT thread#, sequence# AS "SEQ#", name, first_change# AS "FIRSTSCN", 
       next_change# AS "NEXTSCN",archived, deleted,completion_time AS "TIME"
FROM   v$archived_log
/

SQL> @archived_script

THREAD#       SEQ# NAME                   FIRSTSCN    NEXTSCN ARC DEL TIME
------- ---------- -------------------- ---------- ---------- --- --- ---------
      1        947 /arc_dest/arc_1_947       33113      33249 YES NO  23-JUN-99

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

Responding to Events That Affect the Standby Database

Typically, physical changes to the primary database require a manual response on the standby database. This section contains the following topics:

• Adding Tablespaces or Datafiles to the Primary Database
  

• Renaming Datafiles on the Primary Database
  
• Adding or Dropping 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
  
• Performing Direct Path Operations
  
• Refreshing the Standby Database Control File
  
• Clearing Online Redo Logs
  
  

Adding Tablespaces or Datafiles to the Primary Database

Adding a tablespace or datafile to the primary database generates redo that, when applied at the standby database, automatically adds the datafile name to 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.

Perform one of the following procedures to create a new datafile in the primary database and update the standby database. Note that if you do not want the new datafile in the standby database, you can take the datafile offline manually using the following syntax:

To add a tablespace or datafile to the primary database and create the datafile in the standby database:

• Create a tablespace on the primary database as usual.
• If the standby database is shut down, start the standby instance without mounting it. Mount the standby database, then place it in managed recovery mode:
• Switch redo logs on the primary database to initiate redo archival to the standby database. If the recovery process on the standby database tries to apply the redo containing the CREATE TABLESPACE statement, it stops because the new datafile does not exist on the standby site.
  
• Either wait for the standby database to cancel recovery because it cannot find the new datafile, or manually cancel managed recovery. (Note that CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated:)
  
• Create the datafile on the standby database.
  
• Place the standby database in managed recovery mode
  
  

Renaming Datafiles on the Primary Database

Datafile renames on your primary database do 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, perform analogous operations on the standby database.

Adding or Dropping 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 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 old primary database.

To prevent problems after standby activations, Oracle Corporation recommends keeping the online redo log configuration the same at the primary and standby databases. Note that when you enable a log file thread with the ALTER DATABASE ENABLE THREAD statement at the primary database, you must create a new control file for your standby database before activating it. See Refreshing the Standby Database Control File for procedures.

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.

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.

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, you may decide not to recover the primary database's temporary tablespaces on the standby database.

If you execute this statement, then the tablespace containing the offline files must be dropped after opening the standby database.

Performing Direct Path Operations

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

The standby database recovery process continues to sequentially read and apply the redo information generated by the unrecoverable direct load.

Propagating UNRECOVERABLE Processes Manually

Primary database processes using the UNRECOVERABLE option are not propagated to the standby database because these processes 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, Oracle writes error messages in the standby database alert.log file. The following error message is displayed:

26040, 00000, "Data block was loaded using the NOLOGGING option\n" 
//* Cause: Trying to access data in block that was loaded without  
//*        redo generation using the NOLOGGING/UNRECOVERABLE option 
//* Action: Drop the object containing the block.

Although the error message recommends dropping the object that contains the block, do not perform this operation. Instead, perform any one of the following tasks:

• Take the affected datafiles offline in the standby database and drop the tablespace after activation (see Taking Datafiles in the Standby Database Offline).
  

• Re-create the standby database from a new database backup
  
• Back up the affected tablespace and archive the current logs in the primary database, transfer 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: For more details, see Taking Datafiles in the Standby Database Offline

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

Refreshing the Standby Database Control File

To refresh the standby database control file:

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

   SQL> RECOVER CANCEL  # for manual recovery mode
   SQL> RECOVER MANAGED STANDBY DATABASE CANCEL   # for managed recovery mode
   
   Shut down the standby instances:

   SQL> SHUTDOWN IMMEDIATE
   

2. Start a SQL*Plus session on the production instance and create the control file for the standby database:

   SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
   

3. Transfer the standby control file and archived log files to the standby site using an operating system utility appropriate for binary files.
   

4. Connect to the standby instance and mount (but do not open) the standby database:

   SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
   
   Restart the recovery process on the standby database:

   SQL> RECOVER STANDBY DATABASE  # recovers using location for logs 
                                  # specified in initialization parameter file
   SQL> RECOVER FROM 'location' STANDBY DATABASE # recovers from specified
                                                 # location
    
   

Clearing Online Redo Logs

After creating the standby database, you can clear standby database online redo logs to optimize performance by issuing the following statement, where integer refers to the number of the log group:

ALTER DATABASE CLEAR LOGFILE GROUP integer;

This statement optimizes standby activation because it is no longer necessary for Oracle to zero the logs at activation. Zeroing 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. Zeroing occurs during a RESETLOGS operation.

Backing Up the Standby Database

If necessary, you can back up your standby database, but not while the database is in manual or managed recovery mode. You must take the standby database out of managed recovery mode, make the backups, then resume managed recovery. You can make the backups when the database is shut down or in read-only mode.

The following table lists some advantages and disadvantages of these methods:

Method	Advantages	Disadvantages
Shut down the standby database.	Can back up the database after performing other maintenance operations requiring database shutdown.	The primary database may create a gap sequence because the standby database is not receiving archived logs. If you create a gap sequence, you must perform manual recovery before you can place the standby database in managed recovery mode.
Place the standby database in read-only mode.	The standby site continues to receive archived logs from the primary database so no gap sequence is generated.

To back up tablespaces on a standby database when it is in read-only mode:

1. Start a SQL*Plus session on the standby database and take the database out of managed or manual recovery mode:
    
   RECOVER MANAGED STANDBY DATABASE CANCEL    # for managed recovery
   RECOVER CANCEL                             # for manual recovery
    
   

2. Open the database in read-only mode:
    
   ALTER DATABASE OPEN READ ONLY
    
   
3. Take backups of some tablespaces using operating system utilities. You should not back up the standby control file.
    
    
   
4. Minimize the time that the database is down. For example, to back up datafiles tbs11.f, tbs12.f, and tbs13.fin tablespace TBS_1 on UNIX you might enter:

   % cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
   % cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
   % cp /disk1/oracle/dbs/tbs13.f /disk2/backup/tbs13.k
    
   Terminate all active user sessions on the standby database.

5. Place the database in manual or managed recovery mode:

   RECOVER MANAGED STANDBY DATABASE     # for managed recovery             
   RECOVER STANDBY DATABASE             # for manual recovery

6. Back up the control file on the primary database using an operating system utility. You must back up the primary database control file, not the standby database control file.
   
   

7. Repeat the preceding steps until you have backed up each tablespace in the database.
    
    

To back up tablespaces on a standby database when it is shut down:

1. Start a SQL*Plus session on the standby database and take the database out of managed or manual recovery mode:

   RECOVER MANAGED STANDBY DATABASE CANCEL    # for managed recovery
   RECOVER CANCEL                             # for manual recovery

2. Shut down the database:

   SHUTDOWN IMMEDIATE

3. Make cold backups of some tablespaces using operating system utilities. Minimize the time that the database is down. For example, to back up datafiles tbs11.f, tbs12.f, and tbs13.f in tablespace TBS_1 on UNIX you might enter:

   % cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
   % cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
   % cp /disk1/oracle/dbs/tbs13.f /disk2/backup/tbs13.bk

4. Use SQL*Plus to start the Oracle instance at the standby database without mounting it, specifying a parameter file if necessary:

   STARTUP NOMOUNT pfile = initSTANDBY.ora

5. Mount the database:

   ALTER DATABASE MOUNT STANDBY DATABASE

6. Place the database in manual or managed recovery mode:

   RECOVER MANAGED STANDBY DATABASE     # for managed recovery             
   RECOVER STANDBY DATABASE             # for manual recovery

7. Repeat the preceding steps until you have backed up each tablespace in the database.