expdp匯出時卡死 Could not increase the asynch I/O limit to XXX for SQL direct I/O

eric0435發表於2013-10-22
MITSQL
今天expdp匯出時卡死不跟蹤檔案中出現類似下面的錯誤內容：
WARNING:Could not increase the asynch I/O limit to 3328 for SQL direct I/O. It is set to 128
是oracle的BUG，編號：9949948，只發生在10.2.0.5.0和11.2.0.1.0，解決方法有2種，一種是在作業系統層面修改相關引數另一種方法就是打補丁
MOS上的相關內容為：Warning:Could Not Increase The Asynch I/O Limit To XX For Sql Direct I/O [ID 1302633.1]
In this Document
#SYMPTOM">Symptoms
#CAUSE">Cause
#FIX">Solution
#REF">References
Applies to:
Oracle Server - Enterprise Edition - Version:
10.2.0.5 and
later [Release:
10.2 and later ]
Generic Linux
disk_asynch_io = TRUE
filesystemio_options = none
[root@hnz ~]# cat /proc/sys/fs/aio-max-size
cat: /proc/sys/fs/aio-max-size: No such file or directory
[root@hnz ~]# cat /proc/sys/fs/aio-max-nr
65536
Solution
The aio-max-size kernel parameter doesn't exist in the 2.6.x Linux
kernels.
This feature is now "automatic" in the 2.6.x kernel, based on the
physical capabilities of the disk device driver.
This should mean that the Linux Kernel is ready to perform ASYNC
I/O.
All install requirements should be met.
To ensure ASYNC I/O can be performed by Oracle Database you need to
verify or set the following parameters in the Database:
sql>alter system set disk_asynch_io=true scope=spfile;
sql> alter system set filesystemio_options=setall scope=spfile;
Then shutdown and startup the database and check if the warning
reappears.
An HCVE report (refer to Note 250262.1) should report no remaining
issues
If the above doesn't resolve the problem, then increase
fs.aio-max-nr
References
BUG:10334897 -
COULD NOT INCREASE THE ASYNCH I/O LIMIT TO NNN FOR SQL DIRECT I/O.
IT IS SET TO
BUG:9772888 - WARNING:COULD NOT LOWER THE
ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O. IT IS SE
NOTE:205259.1 - Howto Enable Asynchoronous I/O
on Red Hat Linux 2.1
NOTE:225751.1 - Asynchronous I/O (aio) on
RedHat Advanced Server 2.1 and RedHat Enterprise Linux 3
檢查相關內容，
[oracle@hnz ~]$ sqlplus /as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Oct 22
15:57:01 2013
Copyright (c) 1982, 2010, Oracle. All Rights
Reserved.
Connected to:
Oracle Database
10g Enterprise Edition Release 10.2.0.5.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> show parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
SQL> show parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL>
SQL> alter system set filesystemio_options=setall scope=spfile;
修改核心引數的值：aio-max-nr設定太低，推薦設定為fs.aio-max-nr= 3145728。修改引數使用/sbin/sysctl -p重新載入引數後，重啟資料庫即可
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1060985/，如需轉載，請註明出處，否則將追究法律責任。

相關文章