How to Check Sync between Primary & Standby DB ??

by

Primary

1
2
3
4
5
6
7
8
9
10
11
12
13
--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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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)
--
--
--
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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
1
2
3
4
5
6
7
SQL> select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 
 DEST_ID    STATUS    ERROR
---------- --------- -------------------------------------------
      2     VALID
 
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
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
1
2
3
4
5
6
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- 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

1
2
3
4
5
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 !! 🙂