9 Managing Physical and Snapshot Standby Databases

9.2.1.1 Monitoring Apply Lag in a Real-time Query Environment

If you are using real-time query to offload queries from a primary database to a physical standby database, you may want to monitor the apply lag to ensure that it is within acceptable limits.

The current apply lag is the difference, in elapsed time, between when the last applied change became visible on the standby and when that same change was first visible on the primary. This metric is computed to the nearest second.

To obtain the apply lag, query the V$DATAGUARD_STATS view. For example:

SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS -
> WHERE name like 'apply lag';
     
    NAME         VALUE            DATUM_TIME              TIME_COMPUTED
    ---------    -------------    -------------------     -------------------
    apply lag    +00 00:00:00     05/27/2009 08:54:16     05/27/2009 08:54:17

The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated. The difference between the values in these columns should be less than 30 seconds. If the difference is larger than this, the apply lag metric may not be accurate.

To obtain a histogram that shows the history of apply lag values since the standby instance was last started, query the V$STANDBY_EVENT_HISTOGRAM view. For example:

SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' - 
> AND COUNT > 0;

NAME             TIME       UNIT         COUNT        LAST_TIME_UPDATED
---------     ---------   --------    -----------    ------------------------
apply lag         0        seconds        79681          06/18/2009 10:05:00
apply lag         1        seconds         1006          06/18/2009 10:03:56
apply lag         2        seconds           96          06/18/2009 09:51:06
apply lag         3        seconds            4          06/18/2009 04:12:32
apply lag         4        seconds            1          06/17/2009 11:43:51
apply lag         5        seconds            1          06/17/2009 11:43:52

6 rows selected

To evaluate the apply lag over a time period, take a snapshot of V$STANDBY_EVENT_HISTOGRAM at the beginning of the time period and compare that snapshot with one taken at the end of the time period.

9.2.1.2 Configuring Apply Lag Tolerance in a Real-time Query Environment

The STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.

If STANDBY_MAX_DATA_DELAY is set to the default value of NONE, queries issued to a physical standby database will be executed regardless of the apply lag on that database.

If STANDBY_MAX_DATA_DELAY is set to a non-zero value, a query issued to a physical standby database will be executed only if the apply lag is less than or equal to STANDBY_MAX_DATA_DELAY. Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.

If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.

Use the ALTER SESSION SQL statement to set STANDBY_MAX_DATA_DELAY. For example:

SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2

9.2.1.3 Forcing Redo Apply Synchronization in a Real-time Query Environment

Issue the following SQL statement on a physical standby database to ensure that all redo data received from the primary database has been applied to a physical standby database:

SQL> ALTER SESSION SYNC WITH PRIMARY;

This statement will block until all redo data received by the standby database at the time that this command is issued has been applied to the physical standby database. An ORA-3173 error is returned immediately, and synchronization will not occur, if the redo transport status at the standby database is not SYNCHRONIZED or if Redo Apply is not active.

You can ensure that Redo Apply synchronization occurs in specific cases by using the SYS_CONTEXT('USERENV','DATABASE_ROLE') function to create a standby-only trigger (that is, a trigger that is enabled on the primary but that only takes certain actions if it is running on a standby). For example, you could create the following trigger that would execute the ALTER SESSION SYNC WITH PRIMARY statement for a specific user connection at logon:

CREATE TRIGGER adg_logon_sync_trigger
 AFTER LOGON ON user.schema
  begin
    if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE')  IN ('PHYSICAL STANDBY')) then
      execute immediate 'alter session sync with primary';
    end if;
  end;

9.2.1.4 Real-time Query Restrictions

The apply lag control and Redo Apply synchronization mechanisms described above require that the client be connected and issuing queries to a physical standby database that is in real-time query mode.

The following additional restrictions apply if STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:

• The standby database must receive redo data via the SYNC transport.

• The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.

• Real-time apply must be enabled.

• Active Data Guard achieves high performance of real-time queries in an Oracle RAC environment through the use of cache fusion. This allows the Data Guard apply instance and queries to work out of cache and not be slowed down by disk I/O limitations. A consequence of this is that an unexpected failure of the apply instance leaves buffers in inconsistent states across all the open Oracle RAC instances. To ensure data consistency and integrity, Data Guard closes all the other open instances in the Oracle RAC configuration, and brings them to a mounted state. You must manually reopen the instances - at which time the data is automatically made consistent, followed by restarting redo apply on one of the instances. Note that in a Data Guard broker configuration, the instances are automatically reopened and redo apply is automatically restarted on one of the instances.

  See Also:

  • Oracle Data Guard Broker for more information about how the broker handles apply instance failures

  • The My Oracle Support note 1357597.1 at http://support.oracle.com for additional information about apply instance failures in an Active Data Guard Oracle RAC standby

