Skip Headers
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)

Part Number B14239-04
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
View PDF

8 Managing a Physical Standby Database

This chapter describes how to manage physical standby databases. This chapter contains the following topics:

The topics in this chapter describe how to use SQL statements, initialization parameters, and views to manage physical standby databases.

See Oracle Data Guard Broker to use the Data Guard broker to automate the management tasks described in this chapter.

8.1 Starting Up and Shutting Down a Physical Standby Database

This section describes the SQL*Plus statements used to start up and shut down a physical standby database.

8.1.1 Starting Up a Physical Standby Database

To start a physical standby database, use SQL*Plus to connect to the database with administrator privileges, and then use either the SQL*Plus STARTUP or STARTUP MOUNT statement. When used on a physical standby database:

  • The STARTUP statement starts the database, mounts the database as a physical standby database, and opens the database for read-only access.

  • The STARTUP MOUNT statement starts and mounts the database as a physical standby database, but does not open the database.

Once mounted, the database can receive archived redo data from the primary database. You then have the option of either starting Redo Apply or real-time apply, or opening the database for read-only access.

For example:

  1. Start and mount the physical standby database:

    SQL> STARTUP MOUNT;
    
    
  2. Start Redo Apply or real-time apply:

    To start Redo Apply, issue the following statement:

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

    To start real-time apply, issue the following statement:

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

On the primary database, query the RECOVERY_MODE column in the V$ARCHIVED_DEST_STATUS view, which displays the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.

See Section 6.3 for information about Redo Apply, Section 6.2.1 for information about real-time apply, and Section 8.2 for information about opening a physical standby database for read-only or read/write access.

Note:

When you first start Redo Apply on a newly created physical standby database that has not yet received any 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, you must either manually retrieve and register an archived redo log file on the standby database, or wait for the automatic archiving to occur before restarting Redo Apply.

8.1.2 Shutting Down a Physical Standby Database

To shut down a physical standby database and stop Redo Apply, use the SQL*Plus SHUTDOWN statement. 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 destination on the primary database and perform a log switch before shutting down the standby database.

To stop Redo Apply before shutting down the database, use the following steps:

  1. Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. If the MRP0 or MRP process exists, then the standby database is applying redo.

    SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
    
    
  2. If Redo Apply is running, cancel it as shown in the following example:

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

    SQL> SHUTDOWN IMMEDIATE;
    

8.2 Opening a Standby Database for Read-Only or Read/Write Access

When a standby database is open for read-only access, users can query the standby database but cannot update it. Thus, you can reduce the load on the primary database by using the standby database for reporting purposes. You can periodically open the standby database for read-only access and perform ad hoc queries to verify Redo Apply is updating the standby database correctly. (Note that for distributed queries, you must first issue the ALTER DATABASE SET TRANSACTION READ ONLY statement before you can issue a query on the read-only database.)

Figure 8-1 shows a standby database open for read-only access.

Figure 8-1 Standby Database Open for Read-Only Access

Description of Figure 8-1 follows
Description of "Figure 8-1 Standby Database Open for Read-Only Access"

A physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

See Also:

Section 12.6 for a scenario that describes activating a physical standby database as a read/write reporting database, and then resynchronizing the database with the primary database

8.2.1 Assessing Whether or Not to Open a Standby Database

As you decide whether or not to open a physical standby database for read-only or read/write access, consider the following:

  • Opening the physical standby database read-only may lengthen the time it takes to recover from a failure or outage, because the database must be restarted after a failover.

    As long as the physical standby database has not been opened read-only since the last time it was started, a restart is unnecessary after failover, thus increasing system availability.

  • While a standby database is open for read-only or read/write access, it does not apply redo data received from the primary database, thus it is not kept transactionally consistent with the primary database.

    When a physical standby database is open, redo data from the primary database is received by the standby database, but the log files are not applied. At some point, you need to resume Redo Apply on the standby database, and apply the archived redo log files to resynchronize the standby database with the primary database. Because of the additional time required to apply any accumulated archived redo log files, having a standby database open for read-only access can increase the time required to complete failovers or switchovers.

