| Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-01 |
|
|
View PDF |
| Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-01 |
|
|
View PDF |
This chapter describes how to manage physical standby databases. This chapter contains the following topics:
Opening a Standby Database for Read-Only or Read/Write Access
Managing Primary Database Events That Affect the Standby Database
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.
This section describes the SQL*Plus statements used to start up and shut down 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:
Start and mount the physical standby database:
SQL> STARTUP MOUNT;
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, anORA-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. |
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:
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;
If Redo Apply is running, cancel it as shown in the following example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;
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.
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 |
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.
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:
Cancel Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
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, theALTER 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:
Terminate all active user sessions on the standby database.
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.
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:
A SQL ALTER DATABASE statement is issued with the ENABLE THREAD or DISABLE THREAD clause.
The status of a tablespace changes (changes to read/write or read-only, placed online or taken offline).
A data file is added or tablespace is created when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
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 data file or create a tablespace | If you did not set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, you must copy the new data file to the standby database. |
| Section 8.3.2 |
Drop or delete a tablespace or data file | Delete data files 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 data file | Rename the data file 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 data file 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. |
The initialization parameter, STANDBY_FILE_MANAGEMENT, enables you to control whether or not adding a data file 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 data files 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 data file to the standby database when you add a data file to the primary database.
Note that if you copy an existing data file from another database to the primary database, then you must also copy the new data file 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 data file to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.
The following example shows the steps required to add a new data file to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
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;
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;
Verify the new data file 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
Verify the new data file 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
This section shows how to add a new data file 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 data files 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 theDB_FILE_NAME_CONVERT initialization parameter to convert the path names. |
By setting the STANDBY_FILE_MANAGEMENT parameter to AUTO whenever new data files 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 data files, 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 data file.On the primary database, create a new tablespace where the data files 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 data file 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...
To correct the problems described in Section 8.3.1.2.1, perform the following steps:
Create the raw slice on the standby database and assign permissions to the Oracle user.
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';
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
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 data file and recovery continues.
When you delete one or more data files or drop one or more tablespaces in the primary database, you also need to delete the corresponding data files to the standby database. The following sections provide examples of dropping tablespaces and deleting data files when the STANDBY_FILE_MANAGEMENT initialization parameter 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:
Drop the tablespace from the primary database:
SQL> DROP TABLESPACE tbs_4; SQL> ALTER SYSTEM SWITCH LOGFILE;
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 data files are no longer part of the database, query the V$DATAFILE view.
Delete the corresponding data file 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
On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the data file for the tablespace. For example:
% rm /disk1/oracle/oradata/payroll/tbs_4.dbf
You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the data files 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;
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:
Generate a transportable tablespace set that consists of data files for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.
Transport the tablespace set:
Copy the data files and the export file to the primary database.
Copy the data files to the standby database.
The data files 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.
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.
When you rename one or more data files in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same data files 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 data file in the primary database and manually propagate the changes to the standby database.
To rename the data file in the primary database, take the tablespace offline:
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the data file on the primary system:
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
Rename the data file 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;
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;
Shut down the standby database:
SQL> SHUTDOWN;
Rename the data file 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
Start and mount the standby database:
SQL> STARTUP MOUNT;
Rename the data file 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';
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 data file at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed data file, 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 data file 4 - see DBWR trace file ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'
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.
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:
If Redo Apply is running, you must cancel Redo Apply before you can change the log files.
If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.
Add or drop an online redo log file:
Repeat the statement you used in Step 3 on each standby database.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.
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.
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. |
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.
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
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.
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.
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:
Determining the Location and Creator of the Archived Redo Log Files
Viewing Database Incarnations Before and After OPEN RESETLOGS
Determining Which Log Files Were Applied to the Standby Database
Determining Which Log Files Were Not Received by the Standby Site
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
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
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.
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.
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.
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.
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.
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.
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;
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.
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
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.
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.
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.
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 data files is asynchronous. Asynchronous I/O should significantly reduce database file parallel reads and should improve overall recovery time.
