Oracle Standby Gap info
Oracle Standby log gap sql
Standby Gap
On the Both
set pagesize 200
set linesize 200
select
substr(local.name,1,70) "Archive Name",
case when remote.sequence# is null then 'NOT TRANSMITTED'
else 'transmitted'
end,
local.sequence#,
local.thread#
from
(select * from v$archived_log where dest_id = 1) local
left join
(select * from v$archived_log where dest_id = 2) remote
on local.sequence# = remote.sequence# and
local.thread# = remote.thread#
order by local.sequence#;
Archive Name CASEWHENREMOTE. SEQUENCE# THREAD#
---------------------------------------------------------------------- --------------- ---------- ----------
/u01/oracle/Archive/arch_1_0000000025_780268156.arc transmitted 25 1
/u01/oracle/Archive/arch_1_0000000026_780268156.arc transmitted 26 1
/u01/oracle/Archive/arch_1_0000000027_780268156.arc transmitted 27 1
/u01/oracle/Archive/arch_1_0000000028_780268156.arc transmitted 28 1
/u01/oracle/Archive/arch_1_0000000029_780268156.arc transmitted 29 1
/u01/oracle/Archive/arch_1_0000000030_780268156.arc transmitted 30 1
/u01/oracle/Archive/arch_1_0000000031_780268156.arc transmitted 31 1
/u01/oracle/Archive/arch_1_0000000032_780268156.arc transmitted 32 1
On Standby
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
THREAD# SEQUENCE#
---------- ----------
1 49
1 50
1 51
1 52
1 53
1 54
1 59
1 60
8 rows selected.
On Standby
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 60 60 0
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 59 1 1381
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 APPLYING_LOG 1 60 791 102400
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 60 791 1
9 rows selected.
on Primary
column DEST_NAME format A19
column DB_UNIQUE_NAME format A15
column VALID_NOW format A9
column VALID_TYPE format A15
column VALID_ROLE format A15
select DEST_NAME,DB_UNIQUE_NAME,VALID_NOW ,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST WHERE DB_UNIQUE_NAME <> 'NONE';
DEST_NAME DB_UNIQUE_NAME VALID_NOW VALID_TYPE VALID_ROLE
------------------- --------------- --------- --------------- ---------------
LOG_ARCHIVE_DEST_2 STANDBY YES ONLINE_LOGFILE PRIMARY_ROLE
col archive_log_seq format 9999999
col dest format a10
select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#),'2' dest from v$archived_log where DEST_ID=2
or
select * from (select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#) archive_log_seq ,'2' dest from v$archived_log where DEST_ID=2 )
ARCHIVE_LOG_SEQ DEST
--------------- ----------
60 1
60 2
select pri , stand , pri - stand "Diff" from ( select max(SEQUENCE#) pri from v$archived_log where DEST_ID=1) , (select max(b.SEQUENCE#) stand from v$archived_log b where b.DEST_ID=2 );
PRI STAND Diff
---------- ---------- ----------
60 60 0
On standby
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS ;
or
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS where ARCHIVED_THREAD# !=0 ;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 60 0 0
1 60 1 60
on Stnadby
alter session set nls_date_format='dd-mon-yy hh24:mi:ss'
Session altered.
select app_thread, seq_app, tm_applied,
nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd
from (select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread
from v$archived_log where applied = 'YES'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'YES'
group by thread# ) ),(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread
from v$archived_log where applied = 'NO'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'NO'
group by thread# )
)
where rcvd_thread(+)= app_thread
/
APP_THREAD SEQ_APP TM_APPLIED SEQ_RCVD TM_RCVD
---------- ---------- ------------------ ---------- ------------------
1 60 19-jan-13 07:04:29 60 19-jan-13 07:04:29
On primary
select (select name from V$DATABASE) database ,(select max(sequence#) from v$archived_log where dest_id=1) Current_primary_seq,
( select max(sequence#) from v$archived_log where next_time > sysdate - 1
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v$archived_log
where next_time > sysdate - 1 and dest_id=2 and applied='NO'),0) from dual) "To be applied",
((select max(sequence#) from v$archived_log
where dest_id=1) - (select max(sequence#) from v$archived_log where dest_id=2)) "To be Shipped" from dual
/
DATABASE CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
VIHAAN 60 60 0 0
On both The site Primary and Standby
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
Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 60 60
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
Thread Last Sequence Generated
---------- -----------------------
1 60
1 60
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 60 60 0
On Primary
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
Thread Last Sequence Generated
---------- -----------------------
1 60
1 60
On Physical Standby
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;
Thread Last Sequence Generated
---------- -----------------------
1 60
set pagesize 0 select 'Thread : ' || THREAD ||chr(10)|| 'Prod - Archived : ' ||PR_Archived ||chr(10)|| 'STBY - Archived : ' ||STBY_ARCHIVED ||chr(10)|| 'STBY - Applied : ' ||STBY_APPLIED ||chr(10)|| 'Shipping GAP (PR -> STBY) : ' ||SHIPPING_GAP_STBY ||chr(10)|| 'Applied GAP (STBY -> STBY) : ' ||APPLIED_GAP_STBY_STBY from ( select t1 THREAD, pricre "PR_ARCHIVED", stdcre "STBY_ARCHIVED", stdnapp "STBY_APPLIED", pricre-stdcre "SHIPPING_GAP_STBY", stdcre-stdnapp "APPLIED_GAP_STBY_STBY" from (select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a, (select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b, (select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1 ; set pagesize 80 Thread : 1 Prod - Archived : 7105 STBY - Archived : 7105 STBY - Applied : 7104 Shipping GAP (PR -> STBY) : 0 Applied GAP (STBY -> STBY) : 1 set linesize 200 pagesize 200 alter session set nls_date_format='dd-mm-YYYY hh24:mi'; col destination for a15 select /*+ parallel(8) */ sysdate, ar.inst_id "inst_id", ar.dest_id "dest_id", ar.status "dest_status", ar.destination "destination", (select MAX (sequence#) highiest_seq from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and thread# = ar.inst_id and dest_id = ar.dest_id) - NVL ( (select MAX (sequence#) from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and thread# = ar.inst_id and dest_id = ar.dest_id and standby_dest = 'YES' and applied = 'YES'), 0) "applied_gap", (SELECT MAX (sequence#) highiest_seq from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# AND thread# = ar.inst_id) - NVL ( (SELECT MAX (sequence#) from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and thread# = ar.inst_id and dest_id = ar.dest_id and standby_dest = 'YES'), 0) "received_gap", NVL ( (SELECT MAX (sequence#) from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and thread# = ar.inst_id and dest_id = ar.dest_id and standby_dest = 'YES'), 0) "last_received_seq", NVL ( (SELECT MAX (sequence#) from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and thread# = ar.inst_id and dest_id = ar.dest_id and standby_dest = 'YES' and applied = 'YES'), 0) "last_applied_seq" from (SELECT DISTINCT dest_id, inst_id, status, target, destination, error from sys.gv_$archive_dest where target = 'STANDBY' and STATUS <> 'DEFERRED') ar; SYSDATE inst_id dest_id dest_stat destination applied_gap received_gap last_received_seq last_applied_seq ---------------- ---------- ---------- --------- --------------- ----------- ------------ ----------------- ---------------- 17-08-2023 14:58 1 2 VALID *********_****5 1 0 7105 7104SELECT 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; SELECT 'Last Applied : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# 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,thread#,sequence# FROM v$archived_log WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log ); LOGS TIME THREAD# SEQUENCE# ---------------- --------------------------- ---------- ---------- Last Applied : 11-JUN-23:08:35:19 1 35567 Last Received : 11-JUN-23:08:44:26 1 35568 -- Check that Archive Logs are being Shipped -- This query needs to be run on the Primary database SET PAGESIZE 300 COL DB_NAME FORMAT A8 COL HOSTNAME FORMAT A20 COL LOG_ARCHIVED FORMAT 999999 COL LOG_APPLIED FORMAT 999999 COL LOG_GAP FORMAT 9999 COL APPLIED_TIME FORMAT A12 SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM ( SELECT NAME DB_NAME FROM V$DATABASE ), ( SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' );
=====================================================================
Oracle DataGaurd on Standard Edition !!!!!!!!!!!!!!!!!!!
on standby !!!! SE !!!!!! dbvisit
--Prod !!
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2,almax Max_seq_arch1,almax2 Max_seq_arch2
from (select max(sequence#) almax from gv$archived_log where THREAD#=1) al1,
(select max(sequence#) almax2 from gv$archived_log where THREAD#=2) al2,
(select max(sequence#) maxSCN1 from gv$log_history where THREAD#=1) lh,
(select max(sequence#) maxSCN2 from gv$log_history where THREAD#=2) ;
MAX_SEQ_HIST1 MAX_SEQ_HIST2 MAX_SEQ_ARCH1 MAX_SEQ_ARCH2
------------- ------------- ------------- -------------
524064 470606 524064 470606
---on standby !!!! SE !!!!!! dbvisit
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2
--,almax Max_seq_arch1,almax2 Max_seq_arch2
from (select max(sequence#) almax from gv$archived_log where THREAD#=1) al1,
(select max(sequence#) almax2 from gv$archived_log where THREAD#=2) al2,
(select max(sequence#) maxSCN1 from gv$log_history where THREAD#=1) lh,
(select max(sequence#) maxSCN2 from gv$log_history where THREAD#=2) ;
MAX_SEQ_HIST1 MAX_SEQ_HIST2
------------- -------------
524064 470605
Physical Standby
set linesize 300
alter session set nls_date_format = 'dd-Mon-yyyy hh24:mi:ss';
select * from ( select sysdate,count(*) To_Be_Restored from x$kcvfh where fhrba_seq=0),
( select count(*) restored,max(fhrba_seq) max_sequence,min(fhrba_seq) min_sequence,max(fhscn) max_scn from x$kcvfh where fhrba_seq!=0),
( select count(*) total,min(fhscn) min_scn,min(fhrba_seq) min_seq from x$kcvfh)
On Primary SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1; On Physical Standby SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied" 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;
-- Standard edition
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2
--,almax,almax2
from (select max(sequence#) almax from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=1)) al1,
(select max(sequence#) almax2 from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=2)) al2,
(select max(sequence#) maxSCN1 from v$log_history where first_time=(select max(first_time) from v$log_history where THREAD#=1)) lh,
(select max(sequence#) maxSCN2 from v$log_history where first_time=(select max(first_time) from v$log_history where THREAD#=2)) ;
