[20121127]rman備份資料檔案大小與truncate.txt
[20121127]rman備份資料檔案大小與truncate.txt
記得以前看rman的書,rman僅僅備份已經格式話的塊，如果這樣一個表被truncate後，即使空間回收了。但是在rman備份時，備份大小不會變畫太多，這些前面使用的塊一定會備份。
自己做一個測試看看。
1.建立測試環境:
SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--建立表空間AAA
CREATE TABLESPACE AAA DATAFILE
'/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
$ ls -l /u01/app/oracle11g/oradata/test/aaa01.dbf
-rw-r----- 1 oracle11g oinstall 67117056 Nov 27 10:28 /u01/app/oracle11g/oradata/test/aaa01.dbf
--使用rman備份。
RMAN> backup datafile 11 format '/data/testtest/aaa1_%U';
Starting backup at 2012-11-27 10:32:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:32:27
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:32:29
piece handle=/data/testtest/aaa1_0unrbn1r_1_1 tag=TAG20121127T103227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2012-11-27 10:32:29
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
$ du -sk /data/testtest/aaa*
1068 /data/testtest/aaa1_0unrbn1r_1_1
--備份檔案大小僅僅1068K。
--查詢字串BOOTSTRAP$不存在。
$ strings /u01/app/oracle11g/oradata/test/aaa01.dbf | grep 'BOOTSTRAP\$'
$ strings /data/testtest/aaa1_0unrbn1r_1_1 | grep 'BOOTSTRAP\$'
2.在表空間AAA上建立表看看情況：
SQL> create table t tablespace aaa as select * from dba_objects ;
Table created.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
9437184 1152
--大小僅僅9M
SQL> insert into t select * from t;
75198 rows created.
SQL> insert into t select * from t;
150396 rows created.
SQL> commit ;
Commit complete.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
--大小僅僅35M.
RMAN> backup datafile 11 format '/data/testtest/aaa2_%U';
Starting backup at 2012-11-27 10:40:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:40:43
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:40:50
piece handle=/data/testtest/aaa2_0vnrbnhb_1_1 tag=TAG20121127T104042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2012-11-27 10:40:50
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
$ strings /u01/app/oracle11g/oradata/test/aaa01.dbf | grep 'BOOTSTRAP\$'
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
$ strings /data/testtest/aaa2_0vnrbnhb_1_1 | grep 'BOOTSTRAP\$'
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
--可以發現備份存在字串BOOTSTRAP$,正好4次。
3.truncate table t後看看情況:
SQL> truncate table t;
Table truncated.
RMAN> backup datafile 11 format '/data/testtest/aaa3_%U';
Starting backup at 2012-11-27 10:43:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:43:07
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:43:08
piece handle=/data/testtest/aaa3_10nrbnlr_1_1 tag=TAG20121127T104306 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-11-27 10:43:08
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
-rw-r----- 1 oracle11g oinstall 1613824 Nov 27 10:43 /data/testtest/aaa3_10nrbnlr_1_1
--對比備份檔案可以發現truncate後備份檔案確實變小了。但是比第1次檔案大一點（多512K）。
$ strings /data/testtest/aaa3_10nrbnlr_1_1 | grep 'BOOTSTRAP\$'
BOOTSTRAP$
--從strings過濾來看，truncate表後的備份還是備份1點點對應的塊，但是許多塊並沒有備份。
--也許跟這個有關。
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
65536 8
--這樣看來oracle並不是備份已經格式話的塊。
4.再重新來一次看看。
SQL> insert into t select * from dba_objects;
75198 rows created.
SQL> insert into t select * from t;
75198 rows created.
SQL> insert into t select * from t;
150396 rows created.
SQL> commit ;
Commit complete.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
--同樣是truncate操作，但是保留使用空間不回收看看。
SQL> truncate table t reuse storage ;
Table truncated.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
RMAN> backup datafile 11 format '/data/testtest/aaa4_%U';
Starting backup at 2012-11-27 11:00:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 11:00:55
channel ORA_DISK_1: finished piece 1 at 2012-11-27 11:00:58
piece handle=/data/testtest/aaa4_11nrbon7_1_1 tag=TAG20121127T110054 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2012-11-27 11:00:58
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
-rw-r----- 1 oracle11g oinstall 1613824 Nov 27 10:43 /data/testtest/aaa3_10nrbnlr_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 11:00 /data/testtest/aaa4_11nrbon7_1_1
--可以發現aaa2_0vnrbnhb_1_1與aaa4_11nrbon7_1_1大小一致。
--看來以前對這個概念理解存在一些錯誤。
記得以前看rman的書,rman僅僅備份已經格式話的塊，如果這樣一個表被truncate後，即使空間回收了。但是在rman備份時，備份大小不會變畫太多，這些前面使用的塊一定會備份。
自己做一個測試看看。
1.建立測試環境:
SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--建立表空間AAA
CREATE TABLESPACE AAA DATAFILE
'/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
$ ls -l /u01/app/oracle11g/oradata/test/aaa01.dbf
-rw-r----- 1 oracle11g oinstall 67117056 Nov 27 10:28 /u01/app/oracle11g/oradata/test/aaa01.dbf
--使用rman備份。
RMAN> backup datafile 11 format '/data/testtest/aaa1_%U';
Starting backup at 2012-11-27 10:32:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:32:27
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:32:29
piece handle=/data/testtest/aaa1_0unrbn1r_1_1 tag=TAG20121127T103227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2012-11-27 10:32:29
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
$ du -sk /data/testtest/aaa*
1068 /data/testtest/aaa1_0unrbn1r_1_1
--備份檔案大小僅僅1068K。
--查詢字串BOOTSTRAP$不存在。
$ strings /u01/app/oracle11g/oradata/test/aaa01.dbf | grep 'BOOTSTRAP\$'
$ strings /data/testtest/aaa1_0unrbn1r_1_1 | grep 'BOOTSTRAP\$'
2.在表空間AAA上建立表看看情況：
SQL> create table t tablespace aaa as select * from dba_objects ;
Table created.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
9437184 1152
--大小僅僅9M
SQL> insert into t select * from t;
75198 rows created.
SQL> insert into t select * from t;
150396 rows created.
SQL> commit ;
Commit complete.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
--大小僅僅35M.
RMAN> backup datafile 11 format '/data/testtest/aaa2_%U';
Starting backup at 2012-11-27 10:40:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:40:43
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:40:50
piece handle=/data/testtest/aaa2_0vnrbnhb_1_1 tag=TAG20121127T104042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2012-11-27 10:40:50
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
$ strings /u01/app/oracle11g/oradata/test/aaa01.dbf | grep 'BOOTSTRAP\$'
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
$ strings /data/testtest/aaa2_0vnrbnhb_1_1 | grep 'BOOTSTRAP\$'
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
BOOTSTRAP$
--可以發現備份存在字串BOOTSTRAP$,正好4次。
3.truncate table t後看看情況:
SQL> truncate table t;
Table truncated.
RMAN> backup datafile 11 format '/data/testtest/aaa3_%U';
Starting backup at 2012-11-27 10:43:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 10:43:07
channel ORA_DISK_1: finished piece 1 at 2012-11-27 10:43:08
piece handle=/data/testtest/aaa3_10nrbnlr_1_1 tag=TAG20121127T104306 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-11-27 10:43:08
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
-rw-r----- 1 oracle11g oinstall 1613824 Nov 27 10:43 /data/testtest/aaa3_10nrbnlr_1_1
--對比備份檔案可以發現truncate後備份檔案確實變小了。但是比第1次檔案大一點（多512K）。
$ strings /data/testtest/aaa3_10nrbnlr_1_1 | grep 'BOOTSTRAP\$'
BOOTSTRAP$
--從strings過濾來看，truncate表後的備份還是備份1點點對應的塊，但是許多塊並沒有備份。
--也許跟這個有關。
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
65536 8
--這樣看來oracle並不是備份已經格式話的塊。
4.再重新來一次看看。
SQL> insert into t select * from dba_objects;
75198 rows created.
SQL> insert into t select * from t;
75198 rows created.
SQL> insert into t select * from t;
150396 rows created.
SQL> commit ;
Commit complete.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
--同樣是truncate操作，但是保留使用空間不回收看看。
SQL> truncate table t reuse storage ;
Table truncated.
SQL> select bytes,blocks from dba_segments where wner=user and segment_name='T';
BYTES BLOCKS
---------- ----------
35651584 4352
RMAN> backup datafile 11 format '/data/testtest/aaa4_%U';
Starting backup at 2012-11-27 11:00:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 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=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-27 11:00:55
channel ORA_DISK_1: finished piece 1 at 2012-11-27 11:00:58
piece handle=/data/testtest/aaa4_11nrbon7_1_1 tag=TAG20121127T110054 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2012-11-27 11:00:58
$ ll /data/testtest/aaa*
-rw-r----- 1 oracle11g oinstall 1089536 Nov 27 10:32 /data/testtest/aaa1_0unrbn1r_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 10:40 /data/testtest/aaa2_0vnrbnhb_1_1
-rw-r----- 1 oracle11g oinstall 1613824 Nov 27 10:43 /data/testtest/aaa3_10nrbnlr_1_1
-rw-r----- 1 oracle11g oinstall 36741120 Nov 27 11:00 /data/testtest/aaa4_11nrbon7_1_1
--可以發現aaa2_0vnrbnhb_1_1與aaa4_11nrbon7_1_1大小一致。
--看來以前對這個概念理解存在一些錯誤。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-750049/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RMAN說，我能備份(4)--RMAN備份資料檔案和控制檔案
- RMAN備份檔案遠大於資料庫大小的原因分析資料庫
- RMAN備份資料檔案+控制檔案+歸檔日誌
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案
- 表空間資料檔案建立大小與實際使用大小以及rman備份集的關係系列一
- rman備份但丟失一個資料檔案,但有歸檔備份
- rman備份恢復-rman恢復資料檔案測試
- RMAN說，我能備份(5)--RMAN備份歸檔檔案
- [20130412]rman備份檔案大小.txt
- RMAN備份檔案格式
- 非歸檔資料庫RMAN備份資料庫
- 驗證控制檔案、歸檔檔案、不同BLOCK大小的資料檔案對應的RMAN備份集不在同一PIECEBloC
- rman恢復資料庫--用備份的控制檔案資料庫
- rman備份檔案的格式
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份資料庫檔案GUIIDE資料庫
- 歸檔日誌大小 與使用rman 備份後的歸檔日誌產生的備份集大小的關係
- [20161031]rman備份與資料檔案OS塊.txt
- rman全庫備份備份歸檔日誌檔案
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份控制檔案GUIIDE資料庫
- RMAN備份恢復典型案例——資料檔案存在壞快
- 非系統資料檔案損壞，rman備份恢復
- RMAN 驗證 資料檔案 和 備份 的有效性
- Oracle RMAN 不完全恢復（只有資料檔案備份，丟失歸檔日誌備份）Oracle
- [20161031]rman備份與資料檔案變化3.txt
- [20161031]rman備份與資料檔案變化2.txt
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份表空間和資料檔案GUIIDE資料庫
- 用rman建立dataguard備用資料庫繼續(無法找到備份檔案)資料庫
- ORACLE 只讀資料檔案備份與恢復Oracle
- 使用RMAN備份資料庫資料庫
- rman資料庫全庫備份與恢復資料庫
- Oracle資料庫備份與恢復之RMANOracle資料庫
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 【備份】RMAN中對控制檔案的幾種備份方法
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- [20161101]rman備份與資料檔案變化4.txt
- [20161102]rman備份與資料檔案變化5.txt
- [20171123]rman備份與資料檔案變化6.txt