Oracle 12C rman备份占用大量临时表空间

环境说明

DB:Oracle 12.2.0.1.0
OS:SUSE Linux Enterprise Server 12 SP3

检查备份情况：

set line 300
set pagesize 150
col in_size for a10
col out_size for a10
col input_type for a10
col e for a20
col s for a20
select
session_key,
input_type,
compression_ratio,
INPUT_BYTES_DISPLAY in_size,
output_bytes_display out_size,
to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,
to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,
status
from v$rman_backup_job_details where INPUT_TYPE='DB FULL'
order by S DESC;


SESSION_KEY INPUT_TYPE COMPRESSION_RATIO IN_SIZE OUT_SIZE S E STATUS
----------- ---------- ----------------- ---------- ---------- -------------------- -------------------- -----------------------
13816 DB FULL 3.71191612 131.61G 35.46G 20220907 03:20:55 20220907 04:20:44 COMPLETED WITH WARNINGS
13803 DB FULL 3.68448673 130.64G 35.46G 20220906 03:14:07 20220906 03:54:58 FAILED
13790 DB FULL 3.67081775 125.23G 34.11G 20220905 03:14:08 20220905 03:52:58 FAILED

检查RMAN备份日志，在备份数据文件、归档文件结束时，提示临时表空间CJCTEMP不足

using channel ORA_DISK_4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 09/02/2022 03:14:12
ORA-01652: unable to extend temp segment by 128 in tablespace CJCTEMP

检查临时表空间使用情况

SQL> select tablespace_name,file_name,bytes/1024/1024/1024,status,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_temp_files;

TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024 STATUS AUT MAXBYTES/1024/1024/1024
------------------------------ -------------------------------------------------- -------------------- ------- --- -----------------------
CJCTEMP /oracle/CJC/cjcdata1/temp_1/temp.data1 5 ONLINE YES 5

临时表空间很小，对CJCTEMP临时表空间进行扩容，由5G扩容到30G。

第二天检查仍然有相同的报错

ORA-01652: unable to extend temp segment by 128 in tablespace CJCTEMP

为什么在Rman备份时会消耗这么多临时表空间呢，11g数据库备份没有类似问题，难道是12C BUG？

登录MOS，检查发现和2658437相似，对应版本12.1.0.2 and later.

问题原因：

当使用默认optimizer_mode=ALL_ROWS时rman备份会占用大量临时表空间。

解决方案：

备份时，会话级别将optimizer_mode由ALL_ROWS改成RULE。

RMAN> sql "alter session set optimizer_mode=RULE";

第二天检查备份，已经恢复正常。

set line 300
set pagesize 150
col in_size for a10
col out_size for a10
col input_type for a10
col e for a20
col s for a20
select
session_key,
input_type,
compression_ratio,
INPUT_BYTES_DISPLAY in_size,
output_bytes_display out_size,
to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,
to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,
status
from v$rman_backup_job_details where INPUT_TYPE='DB FULL'
order by S DESC;

SESSION_KEY INPUT_TYPE COMPRESSION_RATIO IN_SIZE OUT_SIZE S E STATUS
----------- ---------- ----------------- ---------- ---------- -------------------- -------------------- -----------------------
13835 DB FULL 3.71561534 129.00G 34.72G 20220908 03:12:10 20220908 03:44:42 COMPLETED
13816 DB FULL 3.71191612 131.61G 35.46G 20220907 03:20:55 20220907 04:20:44 COMPLETED WITH WARNINGS
13803 DB FULL 3.68448673 130.64G 35.46G 20220906 03:14:07 20220906 03:54:58 FAILED
13790 DB FULL 3.67081775 125.23G 34.11G 20220905 03:14:08 20220905 03:52:58 FAILED

参考

https://mp.weixin.qq.com/s/R3x0fTome61LNFShYdrcSw