WARNING: COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O
We faced this error in our 10.2.0.2 client-side database server trace logs. Users were not able to access the application and even they could not start the related services on the application server.
When we checked the DB server we found that there was no space left on ORACLE_HOME. Further checking leads us to huge trace files generated and we talking about 20GB. So for a temporary fix, we deleted few largest trace files and some old trace files, as well as the most important thing, is to maintain the state of the application up and running. After this application was back on track.
A little history:-
This server was restored from the crash and localit247.com missed to implement the log purging for this new DB server. So we needed to the RCA.
Below is information shared with the application Team:-
Faced Issue: Users were not able to access the application and cannot start the related services.
Root Cause: No space left for drive causing the server to halt. Oracle tracing logs under DB_INSTANCE > udump folder were the culprit of storage consumption.
Temporary fix: Deleted few largest files.
We checked for "WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O" and found that it's because of the bug. The best way to check that is on the oracle support website. We keep checking on another famous site like uatdb.com
and few good blogs for "WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O".
There are two solutions provided by oracle on the support website for this bug:-
1. Upgrade to 11.2.0.2 or higher
2. Apply 10.2.0.5.2 PSU where the fix for Bug 9772888 has been included.
You need to open a service request with oracle on oracle support if not able to find any patch as per your platform or you may implement temporary by creating a batch script to delete all *.trc files older than 15-30days under UDUMP folder for 10G database.
In our case, we are already in the progress to move the database to Oracle 19C. So we choose to implement a batch script to delete the trace file.
Hope this helps you in handling “WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O”. Keep sharing..