mrcat猫网
  咨询电话:13615337850

mrcat有app嘛

那些年,我们用并行遇到的坑02

环境:Oracle 10.2.0.5现象:RMAN分配多个通道,但实际无法使用到并行。构建测试用例:

create tablespace dbs_d_test;alter tablespace dbs_d_test add datafile; --这里是11alter tablespace dbs_d_test add datafile; --这里是12alter tablespace dbs_d_test add datafile; --这里是13alter database datafile 11,12,13 resize 1G;1.RMAN多通道但未用到并行2.备份语句改写使用到并行3.备份方式改变提高效率

1.RMAN多通道但未用到并行

使用RMAN备份

run {allocate channel c1 device type disk;allocate channel c2 device type disk;allocate channel c3 device type disk;backup as copy datafile 11 format "/tmp/incr/copy11.bak";backup as copy datafile 12 format "/tmp/incr/copy12.bak";backup as copy datafile 13 format "/tmp/incr/copy13.bak";release channel c1;release channel c2;release channel c3;}

使用下面SQL查询长操作:

select inst_id, sid, username, opname, target, sofar, totalwork, sofar * 100 / totalwork from gv$session_longops where sofar < totalwork;

发现上面这种备份写法,虽然分配了多个通道,但实际观察并没有使用到并行。3个文件的备份是串行操作的。这点从上面的长操作中可以看到,同时从RMAN输出日志中同样也可以看到:

RMAN> run {2> allocate channel c1 device type disk;3> allocate channel c2 device type disk;4> allocate channel c3 device type disk;5> 6> backup as copy datafile 11 format "/tmp/incr/copy11.bak";7> backup as copy datafile 12 format "/tmp/incr/copy12.bak";8> backup as copy datafile 13 format "/tmp/incr/copy13.bak";9> 10> release channel c1;11> release channel c2;12> release channel c3;13> }using target database control file instead of recovery catalogallocated channel: c1channel c1: sid=128 instance=jy1 devtype=DISKallocated channel: c2channel c2: sid=117 instance=jy1 devtype=DISKallocated channel: c3channel c3: sid=129 instance=jy1 devtype=DISKStarting backup at 29-AUG-18channel c1: starting datafile copyinput datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387output filename=/tmp/incr/copy11.bak tag=TAG20180829T002101 recid=13 stamp=985393279channel c1: datafile copy complete, elapsed time: 00:00:25Finished backup at 29-AUG-18Starting backup at 29-AUG-18channel c1: starting datafile copyinput datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391output filename=/tmp/incr/copy12.bak tag=TAG20180829T002127 recid=14 stamp=985393305channel c1: datafile copy complete, elapsed time: 00:00:25Finished backup at 29-AUG-18Starting backup at 29-AUG-18channel c1: starting datafile copyinput datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395output filename=/tmp/incr/copy13.bak tag=TAG20180829T002153 recid=15 stamp=985393330channel c1: datafile copy complete, elapsed time: 00:00:25Finished backup at 29-AUG-18released channel: c1released channel: c2released channel: c3

实际是串行操作,都是用的通道c1,这3个数据文件的copy备份消耗3个25s=75s。

2.备份语句改写使用到并行

改进写法,用到了并行:

run {allocate channel c1 device type disk;allocate channel c2 device type disk;allocate channel c3 device type disk;backup as copy datafile 11,12,13 format "/tmp/incr/copy_%u.bak";release channel c1;release channel c2;release channel c3;}

从日志看到:

RMAN> run {2> allocate channel c1 device type disk;3> allocate channel c2 device type disk;4> allocate channel c3 device type disk;5> 6> backup as copy datafile 11,12,13 format "/tmp/incr/copy_%u.bak";7> 8> release channel c1;9> release channel c2;10> release channel c3;11> }using target database control file instead of recovery catalogallocated channel: c1channel c1: sid=129 instance=jy1 devtype=DISKallocated channel: c2channel c2: sid=127 instance=jy1 devtype=DISKallocated channel: c3channel c3: sid=119 instance=jy1 devtype=DISKStarting backup at 29-AUG-18channel c1: starting datafile copyinput datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387channel c2: starting datafile copyinput datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391channel c3: starting datafile copyinput datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395output filename=/tmp/incr/copy_14tbnq76.bak tag=TAG20180829T002302 recid=16 stamp=985393432channel c1: datafile copy complete, elapsed time: 00:00:55output filename=/tmp/incr/copy_15tbnq76.bak tag=TAG20180829T002302 recid=17 stamp=985393432channel c2: datafile copy complete, elapsed time: 00:00:55output filename=/tmp/incr/copy_16tbnq76.bak tag=TAG20180829T002302 recid=18 stamp=985393435channel c3: datafile copy complete, elapsed time: 00:00:55Finished backup at 29-AUG-18released channel: c1released channel: c2released channel: c3

实际是并行操作,分别用的通道c1、c2、c3,这3个数据文件的copy备份消耗1个55s=55s。那为什么并行没有成倍增加效率?跟上一篇提到的一样,系统的整体I/O能力达到瓶颈了。所以一味的增加并行度并不总是有意义的。

3.备份方式改变提高效率

如果数据文件很大,但实际使用的并不多,则可以考虑使用备份集的方式,减少备份对空间的占用,一般同时也会加快备份的速度:

run {allocate channel c1 device type disk;allocate channel c2 device type disk;allocate channel c3 device type disk;backup as compressed backupset datafile 11,12,13 format "/tmp/incr/datafile_%u.bak";release channel c1;release channel c2;release channel c3;}

从日志可以看到:

RMAN> run {2> allocate channel c1 device type disk;3> allocate channel c2 device type disk;4> allocate channel c3 device type disk;5> 6> backup as compressed backupset datafile 11,12,13 format "/tmp/incr/datafile_%u.bak";7> 8> release channel c1;9> release channel c2;10> release channel c3;11> }using target database control file instead of recovery catalogallocated channel: c1channel c1: sid=128 instance=jy1 devtype=DISKallocated channel: c2channel c2: sid=134 instance=jy1 devtype=DISKallocated channel: c3channel c3: sid=116 instance=jy1 devtype=DISKStarting backup at 29-AUG-18channel c1: starting compressed full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387channel c1: starting piece 1 at 29-AUG-18channel c2: starting compressed full datafile backupsetchannel c2: specifying datafile(s) in backupsetinput datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391channel c2: starting piece 1 at 29-AUG-18channel c3: starting compressed full datafile backupsetchannel c3: specifying datafile(s) in backupsetinput datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395channel c3: starting piece 1 at 29-AUG-18channel c1: finished piece 1 at 29-AUG-18piece handle=/tmp/incr/datafile_17tbnqi9.bak tag=TAG20180829T002857 comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02channel c3: finished piece 1 at 29-AUG-18piece handle=/tmp/incr/datafile_19tbnqia.bak tag=TAG20180829T002857 comment=NONEchannel c3: backup set complete, elapsed time: 00:00:01channel c2: finished piece 1 at 29-AUG-18piece handle=/tmp/incr/datafile_18tbnqi9.bak tag=TAG20180829T002857 comment=NONEchannel c2: backup set complete, elapsed time: 00:00:05Finished backup at 29-AUG-18released channel: c1released channel: c2released channel: c3

由于我这里这几个文件根本没有业务数据,所以效率提升尤为明显,只需要5s钟就完成了备份。