开发者社区> lfreeali> 正文

[20121127]rman备份数据文件大小与truncate.txt

简介: [20121127]rman备份数据文件大小与truncate.txt记得以前看rman的书,rman仅仅备份已经格式话的块,如果这样一个表被truncate后,即使空间回收了。
+关注继续查看
[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大小一致。
--看来以前对这个概念理解存在一些错误。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
史上最快! 10小时大数据入门实战(三)-分布式文件系统HDFS
HDFS 环境搭建 HDFS 伪分布式环境搭建 CentOS 环境安装步骤 MacOS安装环境 安装jd...
1478 0
[20121105]重建控制文件少一个数据文件的情况.txt
[20121105]重建控制文件少一个数据文件的情况.txt 我的测试数据库经常做各种测试,resetlogs很多次,产生很多incarnation.今天想通过重建控制文件来清除这些信息.
500 0
结构体存入文件并且取出
首先定义结构体   struct student_type{char name[10];int num;int age;} stud;      将结构体写入   代码 void save(){ FILE *fp;int i;if((fp=fopen("st...
615 0
[20130104]快速移动数据文件.txt
[20130104]快速移动数据文件.txt如果要快速移动数据文件,对业务的影响最小,可以使用rman的backup as copy功能,先拷贝文件到需要移动的目录,然后再追加增量变化,再利用增量备份来恢复copy文件,再切换数据文件。
654 0
Sybase IQ如何将大文件数据迅速加载到数据库
  试想一下,如果一个文件5G、10G甚至更大。如何将它迅速地加载到数据库指定的表呢?我们看看Sybase IQ是如何迅速地将表的数据加载到数据库的。 数据文件格式: 1440,2011-01-09 00:00:00,1,珠海,1,C网,8612345678222,221943,1,12175,...
720 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2122
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载