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

13 thoughts on “Use of v$archived_log and v$log_history on Physical Standby

    1. The purpose of the view on primary and dataguard is different.The max(sequence#) from v$archived_log can be different on prod and DG.

      Anand

  1. Hi Anand,

    What you stated across was informative and it would be same I believe if we carry out snapshot database and back to physical standby.

    let me know your comments on this.

  2. Hi Anand,

    this post is interesting but I have some questions :
    1- where come the 5 extra applied logs at 09:33:45 compare to 09:19:00 when the automatic apply processing was cancelled ?
    2 where come the 15 logs difference at 10:08:35 after manual recovery was done, why are not these 15 logs in v$archived_log ?
    Thanks in advance for any clarification you may provide
    3- why don’t you use also resetlogs_change# for v$log_history ?

    Regards

    1. Here Manually shipping means copying the archives from primary to standby using scp command.Once the archives are shipped using scp command to the standby, we can use ‘recover standby database’ command to apply these transferred logs.

  3. Hello Anand,

    I have made a physical standby database (manual standby), and it seems that the v$archived_log view is not refreshing.

    I manually copy the archived logs from primary, and i am applying OK. But sequence y archived_log is not moving.

    SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#;
    THREAD# LAST_APPLIED_LOG
    ———- —————-
    1 1057

    SQL> select max(sequence#) from v$archived_log where applied=’YES’;
    MAX(SEQUENCE#)
    ————–
    1000

    — You can also use the APPLIED column in the V$ARCHIVED_LOG fixed view on the standby database to find out which log is applied on the standby database. The column displays YES for the log that has been applied.
    SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
    THREAD# SEQUENCE# APP
    ———- ———- —
    1 331 NO
    1 332 NO
    1 330 NO
    1 1000 YES

    ——————————————————————————
    Also, rman crosscheck is not detecting my arch logs.

    RMAN> crosscheck archivelog all;

    archive log filename=/o2/gral/arch/arch_1_1000.arc recid=4 stamp=926346626
    Crosschecked 1 objects

    –And on my FS i have
    /o2/gral/arch/arch_1_1000.arc
    …..
    /o2/gral/arch/arch_1_1157.arc

    Can you help me with this.? maybe a problem with standby control file?

      1. Hello Anand,

        1) I have NO Data guard.. I ship my arch logs manually
        2) Yes, i iam copying them

        3) As I have no data guard, I am applying them with:
        SQL> recover automatic standby database;

        So I think the answer will be: no
        MRP is only for data guard?

        Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s