One Script to see standby sync status

Run the below script on standby and you can get the good information on how it is working .

PROMPT

PROMPT

PROMPT Run on Standby Database. This script checks last log applied and last log received time

PROMPT

PROMPT

col time format a40

select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time

    from v$archived_log

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

    union

    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time

    from v$archived_log

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

PROMPT last sequence# received and the last sequence# applied to standby database.

PROMPT

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"

from (select thread# thrd, max(sequence#) almax

from v$archived_log

where resetlogs_change#=(select resetlogs_change# from v$database)

group by thread#) al,

(select thread# thrd, max(sequence#) lhmax

from v$log_history

where first_time=(select max(first_time) from v$log_history)

group by thread#) lh

where al.thrd = lh.thrd;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

The output of the script shows some thing like this:

====================================================

Run on Standby Database. This script checks last log applied and last log receiv                                                                                        ed time

LOGS             TIME

---------------- ----------------------------------------

Last applied  :  01-JUN-12:03:33:06

Last received :  01-JUN-12:12:22:43

last sequence# received and the last sequence# applied to standby database.

    Thread Last Seq Received Last Seq Applied

---------- ----------------- ----------------

         3             20533            20533

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                  55626                 55625          1

         2                  23183                 23183          0

         3                  20533                 20533          0

There is one more script which you can run on production as well.

  select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;