oracle 异步io 慢,Oracle 异步io异常

Oracle 异步io错误

客户一数据库alert日志持续报以下错误

引用

LNS: Standby redo logfile selected for thread 1 sequence 4060 for destination LOG_ARCHIVE_DEST_2

Thu Apr 22 06:49:04 2010

Errors in file /oracle/admin/archive/udump/archive_ora_5657.trc:

Thu Apr 22 06:49:04 2010

Errors in file /oracle/admin/archive/udump/archive_ora_5657.trc:

Thu Apr 22 06:49:04 2010

Errors in file /oracle/admin/archive/udump/archive_ora_5657.trc:

跟踪文件显示

引用

/oracle/admin/archive/udump/archive_ora_25053.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /oracle/product/10.2.0/db_1

System name:HP-UX

Node name:rp4440

Release:B.11.23

Version:U

Machine:9000/800

Instance name: archive

Redo thread mounted by this instance: 1

Oracle process number: 0

Unix process pid: 25053, image: oracle@rp4440

Ioctl ASYNC_CONFIG error, errno = 1

/oracle/admin/archive/udump/archive_ora_25053.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /oracle/product/10.2.0/db_1

System name:HP-UX

Node name:rp4440

Release:B.11.23

Version:U

Machine:9000/800

Instance name: archive

Redo thread mounted by this instance: 1

Oracle process number: 0

Unix process pid: 25053, image: oracle@rp4440

Ioctl ASYNC_CONFIG error, errno = 1

/oracle/admin/archive/udump/archive_ora_25053.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /oracle/product/10.2.0/db_1

System name:HP-UX

Node name:rp4440

Release:B.11.23

Version:U

Machine:9000/800

Instance name: archive

Redo thread mounted by this instance: 1

Oracle process number: 0

Unix process pid: 25053, image: oracle@rp4440

Ioctl ASYNC_CONFIG error, errno = 1

从跟踪文件可以基本定位为系统异步IO出现问题

操作系统：HP-UX B.11.23

数据库库版本为：10.2.0.1

错误为：Ioctl ASYNC_CONFIG error, errno = 1

进一步诊断：

检查HP-UX异步IO文件，可以看到Oracle具有读写权限

引用

# ls -l /dev/async

crw-rw-rw-   1 bin        bin        101 0x000000 Nov  7  2007 /dev/async

在HP-UX平台下如果Oracle数据文件存放在文件系统中，我们强烈建议不能使用异步IO，可以通过增加dbwr进程来提高写速度，需要注意的是修改其权限可能会影响到其他应用软件使用异步io，并且建议修改之前关闭Oracle

引用

# chmod 660 /dev/async

检查系统参数

引用

# kctune|grep fs_async

fs_async                          0  Default

检查Oracle参数文件

引用

SQL> show parameter disk_async

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

disk_asynch_io                       boolean     TRUE

SQL> show parameter filesystem

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

filesystemio_options                 string      asynch

将参数修改为

disk_asynch_io=FALSE

filesystemio_options=none

为了避免Ioctl ASYNC_CONFIG错误，给dba组赋MLOCK权限

引用

#/usr/sbin/setprivgrp dba MLOCK

# cat /etc/privgroup

dba MLOCK RTSCHED RTPRIO

参考文献：metalink 302801.1