Another Interesting Issue

On the same database of which i’ve already blogged i’ve encountered another interesting issue. Some times the database get down without messages on alert.log. Database is used by two applications, on log of one of this applications there was:

java.sql.SQLException: ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

I’ve also opened a SR on Metalink but with only suggestion that it was an hardware problem. The strange things were two:

1. On alert.log there was nothing
2. The file indicated by the message in application log (#201) did not exist on the database

After a while we was able to reproduce the problem, it was a query with a group by on a large data set. After a couple of test my intuition was that the problem were the TEMPORARY tablespace, so i’ve created a new TEMPORARY tablespace, i’ve setted it as new default temporary tablespace ad re-tried the test with success. It is clear that there is a bug that cause Oracle db (9.2.0.1) to crash with particular corruption on TEMPORARY tablespace.