rman 触发temp空间不足问题

oracle 12.2.0.1 单机，监控提示30G的temp表空间每日使用率很高。

--检查最近5天谁用temp比较多

select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
        from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
                from dba_hist_active_sess_history
               where sample_time between sysdate-5 and sysdate
            group by instance_number, sample_time, sql_id)
       group by instance_number, sql_id
       order by temp_max desc)
where rownum <= 10;

发现一个sql（这是个固定的sql_id）
0az7czjdw8z7j

declare
  lbRec    dbms_rcvman.lbRec_t;
  lbCursor dbms_rcvman.lbCursor_t;
  first    boolean := FALSE;
  ret      boolean;
begin
  if (:first > 0) then
    first := TRUE;
  end if;
  <<next_row>>
  ret := dbms_rcvman.listBackup(lbRecOut => lbRec,
                                 firstCall => first,
                                 only_obsolete => TRUE,
                                 re            dundancy => :redundancy,
                                 piped_call => FALSE,
                                 lbCursor => lbCurso r,
                                 lbState => dbms_rcvman.lbStat ePck,
                                 extRlKeepSCN => NULL);
  if (not ret) then
    raise no_data_found;
  elsif (lbRec.pkey is not null and lbRec.is_rdf = 'YES') then
    :backup_type := lbRec.backup_type;
    :file_ type := lbRec.file_type;
    :key := lbRec. pkey;
  else
    first := FALSE;
    goto next_row;
  end if;
end;

看来是在检索备份信息

--检查sql执行情况

set long 1000000
set longchunksize 1000000
set lin 300 pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;

执行了11秒后失败，且读写超过30G！

看看历史执行情况

col username for a8
col SQL_ID for a13
col max_sample_time for a22
select (select username from dba_users b where b.user_id = a.user_id) username,
       user_id, sql_id, session_id, session_serial# serial#, sql_exec_id, sql_exec_start, count(0),
       to_char(max(sample_time), 'yyyymmdd hh24:mi:ss') max_sample_time,
       (to_date(to_char(max(sample_time), 'yyyymmdd hh24:mi:ss'),
                'yyyymmdd hh24:mi:ss') - sql_exec_start) * 24 * 3600 exec_seconds
  from dba_hist_active_sess_history a
 where sql_id = '0az7czjdw8z7j'
   and to_char(sample_time, 'yyyymmdd hh24:mi') between '20221003 19:00' and '20221009 21:00'
 group by user_id, sql_id, session_id, session_serial#, sql_exec_id, sql_exec_start
 order by sql_exec_start;

为什么别的库没事？

[oracle@db01-[orcl]-/home/oracle]$ opatch lspatches
31219919;OJVM RELEASE UPDATE: 12.2.0.1.200714 (31219919)
31312468;Database Jul 2020 Release Update : 12.2.0.1.200714 (31312468)
31309299;OCW JUL 2020 RELEASE UPDATE 12.2.0.1.200714 (31309299)

是bug吗？

先不要这样想

看看备份信息实际情况
很久的都没清理

基于目前策略不应该
同样的备份软件系统，同样的配置，别的库都没事（极有可能是bug）

搭建测试环境，恢复一份全库来分析
问题复现
搜索一顿MOS

set timing on

alter database datafile 1,2 autoextend on next 128m;

exec dbms_stats.gather_table_stats('SYS','X$KCCOR')
exec dbms_stats.gather_table_stats('SYS','X$KRBPPBCTX')
exec dbms_stats.gather_table_stats('SYS','X$KCCFE')
exec dbms_stats.gather_table_stats('SYS','X$KCCDC')
exec dbms_stats.gather_table_stats('SYS','X$KCCBF')
exec dbms_stats.gather_table_stats('SYS','X$KCCBS')
--exec dbms_stats.gather_table_stats('SYS','X$KCCBP')
exec dbms_stats.gather_table_stats('SYS','X$KCCPD')
exec dbms_stats.gather_fixed_objects_stats;
begin
dbms_stats.gather_table_stats(
  ownname =>'SYS',
  tabname => 'X$KCCBP',
  no_invalidate => false,
  degree=>2,
  method_opt=>'for all columns size auto',
  cascade => true
);
end;
/

再执行report 就不再报错了

原因应该是：
备份信息太多（可能是bug）导致内部检索备份信息时生成了错误的执行计划，消耗过多的temp，收集部分固定表和全部固定表（没错），再过期一下sql area，让优化器产生正确的执行计划。

在带库的备份脚本中增加

delete noprompt backup completed before 'sysdate-30';

先这样。