Skip Headers
Oracle® Data Guard Concepts and Administration
11g Release 1 (11.1)

B28294-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Managing Physical and Snapshot Standby Databases

This chapter describes how to manage physical and snapshot standby databases. The following topics are discussed:

See Oracle Data Guard Broker to learn how the Data Guard broker simplifies the management of physical and snapshot standby databases.

9.1 Starting Up and Shutting Down a Physical Standby Database

This section describes how to start up and shut down a physical standby database.

9.1.1 Starting Up a Physical Standby Database

Use the SQL*Plus STARTUP command to start a physical standby database. The SQL*Plus STARTUP command starts, mounts, and opens a physical standby database in read-only mode when it is invoked without any arguments.

Once mounted or opened, a physical standby database can receive redo data from the primary database.

See Section 7.3 for information about Redo Apply and Section 9.2 for information about opening a physical standby database in read-only mode.

Note:

When Redo Apply is started on a physical standby database that has not yet received redo data from the primary database, an ORA-01112 message may be returned. This indicates that Redo Apply is unable to determine the starting sequence number for media recovery. If this occurs, manually retrieve an archived redo log file from the primary database and register it on the standby database, or wait for redo transport to begin before starting Redo Apply.

9.1.2 Shutting Down a Physical Standby Database

Use the SQL*Plus SHUTDOWN command to stop Redo Apply and shut down a physical standby database. Control is not returned to the session that initiates a database shutdown until shutdown is complete.

If the primary database is up and running, defer the standby destination on the primary database and perform a log switch before shutting down the physical standby database.

9.2 Opening a Physical Standby Database

A physical standby database can be opened for read-only access and used to offload queries from a primary database.

If a license for the Oracle Active Data Guard option has been purchased, a physical standby database can be open while redo apply is active. This capability is known as real-time query. See Section 9.2.1 for more details.

If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while redo apply is active, so the following rules must be observed when opening a physical standby database instance or starting redo apply:

Note:

The SET TRANSACTION READ ONLY SQL statement must be executed before performing a distributed query on a physical standby database.

9.2.1 Real-time query

A physical standby database can be open for read-only access while Redo Apply is active if a license for the Oracle Active Data Guard option has been purchased. This capability is known as real-time query.

A physical standby database instance cannot be opened if Redo Apply is active on that instance or on any other mounted instance. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
  2> DISCONNECT; 

Note:

If Redo Apply is active on an open instance, any other physical standby database instance can be opened without having to stop apply.

Redo Apply cannot be started on a mounted physical standby instance if any other instance is open. The instance where Redo Apply is to be started must be opened before starting Redo Apply.

After a physical standby apply instance is terminated abnormally (for example, by a shutdown abort or a node crash), an attempt to open the physical standby database will result in an ORA-16004: backup database requires recovery error. If this happens, Redo Apply must be started and then stopped before you try again to open the physical standby database.

Example 9-1 illustrates the failure and recovery of a physical standby database instance named Boston, and then after recovery, opening Boston while Redo Apply is active (real-time query).

Example 9-1 Real-time query

At the start of this example, the physical standby instance Boston is mounted with Redo Apply active. Then Boston crashes due to a power outage and is re-started:

SQL> STARTUP
ORACLE instance started.
 
Total System Global Area  234364928 bytes
Fixed Size                  1298908 bytes
Variable Size             209718820 bytes
Database Buffers           16777216 bytes
Redo Buffers                6569984 bytes
Database mounted.
ORA-16004: backup database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/scratch/datafiles/oracle/dbs/system1.f'

The physical standby database is inconsistent because the Boston instance crashed while Redo Apply was active on it. Start Redo Apply so that the database can be recovered to a consistent SCN:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.

Wait for about a minute and then stop Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

Note that the alert log will list the following warning which is normal and expected: ORA-16037: user requested cancel of managed recovery operation.

Boston is now recovered to a consistent SCN.

Open Boston and start Redo Apply:

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
  2> CURRENT LOGFILE DISCONNECT;
Database altered.

Boston is now open and Redo Apply is active meaning that real-time query is in effect.

