rman备份对各种数据块操作

    有不少人对于rman的backup功能,到底备份数据文件的什么级别,一直有着不明确的说法,我这里以10.2.0.4版本的rman backup 测试,进行一个简单的说明.这里提供的是一种思路.如果你在实际工作中,遇到一些rman到底会不会备份相关数据块的时候,可以通过类此的试验来证明你的版本的rman的功能.

     模拟环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile \'/u01/oracle/oradata/XFF/xifenfei01.dbf\'
   2 size 10m autoextend on next 10m maxsize 30g;

Tablespace created.

    备份空数据文件

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  10485760

SQL> select BYTES from dba_free_space where TABLESPACE_NAME=\'XIFENFEI\';

     BYTES
----------
  10420224

SQL> SELECT 10485760-10420224 FROM DUAL;

10485760-10420224
-----------------
            65536

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/no_table_xifenfei\';

[root@xifenfei tmp]# ls -l no_table_xifenfei
-rw-r----- 1 oracle oinstall 106496 Dec 15 01:03 no_table_xifenfei

    从这里可以看出来rman备份的时候,数据文件中未格式化的块并没有备份(数据文件10m,备份集只有106k左右,比文件实际使用的65536b稍微大点)

     备份create表数据文件

SQL> create table t_rman tablespace xifenfei
  2  as
  3  select * from chf.t_xifenfei1;

Table created.

SQL> select BYTES from dba_free_space where TABLESPACE_NAME=\'XIFENFEI\';

     BYTES
----------
   9371648

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  20971520

SQL> select 20971520-9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/crt_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/crt_table_xifenfei
-rw-r----- 1 oracle oinstall 11608064 Dec 15 01:29 /u01/oracle/oradata/tmp/crt_table_xifenfei

    这里可以得出结论,rman的备份集大小可以从一定程度上近似等于数据文件使用空间大小

     备份truncate表数据文件

SQL> truncate table t_rman;

Table truncated.

SQL> SELECT 20840448-9371648 from dual;

20840448-9371648
----------------
        11468800

SQL> select 20971520-20840448 from dual;

20971520-20840448
-----------------
           131072

RMAN>  backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/truncate_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/truncate_table_xifenfei
-rw-r----- 1 oracle oinstall 630784 Dec 15 01:30 /u01/oracle/oradata/tmp/truncate_table_xifenfei

    通过这里可以看出来,truncate 对象后,数据文件释放了对象空间,rman备份集也同样未备份这部分空间

     备份insert表数据文件

SQL> insert into t_rman  select * from chf.t_xifenfei1;

100062 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  20971520

SQL> select BYTES from dba_free_space where TABLESPACE_NAME=\'XIFENFEI\';

     BYTES
----------
   9371648

SQL>  select 20971520 - 9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/insert_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/insert_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:19 /u01/oracle/oradata/tmp/insert_table_xifenfei

    和直接创建表的出来结论相似

     备份delete表数据文件

SQL> delete from t_rman;

100062 rows deleted.

SQL> commit;

Commit complete.

SQL>  alter system checkpoint;

System altered.

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  20971520

SQL>  select BYTES from dba_free_space where TABLESPACE_NAME=\'XIFENFEI\';

     BYTES
----------
   9371648

SQL> select 20971520 - 9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/delete_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/delete_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:45 /u01/oracle/oradata/tmp/delete_table_xifenfei

    这里是直接delete数据,产生了明显的高水位现象(高水位之下部分无数据),但是rman备份,还是会备份高水位之下的所有数据

     备份drop表数据文件

SQL> drop table t_rman;

Table dropped.

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  20971520

SQL> select BYTES from v$datafile where name=\'/u01/oracle/oradata/XFF/xifenfei01.dbf\';

     BYTES
----------
  20971520
SQL> select sum(bytes) from  dba_free_space where TABLESPACE_NAME=\'XIFENFEI\';

SUM(BYTES)
----------
  20905984

SQL> select 20971520-20905984 from dual;

20971520-20905984
-----------------
            65536

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/drop_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/drop_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:51 /u01/oracle/oradata/tmp/drop_table_xifenfei

    在10g中,因为默认使用回收站功能,对象还存在回收站中,rman为了使得还原出来的数据库可以继续使用回收站中相应的表的闪回功能,所以也会备份回收站中数据

     备份purge表数据文件

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tBHa31bTe3jgQKjACgEImw==$0 T_RMAN

SQL> purge table "BIN$tBHa31bTe3jgQKjACgEImw==$0";

Table purged.

RMAN> backup tablespace xifenfei format \'/u01/oracle/oradata/tmp/PURGE_table_xifenfei\';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/PURGE_table_xifenfei
-rw-r----- 1 oracle oinstall 106496 Dec 15 03:08 /u01/oracle/oradata/tmp/PURGE_table_xifenfei

    可以看到purge表之后,其实效果类此truncate(当然truncate做的工作更多),rman备份集大小和无数据对象时相同,结合drop和purge也可以知道在删除大对象或者比较多对象而且又确定不再需要,且有rman备份,这个时候建议直接加上purge.

     各个备份集汇总

[root@xifenfei tmp]# ll *table_xifenfei
-rw-r----- 1 oracle oinstall 11608064 Dec 15 01:29 crt_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:45 delete_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:51 drop_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:19 insert_table_xifenfei
-rw-r----- 1 oracle oinstall   106496 Dec 15 01:03 no_table_xifenfei
-rw-r----- 1 oracle oinstall   106496 Dec 15 03:08 PURGE_table_xifenfei
-rw-r----- 1 oracle oinstall   630784 Dec 15 01:30 truncate_table_xifenfei

    rman的备份功能本身就是在不断的增强,不同的版本会有不同的结果,最明显的就是在9i版本会备份truncate的数据.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile
  2  \'/u01/oracle/oradata/xifenfei/xifenfei01.dbf\' size 10m autoextend on next 10m maxsize 10240m;

Tablespace created.

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> insert into t_xifenfei
  2  select * from dba_objects;

30803 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

RMAN> backup tablespace xifenfei format \'/tmp/no_truncate_xifenfei\';

SQL> truncate table t_xifenfei;

Table truncated.

[oracle@xifenfei ~]$ ls -l /tmp/*truncate_xifenfei
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:52 /tmp/no_truncate_xifenfei
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:53 /tmp/truncate_xifenfei