sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

学习笔记:Oracle RMAN备份入门 汇总RMAN对数据库备份方式案例

时间:2016-06-25 23:29   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 rman备份对各种数据块操作,汇总DBA常用RMAN备份方式:备份数据文件、控制文件、备份create表数据文件、备份truncate表数据文件、备份insert表数据文件等

有不少人对于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 oracleplus datafile '/u01/oracle/oradata/XFF/oracleplus01.dbf' 
   2 size 10m autoextend on next 10m maxsize 30g;

Tablespace created.

1.备份空数据文件

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

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

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='oracleplus';

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

SQL> SELECT 10485760-10420224 FROM DUAL;

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

RMAN> backup tablespace oracleplus format '/u01/oracle/oradata/tmp/no_table_oracleplus';

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

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

2.备份create表数据文件

SQL> create table t_rman tablespace oracleplus
  2  as
  3  select * from chf.t_oracleplus1;

Table created.

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='oracleplus';

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

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

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

SQL> select 20971520-9371648 from dual;

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

RMAN> backup tablespace oracleplus format '/u01/oracle/oradata/tmp/crt_table_oracleplus';

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

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

3.备份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 oracleplus format '/u01/oracle/oradata/tmp/truncate_table_oracleplus';

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

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

4.备份insert表数据文件

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

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/oracleplus01.dbf';

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

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='oracleplus';

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

SQL>  select 20971520 - 9371648 from dual;

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

RMAN> backup tablespace oracleplus format '/u01/oracle/oradata/tmp/insert_table_oracleplus';

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

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

5.备份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/oracleplus01.dbf';

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

SQL>  select BYTES from dba_free_space where TABLESPACE_NAME='oracleplus';

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

SQL> select 20971520 - 9371648 from dual;

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

RMAN> backup tablespace oracleplus format '/u01/oracle/oradata/tmp/delete_table_oracleplus';

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

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

6.备份drop表数据文件

SQL> drop table t_rman;

Table dropped.

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

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

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

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

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

SQL> select 20971520-20905984 from dual;

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

RMAN> backup tablespace oracleplus format '/u01/oracle/oradata/tmp/drop_table_oracleplus';

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

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

7.备份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 oracleplus format '/u01/oracle/oradata/tmp/PURGE_table_oracleplus';

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

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

9.各个备份集汇总

[root@oracleplus tmp]# ll *table_oracleplus
-rw-r----- 1 oracle oinstall 11608064 Dec 15 01:29 crt_table_oracleplus
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:45 delete_table_oracleplus
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:51 drop_table_oracleplus
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:19 insert_table_oracleplus
-rw-r----- 1 oracle oinstall   106496 Dec 15 01:03 no_table_oracleplus
-rw-r----- 1 oracle oinstall   106496 Dec 15 03:08 PURGE_table_oracleplus
-rw-r----- 1 oracle oinstall   630784 Dec 15 01:30 truncate_table_oracleplus

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 oracleplus datafile
  2  '/u01/oracle/oradata/oracleplus/oracleplus01.dbf' size 10m autoextend on next 10m maxsize 10240m;

Tablespace created.

SQL> create table t_oracleplus tablespace oracleplus
  2  as
  3  select * from dba_objects;

Table created.

SQL> insert into t_oracleplus
  2  select * from dba_objects;

30803 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

RMAN> backup tablespace oracleplus format '/tmp/no_truncate_oracleplus';

SQL> truncate table t_oracleplus;

Table truncated.

[oracle@oracleplus ~]$ ls -l /tmp/*truncate_oracleplus
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:52 /tmp/no_truncate_oracleplus
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:53 /tmp/truncate_oracleplus

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle RMAN备份入门 汇总RMAN对数据库备份方式案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/548.html

Oracle研究中心

关键词:

Oracle RMAN备份入门

汇总RMAN对数据库备份方式案例