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:

■ Monitoring the Process Activities

■ Determining the Progress of Redo Apply

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

■ Viewing Database Incarnations Before and After OPEN RESETLOGS

■ Viewing the Archived Redo Log History

■ Determining Which Log Files Were Applied to the Standby Database

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

8.5.3.1 Monitoring the Process Activities

You can obtain information about Redo Apply on a standby database by monitoring

Monitoring the Primary and Standby Databases

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.

Reference Name System Process Names

ARCH ARC0,ARC1,ARC2,…

MRP MRP, MRP0

RFS ORACLE{SID}

Monitoring the Primary and Standby Databases

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

Monitoring the Primary and Standby Databases

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

FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#

--- --- --

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

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

Monitoring the Primary and Standby Databases

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.