[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大小一致。
--看来以前对这个概念理解存在一些错误。

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

转载于:http://blog.itpub.net/267265/viewspace-750049/