ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11新特性——备份恢复功能增强(三)

Oracle11新特性——备份恢复功能增强(三)

原创 Linux操作系统 作者:yangtingkun 时间:2007-11-22 00:00:00 0 删除 编辑

打算写一系列的文章介绍11g的新特性和变化。

Oracle11g在备份和恢复方面新增了很多的功能,无论是性能、功能性、安全性和可操作性方面都有了不同程度的提高。

这一篇介绍RMAN对于UNDO表空间的优化。

Oracle11新特性——备份恢复功能增强(一):http://yangtingkun.itpub.net/post/468/412991

Oracle11新特性——备份恢复功能增强(二):http://yangtingkun.itpub.net/post/468/414647


Oracle11g新增了对于UNDO表空间的优化功能,对于UNDO表空间中的内容,对于恢复没有帮助的数据,Oracle不会备份。也就是说,Oracle不备份已经提交的数据。

首先对比一下10g和11g的情况:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期六 9 22 23:36:04 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select
2 (select sum(bytes)/1024/1024 from dba_data_files where tablespace_name = 'UNDOTBS1') total,
3 (select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'UNDOTBS1') free
4 from dual;

TOTAL FREE
---------- ----------
2048 1960.3125

SQL> exit Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
断开

$ rman target /

恢复管理器: Release 10.2.0.3.0 - Production on 星期六 9 22 23:39:11 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: TESTRAC (DBID=4291216984)

RMAN> run
2> {
3> allocate channel c1 device type disk format '/data1/backup/%U';
4> backup tablespace undotbs1;
5> }

使用目标数据库控制文件替代恢复目录分配的通道: c1通道 c1: sid=301 实例=testrac1 devtype=DISK

启动 backup 22-9 -07通道 c1: 启动全部数据文件备份集通道 c1: 正在指定备份集中的数据文件输入数据文件 fno=00002 name=+DISK/testrac/datafile/undotbs1.263.618591197通道 c1: 正在启动段 1 22-9 -07通道 c1: 已完成段 1 22-9 -07段句柄=/data1/backup/07isk6i2_1_1 标记=TAG20070922T233945 注释=NONE通道 c1: 备份集已完成, 经过时间:00:00:03完成 backup 22-9 -07

启动 Control File and SPFILE Autobackup 22-9 -07 handle=/data/oracle/product/10.2/database/dbs/c-4291216984-20070922-00 comment=NONE完成 Control File and SPFILE Autobackup 22-9 -07释放的通道: c1

RMAN> list backup of tablespace undotbs1;

备份集列表
===================

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
829 Full 88.10M DISK 00:00:03 22-9
-07
BP
关键字: 1039 状态: AVAILABLE 已压缩: NO 标记
: TAG20070922T233945段名:/data1/backup/07isk6i2_1_1
备份集 829 中的数据文件列表

文件 LV 类型 Ckp SCN Ckp 时间 名称
---- -- ---- ---------- ---------- ----
2 Full 5705040412 22-9
-07 +DISK/testrac/datafile/undotbs1.263.618591197

从上面的测试看,UNDO表空间占有了80M的空间,而备份的大小也是80M

再来看看11g的情况:

[oracle@yangtk ~]$ sqlplus yangtk/yangtk

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 27 23:31:58 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select
2 (select sum(bytes)/1024/1024 from dba_data_files where tablespace_name = 'UNDOTBS1') total,
3 (select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'UNDOTBS1') free
4 from dual;

TOTAL FREE
---------- ----------
400 279.75

