Re: Why the SQL of checking gap num is wrong on oracle data guard primary (primary is RAC and physical standby is single instance)?

On 11/9/22 20:38, Quanwen Zhao wrote:
> 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

This is the case of the wrong tool being used. I usually write a little Python script to detect the gaps on the disk. Complex queries like this one are definitely an overkill.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l