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時會佔用大量臨時表空間。

解決方案：

備份時，會話級別將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