[20171117]参数filesystemio_options.txt

--//前几天看别人的awr报表发现设置参数filesystemio_options=setall,问为什么?对方给出一个链接,某某人都是这样设置的,
--//自己很无语,我希望对方能提出自己的见解.

--//首先给出oracle官方的解析:

https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410

9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter

You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on
file system files. This parameter is platform-specific and has a default value that is best for a particular platform.

FILESYTEMIO_OPTIONS can be set to one of the following values:

ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
SETALL: enable both asynchronous and direct I/O on file system files.
NONE: disable both asynchronous and direct I/O on file system files.

--//我以前在学习时也测试过这个参数,因为对于os没有相关的命令了解某个文件的缓存情况,我仅仅在dg上测试,因为没有负载很难了解
--//该参数的设置的效果,仅仅在生产系统遇到一个存储的问题,获得一个小工具nocache,一下子茅舍顿开.
--//参考链接:http://blog.itpub.net/267265/viewspace-2147398/

--//首先在讲解之前,我仅仅说明数据库采用文件系统的模式,ASM不在讨论范围.先讲讲我以前遇到的问题.

1.对于内存盘,不能设置FILESYSTEMIO_OPTIONS=setall,directio.

--//我的测试环境是建立在ramdisk上的,方法可以参考链接http://blog.itpub.net/267265/viewspace-1283884/

# mount | column -t | grep tmpfs
tmpfs              on  /dev/shm                  type  tmpfs        (rw)
tmpfs              on  /mnt/ramdisk              type  tmpfs        (rw,size=8G)

--//当然如果重启机器,测试数据库就消失了,不过因为是测试,我保留一个冷备份在磁盘中.直接拷贝就ok了.

# find /mnt/ramdisk/book -name "*.*" -print0  | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/sysaux01.dbf           pages in cache: 240642/240642 (100.0%)  [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/tea01.dbf              pages in cache: 10242/10242 (100.0%)  [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/redo03.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo02.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf            pages in cache: 65538/65538 (100.0%)  [filesize=262152.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl          pages in cache: 2612/2612 (100.0%)  [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl          pages in cache: 2612/2612 (100.0%)  [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf          pages in cache: 275202/275202 (100.0%)  [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf             pages in cache: 104851/105986 (98.9%)  [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf           pages in cache: 194562/194562 (100.0%)  [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf          pages in cache: 88642/88642 (100.0%)  [filesize=354568.0K, pagesize=4K]

# find /mnt/ramdisk/book -name "*.*" -print0  | xargs -0 -I{} cachedel {}

# find /mnt/ramdisk/book -name "*.*" -print0  | xargs -0 -I{} cachestats {}
/mnt/ramdisk/book/sysaux01.dbf           pages in cache: 240642/240642 (100.0%)  [filesize=962568.0K, pagesize=4K]
/mnt/ramdisk/book/tea01.dbf              pages in cache: 10242/10242 (100.0%)  [filesize=40968.0K, pagesize=4K]
/mnt/ramdisk/book/redo03.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo02.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redo01.log             pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/users01.dbf            pages in cache: 65538/65538 (100.0%)  [filesize=262152.0K, pagesize=4K]
/mnt/ramdisk/book/control02.ctl          pages in cache: 2612/2612 (100.0%)  [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/control01.ctl          pages in cache: 2612/2612 (100.0%)  [filesize=10448.0K, pagesize=4K]
/mnt/ramdisk/book/undotbs01.dbf          pages in cache: 275202/275202 (100.0%)  [filesize=1100808.0K, pagesize=4K]
/mnt/ramdisk/book/temp01.dbf             pages in cache: 104851/105986 (98.9%)  [filesize=423944.0K, pagesize=4K]
/mnt/ramdisk/book/system01.dbf           pages in cache: 194562/194562 (100.0%)  [filesize=778248.0K, pagesize=4K]
/mnt/ramdisk/book/redostb04.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb03.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb02.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/redostb01.log          pages in cache: 12801/12801 (100.0%)  [filesize=51200.5K, pagesize=4K]
/mnt/ramdisk/book/example01.dbf          pages in cache: 88642/88642 (100.0%)  [filesize=354568.0K, pagesize=4K]

--//可以发现对于tmpfs类型文件系统无法清除缓存的.至于temp01.dbf不是100%,我估计可能sparse file的原因.
--//补充说明:
# ls -lk temp01.dbf
-rw-r----- 1 oracle oinstall 423944 2017-11-16 08:35:13 temp01.dbf
# du -sk temp01.dbf
420236  temp01.dbf
# cachestats temp01.dbf
temp01.dbf                               pages in cache: 104851/105986 (98.9%)  [filesize=423944.0K, pagesize=4K]
--//两者看到大小不一致. 423944/4=105986. 也就是实际文件没有这么大,还是完全缓存的.
----------------

SYS@book> alter system set filesystemio_options=setall scope=spfile;
System altered.

SYS@book> startup mount
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
ORA-00205: error in identifying control file, check alert log for more info

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/mnt/ramdisk/book/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2
ORA-205 signalled during: ALTER DATABASE   MOUNT...

--//设置filesystemio_options=directio也一样.

2.rman恢复与filesystemio_options:
--//参考链接:http://blog.itpub.net/267265/viewspace-2122164/

--//我当时将移动硬盘插入我的机器,使用mount.cifs安装在服务端,在设置filesystemio_options=setall的情况下,rman恢复时报如下错
--//误.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/18/2016 08:34:31
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

3.下面为了测试方面,我将数据库移到本地硬盘. 具体步骤略..
--//另外4种设置情况,仅仅介绍2中setall,asynch. 限于篇幅另写一篇文章.