How to check standby database sync status in Oracle?
sql scrip to check Standby database sync status:
Check Primary and Standby Databases sync Status:
Script:
You can download this script here
REM
REM Purpose: This script will help in cheking archive log gap or sync status between PROD & Standby Database
REM AUTHOR: Mallikarjun Ramadurg
REM SCRIPT Name: dg_gap_on_standby.sql
REM Usage: Connect to Standby Database on sql command prompt and run as below
REM Example: SQL>@dg_gap_on_standby.sql
REM
set verify off
set feed off
set timing off
PROMPT
PROMPT Recovery lag time from Source...
select 'This DR env is '||trunc(sysdate-max(first_time))||' Hrs and '||
trunc(((sysdate-max(first_time))*24-trunc((sysdate-max(first_time))*24))*60)||' Mins'||
' behind last archive log from PROD...' "TIME LAG"
from v$archived_log
where applied = 'YES';
PROMPT
PROMPT Recovery details per thread
PROMPT '--------------------------'
select al.thread# , min(al.sequence#) "MIN SEQ#", max(al.sequence#) "MAX SEQ#" ,
min(to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss')) "MIN FIRST TIME", round(sum(blocks * block_size) /1024/1024,2) "MB"
from v$archived_log al,
(select thread#, nvl(max(sequence#),0) max_seq from v$archived_log
where applied = 'YES' and registrar = 'RFS' and standby_dest = 'NO'
group by thread#) maxa
where applied= 'NO'
and registrar = 'RFS'
and standby_dest = 'NO'
and (al.thread# = maxa.thread# and al.sequence# > maxa.max_seq)
and exists (select 'x' from v$instance where status != 'OPEN')
group by al.thread#
having min(al.sequence#) > 0
;
PROMPT
PROMPT Last logs applied
PROMPT '----------------'
col archived for a8
col applied for a7
SELECT * FROM (
SELECT sequence#, thread#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI:SS') AS completed
FROM sys.v$archived_log
WHERE applied='YES'
ORDER BY completed DESC)
WHERE ROWNUM <= 10;
PROMPT
PROMPT The following query should show varying block# for error thread#
PROMPT '---------------------------------------------------------------'
select process, status, sequence#, blocks, block#
from v$managed_standby
where sequence# <> 0 ;
PROMPT
PROMPT Number of files to catch up; registered by NOT applied...
PROMPT '--------------------------------------------------------'
break on report
compute sum of count(1) on report
SELECT thread#, count(1) FROM V$ARCHIVED_LOG where applied <> 'YES' group by thread# order by thread#;
set timing on
Sample Output:
SQL> @dg_gap_on_standby.sql
Recovery lag time from Source...
TIME LAG
--------------------------------------------------------------------------------
This DR env is 0 Hrs and 11 Mins behind last archive log from PROD...
Recovery details per thread
'--------------------------'
Last logs applied
'----------------'
SEQUENCE# THREAD# ARCHIVED APPLIED COMPLETED
---------- ---------- -------- ------- -------------------
15579 1 YES YES 2022/11/11 15:21:10
15578 1 YES YES 2022/11/11 15:21:07
15577 1 YES YES 2022/11/11 15:21:04
15576 1 YES YES 2022/11/11 15:21:01
15575 1 YES YES 2022/11/11 15:20:58
15574 1 YES YES 2022/11/11 15:20:55
15571 1 YES YES 2022/11/11 15:20:52
15570 1 YES YES 2022/11/11 15:20:50
15569 1 YES YES 2022/11/11 15:20:49
15568 1 YES YES 2022/11/11 15:14:57
The following query should show varying block# for error thread#
'---------------------------------------------------------------'
PROCESS STATUS SEQUENCE# BLOCKS BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 15573 4 1
ARCH CLOSING 15578 7 1
ARCH CLOSING 15569 434 1
ARCH CLOSING 15579 8 1
RFS IDLE 15580 1 529
MRP0 APPLYING_LOG 15580 409600 529
Number of files to catch up; registered by NOT applied...
'--------------------------------------------------------'
THREAD# COUNT(1)
---------- ----------
1 6
----------
sum 6
SQL>
Regards,
Mallik
No comments:
Post a Comment