12C rman备份占用大量TEMP ORA-01652: unable to extend temp segment

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

RMAN backup may fail at the end of its completion complaining about the TEMP Tablespace usage being full.  

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

 Even after increasing the Temp tablespace size, the TEMP usage is very High during the backup.

The backup may complete without failing on data files or archivelog files and the Errors may be encountered at the end of the backup.

CHANGES

none

CAUSE

 Setting optimizer_mode = ALL_ROWS can cause non-optimized usage Of Temp.

SOLUTION

If the OPTIMIZER_MODE for the database cannot be changed,  The RMAN Session can use it with the below change in the backup command / Script:     

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