Oracle 12c rman备份占用大量临时表空间
Tags： 12cORA-01652Oraclerman临时表空间故障处理
👉 本文共约539个字，系统预计阅读时间或需3分钟。
环境说明
1 2 3 | DB:Oracle 12.2.0.1.0 OS:SUSE Linux Enterprise Server 12 SP3 |
检查备份情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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不足
1 2 3 4 5 6 | 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 |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
检查临时表空间使用情况
1 2 3 4 5 6 | 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。
第二天检查仍然有相同的报错
1 | 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。
1 | RMAN> sql "alter session set optimizer_mode=RULE"; |
第二天检查备份，已经恢复正常。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 |