9.3 Primary Database Changes That Require Manual Intervention at a Physical Standby

Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Table 9-1 lists primary database structural and configuration changes which require manual intervention at a physical standby database.

Table 9-1 Primary Database Changes That Require Manual Intervention at a Physical Standby

Reference Primary Database Change Action Required on Physical Standby Database

Section 9.3.1

Add a datafile or create a tablespace

No action is required if the STANDBY_FILE_MANAGEMENT database initialization parameter is set to AUTO. If this parameter is set to MANUAL, the new datafile must be copied to the physical standby database.

Section 9.3.2

Drop or delete a tablespace or datafile

Delete datafile from primary and physical standby database after the redo data containing the DROP or DELETE command is applied to the physical standby.

Section 9.3.3

Use transportable tablespaces

Move tablespace between the primary and the physical standby database.

Section 9.3.4

Rename a datafile

Rename the datafile on the physical standby database.

Section 9.3.5

Add or drop a redo log file group

Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary.

Section 9.3.6

Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause

Copy the datafile containing the unlogged changes to the physical standby database.

Section 9.3.7

Grant or revoke administrative privileges or change the password of a user who has administrative privileges

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE, replace the password file on the physical standby database with a fresh copy of the password file from the primary database.

Section 9.3.8

Reset the TDE master encryption key

Replace the database encryption wallet on the physical standby database with a fresh copy of the database encryption wallet from the primary database.

Chapter 14

Change initialization parameters

Evaluate whether a corresponding change must be made to the initialization parameters on the physical standby database.


9.3.1 Adding a Datafile or Creating a Tablespace

The STANDBY_FILE_MANAGEMENT database initialization parameter controls whether the addition of a datafile to the primary database is automatically propagated to a physical standby databases.

  • If the STANDBY_FILE_MANAGEMENT parameter on the physical standby database is set to AUTO, any new datafiles created on the primary database are automatically created on the physical standby database.

  • If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to MANUAL, a new datafile must be manually copied from the primary database to the physical standby databases after it is added to the primary database.

Note that if an existing datafile from another database is copied to a primary database, that it must also be copied to the standby database and that the standby control file must be re-created, regardless of the setting of STANDBY_FILE_MANAGEMENT parameter.

9.3.1.1 Using the STANDBY_FILE_MANAGEMENT Parameter with Raw Devices

Note:

Do not use the following procedure with databases that use Oracle Managed Files. Also, if the raw device path names are not the same on the primary and standby servers, use the DB_FILE_NAME_CONVERT database initialization parameter to convert the path names.

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
    2  '/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007'
    3  AS
    4  '/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 Dropping Tablespaces and Deleting Datafiles

When a tablespace is dropped or a datafile is deleted from a primary database, the corresponding datafile(s) must be deleted from the physical standby database. The following example shows how to drop a tablespace:

SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;

To verify that deleted datafiles are no longer part of the database, query the V$DATAFILE view.

Delete the corresponding datafile on the standby system after the redo data that contains the previous changes is applied to the standby database. For example:

% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf

On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:

% rm /disk1/oracle/oradata/payroll/tbs_4.dbf

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.3.3 Using Transportable Tablespaces with a Physical Standby Database

You can use the Oracle transportable tablespaces feature to move a subset of an Oracle database and plug it in to another Oracle database, essentially moving tablespaces between the databases.

To move or copy a set of tablespaces into a primary database when a physical standby is being used, perform the following steps:

  1. Generate a transportable tablespace set that consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.

  2. Transport the tablespace set:

    1. Copy the datafiles and the export file to the primary database.

    2. Copy the datafiles to the standby database.

    The datafiles must be copied in a directory defined by the DB_FILE_NAME_CONVERT initialization parameter. If DB_FILE_NAME_CONVERT is not defined, then issue the ALTER DATABASE RENAME FILE statement to modify the standby control file after the redo data containing the transportable tablespace has been applied and has failed. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO.

  3. Plug in the tablespace.

    Invoke the Data Pump utility to plug the set of tablespaces into the primary database. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database.