You can use a physical standby database for reporting purposes or as a clone database while also maintaining the ability to complete a failover or switchover quickly if you configure more than one standby database on the standby system.

For example, based on your business requirements, you might:

  • Configure two physical standby databases with one standby database always performing Redo Apply to be as current as possible with the primary database and the other standby database open in read-only mode during business hours for reporting purposes.

  • Configure a physical standby database to maintain a copy of the primary database for disaster recovery purposes and also configure a logical standby database to off-load reporting tasks that require access to the latest data from the primary database.

When configuring more than one standby database on the same system, consider using the DEPENDENCY attribute of the LOG_ARCHIVE_DEST_n initialization parameter to define one archival destination to receive redo data on behalf of all of the destinations, rather than transmitting redo data to each individual destination. See Section 5.7.5 for more information.

8.2.2 Opening a Physical Standby Database for Read-Only Access

You can alternate between having a physical standby database open for read-only access and performing Redo Apply using the following procedures.

To open a standby database for read-only access when it is currently shut down:

Start, mount, and open the database for read-only access using the following statement:

SQL> STARTUP;

To open a standby database for read-only access when it is currently performing Redo Apply:

  1. Cancel Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    
    
  2. Open the database for read-only access:

    SQL> ALTER DATABASE OPEN;
    
    

You do not need to shut down the instance to open it for read-only access.

Note:

By default, the ALTER DATABASE OPEN statement opens physical standby databases in read-only mode. The Oracle database determines if this is a physical standby database based on information in the control file.

To change the standby database from being open for read-only access to performing Redo Apply:

  1. Terminate all active user sessions on the standby database.

  2. Restart Redo Apply. To start Redo Apply, issue the following statement:

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

    To enable real-time apply, include the USING CURRENT LOGFILE clause:

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

You do not need to shut down the instance to start either of these apply modes.

8.3 Managing Primary Database Events That Affect the Standby Database

To prevent possible problems, you must be aware of events on the primary database that affect a standby database and learn how to respond to them. This section describes these events and the recommended responses to these events.

In some cases, the events or changes that occur on a primary database are automatically propagated through redo data to the standby database and thus require no extra action on the standby database. In other cases, you might need to perform maintenance tasks on the standby database.

Table 8-1 indicates whether or not a change made on the primary database requires additional intervention by the database administrator (DBA) to be propagated to the standby database. It also briefly describes how to respond to these events. Detailed descriptions of the responses are described in the section references provided.

The following events are automatically administered by redo transport services and Redo Apply, and therefore require no intervention by the database administrator:

Table 8-1 Actions Required on a Standby Database After Changes to a Primary Database

Reference Change Made on Primary Database Action Required on Standby Database

Section 8.3.1


Add a datafile or create a tablespace

If you did not set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, you must copy the new datafile to the standby database.

Section 8.3.2


Drop or delete a tablespace or datafile

Delete datafiles from primary and standby databases after the archived redo log file containing the DROP or DELETE command was applied.

Section 8.3.3


Use transportable tablespaces

Move tablespaces between the primary and standby databases.

Section 8.3.4


Rename a datafile

Rename the datafile on the standby database.

Section 8.3.5


Add or drop redo log files

Synchronize changes on the standby database.

Section 8.3.6


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

Send the datafile containing the unlogged changes to the standby database.

Chapter 13


Change initialization parameters

Dynamically change the standby parameters or shut down the standby database and update the initialization parameter file.


8.3.1 Adding a Datafile or Creating a Tablespace

The initialization parameter, STANDBY_FILE_MANAGEMENT, enables you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:

  • If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file (SPFILE) to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.

  • If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.

Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.

The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.

8.3.1.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO

