Oracle 11g 新特性：优化Rman备份UNDO表空间

共计 4258 个字符，预计需要花费 11 分钟才能阅读完成。

Oracle 11gR1 的新特性，Rman 备份 UNDO 表空间时排除已经提交的会话对应的数据，提高了 Rman 备份的效率。

官方文档：http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#AREANO02323
我们知道，UNDO 表空间主要用于存储前镜像数据，这些数据在回滚以及恢复过程中可能被用到。但是一个生产数据库的 UNDO 表空间可能会变得非常巨大，而备份完整的 UNDO 数据文件在恢复时一般可能用到的比例很小。
测试一下：

– 数据库版本
sys@ORCL>select * from v$version;
 
BANNER
————————————————————————————————————————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
– 创建环境
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;
 
288463 rows deleted.
 
zx@ORCL>select status,sum(bytes)/1024/1024 from dba_undo_extents group by status;
 
STATUS              SUM(BYTES)/1024/1024
————————— ——————–
UNEXPIRED                  9.125
EXPIRED                  .4375
ACTIVE                    89.125
 
zx@ORCL>commit;
 
Commit complete.
 
– 两次备份 undo 表空间文件
RMAN> backup datafile 5;
 
Starting backup at 2016-12-22 13:09:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/app/oracle/oradata/orcl/undotbs2_01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-12-22 13:09:27
channel ORA_DISK_1: finished piece 1 at 2016-12-22 13:09:28
piece handle=/u02/app/oracle/product/11.2.4/db1/dbs/3aro4007_1_1 tag=TAG20161222T130927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-12-22 13:09:28
 
Starting Control File and SPFILE Autobackup at 2016-12-22 13:09:28
piece handle=/u02/app/oracle/product/11.2.4/db1/dbs/c-1444351641-20161222-0f comment=NONE
Finished Control File and SPFILE Autobackup at 2016-12-22 13:09:31
– 查看备份后的文件大小
RMAN> list backup of datafile 5;
 
 
List of Backup Sets
===================
– 第一次备份文件大小 99.27M
BS 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/app/oracle/oradata/orcl/undotbs2_01.dbf
– 第二次备份文件大小 2.16M
BS Key  Type LV Size      Device Type Elapsed Time Completion Time   
——- —- — ———- ———– ———— ——————-
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/app/oracle/oradata/orcl/undotbs2_01.dbf
– 查看操作系统文件大小
[oracle@rhel6 release]$ ls -lh /u02/app/oracle/product/11.2.4/db1/dbs/36ro3sk7_1_1
-rw-r—– 1 oracle oinstall 100M Dec 22 12:11 /u02/app/oracle/product/11.2.4/db1/dbs/36ro3sk7_1_1
[oracle@rhel6 release]$ ls -lh /u02/app/oracle/product/11.2.4/db1/dbs/38ro3tv1_1_1
-rw-r—– 1 oracle oinstall 2.2M Dec 22 12:34 /u02/app/oracle/product/11.2.4/db1/dbs/38ro3tv1_1_1

这个新特性也有一些限制
– Compatible parameter must be set to 11.0 or higher
– Backup must use a disk or OSB channel
– For ‘backup copy of <object>’ 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 文档：RMAN 11G : RMAN UNDO backup optimization (文档 ID 406468.1)
A Complete Understanding of RMAN Compression (文档 ID 563427.1)

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139048.htm