Data Guard主备GAP查询select * from V$ARCHIVE_GAP报错（ORA-01220）

现象描述：

Oracle 11g主库归档日志无法删除，怀疑主备之间存在GAP，Data Guard备端执行select * from V$ARCHIVE_GAP; 出现如下报错，导致无法直观得到主备之间具体的GAP所在。

SQL> select OPEN_MODE,DATABASE_ROLE from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1     199322      39768             0            0
RFS       IDLE         LGWR              2     142464      63290             0            0
MRP0      APPLYING_LOG N/A               1     199322      39764             9            9

SQL> select * from V$ARCHIVE_GAP;

select * from V$ARCHIVE_GAP
*
ERROR at line 1:
ORA-01220: file based sort illegal before database is open

分析：

摘录一下Oracle官方对ORA-01220的解释：

Database: 11g Release 2
Error code: ORA-01220
Description: file based sort illegal before database is open
Cause: A query issued against a fixed table or view required a temporary segment for sorting before the database was open. Only in-memory sorts are supported before the database is open.
Action: Re-phrase the query to avoid a large sort, increase the values of the SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE initialization parameters to enable the sort to be done in memory.

但由于是生产数据库的Data Guard，且其他生产数据库的Data Guard相关参数与之一致，执行select * from V$ARCHIVE_GAP; 却没问题，故未进行相关参数的调整，通过日志及主库的v$archived_log视图推断确定了GAP所在。

结论：

事后通过查阅，推断可能是V$ARCHIVE_GAP动态视图背后基表的处理逻辑涉及到了大量的排序，所以导致执行select * from V$ARCHIVE_GAP; 速度较慢，以及可能出现ORA-01220的报错。同时，也查询到Oracle关于V$ARCHIVE_GAP的一个BUG(18411339)，但遗憾的是该BUG在**12.2.0.1 (Base Release)**之后才有相应的补丁，根据该BUG的介绍，确认ORA-01220是由于该BUG引起的。

曲线解决方案：

与此同时，该BUG的描述页面提供了一种间接的查询主备间GAP的方式，亲测通过该SQL查询速度要快于select * from V$ARCHIVE_GAP; ，且有无GAP情况下，二者得到的结果都是相同的，唯一的缺点就是太长了。

SELECT USERENV('Instance'),
       high.thread#,
       low.lsq,
       high.hsq
FROM
  (SELECT a.thread#,
          rcvsq,
          min(a.sequence#)-1 hsq
   FROM v$archived_log a,
     (SELECT lh.thread#,
             lh.resetlogs_change#,
             max(lh.sequence#) rcvsq
      FROM v$log_history lh,
           v$database_incarnation di
      WHERE lh.resetlogs_time = di.resetlogs_time
        AND lh.resetlogs_change# = di.resetlogs_change#
        AND di.status = 'CURRENT'
        AND lh.thread# IS NOT NULL
        AND lh.resetlogs_change# IS NOT NULL
        AND lh.resetlogs_time IS NOT NULL
      GROUP BY lh.thread#,
               lh.resetlogs_change# ) b
   WHERE a.thread# = b.thread#
     AND a.resetlogs_change# = b.resetlogs_change#
     AND a.sequence# > rcvsq
   GROUP BY