For more information about transportable tablespaces, see Oracle Database Administrator's Guide.

9.3.4 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database.

  1. To rename the datafile in the primary database, take the tablespace offline:

    SQL> ALTER TABLESPACE tbs_4 OFFLINE;
    
  2. Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:

    % mv /disk1/oracle/oradata/payroll/tbs_4.dbf 
    /disk1/oracle/oradata/payroll/tbs_x.dbf
    
  3. Rename the datafile in the primary database and bring the tablespace back online:

    SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE      2> '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
      3>  TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
    SQL> ALTER TABLESPACE tbs_4 ONLINE;
    
  4. Connect to the standby database and stop Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    
  5. Shut down the standby database:

    SQL> SHUTDOWN;
    
  6. Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:

    % mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
    
  7. Start and mount the standby database:

    SQL> STARTUP MOUNT;
    
  8. Rename the datafile in the standby control file. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

    SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
      2> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
    
  9. On the standby database, restart Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
      2> DISCONNECT FROM SESSION;
    

If you do not rename the corresponding datafile at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file
ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'

9.3.5 Add or Drop a Redo Log File Group

The configuration of the redo log and standby redo log on a physical standby database should be reevaluated and adjusted as necessary after adding or dropping a redo log file group on the primary database.

Take the following steps to add or drop a redo log file group or standby redo log file group on a physical standby database:

  1. Stop Redo Apply.

  2. If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.

  3. Add or drop a log file group.

  4. Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.

  5. Restart Redo Apply.

9.3.6 NOLOGGING or Unrecoverable Operations

When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and may require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCELOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.

See Section 13.4 for information about recovering after the NOLOGGING clause is used.

9.3.7 Refresh the Password File

If the REMOTE_LOGIN_PASSWORDFILE database initialization parameter is set to SHARED or EXCLUSIVE, the password file on a physical standby database must be replaced with a fresh copy from the primary database after granting or revoking administrative privileges or changing the password of a user with administrative privileges.

Failure to refresh the password file on the physical standby database may cause authentication of redo transport sessions or connections as SYSDBA or SYSOPER to the physical standby database to fail.

9.3.8 Reset the TDE Master Encryption Key

The database encryption wallet on a physical standby database must be replaced with a fresh copy of the database encryption wallet from the primary database whenever the TDE master encryption key is reset on the primary database.

Failure to refresh the database encryption wallet on the physical standby database will prevent access to encrypted columns on the physical standby database that are modified after the master encryption key is reset on the primary database.

9.4 Recovering Through the OPEN RESETLOGS Statement

Data Guard allows recovery on a physical standby database to continue after the primary database has been opened with the RESETLOGS option. When an ALTER DATABASE OPEN RESETLOGS statement is issued on the primary database, the incarnation of the database changes, creating a new branch of redo data.

When a physical standby database receives a new branch of redo data, Redo Apply automatically takes the new branch of redo data. For physical standby databases, no manual intervention is required if the standby database did not apply redo data past the new resetlogs SCN (past the start of the new branch of redo data). The following table describes how to resynchronize the standby database with the primary database branch.

If the standby database. . . Then. . . Perform these steps. . .
Has not applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) Redo Apply automatically takes the new branch of redo. No manual intervention is necessary. The MRP automatically resynchronizes the standby database with the new branch of redo data.
Has applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) and Flashback Database is enabled on the standby database The standby database is recovered in the future of the new branch of redo data.
  1. Follow the procedure in Section 13.3.1 to flash back a physical standby database.
  2. Restart Redo Apply to continue application of redo data onto new reset logs branch.

The MRP automatically resynchronizes the standby database with the new branch.

Has applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) and Flashback Database is not enabled on the standby database The primary database has diverged from the standby on the indicated primary database branch. Re-create the physical standby database following the procedures in Chapter 3.
Is missing intervening archived redo log files from the new branch of redo data The MRP cannot continue until the missing log files are retrieved. Locate and register missing archived redo log files from each branch.
Is missing archived redo log files from the end of the previous branch of redo data. The MRP cannot continue until the missing log files are retrieved. Locate and register missing archived redo log files from the previous branch.

