Oracle RMAN备份为什么会大量使用temp表空间？
导读：Oracle Rman不备份temp表空间，为什么会大量使用temp表空间呢？rman备份datafiles或archivelog files结束时需将rman jobs信息写入controfiles中，其中rman jobs的信息是通过视图v$rman_status查询得知，正是v$rman_status查询消耗了大量的temp表空间，并不是rman程序。通过mos查询发现当FIXED objects的统计信息不准确时，可能导致v$rman_status查询消耗了大量的temp表空间。
1.备份报错(以mos的case为例分析)
Finished Control File and SPFILE Autobackup at 22-MAR-20 ORA-00604: error occurred at recursive SQL level 1 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP RMAN-08132: warning: cannot update recovery area reclaimable file list sql statement: alter system archive log current
2.原因分析：(以提问的形式来分析原因)
--2.1 报错ORA-01652均在datafiles或archivelog files结束时 问：为什么rman程序报错ORA-01652均是在datafiles或archivelog files结束时呢？仅在结束时不会是一个巧合吧？我肯定不信。 答：rman备份datafiles或archivelog files结束时， 通过v$rman_status视图来查询rman jobs的progress and status信息，并写入controlfile中。 --2.2 v$rman_status导致大量temp space 问：查询v$rman_status视图为什么会导致大量temp space呢？ 答：v$rman_status这个视图的信息来源于FIXED objects，若FIXED objects的统计信息不准确， 可能造成大量temp space(排序)或hangs(x$导致各种各样的latches)
3.解决办法：
--3.1 第一种情况 问:FIXED objects统计信息不准确怎么办？ 答：exec dbms_stats.gather_fixed_objects_stats; #下面的解决rman备份绕过FIXED objects统计信息不准确的问题 或者RMAN> sql "alter session set optimizer_mode=RULE"; --3.2 第二种情况 问:FIXED objects行数据过大怎么办？ 答:重启实例，减少FIXED objects行数据
4.理论依据
--4.1 fixed tables统计信息不准确会导致performance degradation or hangs，或是导致大量使用TEMP。 Missing or bad statistics on the X$ / fixed tables that can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large and busy systems. RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often encounter performance issues. Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables. 参考：Queries from V$BACKUP_PIECE_DETAILS or V$RMAN_STATUS Return Error ORA-01652 (Doc ID 2119607.1) --4.2 当fixed tables统计信息不准确导致大量使用TEMP时，可重新收集统计信息 原因： SQL> select count(*) from v$rman_status ; select count(*) from v$rman_status * ERROR at line 1: ORA-01652: unable to extend temp segment by 640 in tablespace TEMP V$RMAN_STATUS displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs which are in progress are stored only in memory while the finished jobs are stored in the controlfile. 原理： Incorrect statistics on FIXED objects which resulted in an ineffective execution plan, needed much sort-space. Multiple waitevents on 'direct path write temp' 解决办法： exec dbms_stats.gather_fixed_objects_stats; If the above doesnot work you can explore Purging just the failing statement/Cursors using dbms_shared_pool.purge How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package (Doc ID 457309.1) 参考：Rman uses a lot Of Temporary Segments ORA-1652: Unable To Extend Temp Segment (Doc ID 357765.1) ##注意：dbms_stats.gather_fixed_objects_stats这种方法仅仅是收集fixed统计信息，不会减少fixed objects的行数据。因为fixed objects的行数据是随着实例的运行逐步增加的，所以你可以采取重启实例来减少fixed objects的行数据。 --4.3 当fixed tables统计信息不准确导致大量使用TEMP时，不想收集统计信息，可通过设置session级优化器绕过 问题： Finished Control File and SPFILE Autobackup at 22-MAR-20 ORA-00604: error occurred at recursive SQL level 1 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP RMAN-08132: warning: cannot update recovery area reclaimable file list sql statement: alter system archive log current 原因： Setting optimizer_mode = ALL_ROWS can cause non-optimized usage Of Temp 解决办法： RMAN> sql "alter session set optimizer_mode=RULE";