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

jiawang

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";