See Oracle Database Backup and Recovery User's Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and Flashback Database.

9.5 Monitoring Primary, Physical Standby, and Snapshot Standby Databases

This section describes where to find useful information for monitoring primary and standby databases.

Table 9-2 summarizes common primary database management actions and where to find information related to these actions.

Table 9-2 Sources of Information About Common Primary Database Management Actions

Primary Database Action Primary Site Information Standby Site Information

Enable or disable a redo thread

  • Alert log

  • V$THREAD

Alert log

Display database role, protection mode, protection level, switchover status, fast-start failover information, and so forth

V$DATABASE

V$DATABASE

Add or drop a redo log file group

  • Alert log

  • V$LOG

  • STATUS column of V$LOGFILE

Alert log

CREATE CONTROLFILE

Alert log

Alert log

Monitor Redo Apply

  • Alert log

  • V$ARCHIVE_DEST_STATUS

  • Alert log

  • V$ARCHIVED_LOG

  • V$LOG_HISTORY

  • V$MANAGED_STANDBY

Change tablespace status

  • V$RECOVER_FILE

  • DBA_TABLESPACES

  • Alert log

  • V$RECOVER_FILE

  • DBA_TABLESPACES

Add or drop a datafile or tablespace

  • DBA_DATA_FILES

  • Alert log

  • V$DATAFILE

  • Alert log

Rename a datafile

  • V$DATAFILE

  • Alert log

  • V$DATAFILE

  • Alert log

Unlogged or unrecoverable operations

  • V$DATAFILE

  • V$DATABASE

Alert log

Monitor redo transport

  • V$ARCHIVE_DEST_STATUS

  • V$ARCHIVED_LOG

  • V$ARCHIVE_DEST

  • Alert log

  • V$ARCHIVED_LOG

  • Alert log

Issue OPEN RESETLOGS or CLEAR UNARCHIVED LOGFILES statements

Alert log

Alert log

Change initialization parameter

Alert log

Alert log


9.5.1 Using Views to Monitor Primary, Physical, and Snapshot Standby Databases

This section shows how to use dynamic performance views to monitor primary, physical standby, and snapshot standby databases.

The following dynamic performance views are discussed:

See Also:

Oracle Database Reference for complete reference information about views

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         7474

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.6 Tuning Redo Apply

The Oracle Data Guard Redo Apply and Media Recovery Best Practices white paper describes how to optimize Redo Apply and media recovery performance. This paper is available on the Oracle Maximum Availability Architecture (MAA) home page at:

http://otn.oracle.com/deploy/availability/htdocs/maa.htm

See Also:

Oracle MetaLink note 454848.1 at

https://metalink.oracle.com for information about the installation and use of the Standby Statspack, which can be used to collect redo apply performance data from a physical standby database

9.7 Managing a Snapshot Standby Database

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.

9.7.1 Converting a Physical Standby Database into a Snapshot Standby Database

Perform the following steps to convert a physical standby database into a snapshot standby database:

  1. Stop Redo Apply, if it is active.

  2. On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

  3. Ensure that the database is mounted, but not open.

  4. Issue the following SQL statement to perform the conversion:

    SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
    

The database is dismounted after conversion and must be restarted.

Note:

A physical standby database that is managed by the Data Guard broker can be converted into a snapshot standby database using either DGMGRL or Oracle Enterprise Manager. See Oracle Data Guard Broker for more details.

9.7.2 Using a Snapshot Standby Database

A snapshot standby database can be opened in read-write mode and is fully updatable.

A snapshot standby database has the following characteristics:

  • A snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

  • A snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Note:

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

9.7.3 Converting a Snapshot Standby Database into a Physical Standby Database

Perform the following steps to convert a snapshot standby database into a physical standby database:

  1. On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

  2. Ensure that the database is mounted, but not open.

  3. Issue the following SQL statement to perform the conversion:

    SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    

The database is dismounted after conversion and must be restarted.

Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.

Note:

A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.