Why the SQL of checking gap num is wrong on oracle data guard primary (primary is RAC and physical standby is single instance)?
From: Quanwen Zhao <quanwenzhao_at_gmail.com>
Date: Thu, 10 Nov 2022 09:38:04 +0800
Message-ID: <CABpiuuT-VLTgkr32g+jMLaE-O3JKEAQAVc6fwYcopYvpWQ=XVw_at_mail.gmail.com>
Hello listeners :-),
) D
WHERE C.INST_ID = D.INST_ID; Very appreciated if any help, thanks for advance!
Date: Thu, 10 Nov 2022 09:38:04 +0800
Message-ID: <CABpiuuT-VLTgkr32g+jMLaE-O3JKEAQAVc6fwYcopYvpWQ=XVw_at_mail.gmail.com>
Hello listeners :-),
Why the SQL of checking gap num is wrong on oracle data guard primary (primary is RAC and physical standby is single instance)?
GAP_STATUS PRIMARY_MAXSEQ# STANDBY_MAXSEQ# GAP_NUMS
------------------- ------------------------------
------------------------------ ------------------
NO GAP 2163678 1262072
901606
NO GAP 1262073 1262073
0
The following is my SQL statement:
SELECT D.INST_ID,
D.DB_UNIQUE_NAME,
D.DEST_NAME,
D.STATUS,
D.GAP_STATUS TRANS_GAP,
C.PRIMARY_SEQ,
C.STANDBY_SEQ,
C.APPLIED_GAP
FROM (SELECT A.INST_ID,
B.LOG_SEQUENCE PRIMARY_SEQ,
A.LOG_SEQUENCE STANDBY_SEQ,
B.LOG_SEQUENCE - A.LOG_SEQUENCE APPLIED_GAP
FROM (SELECT INST_ID,
MAX(DECODE(GAP_STATUS, 'NO GAP', APPLIED_SEQ#+1, 'LOG
SWITCH GAP', APPLIED_SEQ#, 'UNRESOLVABLE GAP', APPLIED_SEQ#, 'LOCALLY
UNRESOLVABLE GAP', APPLIED_SEQ#)) LOG_SEQUENCE
FROM GV$ARCHIVE_DEST_STATUS
WHERE TYPE = 'PHYSICAL'
AND STATUS = 'VALID'
GROUP BY INST_ID
) A,
(SELECT INST_ID,
MAX(LOG_SEQUENCE) LOG_SEQUENCE
FROM GV$ARCHIVE_DEST
WHERE TARGET = 'PRIMARY'
AND STATUS = 'VALID'
GROUP BY INST_ID
) B
WHERE A.INST_ID = B.INST_ID
) C,
(SELECT INST_ID,
DB_UNIQUE_NAME,
DEST_ID,
DEST_NAME,
STATUS,
GAP_STATUS
FROM GV$ARCHIVE_DEST_STATUS
WHERE STATUS = 'VALID'
AND TYPE = 'PHYSICAL'
) D
WHERE C.INST_ID = D.INST_ID; Very appreciated if any help, thanks for advance!
Best Regards
Quanwen Zhao
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 10 2022 - 02:38:04 CET