9.2.1.5 Automatic Repair of Corrupt Data Blocks

If a corrupt data block is encountered when a primary database is accessed, it is automatically replaced with an uncorrupted copy of that block from a physical standby database. This requires the following conditions:

• The physical standby database must be operating in real-time query mode, which requires an Active Data Guard license.

• The physical standby database must be running real-time apply.

Also keep the following in mind:

• Automatic repair is supported with any Data Guard protection mode. However, the effectiveness of repairing a corrupt block at the primary using the noncorrupt version of the block from the standby depends on how closely the standby apply is synchronized with the redo generated by the primary.

• When an automatic block repair has been performed, a message is written to the database alert log.

• If automatic block repair is not possible, an ORA-1578 error is returned.

If a corrupt data block is discovered on a physical standby database, the server attempts to automatically repair the corruption by obtaining a copy of the block from the primary database if the following database initialization parameters are configured on the standby database:

• The LOG_ARCHIVE_CONFIG parameter is configured with a DG_CONFIG list and a LOG_ARCHIVE_DEST_n parameter is configured for the primary database.

  or

• The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database.

9.2.1.6 Manual Repair of Corrupt Data Blocks

The RMAN RECOVER BLOCK command is used to manually repair a corrupted data block. This command searches several locations for an uncorrupted copy of the data block. By default, one of the locations is any available physical standby database operating in real-time query mode. The EXCLUDE STANDBY option of the RMAN RECOVER BLOCK command can be used to exclude physical standby databases as a source for replacement blocks.

9.2.1.7 Tuning Queries on a Physical Standby Database

Appendix D of the Active Data Guard 11g Best Practices white paper describes how to tune queries for optimal performance on a physical standby database. This paper is available on the Oracle Maximum Availability Architecture (MAA) home page at:

http://www.oracle.com/goto/maa

9.2.1.8 Adding Temp Files to a Physical Standby Database

If you are using a standby to offload queries from the primary database, and the nature of the workload requires more temp table space than is automatically created when the standby is first created, then you may need to manually add additional space.

To add temporary files to the physical standby database, perform the following tasks:

1. Identify the tablespaces that should contain temporary files. Do this by entering the following command on the standby database:

   SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES
   2> WHERE CONTENTS = 'TEMPORARY';
    
   TABLESPACE_NAME
   --------------------------------
   TEMP1
   TEMP2
   

2. For each tablespace identified in the previous query, add a new temporary file to the standby database. The following example adds a new temporary file called TEMP1 with size and reuse characteristics that match the primary database temporary files:

   SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE
   2> '/arch1/boston/temp01.dbf'
   3> SIZE 40M REUSE;
   

9.3.1.1 Using the STANDBY_FILE_MANAGEMENT Parameter with Raw Devices

By setting the STANDBY_FILE_MANAGEMENT parameter to AUTO whenever new datafiles are added or dropped on the primary database, corresponding changes are made in the standby database without manual intervention. This is true as long as the standby database is using a file system. If the standby database is using raw devices for datafiles, then the STANDBY_FILE_MANAGEMENT parameter will continue to work, but manual intervention is needed. This manual intervention involves ensuring the raw devices exist before Redo Apply applies the redo data that will create the new datafile.On the primary database, create a new tablespace where the datafiles reside in a raw device. At the same time, create the same raw device on the standby database. For example:

SQL> CREATE TABLESPACE MTS2 DATAFILE '/dev/raw/raw100' size 1m;
Tablespace created.
 
SQL> ALTER SYSTEM SWITCH LOGFILE; 
System altered.

The standby database automatically adds the datafile because the raw devices exist. The standby alert log shows the following:

Fri Apr  8 09:49:31 2005
Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_7_15ffgt0z_.arc
Recovery created file /dev/raw/raw100
Successfully added datafile 6 to media recovery
Datafile #6: '/dev/raw/raw100'
Media Recovery Waiting for thread 1 sequence 8 (in transit)

However, if the raw device was created on the primary system but not on the standby, then Redo Apply will stop due to file-creation errors. For example, issue the following statements on the primary database:

SQL> CREATE TABLESPACE MTS3 DATAFILE '/dev/raw/raw101' size 1m;
Tablespace created.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

The standby system does not have the /dev/raw/raw101 raw device created. The standby alert log shows the following messages when recovering the archive:

