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

2017-01-04 10:31:42来源:作者:Linux公社人点击

第七城市

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

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台