现象描述:
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