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