The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

  1. Add a new tablespace to the primary database:

    SQL> CREATE TABLESPACE new_ts DATAFILE '/disk1/oracle/oradata/payroll/t_db2.dbf'
      2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
    
    
  2. Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    
    
  3. Verify the new datafile was added to the primary database:

    SQL> SELECT NAME FROM V$DATAFILE;
    NAME
    ----------------------------------------------------------------------
    /disk1/oracle/oradata/payroll/t_db1.dbf
    /disk1/oracle/oradata/payroll/t_db2.dbf
    
    
  4. Verify the new datafile was added to the standby database:

    SQL> SELECT NAME FROM V$DATAFILE;
    NAME
    ----------------------------------------------------------------------
    /disk1/oracle/oradata/payroll/s2t_db1.dbf
    /disk1/oracle/oradata/payroll/s2t_db2.dbf
    

8.3.1.2 When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

This section shows how to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices. This section also describes how to recover from errors after they have occurred.

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 initialization parameter to convert the path names.
8.3.1.2.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 initialization parameter will continue to work, but manual intervention is needed. This manual intervention involves ensuring the raw devices exist before log apply services on the standby database recover 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 as 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 the MRP process will shut down 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 /Dave/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...
8.3.1.2.2 Recovering From Errors

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

  1. Create the raw slice 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.

8.3.2 Dropping Tablespaces and Deleting Datafiles

When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles to the standby database. The following sections provide examples of dropping tablespaces and deleting datafiles when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO or MANUAL.

8.3.2.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL

