Data Guard FAQ

Oracle Data Guard (standby database) FAQ:

How does one create a standby database?

While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!!

On your standby database, issue the following commands:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'file_name';
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;

On systems prior to Oracle 8i, write a job to copy archived redo log files from the primary database to the standby system, and apply the redo log files to the standby database (pipe it). Remember the database is recovering and will prompt you for the next log file to apply.

Oracle 8i onwards provide an "Automated Standby Database" feature which will send archived log files to the remote site via NET8, and apply then to the standby database.

When one needs to activate the standby database, stop the recovery process and activate it:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

How to check whether standby is doing recovery?

All recovery activity is recorded in the database ALERT.LOG file. You can also run the following query to check if log files are being applied to the standby database:

SQL> SELECT * FROM (
 2    SELECT sequence#, archived, applied,
 3           TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
 4     FROM sys.v$archived_log
 5     ORDER BY sequence# DESC)
 6  WHERE ROWNUM <= 10
 7  /

SEQUENCE# ARCHIVED APPLIED COMPLETED
---------- -------- ------- ----------------
    11211 YES      NO      2004/09/16 09:30
    11210 YES      YES     2004/09/16 09:00
    11209 YES      YES     2004/09/16 08:30
    11208 YES      YES     2004/09/16 08:00
    11207 YES      YES     2004/09/16 07:30
    11206 YES      YES     2004/09/16 07:00
    11205 YES      YES     2004/09/16 06:30
    11204 YES      YES     2004/09/16 06:30
    11203 YES      YES     2004/09/16 06:30
    11202 YES      YES     2004/09/16 06:00

10 rows selected.

How to delay the application of logs to a physical standby?

A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

The following is an example of how to add a 1-hour delay:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby_srvc DELAY=60';

The DELAY attribute is expressed in minutes.

The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.