Fri Apr  8 10:00:22 2005
Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_8_15ffjrov_.arc
File #7 added to control file as 'UNNAMED00007'.
Originally created as:
'/dev/raw/raw101'
Recovery was unable to create the file as:
'/dev/raw/raw101'
MRP0: Background Media Recovery terminated with error 1274
Fri Apr  8 10:00:22 2005
Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:
ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created
ORA-01119: error in creating database file '/dev/raw/raw101'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 1
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Fri Apr  8 10:00:22 2005
Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:
ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created
ORA-01119: error in creating database file '/dev/raw/raw101'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 1
Fri Apr  8 10:00:22 2005
MTS; MRP0: Background Media Recovery process shutdown
ARCH: Connecting to console port...

9.3.1.2 Recovering from Errors

To correct the problems described in Section 9.3.1.1, perform the following steps:

1. Create the raw device on the standby database and assign permissions to the Oracle user.

2. Query the V$DATAFILE view. For example:

   SQL> SELECT NAME FROM V$DATAFILE;
   
   NAME -------------------------------------------------------------------------------
   /u01/MILLER/MTS/system01.dbf
   /u01/MILLER/MTS/undotbs01.dbf
   /u01/MILLER/MTS/sysaux01.dbf
   /u01/MILLER/MTS/users01.dbf
   /u01/MILLER/MTS/mts.dbf
   /dev/raw/raw100
   /u01/app/oracle/product/10.1.0/dbs/UNNAMED00007
   
   SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
   
   SQL> ALTER DATABASE CREATE DATAFILE -
   > '/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007' -
   >  AS -
   > '/dev/raw/raw101';
   

3. In the standby alert log you should see information similar to the following:

   Fri Apr  8 10:09:30 2005
   alter database create datafile
   '/dev/raw/raw101' as '/dev/raw/raw101'
   
   Fri Apr  8 10:09:30 2005
   Completed: alter database create datafile
   '/dev/raw/raw101' a
   

4. On the standby database, set STANDBY_FILE_MANAGEMENT to AUTO and restart Redo Apply:

   SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
   SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
   

At this point Redo Apply uses the new raw device datafile and recovery continues.

9.3.2.1 Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES

You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases. To use this statement, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. For example, to drop the tablespace at the primary site:

SQL> DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;

9.5.1.1 V$DATABASE

The following query displays the data protection mode, data protection level, database role, and switchover status for a primary, physical standby or snapshot standby database:

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, –
> DATABASE_ROLE ROLE, SWITCHOVER_STATUS –
> FROM V$DATABASE;

The following query displays fast-start failover status:

SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS", -
> FS_FAILOVER_CURRENT_TARGET TARGET, -
> FS_FAILOVER_THRESHOLD THRESHOLD, -
> FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" –
> FROM V$DATABASE;

9.5.1.2 V$MANAGED_STANDBY

The following query displays Redo Apply and redo transport status on a physical standby database:

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,-
> BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
 
PROCESS STATUS       THREAD#    SEQUENCE#  BLOCK#     BLOCKS
------- ------------ ---------- ---------- ---------- ----------
RFS     ATTACHED     1          947        72         72
MRP0    APPLYING_LOG 1          946        10         72

The sample output shows that a RFS process completed archiving a redo log file with a sequence number of 947 and that Redo Apply is actively applying an archived redo log file with a sequence number of 946. Redo Apply is currently recovering block number 10 of the 72-block archived redo log file.

9.5.1.3 V$ARCHIVED_LOG

The following query displays information about archived redo log files that have been received by a physical or snapshot standby database from a primary database:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
> NEXT_CHANGE# FROM V$ARCHIVED_LOG;
 
THREAD#    SEQUENCE#  FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1          945        74651         74739
1          946        74739         74772
1          947        74772         74795

The sample output shows that three archived redo log files have been received from the primary database.

9.5.1.4 V$LOG_HISTORY

The following query displays archived log history information:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
> NEXT_CHANGE# FROM V$LOG_HISTORY;

9.5.1.5 V$DATAGUARD_STATUS

The following query displays messages generated by Data Guard events that caused a message to be written to the alert log or to a server process trace file:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

9.5.1.6 V$ARCHIVE_DEST

The following query shows the status of each redo transport destination, and for redo transport destinations that are standby databases, the SCN of the last primary database redo applied at that standby database:

SQL> SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';
   
DEST_ID    STATUS    APPLIED_SCN
---------- --------- -----------
2          VALID     439054
3          VALID     439054