The following procedure works whether the STANDBY_FILE_MANAGEMENT initialization parameter is set to either MANUAL or AUTO, as follows:

  1. Drop the tablespace from the primary database:

    SQL> DROP TABLESPACE tbs_4;
    SQL> ALTER SYSTEM SWITCH LOGFILE;
    
    
  2. Make sure that Redo Apply is running (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, Redo Apply is running.

    SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
    
    

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

  3. Delete the corresponding datafile on the standby system after the archived redo log file was applied to the standby database. For example:

    % rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf
    
    
  4. 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
    

8.3.2.2 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;

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

8.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, query the V$ARCHIVED_LOG view to verify all of the archived redo log files are applied, and then stop Redo Apply:

    SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    SEQUENCE# APP
    --------- ---
    8 YES
    9 YES
    10 YES
    11 YES
    4 rows selected.
    
    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
      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'

8.3.5 Adding or Dropping Online Redo Log Files

Changing the size and number of the online redo log files is sometimes done to tune the database. You can add or drop online 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. However, these changes do affect the performance of the standby database after switchover.

Caution:

Whenever you add an online redo log file to the primary database, you should add corresponding online and standby redo log files to the standby database.

For example, if the primary database has 10 online redo log files and the standby database has 2, and then you switch over to the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database.

Consequently, when you add or drop an online redo log file at the primary site, it is important that you synchronize the changes in the standby database by following these steps:

  1. If Redo Apply is running, you must cancel Redo Apply before you can change the log files.

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

  3. Add or drop an online redo log file:

    • To add an online redo log file, use a SQL statement such as this:

      SQL> ALTER DATABASE ADD LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log' SIZE 100M;
      
      
    • To drop an online redo log file, use a SQL statement such as this:

      SQL> ALTER DATABASE DROP LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log';
      
      
  4. Repeat the statement you used in Step 3 on each standby database.

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

8.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 might 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 12.10 for information about recovering after the NOLOGGING clause is used.

8.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 12.5.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 Advanced User's Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and Flashback Database.

8.5 Monitoring the Primary and Standby Databases

This section gives you a general overview on where to find information for monitoring the primary and standby databases in a Data Guard environment.

This section contains the following topics:

Table 8-2 summarizes common events that occur on the primary database and pointers to the files and views where you can monitor these events on the primary and standby sites.

Table 8-2 Location Where Common Actions on the Primary Database Can Be Monitored

Primary Database Event Primary Site Information Standby Site Information

A SQL ALTER DATABASE statement is issued with the ENABLE THREAD or DISABLE THREAD clause specified

  • Alert log

  • V$THREAD view

Alert log

Current database role, protection mode and level, switchover status, and fast-start failover information

V$DATABASE

V$DATABASE

Redo log changed

  • Alert log

  • V$LOG view

  • STATUS column of V$LOGFILE view

Alert log

CREATE CONTROLFILE statement issued

Alert log

Alert log

Managed recovery performed

Alert log

Alert log

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

  • DBA_TABLESPACES view

  • Alert log

V$RECOVER_FILE view

Datafile added or tablespace created

  • DBA_DATA_FILES view

  • Alert log

V$DATAFILE view

Alert log

Tablespace dropped

  • DBA_DATA_FILES view

  • Alert log

V$DATAFILE view

Alert log

Tablespace or datafile taken offline, or datafile is deleted offline

  • V$RECOVER_FILE view

  • Alert log

  • DBA_TABLESPACES

V$RECOVER_FILE view

DBA_TABLESPACES

Rename datafile

  • V$DATAFILE

  • Alert log

V$DATAFILE view

Alert log

Unlogged or unrecoverable operations

  • V$DATAFILE view

  • V$DATABASE view

Alert log

Recovery progress

  • V$ARCHIVE_DEST_STATUS view

  • Alert log

V$ARCHIVED_LOG view

V$LOG_HISTORY view

V$MANAGED_STANDBY view

Alert log

Redo transport status and progress

  • V$ARCHIVE_DEST_STATUS view

  • V$ARCHIVED_LOG view

  • V$ARCHIVE_DEST view

  • Alert log

V$ARCHIVED_LOG view

Alert log

Auto extend a datafile

Alert log

Alert log

Issue OPEN RESETLOGS or CLEAR UNARCHIVED LOGFILES statements

Alert log

Alert log

Change initialization parameter

Alert log

Alert log


8.5.1 Alert Log

The database alert log is a chronological record of messages and errors. In addition to providing information about the Oracle database, it also includes information about operations specific to Data Guard, including the following:

  • Messages related to administrative operations such as the following SQL statements: ALTER DATABASE RECOVER MANAGED STANDBY, STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER

  • Errors related to administrative operations that are reported by background processes, such as ARC0, MRP0, RFS, LGWR

  • The completion timestamp for administrative operations

The alert log also provides pointers to the trace or dump files generated by a specific process.

8.5.2 Dynamic Performance Views (Fixed Views)

The Oracle database contains a set of underlying views. These views are often called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. These views are also called fixed views because they cannot be altered or removed by the database administrator.

These view names are prefixed with either V$ or GV$, for example, V$ARCHIVE_DEST or GV$ARCHIVE_DEST.

Standard dynamic performance views (V$ fixed views) store information about the local instance. In contrast, global dynamic performance views (GV$ fixed views), store information about all open instances in a Real Applications Cluster (RAC). Each V$ fixed view has a corresponding GV$ fixed view. Selects on GV$ fixed views use parallel query slaves to obtain information on all instances. See Chapter 16, "Views Relevant to Oracle Data Guard" and Oracle Database Reference for additional information.

8.5.3 Monitoring Recovery Progress

This section shows some samples of the types of views discussed in Section 8.5.2 for monitoring recovery progress in a Data Guard environment. It contains the following examples:

8.5.3.1 Monitoring the Process Activities

You can obtain information about Redo Apply on a standby database by monitoring the activities performed by the following processes:

Reference Name System Process Names
ARCH ARC0,ARC1,ARC2,…
MRP MRP, MRP0
RFS ORACLE{SID}

The V$MANAGED_STANDBY view on the standby database site shows you the activities performed by both redo transport and Redo Apply processes in a Data Guard environment. The CLIENT_P column in the output of the following query identifies the corresponding primary database process.

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P  SEQUENCE# STATUS
------- -------- ---------- ------------
ARCH    ARCH              0 CONNECTED
ARCH    ARCH              0 CONNECTED
MRP0    N/A             204 WAIT_FOR_LOG
RFS     LGWR            204 WRITING
RFS     N/A               0 RECEIVING

8.5.3.2 Determining the Progress of Redo Apply

The V$ARCHIVE_DEST_STATUS view on either a primary or standby database site provides you information such as the online redo log files that were archived, the archived redo log files that are applied, and the log sequence numbers of each. The following query output shows the standby database is two archived redo log files behind in applying the redo data received from the primary database.

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
  2> FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1                947           1               945

8.5.3.3 Determining the Location and Creator of the Archived Redo Log Files

Query the V$ARCHIVED_LOG view on the standby database to find additional information about the archived redo log. Some information you can get includes the location of the archived redo log, which process created the archived redo log, redo log sequence number of each archived redo log file, when each log file was archived, and whether or not the archived redo log file was applied. For example:

SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME 
  2> FROM V$ARCHIVED_LOG;

NAME                                            CREATOR SEQUENCE# APP COMPLETIO
----------------------------------------------  ------- --------- --- ---------
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00198.001  ARCH          198 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00199.001  ARCH          199 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00200.001  ARCH          200 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00201.001  LGWR          201 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00202.001  ARCH          202 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00203.001  LGWR          203 YES 30-MAY-02

6 rows selected.

8.5.3.4 Viewing Database Incarnations Before and After OPEN RESETLOGS

Query the V$DATABASE_INCARNATION view on the standby database to monitor database incarnations and the RESETLOGS_ID column.

The following queries were issued on the standby database before an OPEN RESETLOGS statement was issued on the primary database:

SQL> SELECT INCARNATION#, RESETLOGS_ID, STATUS FROM V$DATABASE_INCARNATION ;

INCARNATION# RESETLOGS_ID STATUS
------------ ------------ ------- 
           1    509191005 PARENT 
           2    509275501 CURRENT

SQL> SELECT RESETLOGS_ID,THREAD#,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
  2  ORDER BY RESETLOGS_ID,SEQUENCE# ;

RESETLOGS_ID  THREAD#  SEQUENCE# S ARC
------------  -------  --------- - ---- 
   509275501        1          1 A YES
   509275501        1          2 A YES
   509275501        1          3 A YES
   509275501        1          4 A YES
   509275501        1          5 A YES 

5 rows selected.

The following queries were issued on the standby database after an OPEN RESETLOGS statement was issued on the primary database and the standby database started to receive redo data on the new branch of redo:

SQL> SELECT INCARNATION#, RESETLOGS_ID, STATUS FROM V$DATABASE_INCARNATION ;

INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
           1    509191005 PARENT
           2    509275501 PARENT
           3    509278970 CURRENT   

SQL> SELECT RESETLOGS_ID,THREAD#,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
  2  ORDER BY RESETLOGS_ID,SEQUENCE# ;

RESETLOGS_ID  THREAD#  SEQUENCE# S ARC
------------  -------  --------- - ---
   509275501        1          1 A YES
   509275501        1          2 A YES
   509275501        1          3 A YES
   509275501        1          4 A YES
   509275501        1          5 A YES
   509278970        1          1 A YES
   509278970        1          2 A YES
   509278970        1          3 A YES
8 rows selected.

8.5.3.5 Viewing the Archived Redo Log History

The V$LOG_HISTORY on the standby site shows you a complete history of the archived redo log, including information such as the time of the first entry, the lowest SCN in the log, the highest SCN in the log, and the sequence numbers for the archived redo log files.

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

FIRST_TIM FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--------- ------------- ------------ ----------
13-MAY-02        190578       214480          1
13-MAY-02        214480       234595          2
13-MAY-02        234595       254713          3
.
.
.
30-MAY-02       3418615      3418874        201
30-MAY-02       3418874      3419280        202
30-MAY-02       3419280      3421165        203
203 rows selected.

8.5.3.6 Determining Which Log Files Were Applied to the Standby Database

Query the V$LOG_HISTORY view on the standby database, which records the latest log sequence number that was 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

In this example, the archived redo log file with log sequence number 967 is the most recently applied log file.

You can also use the APPLIED column in the V$ARCHIVED_LOG fixed view on the standby database to find out which log files were applied on the standby database. For example:

SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG; 
   THREAD#  SEQUENCE# APP 
---------- ---------- --- 
         1          2 YES 
         1          3 YES 
         1          4 YES 
         1          5 YES 
         1          6 YES 
         1          7 YES 
         1          8 YES 
         1          9 YES 
         1         10 YES 
         1         11 NO 
10 rows selected.

8.5.3.7 Determining Which Log Files Were Not Received by the Standby Site

Each archive destination has a destination ID assigned to it. You can query the DEST_ID column in the V$ARCHIVE_DEST fixed view to find out your destination ID. You can then use this destination ID in a query on the primary database to discover log files that were not sent to a particular standby site.

For example, assume the current local archive destination ID on your primary database is 1, and the destination ID of one of your remote standby databases is 2. To find out which log files were not received by this standby destination, issue the following query on the primary database:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM 
  2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
  3>  WHERE LOCAL.SEQUENCE# NOT IN 
  5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND 
  6> THREAD# = LOCAL.THREAD#); 
 THREAD#  SEQUENCE# 
---------- ---------- 
  1        12 
  1        13 
  1        14 

The preceding example shows the log files that were not received by standby destination 2.

8.5.4 Monitoring Log Apply Services on Physical Standby Databases

To monitor the status of log apply services on a physical standby database, query the fixed views described in this section. You can also monitor the standby database using the Oracle Enterprise Manager GUI.

This section contains the following topics:

Also, see Oracle Database Reference for complete reference information about views.

8.5.4.1 Accessing the V$DATABASE View

Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, -
       PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS -
       FROM V$DATABASE;

Issue the following query to show information about fast-start failover:

SQL> SELECT FS_FAILOVER_STATUS FSFO_STATUS, FS_FAILOVER_CURRENT_TARGET -
       TARGET_STANDBY, FS_FAILOVER_THRESHOLD THRESHOLD, -
       FS_FAILOVER_OBSERVER_PRESENT OBS_PRES - 
       FROM V$DATABASE;

8.5.4.2 Accessing the V$MANAGED_STANDBY Fixed View

Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
  2> FROM V$MANAGED_STANDBY;

PROCESS STATUS       THREAD#    SEQUENCE#  BLOCK#     BLOCKS
------- ------------ ---------- ---------- ---------- ----------
RFS     ATTACHED     1          947        72         72
MRP0    APPLYING_LOG 1          946        10         72

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

8.5.4.3 Accessing the V$ARCHIVE_DEST_STATUS Fixed View

To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database:

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
  2> FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1                947           1               945

The previous query output shows that the standby database is two archived redo log files behind the primary database.

To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view. It will contain the value MANAGED REAL TIME APPLY when real-time apply is enabled, as shown in the following example:

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

8.5.4.4 Accessing the V$ARCHIVED_LOG Fixed View

The V$ARCHIVED_LOG fixed view on the physical standby database shows all the archived redo log files received from the primary database. This view is only useful after the standby site starts receiving redo data; before that time, the view is populated by old archived redo log records generated from the primary control file.

For example, you can execute the following SQL*Plus statement:

SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,
  2> NEXT_CHANGE# FROM V$ARCHIVED_LOG;

REGISTRAR CREATOR THREAD#    SEQUENCE#  FIRST_CHANGE# NEXT_CHANGE#
--------- ------- ---------- ---------- ------------- ------------
RFS       ARCH    1          945        74651         74739
RFS       ARCH    1          946        74739         74772
RFS       ARCH    1          947        74772         74774

The previous query output shows three archived redo log files received from the primary database.

8.5.4.5 Accessing the V$LOG_HISTORY Fixed View

Query the V$LOG_HISTORY fixed view on the physical standby database to show all the archived redo log files that were applied:

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

THREAD#    SEQUENCE#  FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1          945        74651         74739

The previous query output shows that the most recently applied archived redo log file was sequence number 945.

8.5.4.6 Accessing the V$DATAGUARD_STATUS Fixed View

The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.

The following example shows output from the V$DATAGUARD_STATUS view on a primary database:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
Archivelog destination LOG_ARCHIVE_DEST_2 validated for no-data-loss
recovery
Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2'
ARCH: Transmitting activation ID 0
LGWR: Completed archiving log 3 thread 1 sequence 11
Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2'
LGWR: Transmitting activation ID 6877c1fe
LGWR: Beginning to archive log 4 thread 1 sequence 12
ARC0: Evaluating archive   log 3 thread 1 sequence 11
ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed
ARC0: Beginning to archive log 3 thread 1 sequence 11
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/arch/arch_1_11.arc'
ARC0: Completed archiving  log 3 thread 1 sequence 11
ARC1: Transmitting activation ID 6877c1fe
15 rows selected.

The following example shows the contents of the V$DATAGUARD_STATUS view on a physical standby database:

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
RFS: Successfully opened standby logfile 6: '/oracle/dbs/sorl2.log'
ARC1: Evaluating archive   log 6 thread 1 sequence 11
ARC1: Beginning to archive log 6 thread 1 sequence 11
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/arch/arch_1_11.arc'
ARC1: Completed archiving  log 6 thread 1 sequence 11
RFS: Successfully opened standby logfile 5: '/oracle/dbs/sorl1.log'
Attempt to start background Managed Standby Recovery process
Media Recovery Log /oracle/arch/arch_1_9.arc

10 rows selected.

8.6 Tuning the Log Apply Rate for a Physical Standby Database

Consider using the following methods to optimize the time it takes to apply redo to physical standby databases. Also, see the Oracle Media Recovery Best Practices white paper for more information: http://otn.oracle.com/deploy/availability/htdocs/maa.htm.

Set Parallel Recovery to Twice the Number of CPUs on One Standby Host

During media recovery or Redo Apply, the redo log file is read, and data blocks that require redo application are parsed out. With parallel media recovery, these data blocks are subsequently distributed evenly to all recovery processes to be read into the buffer cache. The default is serial recovery or zero parallelism, which implies that the same recovery process reads the redo, reads the data blocks from disk, and applies the redo changes.

To implement parallel media recovery or Redo Apply, add the optional PARALLEL clause to the recovery command. Furthermore, set the database parameter PARALLEL_MAX_SERVERS to at least the degree of parallelism. The following examples show how to set recovery parallelism:

RECOVER STANDBY DATABASE PARALLEL #CPUs * 2;

You should compare several serial and parallel recovery runs to determine optimal recovery performance.

Set DB_BLOCK_CHECKING=FALSE for Faster Redo Apply Rates

Setting the DB_BLOCK_CHECKING=FALSE parameter during standby or media recovery can provide as much as a twofold increase in the apply rate. The lack of block checking during recovery must be an accepted risk. Block checking should be enabled on the primary database. The DB_BLOCK_CHECKSUM=TRUE (the default) should be enabled for both production and standby databases. Because the DB_BLOCK_CHECKING parameter is dynamic, it can be toggled without shutting down the standby database.

Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.

The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query operations and should be tested with any parallel query operations to ensure there is sufficient memory on the system. A large number of parallel query slaves on a 32-bit installation may reach memory limits and prohibit increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K (2048) to 4K.

Tune Disk I/O

The biggest bottlenecks encountered during recovery are read and write I/O. To relieve the bottleneck, use native asynchronous I/O and set the database parameter DISK_ASYNCH_IO to TRUE (the default). The DISK_ASYNCH_IO parameter controls whether or not disk I/O to datafiles is asynchronous. Asynchronous I/O should significantly reduce database file parallel reads and should improve overall recovery time.