Use of v$archived_log and v$log_history on Physical Standby

This blog entry is about the use of v$archived_log and v$log_history views on the standby database, which many of you would be aware of, and few like me mite not.

Till now i always used the below sql to check the last “applied” archived log on the standby, which had almost given me the correct sequence number :-

select max(sequence#) from v$archived_log where applied='YES';

Today,the same above query gave me sequence number far less ,than the actual applied.The reason being, automatic recovery of the dataguard was cancelled and archives were applied manually using recover standby database.

The physical dataguard is opened in read only mode for some period of time and as it was almost lagging with ~20 archives logs (slow network), the automatic recovery was cancelled, archives were shipped using FTP and applied manually and then opened in READ ONLY mode.

Once, the database was opened in read only mode, i checked the maximum sequence number applied using the above mentioned sql and found the sequence to be wrong.

Before cancelling the automatic recover

09:18:38 SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ------------- ------------
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
MRP0      WAIT_FOR_GAP N/A          123712          0             0            0
RFS       IDLE         UNKNOWN           0          0             0            0
RFS       IDLE         UNKNOWN           0          0             0            0

The MRP process shows WAIT_FOR_GAP , sequence number 123712.
Automatic recovery was cancelled and, archives were transferred from production to physical standby server using FTP.

To cancel the automatic recovery :-

09:19:00 SQL>alter database recover managed standby database cancel;

09:19:48 SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
        123711

09:19:49 SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ------------- ------------
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
RFS       IDLE         UNKNOWN           0          0             0            0
RFS       IDLE         UNKNOWN           0          0             0            0

Archive logs till sequence number 123739 were shipped and applied manually.Once,the manual recovery was done, the database was opened in the read only mode using

09:33:35 SQL>alter database open read only;

09:33:45 SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
        123716

10:08:35 SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
10:08:35   2  from (select thread# thrd, max(sequence#) almax
10:08:35   3        from v$archived_log
10:08:35   4        where resetlogs_change#=(select resetlogs_change# from v$database)
10:08:35   5        group by thread#) al,
10:08:35   6       (select thread# thrd, max(sequence#) lhmax
10:08:35   7        from v$log_history
10:08:35   8        where first_time=(select max(first_time) from v$log_history)
10:08:35   9        group by thread#) lh
10:08:35  10  where al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1            123724           123739

10:08:40 SQL>
10:08:47 SQL>
10:10:06 SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
        123716

10:10:26 SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
        123739

The view v$log_history shows the correct max sequence number.So,the following views which are useful on a standby database:-

V$ARCHIVED_LOG: displays archive logs “received” from the primary
V$LOG_HISTORY: displays archive logs that have “started” to be applied (not necessarily completed)
V$LOG: displays “primary database” logfile information
V$STANDBY_LOG: displays “standby database” logfile information (if standby redo logfiles are used).

Advertisement