How to Check Sync between Primary & Standby DB ??
6 min readPrimary
--Check DB role (PRIMARY/STANDBY)
SELECT DATABASE_ROLE,
DB_UNIQUE_NAME AS INSTANCE,
OPEN_MODE,
PROTECTION_MODE,
PROTECTION_LEVEL,
SWITCHOVER_STATUS
FROM V$DATABASE;
DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
PRIMARY pvtnddb READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
--Monitor standby background process
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 760 317440 813
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 761 317440 776
ARCH CLOSING 1 758 317440 743
ARCH CLOSING 1 759 344064 2046
LNS WRITING 1 762 143052 1
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
9 rows selected.
SELECT /*+ RULE */
ARCH.THREAD# AS "Thread",
ARCH.SEQUENCE# AS "Last Sequence Received",
APPL.SEQUENCE# AS "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "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#;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 761 761 0
1 761 761 0
SQL>
set line 150 pages 1000
set verify off
set trimspool on
set echo off
set feedback off
set termout off
col Destination for a40
set pages 1000 lines 175
col Thread for 9999999
col Primary form 9999999
col Standby form 9999999
col STBYApplied form 9999999
col ShipGAP(PR2STBY) form 9999999
col ApplGAP(STBY2STBY) form 9999999
col Destination for a40
col INSTANCE for a10
col HOST for a30 set head off
select 'Sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
SELECT DEST_ID, DESTINATION, TARGET, STATUS
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
-- Calculate the gap between primary and standby logs
SELECT t1 AS "Thread",
pricre AS "Primary",
stdcre AS "Standby",
stdnapp AS "STBYApplied",
pricre - stdcre AS "ShipGAP(PR2STBY)",
stdcre - stdnapp AS "ApplGAP(STBY2STBY)"
FROM (SELECT MAX(sequence#) stdcre, thread# t1
FROM gv$archived_log
WHERE STANDBY_DEST = 'YES'
GROUP BY thread#) a,
(SELECT MAX(sequence#) stdnapp, thread# t2
FROM gv$archived_log
WHERE STANDBY_DEST = 'YES'
AND applied = 'YES'
GROUP BY thread#) b,
(SELECT MAX(sequence#) pricre, thread# t3
FROM gv$archived_log
WHERE STANDBY_DEST = 'NO'
GROUP BY thread#) c
WHERE a.t1 = b.t2
AND b.t2 = c.t3;
SET HEADING OFF
'SYSDATE:'||TO_CHAR(SYSDATE,'DD-MON-YY
--------------------------------------
Sysdate: 14-Sep-2024 00:42:13
SQL>
'****************STANDBYLOGSHIPANDLOGAPPLYSTATUS*****************'
----------------------------------------------------------------------
****************Standby Log ship and Log Apply Status*****************
DEST_ID DESTINATION TARGET STATUS
---------- ---------------------------------------- ---------------- ---------
1 USE_DB_RECOVERY_FILE_DEST PRIMARY VALID
2 cert101_standby_dgmgrl STANDBY VALID
Thread Primary Standby STBYApplied ShipGAP(PR2STBY) ApplGAP(STBY2STBY)
-------- -------- -------- ----------- ---------------- ------------------
1 761 761 761 0 0
-- View dataguard message or errors
SQL> select message from v$dataguard_status;
Beginning to archive T-1.S-698 (SCN:0x0000000004c764ac-SCN:0x0000000004c90e55)
Completed archiving LNO:2 T-1.S-698
Beginning to archive LNO:3 T-1.S-699
Completed archiving T-1.S-698 (SCN:0x0000000004c764ac-SCN:0x0000000004c90e55)
Beginning to archive T-1.S-699 (SCN:0x0000000004c90e55-SCN:0x0000000004cb26fb)
--
--
--
--
-- Last log applied/Received in standby
SET Lin 200 Pages 200
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A30
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' );
DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
-------- --------------- ------------ ----------- ------------ -------
CERT101 LOCALHOST 761 761 13-SEP/13:26 0
-- Last log applied/Received in standby
SELECT 'Last Log Applied: ' AS Logs,
TO_CHAR(next_time, 'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT 'Last Log Received: ' AS Logs,
TO_CHAR(next_time, 'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#)
FROM v$archived_log);
LOGS TIME
------------------- ---------------------------
Last Log Applied: 13-SEP-24:13:26:17
Last Log Received: 13-SEP-24:13:26:17
SQL> select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
DEST_ID STATUS ERROR
---------- --------- -------------------------------------------
2 VALID
SQL>
SQL> select thread#, max(sequence#) from v$archived_log GROUP BY thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 761
--Standby:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 761
SQL> 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 761
1 761
--STANDBY
SELECT ARCH.THREAD# AS "Thread",
ARCH.SEQUENCE# AS "Last Sequence Received",
APPL.SEQUENCE# AS "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "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 761 761 0
Get standby redo log info:
SET LINESIZE 200 pages 200
COLUMN member FORMAT A70
SELECT st.group#,
st.sequence#,
CEIL(st.bytes / 1048576) AS mb,
lf.member
FROM v$standby_log st,
v$logfile lf
WHERE st.group# = lf.group#;
GROUP# SEQUENCE# MB MEMBER
---------- ---------- ---------- ----------------------------------------------------------------------
4 0 200 +DATA/CERTDB/ONLINELOG/group_4.324.1155663001
4 0 200 +DATA/CERTDB/ONLINELOG/group_4.325.1155663005
5 0 200 +DATA/CERTDB/ONLINELOG/group_5.326.1155663017
5 0 200 +DATA/CERTDB/ONLINELOG/group_5.327.1155663021
6 0 200 +DATA/CERTDB/ONLINELOG/group_6.328.1155663033
6 0 200 +DATA/CERTDB/ONLINELOG/group_6.329.1155663037
7 0 200 +DATA/CERTDB/ONLINELOG/group_7.330.1155663045
7 0 200 +DATA/CERTDB/ONLINELOG/group_7.331.1155663051
8 rows selected.
Monitor lag in standby including RAC
-- Applicable for 2-NODE RAC
COLUMN applied_time FORMAT A30
SET LINESIZE 140
-- Display current system time
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') AS "Current Time" FROM dual;
-- Query for Node 1
SELECT DB_NAME,
APPLIED_TIME,
LOG_ARCHIVED - LOG_APPLIED AS LOG_GAP,
(CASE
WHEN ((APPLIED_TIME IS NOT NULL AND LOG_ARCHIVED - LOG_APPLIED IS NULL) OR
(APPLIED_TIME IS NULL AND LOG_ARCHIVED - LOG_APPLIED IS NOT NULL) OR
(LOG_ARCHIVED - LOG_APPLIED > 5))
THEN 'Error! Log Gap is '
ELSE 'OK!'
END) AS Status
FROM (
SELECT INSTANCE_NAME AS DB_NAME
FROM GV$INSTANCE
WHERE INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) AS LOG_ARCHIVED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1
AND ARCHIVED = 'YES'
AND THREAD# = 1
),
(
SELECT MAX(SEQUENCE#) AS LOG_APPLIED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
AND APPLIED = 'YES'
AND THREAD# = 1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME), 'DD-MON/HH24:MI') AS APPLIED_TIME
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
AND APPLIED = 'YES'
AND THREAD# = 1
)
UNION ALL
-- Query for Node 2
SELECT DB_NAME,
APPLIED_TIME,
LOG_ARCHIVED - LOG_APPLIED AS LOG_GAP,
(CASE
WHEN ((APPLIED_TIME IS NOT NULL AND LOG_ARCHIVED - LOG_APPLIED IS NULL) OR
(APPLIED_TIME IS NULL AND LOG_ARCHIVED - LOG_APPLIED IS NOT NULL) OR
(LOG_ARCHIVED - LOG_APPLIED > 5))
THEN 'Error! Log Gap is '
ELSE 'OK!'
END) AS Status
FROM (
SELECT INSTANCE_NAME AS DB_NAME
FROM GV$INSTANCE
WHERE INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) AS LOG_ARCHIVED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1
AND ARCHIVED = 'YES'
AND THREAD# = 2
),
(
SELECT MAX(SEQUENCE#) AS LOG_APPLIED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
AND APPLIED = 'YES'
AND THREAD# = 2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME), 'DD-MON/HH24:MI') AS APPLIED_TIME
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
AND APPLIED = 'YES'
AND THREAD# = 2
);
Monitor recovery progress in standby DB
SELECT TO_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI:SS') AS "Recovery Start Time",
TO_CHAR(item) || ' = ' || TO_CHAR(sofar) || ' ' || TO_CHAR(units) AS "Progress"
FROM v$recovery_progress
WHERE start_time = (SELECT MAX(start_time)
FROM v$recovery_progress);
Hope it helped !! 🙂
