Oracle11新特性——備份恢複功能增強（三）

打算寫一系列的文章介紹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

連接配接到:

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

連接配接到目标資料庫: 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表空間占有了80多M的空間，而備份的大小也是80多M。

再來看看11g的情況：

[[email protected] ~]$ sqlplus yangtk/yangtk

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

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

[[email protected] ~]$ rman target /

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

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.

[[email protected] ~]$ 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

[[email protected] ~]$ rman target /

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

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.

[[email protected] ~]$ 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

[[email protected] ~]$ rman target /

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

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/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/4227/viewspace-69511/

Oracle11新特性——備份恢複功能增強（三）

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述