![e160a621c84a33f17a87b73843659118.png](https://i-blog.csdnimg.cn/blog_migrate/ead0e43b6b20fee7a5ea4ad102543c71.png)
Oracle RMAN(Recovery Manager) – 备份的优化
看到标题有些人可能疑惑,RMAN不就是备份恢复管理器么?我只要写好几个命令去执行就行了,还有什么可优化的?
但,RMAN备份的优化其实也很重要的,那优化目标是什么:让备份更节省空间、让备份的速度更快、让还原或恢复的速度更快。
备份集备份
备份集备份有压缩功能,这是镜像复制备份没有具备的功能。为了节省磁盘或磁带的空间,备份集可以采取3种压缩方式:空块压缩、未使用块压缩和二进制压缩。
空块压缩
空块压缩发生在通道工作的复制阶段,RMAN将块读入输出缓冲时会检查该块是否为从来没有修改并保存过数据的块,如果是,则RMAN会跳出此块,结果是空块没有进入备份片。此操作与全表扫描后再过滤类似。
未使用块压缩
未使用块压缩发生在通道的读阶段,RMAN读取数据文件头部的区分配位图(Bitmap),以此获知哪些区(Extent,是由多个连续的数据块组成的一种空间分配单位)已经分配给段使用了,然后仅读取这些区中的块。此操作与利用索引访问表很类似。
如果下面条件符合就会发生未使用块压缩:数据库没有确保的还原点、数据文件的区是本地管理的、备份集的形式是全备或等级0的增量备份、通道的类型是DISK或OSB实现的SBT。
二进制压缩
RMAN支持3个等级的二进制压缩:LOW(低)、BASIC或MEDIUM(中)、HIGH(高)。等级越高,备份时CPU开销越大。
使用configure命令可以设置二进制压缩的等级。
将压缩等级改为CPU消耗最小的LOW:
RMAN> configure compression algorithm 'LOW';
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
并行度
RMAN的并行度和通道数有紧密的联系,当然也不是越多越好,每个站点总有一个最佳的通道数的值:SBT设备可以使驱动器的数量;DISK设备主要取决于磁盘书和条带的情况,经过测试找到一个瓶颈最小的最大通道数,可以显著提高备份与还原(大量数据文件)的速度。必要的时候,针对一个数据文件,一般是一个比较大的数据文件,RMAN可以通过"section size"子句利用多个通道对其进行并行备份。
SQL> set pagesize 999
SQL> set line 150
SQL> col name for a60
SQL> select bytes/1024/1024, name from v$datafile where file# = 2;
BYTES/1024/1024 NAME
--------------- ------------------------------------------------------------
600 +MSDATA/maa/datafile/sysaux.261.792009871
设置为200M是因为数据文件大小600M,而且我设置了3个通道。
RMAN> run{
2> allocate channel a1 device type disk;
3> allocate channel a2 device type disk;
4> allocate channel a3 device type disk;
5> backup section size 200M datafile 2;
6> }
allocated channel: a1
channel a1: SID=45 device type=DISK
allocated channel: a2
channel a2: SID=25 device type=DISK
allocated channel: a3
channel a3: SID=34 device type=DISK
Starting backup at 06-NOV-2012 23:55:43
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00002 name=+MSDATA/maa/datafile/sysaux.261.792009871
backing up blocks 1 through 25600
channel a1: starting piece 1 at 06-NOV-2012 23:55:43
channel a2: starting full datafile backup set
channel a2: specifying datafile(s) in backup set
input datafile file number=00002 name=+MSDATA/maa/datafile/sysaux.261.792009871
backing up blocks 25601 through 51200
channel a2: starting piece 2 at 06-NOV-2012 23:55:45
channel a3: starting full datafile backup set
channel a3: specifying datafile(s) in backup set
input datafile file number=00002 name=+MSDATA/maa/datafile/sysaux.261.792009871
backing up blocks 51201 through 76800
channel a3: starting piece 3 at 06-NOV-2012 23:55:46
channel a1: finished piece 1 at 06-NOV-2012 23:55:53
piece handle=/u01/recovery/MAA/backupset/2012_11_06/o1_mf_nnndf_TAG20121106T235543_89ldqzk0_.bkp tag=TAG20121106T235543 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:10
channel a2: finished piece 2 at 06-NOV-2012 23:55:58
piece handle=/u01/recovery/MAA/backupset/2012_11_06/o1_mf_nnndf_TAG20121106T235543_89ldr1x0_.bkp tag=TAG20121106T235543 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:13
channel a3: finished piece 3 at 06-NOV-2012 23:55:58
piece handle=/u01/recovery/MAA/backupset/2012_11_06/o1_mf_nnndf_TAG20121106T235543_89ldr49f_.bkp tag=TAG20121106T235543 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:12
Finished backup at 06-NOV-2012 23:55:58
released channel: a1
released channel: a2
released channel: a3
Todd Bao在书中也稍微花些功夫介绍了优化SBT设备备份内容,但我这里省略。
数据块变更跟踪
为了加快等级1增量备份的速度,DBA可以创建一种由位图构成的名为数据块变更跟踪(Block Change Tracking,简称BCT)的文件,该文件以比特位自动标记自最近一次增量备份以来经过变更的数据块在数据文件中的位置。有了这种文件,进行等级1增量备份时,RMAN无须扫描整个数据文件,而只要扫描数据块变更跟踪文件即可发现什么数据块需要备份了。这对缩短增量备份的时间有显而易见的作用,然而,这并非毫无代价,实例必须额外启动一个名为CTWR的后台进程修改数据块变更跟踪文件,对OLTP的应用来说这额外的压力不可以忽略。启用此功能的命令是"alter database enable block change tracking",比如将跟踪文件取名为BCT:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/bak/disk/BCT' reuse;
关闭该功能的命令为:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
使用v$backup_datafile视图能够验证增量备份的效率,如下所示blocks_read与datafile_blocks的比值越小说明增量备份读取数据块的数量占文件总数据块的数量就越小,增量备份的效率则越高,启用数据块变更跟踪回报就越高:
SQL> select FILE#, SUM(BLOCKS_READ)/SUM(DATAFILE_BLOCKS) RATIO FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 GROUP BY FILE#;
FILE# RATIO
---------- ----------
1 1
6 1
2 1
5 1
4 1
3 1
7 1
7 rows selected.
若比值相当高甚至接近于1,就没有理由进行增量备份,不如直接全备,更不用说数据块变更跟踪文件了。
而我这里全部显示为1,因为从0备份到1备份,我的数据库什么变化也没所致。
镜像复制备份优化
镜像复制备份的优点在以前已经说过,而它最大的缺点是浪费空间,根据输入文件大小决定着镜像复制备份文件的大小。但正因为这个备份时的缺点,才造就了其还原时的优点 — 镜像复制利用文件路径的重命名可以使还原操作的时间忽略不计。RMAN的switch命令能够将当前数据文件与其镜像复制备份文件快速互换。
但是这样会带来另一个问题,如果镜像复制备份的频率不高,那么恢复的时候会消耗大量时间,你的镜像复制备份文件是多长时间前的,那么得应用这个时间段所有的归档日志,这基本抹去了镜像复制备份快速还原的好处。但很难找到哪个公司哪个DBA以高频率进行镜像复制备份,尤其是数据库又多又大。
Oracle的给了个优化方案:使用等级1增量备份"更新"镜像复制。
比如这样的方案:
第一次执行整个数据库所有数据文件的镜像复制,并标记为EVERYDAY:
RMAN> backup incremental level 1 for recover of copy with tag 'EVERYDAY' database;
第二次执行相同命令得到以"EVERYDAY"镜像为基础的等级1增量备份:
RMAN> backup incremental level 1 for recover of copy with tag 'EVERYDAY' database;
第二次接着执行以下命令,RMAN会使用增量备份恢复标签为"EVERYDAY"的数据库镜像复制,即所谓的更新镜像复制备份。
RMAN> recover copy of database with tag 'EVERYDAY';
以后,只要保证执行上面的"backup incremental"命令和"recover copy of"命令,就能获得一个"新鲜"的数据库镜像复制,即增量备份的时间换取新的数据库的镜像复制。
下面看具体示例:
先产生一些数据:
SQL> create table luocs.test as select * from dba_objects;
Table created.
SQL> select count(*) from luocs.test;
COUNT(*)
----------
22716
进行一次备份:
RMAN> backup incremental level 1 for recover of copy with tag 'EVERYDAY' database;
Starting backup at 07-NOV-2012 16:49:09
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 7 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+MSDATA/maa/datafile/system.260.792009857
output file name=/u01/recovery/MAA/datafile/o1_mf_system_89n845xo_.dbf tag=EVERYDAY RECID=23 STAMP=798742171
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+MSDATA/maa/datafile/sysaux.261.792009871
output file name=/u01/recovery/MAA/datafile/o1_mf_sysaux_89n84zcv_.dbf tag=EVERYDAY RECID=24 STAMP=798742196
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+MSDATA/maa/datafile/undotbs1.262.792009883
output file name=/u01/recovery/MAA/datafile/o1_mf_undotbs1_89n85s1n_.dbf tag=EVERYDAY RECID=25 STAMP=798742211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+MSDATA/maa/datafile/l.266.798569345
output file name=/u01/recovery/MAA/datafile/o1_mf_l_89n867rj_.dbf tag=EVERYDAY RECID=26 STAMP=798742216
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+MSDATA/maa/datafile/l.267.798570165
output file name=/u01/recovery/MAA/datafile/o1_mf_l_89n868x0_.dbf tag=EVERYDAY RECID=27 STAMP=798742217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+MSDATA/maa/datafile/l.268.798572257
output file name=/u01/recovery/MAA/datafile/o1_mf_l_89n86b34_.dbf tag=EVERYDAY RECID=28 STAMP=798742218
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+MSDATA/maa/datafile/users.264.792009897
output file name=/u01/recovery/MAA/datafile/o1_mf_users_89n86c7y_.dbf tag=EVERYDAY RECID=29 STAMP=798742219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-NOV-2012 16:50:21
channel ORA_DISK_1: finished piece 1 at 07-NOV-2012 16:50:22
piece handle=/u01/recovery/MAA/backupset/2012_11_07/o1_mf_ncsn1_EVERYDAY_89n86fdg_.bkp tag=EVERYDAY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-2012 16:50:22
我们再产生一些数据:
SQL> create table luocs.test as select * from dba_objects;
Table created.
SQL> select count(*) from luocs.test;
COUNT(*)
----------
22716
SQL> create table luocs.everyday as select * from dba_objects;
Table created.
SQL> insert into luocs.everyday select * from luocs.everyday;
22717 rows created.
SQL> /
45434 rows created.
SQL> /
90868 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from luocs.everyday;
COUNT(*)
----------
181736
SQL> select count(*) from luocs.test;
COUNT(*)
----------
22716
在用同样的backup incremental命令备份:
RMAN> backup incremental level 1 for recover of copy with tag 'EVERYDAY' database;
Starting backup at 07-NOV-2012 16:55:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+MSDATA/maa/datafile/system.260.792009857
input datafile file number=00002 name=+MSDATA/maa/datafile/sysaux.261.792009871
input datafile file number=00003 name=+MSDATA/maa/datafile/undotbs1.262.792009883
input datafile file number=00004 name=+MSDATA/maa/datafile/users.264.792009897
input datafile file number=00005 name=+MSDATA/maa/datafile/l.266.798569345
input datafile file number=00006 name=+MSDATA/maa/datafile/l.267.798570165
input datafile file number=00007 name=+MSDATA/maa/datafile/l.268.798572257
channel ORA_DISK_1: starting piece 1 at 07-NOV-2012 16:55:27
channel ORA_DISK_1: finished piece 1 at 07-NOV-2012 16:55:52
piece handle=/u01/recovery/MAA/backupset/2012_11_07/o1_mf_nnnd1_EVERYDAY_89n8j02v_.bkp tag=EVERYDAY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-NOV-2012 16:55:53
channel ORA_DISK_1: finished piece 1 at 07-NOV-2012 16:55:54
piece handle=/u01/recovery/MAA/backupset/2012_11_07/o1_mf_ncsn1_EVERYDAY_89n8jswn_.bkp tag=EVERYDAY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-2012 16:55:54
-- 这次输出内容很明显与上一次不一样,生成了个数据文件备份集和控制文件、参数文件的备份集。
执行recover copy of命令
RMAN> recover copy of database with tag 'EVERYDAY';
Starting recover at 07-NOV-2012 16:57:50
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/recovery/MAA/datafile/o1_mf_system_89n845xo_.dbf
recovering datafile copy file number=00002 name=/u01/recovery/MAA/datafile/o1_mf_sysaux_89n84zcv_.dbf
recovering datafile copy file number=00003 name=/u01/recovery/MAA/datafile/o1_mf_undotbs1_89n85s1n_.dbf
recovering datafile copy file number=00004 name=/u01/recovery/MAA/datafile/o1_mf_users_89n86c7y_.dbf
recovering datafile copy file number=00005 name=/u01/recovery/MAA/datafile/o1_mf_l_89n867rj_.dbf
recovering datafile copy file number=00006 name=/u01/recovery/MAA/datafile/o1_mf_l_89n868x0_.dbf
recovering datafile copy file number=00007 name=/u01/recovery/MAA/datafile/o1_mf_l_89n86b34_.dbf
channel ORA_DISK_1: reading from backup piece /u01/recovery/MAA/backupset/2012_11_07/o1_mf_nnnd1_EVERYDAY_89n8j02v_.bkp
channel ORA_DISK_1: piece handle=/u01/recovery/MAA/backupset/2012_11_07/o1_mf_nnnd1_EVERYDAY_89n8j02v_.bkp tag=EVERYDAY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 07-NOV-2012 16:57:54
-- 已经更新了第一次执行的镜像备份文件了。
再模拟一些交易:
SQL> truncate table luocs.test;
Table truncated.
SQL> select count(*) from luocs.test;
COUNT(*)
----------
0
SQL> select tablespace_name from dba_tables where owner = 'LUOCS' and rownum <= 1 ;
TABLESPACE_NAME
------------------------------------------------------------
USERS
SQL> select file#, name from v$datafile where name like '%user%';
FILE# NAME
---------- ------------------------------------------------------------
4 +MSDATA/maa/datafile/users.264.792009897
然后我们模拟丢失4号数据文件或者破坏4号数据文件
[grid@maa3 ~]$ asmcmd -p
ASMCMD [+] > cd +MSDATA/maa/datafile/
ASMCMD [+MSDATA/maa/datafile] > ls
L.266.798569345
L.267.798570165
L.268.798572257
SYSAUX.261.792009871
SYSTEM.260.792009857
UNDOTBS1.262.792009883
USERS.264.792009897
ASMCMD [+MSDATA/maa/datafile] > rm -rf USERS.264.792009897
ORA-15032: not all alterations performed
ORA-15028: ASM file '+MSDATA/maa/datafile/USERS.264.792009897' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
-- 这是基于ASM的安全机制受到保护导致的,也证实了ASMCMD命令与Linux终端命令不同支处。
我们就将此数据文件脱机
SQL> alter database datafile 4 offline;
Database altered.
ASMCMD [+MSDATA/maa/datafile] > rm -rf USERS.264.792009897
-- 好,成功删除
这时候,因为重要的数据文件没有被删除,所以数据库实例还是正常的,只不过访问该数据文件上的数据就会报错:
SQL> select count(*) from luocs.test;
select count(*) from luocs.test
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+MSDATA/maa/datafile/users.264.792009897'
普通数据文件的恢复可以在OPEN模式进行
RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> sql 'alter database datafile 4 online';
5> }
Starting restore at 07-NOV-2012 17:15:13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=33 STAMP=798742672 file name=/u01/recovery/MAA/datafile/o1_mf_users_89n86c7y_.dbf
destination for restore of datafile 00004: +MSDATA/maa/datafile/users.264.792009897
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=+MSDATA/maa/datafile/users.264.798743713 RECID=0 STAMP=0
Finished restore at 07-NOV-2012 17:15:14
Starting recover at 07-NOV-2012 17:15:14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-NOV-2012 17:15:15
sql statement: alter database datafile 4 online
然后我们查看一下恢复状态
SQL> select count(*) from luocs.everyday;
COUNT(*)
----------
181736
SQL> select count(*) from luocs.test;
COUNT(*)
----------
0
-- 到这里可能一些朋友会疑惑为什么我是拿镜像备份文件还原的,而最后的镜像备份文件是truncate test表之前的。这里查询怎么还会查到truncate之后的数据?
这个其实非常简单,因为我们在run运行里加入了recover datafile,并且没有丢失归档文件,所以做到了零丢失。