[20121127]rman备份数据文件大小与truncate.txt
记得以前看rman的书,rman仅仅备份已经格式话的块，如果这样一个表被truncate后，即使空间回收了。但是在rman备份时，备份大小不会变画太多，这些前面使用的块一定会备份。
自己做一个测试看看。
1.建立测试环境:
SQL> select * from v$version where rownum
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大小一致。
--看来以前对这个概念理解存在一些错误。