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.






Primary Database Event

Detection at Primary Site

Detection at Standby Site

Response

Archiving errors 

  • ERROR.V$ARCHIVE _DESTINATION 

  • alert.log 
  • ARCHIVED.V$LOG 
  • Archiving trace files 

remote file server (RFS) process trace file 

Create scripts to push or pull archived redo logs if errors occur or if performance is degraded. See Adding Tablespaces or Datafiles to the Primary Database

Thread events 

  • alert.log 

  • V$THREAD 

alert.log

Thread events are automatically propagated through archived logs, so no extra action is necessary. 

Redo log changes 

  • alert.log 

  • V$LOG and STATUS.V$LOGFILE 

N/A 

Redo log changes do not affect standby database unless a redo log is cleared or lost. In these cases, you must rebuild the standby database. 

Pre-clear the logs on the standby database with the ALTER DATABASE CLEAR LOGFILE statement. See Clearing Online Redo Logs

Issue CREATE CONTROLFILE 

alert.log

Database functions normally until it encounters redo depending on any parameter changes. 

Re-create the standby control file (see Refreshing the Standby Database Control File). Re-create the standby database if the primary database is opened RESETLOGS. 

Media recovery performed 

alert.log

N/A 

Re-create the standby database if the RESETLOGS option is utilized. 

Tablespace status changes (made read/write or read-only, placed online or offline) 

  • DBA_TABLESPACES 

  • alert.log 
  • Verify that all datafiles are online. 

  • V$RECOVER_FILE 

Status changes are automatically propagated, so no response is necessary. Datafiles remain online. 

Add datafile or create tablespace 

  • DBA_DATA_FILES 

  • alert.log 
  • ORA-283, ORA-1670, ORA-1157, ORA-1110 

  • Standby recovery stops. 

Manually create datafile and restart recovery. See Adding Tablespaces or Datafiles to the Primary Database

Drop tablespace 

  • DBA_DATA_FILES 

  • alert.log 

alert.log

Remove datafile from operating system. 

Tablespace or datafile taken offline, or datafile is dropped offline 

  • V$RECOVER_FILE 

  • alert.log 

The tablespace or datafile requires recovery when you attempt to bring it online. 

  • Verify that all datafiles are online. 

  • V$RECOVER_FILE 

Datafiles remain online. The tablespace or datafile is fine after standby database activation. 

Rename datafile 

alert.log

N/A 

N/A 

Unlogged or unrecoverable operations 

  • Direct loader invalidates block range redo entry in online redo log. Check V$DATAFILE. 

  • V$DATABASE 

alert.log. File blocks are invalidated unless they are in the future of redo, in which case they are not touched. 

Unlogged changes are not propagated to the standby database. If you want to apply these changes, see Performing Direct Path Operations

Recovery progress 

alert.log

  • V$RECOVER_LOG 

  • alert.log 

Make sure the standby database is not following behind the primary database. 

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. 

Must rebuild the standby database. 

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. 

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

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:

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:

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 Refreshing the Standby Database Control File.

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

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:

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

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 dropping files.

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.

If you clear the logs manually, Oracle realizes at activation that the logs already have zeros and skips the zeroing 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, Oracle clears the online logs automatically during activation.

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.