New Oracle11g feature: optimizing Rman backup UNDO tablespaces

A new feature of Oracle11gR1, Rman excludes data corresponding to submitted sessions when backing up UNDO tablespaces, which improves the efficiency of Rman backup.

Official document: http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#AREANO02323

We know that UNDO tablespaces are mainly used to store pre-mirrored data, which may be used during rollback and recovery. However, the UNDO tablespace of a production database may become very large, and the percentage of full backup UNDO data files is generally very small.

Test it:

-- Database version sys@ORCL > select * from v$version BANNER- -Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionPL/SQL Release 11.2.0.4.0-ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0- ProductionNLSRTL Version 11.2.0.4.0-Production-- creation environment zx@ORCL > insert into T1 select * from dba_segments 5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments;5887 rows created.zx@ORCL > insert into T1 select * from dba_segments 5887 rows created.zx@ORCL > commit;Commit complete.zx@ORCL > delete from T1 from dba_undo_extents group by status 288463 rows deleted.zx@ORCL > select status,sum (bytes) / 1024 Lacer1024 STATUS SUM (BYTES) / 1024amp 1024Murray-UNEXPIRED 9.125EXPIRED .4375ACTIVE 89.125zx@ORCL > commit Commit complete.-- backs up undo tablespace files RMAN > backup datafile 5 twice Starting backup at 2016-12-22 13:09:27using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00005 name=/u02/app/oracle/oradata/orcl/undotbs2_01.dbfchannel ORA_DISK_1: starting piece 1 at 2016-12-22 13:09:27channel ORA_DISK_1: finished piece 1 at 2016-12-22 13:09:28piece handle=/u02/app/oracle/product/11.2.4/ Db1/dbs/3aro4007_1_1 tag=TAG20161222T130927 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01Finished backup at 2016-12-22 13:09:28Starting Control File and SPFILE Autobackup at 2016-12-22 13:09:28piece handle=/u02/app/oracle/product/11.2.4/db1/dbs/c-1444351641-20161222-0f comment=NONEFinished Control File and SPFILE Autobackup at 2016-12-22 13 13:09:28Starting Control File and SPFILE Autobackup at 09 13:09:28piece handle=/u02/app/oracle/product/11.2.4/db1/dbs/c-1444351641-View backup file size RMAN > list backup of datafile 5 List of Backup Sets=-- first backup file size 99.27MBS Key Type LV Size Device Type Elapsed Time Completion Time-87 Full 99.27m DISK 00: 00:03 2016-12-22 12:11:54 BP Key: 87 Status: AVAILABLE Compressed: NO Tag: TAG20161222T121151 Piece Name: / u02/app/oracle/product/11.2.4/db1/dbs/36ro3sk7_1_1 List of Datafiles in backup set 87 File LV Type Ckp SCN Ckp Time Name- -5 Full 9042031 2016-12-22 12:11:51 / u02 ApplicationoraclePlacement oradata-second backup file size 2.16MBS Key Type LV Size Device Type Elapsed Time Completion Time-second backup file size -- 89 Full 2.16M DISK 00:00:01 2016-12-22 12:34:42 BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20161222T123441 Piece Name: / u02/app/oracle/product/11.2.4/db1/dbs/38ro3tv1_1_1 List of Datafiles in backup set 89 File LV Type Ckp SCN Ckp Time Name-5 Full 9042576 2016-12-22 12:34:41 / u02 Full / 11.2.4App lh Dec 11.2.4According to Dec Dec 11.2.4According to oracle oinstall Dec Dec 36ro3sk7march 1copyright 1 oracle oinstall RWML r-1 oracle oinstall 100m Dec 22 12:11 / u02/app/oracle/product/11.2.4/db1/dbs/36ro3sk7_1_1 [oracle@rhel6 release] $oracleDec 22 12:34 / u02/app/oracle/product / 11.2.4/db1/dbs/38ro3tv1_1_1

This new feature also has some limitations.

-Compatible parameter must be set to 11.0 or higher

-Backup must use a disk or OSB channel

-For 'backup copy of' or 'backup datafilecopy' the database must be open for undo optimization to be used.

-Not active for LEVEL 1 incremental backups, only for LEVEL 0 and FULL backups

MOS document: RMAN 11G: RMAN UNDO backup optimization (document ID 406468.1)

A Complete Understanding of RMAN Compression (document ID 563427.1)