Whenever I deliver training or conference presentations on advanced troubleshooting topics, I usually spend some time demonstrating how to get and interpret Oracle server process stack traces.
As I’ve mentioned before, stack traces are the ultimate indicators showing where in Oracle kernel (or whatever application) code the execution currently is (or where it was when a crash occurred). This is the reason Oracle Support asks for stack traces whenever there’s a crash or non-trivial hang involved, that’s why Oracle database dumps errorstacks when ORA-600′s and other exceptions occur.
There are multiple ways for getting stack traces for Oracle, but not all ways are equal. Some give you more contextual info, some less, but what I’m blogging about today is that some ways are less safe than others.
I was using pstack on Linux for diagnosing an IO related performance issue. I executed a create table as select statement and ran pstack in a loop for getting stack traces from the running process.
However in one of the test runs I got following error in my Oracle session:
SQL> create table t as select * from dba_source; create table t as select * from dba_source * ERROR at line 1: ORA-01115: IO error reading block from file 1 (block # 11161) ORA-01110: data file 1: '/u01/oradata/LIN10G/system01.dbf' ORA-27091: unable to queue I/O ORA-27072: File I/O error Additional information: 3 Additional information: 11145 Additional information: 32768
I suspected that this issue was due Linux pstack, stopped the pstack script and ran my CTAS from the same Oracle session again: