Search This Blog

Total Pageviews

Tuesday 16 August 2011

Oracle Archive Gaps in Standby Database

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             7104






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;




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


Oracle DBA

anuj blog Archive