[20171122]rman备份与参数filesystemio_options设置.txt
--//这几天写了几篇filesystemio_options参数设置的文章,单独测试这个参数对rman备份以及os缓存的影响.
1.测试环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试一:
--//首先测试filesystemio_options=SETALL的情况:
SYS@book> show parameter filesystem
NAME TYPE VALUE
-------------------- ------ ------
filesystemio_options string SETALL
--//在rman备份前我清除os缓存.
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachedel {}
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/redo03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf pages in cache: 0/194562 (0.0%) [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf pages in cache: 0/275202 (0.0%) [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl pages in cache: 0/2612 (0.0%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf_20171120 pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf pages in cache: 0/105986 (0.0%) [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl pages in cache: 0/2612 (0.0%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf pages in cache: 0/554498 (0.0%) [filesize=2217992.0K, pagesize=4K]
/mnt/ramdisk/book/tea01.dbf pages in cache: 0/10242 (0.0%) [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf pages in cache: 0/88642 (0.0%) [filesize=354568.0K, pagesize=4K]
/mnt/ramdisk/book/redo02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
$ free -m
total used free shared buffers cached
Mem: 129161 105226 23935 0 1097 100197
-/+ buffers/cache: 3930 125230
Swap: 30718 2 30715
RMAN> backup database format '/home/oracle/backup/%U';
Starting backup at 2017-11-22 09:49:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=106 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=119 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=132 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=00004 name=/mnt/ramdisk/book/users01.dbf
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-22 09:49:14
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/mnt/ramdisk/book/sysaux01.dbf
input datafile file number=00001 name=/mnt/ramdisk/book/system01.dbf
channel ORA_DISK_2: starting piece 1 at 2017-11-22 09:49:14
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf
input datafile file number=00005 name=/mnt/ramdisk/book/example01.dbf
channel ORA_DISK_3: starting piece 1 at 2017-11-22 09:49:14
channel ORA_DISK_1: finished piece 1 at 2017-11-22 09:49:21
piece handle=/home/oracle/backup/gjsk6o4q_1_1 tag=TAG20171122T094913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 2017-11-22 09:49:21
piece handle=/home/oracle/backup/glsk6o4q_1_1 tag=TAG20171122T094913 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 2017-11-22 09:49:29
piece handle=/home/oracle/backup/gksk6o4q_1_1 tag=TAG20171122T094913 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
Finished backup at 2017-11-22 09:49:29
Starting Control File and SPFILE Autobackup at 2017-11-22 09:49:29
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_11_22/o1_mf_s_960716969_f19ox9rn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-11-22 09:49:30
--//17秒完成.
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/redo03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf pages in cache: 0/194562 (0.0%) [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf pages in cache: 0/275202 (0.0%) [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl pages in cache: 0/2612 (0.0%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf_20171120 pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf pages in cache: 0/105986 (0.0%) [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl pages in cache: 0/2612 (0.0%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf pages in cache: 0/554498 (0.0%) [filesize=2217992.0K, pagesize=4K]
/mnt/ramdisk/book/tea01.dbf pages in cache: 0/10242 (0.0%) [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf pages in cache: 0/88642 (0.0%) [filesize=354568.0K, pagesize=4K]
/mnt/ramdisk/book/redo02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
$ free -m
total used free shared buffers cached
Mem: 129161 105349 23811 0 1098 100187
-/+ buffers/cache: 4063 125098
Swap: 30718 2 30715
--//可以发现rman备份也OS也不缓存,free查看注意free列,基本没有变化.
--//补充测试:看看备份文件是否也缓存.
$ find /home/oracle/backup/ -name "AA*" -print0 | xargs -0 -I{} cachestats {}
/home/oracle/backup/AA_h7sk6p7f_1_1 pages in cache: 0/85158 (0.0%) [filesize=340632.0K, pagesize=4K]
/home/oracle/backup/AA_h9sk6p7f_1_1 pages in cache: 0/19382 (0.0%) [filesize=77528.0K, pagesize=4K]
/home/oracle/backup/AA_h8sk6p7f_1_1 pages in cache: 0/322672 (0.0%) [filesize=1290688.0K, pagesize=4K]
--//噢!my god!!备份集文件,os也是不缓存.采用直接写的方式.
3.测试二:
--//首先测试filesystemio_options=asynch的情况:
SYS@book> alter system set filesystemio_options=asynch scope=spfile;
System altered.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachedel {}
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/redo03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf pages in cache: 6/194562 (0.0%) [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf pages in cache: 0/275202 (0.0%) [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl pages in cache: 4/2612 (0.2%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf_20171120 pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf pages in cache: 0/105986 (0.0%) [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl pages in cache: 32/2612 (1.2%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf pages in cache: 28/554498 (0.0%) [filesize=2217992.0K, pagesize=4K]
/mnt/ramdisk/book/tea01.dbf pages in cache: 0/10242 (0.0%) [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf pages in cache: 0/88642 (0.0%) [filesize=354568.0K, pagesize=4K]
/mnt/ramdisk/book/redo02.log pages in cache: 2/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
$ free -m
total used free shared buffers cached
Mem: 129161 103838 25322 0 1100 98725
-/+ buffers/cache: 4012 125148
Swap: 30718 2 30715
RMAN> backup database format '/home/oracle/backup/%U';
Starting backup at 2017-11-22 09:54:23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
....略..
Finished backup at 2017-11-22 09:54:38
--//15秒.
$ find /mnt/ramdisk/book/ -name "*.*" -print0 | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/redo03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf pages in cache: 194562/194562 (100.0%) [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf pages in cache: 215050/275202 (78.1%) [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl pages in cache: 100/2612 (3.8%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf_20171120 pages in cache: 0/240642 (0.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/sysaux01.dbf pages in cache: 240642/240642 (100.0%) [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log pages in cache: 0/12801 (0.0%) [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf pages in cache: 0/105986 (0.0%) [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl pages in cache: 2604/2612 (99.7%) [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf pages in cache: 215322/554498 (38.8%) [filesize=2217992.0K, pagesize=4K]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/mnt/ramdisk/book/tea01.dbf pages in cache: 10242/10242 (100.0%) [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf pages in cache: 88642/88642 (100.0%) [filesize=354568.0K, pagesize=4K]
/mnt/ramdisk/book/redo02.log pages in cache: 11/12801 (0.1%) [filesize=51200.5K, pagesize=4K]
$ free -m
total used free shared buffers cached
Mem: 129161 109332 19829 0 1102 104181
-/+ buffers/cache: 4048 125112
Swap: 30718 2 30715
--//可以发现rman备份也OS也缓存,free查看注意free列,备份前23811,备份后19829,23811-19829=3982M.
--//另外注意下划线内容,数据文件users01.dbf并没有缓存全部,这是因为许多数据库还没有数据,rman不会备份空块,这样仅仅读取缓存了
--//38.8%.
$ find /home/oracle/backup/ -name "h*" -print0 | xargs -0 -I{} cachestats {}
/home/oracle/backup/h5sk6ovl_1_1 pages in cache: 19348/19348 (100.0%) [filesize=77392.0K, pagesize=4K]
/home/oracle/backup/h3sk6ovl_1_1 pages in cache: 85158/85158 (100.0%) [filesize=340632.0K, pagesize=4K]
/home/oracle/backup/h4sk6ovl_1_1 pages in cache: 322670/322670 (100.0%) [filesize=1290680.0K, pagesize=4K]
--//备份集文件也会缓存.
--//换一句话,如果os缓存部分,rman备份还可以更快一些.再次备份应该会快一些.
RMAN> backup database format '/home/oracle/backup/%U';
Starting backup at 2017-11-22 10:00:46
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-22 10:00:46
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/mnt/ramdisk/book/sysaux01.dbf
input datafile file number=00001 name=/mnt/ramdisk/book/system01.dbf
channel ORA_DISK_2: starting piece 1 at 2017-11-22 10:00:46
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf
input datafile file number=00005 name=/mnt/ramdisk/book/example01.dbf
channel ORA_DISK_3: starting piece 1 at 2017-11-22 10:00:46
channel ORA_DISK_3: finished piece 1 at 2017-11-22 10:00:47
piece handle=/home/oracle/backup/h1sk6oqe_1_1 tag=TAG20171122T100046 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 2017-11-22 10:00:49
piece handle=/home/oracle/backup/gvsk6oqe_1_1 tag=TAG20171122T100046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 2017-11-22 10:00:53
piece handle=/home/oracle/backup/h0sk6oqe_1_1 tag=TAG20171122T100046 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
Finished backup at 2017-11-22 10:00:53
Starting Control File and SPFILE Autobackup at 2017-11-22 10:00:54
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_11_22/o1_mf_s_960717654_f19plp35_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-11-22 10:00:55
--//7秒,os缓存加快了备份速度.