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
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表空間佔有了80多M的空間,而備份的大小也是80多M。
再來看看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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle11新特性——備份恢復功能增強Oracle
- Oracle11新特性——備份恢復功能增強(六)Oracle
- Oracle11新特性——備份恢復功能增強(五)Oracle
- Oracle11新特性——備份恢復功能增強(四)Oracle
- Oracle11新特性——備份恢復功能增強(二)Oracle
- Oracle11新特性——備份恢復功能增強(一)Oracle
- Oracle11新特性——備份恢復功能增強(十)Oracle
- Oracle11新特性——備份恢復功能增強(九)Oracle
- Oracle11新特性——備份恢復功能增強(八)Oracle
- Oracle11新特性——備份恢復功能增強(七)Oracle
- Oracle11新特性——備份恢復功能增強(十一)Oracle
- Oracle11新特性——分割槽功能增強(三)Oracle
- Oracle11新特性——線上操作功能增強(三)Oracle
- Oracle11新特性——分割槽功能增強Oracle
- Oracle11新特性——分割槽功能增強(五)Oracle
- Oracle11新特性——分割槽功能增強(四)Oracle
- Oracle11新特性——分割槽功能增強(二)Oracle
- Oracle11新特性——分割槽功能增強(一)Oracle
- Oracle11新特性——線上操作功能增強(七)Oracle
- Oracle11新特性——線上操作功能增強(六)Oracle
- Oracle11新特性——線上操作功能增強(五)Oracle
- Oracle11新特性——線上操作功能增強(四)Oracle
- Oracle11新特性——線上操作功能增強(二)Oracle
- Oracle11新特性——線上操作功能增強(一)Oracle
- 10G新特性筆記之備份恢復新特性筆記
- 【RMAN】Oracle11g備份恢復新特性Oracle
- Oracle11新特性——PLSQL新特性(三)OracleSQL
- Oracle12c功能增強 新特性之管理功能的增強Oracle
- Oracle12c功能增強新特性之維護&升級&恢復&資料泵等Oracle
- ORACLE備份&恢復案例三(轉)Oracle
- 【備份恢復】從備份恢復資料庫資料庫
- 【管理篇備份恢復】備份恢復基礎
- RMAN備份恢復之控制檔案的恢復(三)
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- MySQL入門--備份與恢復(三)MySql
- Oracle資料庫備份與恢復之三:OS備份/使用者管理的備份與恢復Oracle資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 備份與恢復--利用備份的控制檔案恢復