SQL> host
[oracle@yangtk ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Oct 27 23:33:26 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA11G (DBID=4026820313)

RMAN> backup tablespace undotbs1;

Starting backup at 27-OCT-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf
channel ORA_DISK_1: starting piece 1 at 27-OCT-07
channel ORA_DISK_1: finished piece 1 at 27-OCT-07
piece handle=/data1/backup/0hivj3mf_1_1 tag=TAG20071027T233335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 27-OCT-07

RMAN> list backup of tablespace undotbs1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 432.00K DISK 00:00:18 27-OCT-07
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20071027T233335
Piece Name: /data1/backup/0hivj3mf_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4148196 27-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

11g中,UNDO表空间占有了120M左右,但是备份的结果只有432K

下面继续测试:

RMAN> exit


Recovery Manager complete.
[oracle@yangtk ~]$ exit
exit

SQL> CREATE TABLE T_BACKUP AS SELECT * FROM DBA_OBJECTS WHERE 1 = 2;

Table created.

SQL> INSERT INTO T_BACKUP SELECT * FROM DBA_OBJECTS;

68467 rows created.

SQL> INSERT INTO T_BACKUP SELECT * FROM T_BACKUP;

68467 rows created.

SQL> INSERT INTO T_BACKUP SELECT * FROM T_BACKUP;

136934 rows created.

SQL> INSERT INTO T_BACKUP SELECT * FROM T_BACKUP;

273868 rows created.

SQL> UPDATE T_BACKUP SET OWNER = OWNER;

547736 rows updated.

SQL> SELECT
2 (SELECT SUM(BYTES)/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS1') TOTAL,
3 (SELECT SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'UNDOTBS1') FREE
4 FROM DUAL;

TOTAL FREE
---------- ----------
400 251.75

SQL> HOST
[oracle@yangtk ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Oct 28 00:06:19 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA11G (DBID=4026820313)

RMAN> backup tablespace undotbs1;

Starting backup at 28-OCT-07
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=00003 name=/data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf
channel ORA_DISK_1: starting piece 1 at 28-OCT-07
channel ORA_DISK_1: finished piece 1 at 28-OCT-07
piece handle=/data1/backup/0iivj5l1_1_1 tag=TAG20071028T000656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37
Finished backup at 28-OCT-07

RMAN> list backup of tablespace undotbs1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 432.00K DISK 00:00:18 27-OCT-07
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20071027T233335
Piece Name: /data1/backup/0hivj3mf_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4148196 27-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 28.05M DISK 00:00:40 28-OCT-07
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20071028T000656
Piece Name: /data1/backup/0iivj5l1_1_1
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4165861 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

Oracle会备份UNDO表空间中没有COMMIT的数据。如果将数据COMMIT后,会发现结果集又恢复了原来的大小:

RMAN> exit


Recovery Manager complete.
[oracle@yangtk ~]$ exit
exit

SQL> COMMIT;

Commit complete.

SQL> SELECT
2 (SELECT SUM(BYTES)/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS1') TOTAL,
3 (SELECT SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'UNDOTBS1') FREE
4 FROM DUAL;

TOTAL FREE
---------- ----------
400 251.75

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@yangtk ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Oct 28 00:12:25 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA11G (DBID=4026820313)

RMAN> backup tablespace undotbs1;

Starting backup at 28-OCT-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=170 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf
channel ORA_DISK_1: starting piece 1 at 28-OCT-07
channel ORA_DISK_1: finished piece 1 at 28-OCT-07
piece handle=/data1/backup/0jivj5vk_1_1 tag=TAG20071028T001236 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-OCT-07

RMAN> list backup of tablespace undotbs1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 432.00K DISK 00:00:18 27-OCT-07
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20071027T233335
Piece Name: /data1/backup/0hivj3mf_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4148196 27-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 28.05M DISK 00:00:40 28-OCT-07
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20071028T000656
Piece Name: /data1/backup/0iivj5l1_1_1
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4165861 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 28.08M DISK 00:00:19 28-OCT-07
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20071028T001236
Piece Name: /data1/backup/0jivj5vk_1_1
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4166069 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

RMAN> backup tablespace undotbs1;

Starting backup at 28-OCT-07
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=00003 name=/data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf
channel ORA_DISK_1: starting piece 1 at 28-OCT-07
channel ORA_DISK_1: finished piece 1 at 28-OCT-07
piece handle=/data1/backup/0kivj7b8_1_1 tag=TAG20071028T003551 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-OCT-07

RMAN> list backup of tablespace undotbs1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 432.00K DISK 00:00:18 27-OCT-07
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20071027T233335
Piece Name: /data1/backup/0hivj3mf_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4148196 27-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 28.05M DISK 00:00:40 28-OCT-07
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20071028T000656
Piece Name: /data1/backup/0iivj5l1_1_1
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4165861 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 28.08M DISK 00:00:19 28-OCT-07
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20071028T001236
Piece Name: /data1/backup/0jivj5vk_1_1
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4166069 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 424.00K DISK 00:00:16 28-OCT-07
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20071028T003551
Piece Name: /data1/backup/0kivj7b8_1_1
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 4166605 28-OCT-07 /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_undotbs1_3d379k48_.dbf

最后两个备份都发生在COMMIT之后,唯一的区别在于一个是COMMIT刚刚结束,而第二个备份是过了一段时间才执行。

对于刚刚提交的内容,Oracle在备份的时候还无法确定是否被恢复所需要,只要过一段时间,UNDO备份的优化就生效了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69511/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10353590