Home » Server Options » Data Guard » Issue in finding logs applied on Standby database (Oracle 10g R2 on HP-UX)
Issue in finding logs applied on Standby database [message #574718] Tue, 15 January 2013 07:49
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello

I am trying to check if the archivelogs are Not only "shipped" but also "Applied" properly without any issue on the standby

which of the following correctly answers 1) last log shipped but not applied 2) last log applied (thus shipped too) on the standby database

suppose on primary database the dest_id for standby database is 3


1)
From my primary database
----------------------------
I am checking the status of logs shipped & applied using folloiwng query
Does it look correct?

select (select max(SEQUENCE#) from gv$archived_log where thread#=1 and applied='YES' and dest_id=3) thread_1_max_applied,
	(select max(SEQUENCE#) from gv$archived_log where thread#=1 and archived='YES' and dest_id=3) thread_1_max_archived,
	(select max(SEQUENCE#) from gv$archived_log where thread#=2 and applied='YES' and dest_id=3) thread_2_max_applied,
        (select max(SEQUENCE#) from gv$archived_log where thread#=2 and archived='YES' and dest_id=3) thread_2_max_archived
from dual;

2)
From Primary database
---------------------------
select inst_id, dest_id,dest_name,status,type,destination,archived_thread#,archived_seq#,applied_thread#,applied_seq# from gv$archive_dest_status 
where status<>'INACTIVE' and dest_id=3 order by dest_id,inst_id;



3)
From Standby database
---------------------------
select max(sequence#),thread# from gv$archived_log group by thread#;


4)
From Standby database
---------------------------
select max(sequence#),thread# from gv$log_history group by thread#;


5)
From Standby database
---------------------------

Last RFS entries in alert.log 
RFS[1]: Archived Log: '/u02/oracle/admin/stgprd/arch/stgprd1/stgprd_2_15312_710867373.arc'
RFS[2]: Archived Log: '/u02/oracle/admin/stgprd/arch/stgprd1/stgprd_1_28040_710867373.arc'

Last "Media Recovery Log" entries in alert.log
Media Recovery Log /u02/oracle/admin/stgprd/arch/stgprd1/stgprd_1_28022_710867373.arc
Media Recovery Log /u02/oracle/admin/stgprd/arch/stgprd1/stgprd_2_15304_710867373.arc



6)
added to this following query never returns any records (in suspicion I executed it many times ,on many occasions on both standby and primry database.. but no luck!)
SQL> select * from v$archive_gap;
no rows selected

Is that a bug?




The record of logs which are shipped to standby matches on primary.gv$archive_dest_status, primary.gv$archived_log and standby.gv$archived_log
(does these queries look correct?)
However the record of logs which are applied on standby does not match between the views

Following are the Actual results when above queries are executed
Results on Primary
------------------------------------------------
------------------------------------------------

SQL> col dest_name form a30
SQL> col destination form a30
SQL> select inst_id, dest_id,dest_name,status,type,destination,archived_thread#,archived_seq#,applied_thread#,applied_seq# from gv$archive_dest_status
where status<>'INACTIVE' and dest_id=3 order by dest_id,inst_id;
  2
   INST_ID    DEST_ID DEST_NAME                      STATUS    TYPE           DESTINATION                    ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------- ---------- ------------------------------ --------- -------------- ------------------------------ ---------------- ------------- --------------- ------------
         1          3 LOG_ARCHIVE_DEST_3             VALID     PHYSICAL       stgprd_std_dg                                  1         28040               2        15304
         2          3 LOG_ARCHIVE_DEST_3             VALID     PHYSICAL       stgprd_std_dg                                  2         15312               2        15304

SQL> select (select max(SEQUENCE#) from gv$archived_log where thread#=1 and applied='YES' and dest_id=3) thread_1_max_applied,
  2     (select max(SEQUENCE#) from gv$archived_log where thread#=1 and archived='YES' and dest_id=3) thread_1_max_archived,
  3     (select max(SEQUENCE#) from gv$archived_log where thread#=2 and applied='YES' and dest_id=3) thread_2_max_applied,
  4          (select max(SEQUENCE#) from gv$archived_log where thread#=2 and archived='YES' and dest_id=3) thread_2_max_archived
  5  from dual;


THREAD_1_MAX_APPLIED THREAD_1_MAX_ARCHIVED THREAD_2_MAX_APPLIED THREAD_2_MAX_ARCHIVED
-------------------- --------------------- -------------------- ---------------------
               28021                 28040                15304                 15312


Results on Standby
------------------------------------------------
------------------------------------------------

SQL> select max(sequence#),thread# from gv$archived_log group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         28040          1
         15312          2


SQL> select max(sequence#),thread# from gv$log_history group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         15304          2
         28022          1



Please suggest

purpose is to find following 2 things on standby , if possible while queried from both primary and standby
1) last log shipped but not applied 2) last log applied (thus shipped too) on the standby database

Also I was thinking "Media Recovery Log" entry denotes the log which is applied to the standby database but many times this entry does not match with last applied; So what does

the "Media Recovery Log" indicates actually in alert.log of standby database?


Thank you

Regard
sysdba007
Previous Topic: Difficulty in detecting Archive gap on Physical Standby
Next Topic: not able to get data of primary in standby database (dataguard)
Goto Forum:
  


Current Time: Thu Mar 28 07:10:49 CDT 2024