Re: Oracle hangs, no errors

In article <20011126.163450.1295166342.813_at_netlojix.comnospam>, "Sean Leach" <sleachNOSPAM_at_netlojix.comnospam> wrote:

> Hi,
>
> Sorry, but here is a very vague trouble description:
>
> Platform: Sun Solaris 8 (Sparc), latest patch clusters DB: Oracle
> 8.1.7.0.0
>
> For some reason, the database will just hang, no errors in any of the
> alert or trace logs, and sometimes, it will correct itself, and other, we
> can do a "shutdown abort" to stop the instance, but all of the child SID
> processes are still there, and eventually go to a "<defunct>" state, such
> that we have to reboot the machine to fix it.
>
> Sometimes, we can do a SELECT COUNT(*) FROM table1, and it will succeed,
> but then a SELECT COUNT(*) FROM table2 will fail. Sounded like a table
> lock issue, but not sure...
>
> Sometimes, we can restart the listener, and that fixes it, other times,
> it doesn't.
>
> The db data is mounted on a NetApp, but we ran for over 200 days with no
> problems before, and then this problems just suddenly started happening.
> We have disk_async_io set to false, as we had a problem with asynchronous
> IO before, and that fixed THAT problem, but now we have this no error
> hanging. It sucks as I have no where to start looking. Any tips?
>
> Thanks,
> Sean

You should take a look in v$session_wait and see what are the processes waiting for. At any rate, you should use the following sequence of commands from sqlplus on all nodes:

connect internal
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
oradebug flush
oradebug tracefile_name

You should take at least 2 systemstate dumps per node and ship them to Oracle support. Things like this are normally a severity 1 TAR and they do get resolved very quickly. Be prepared to leave your beeper & cell phone number to the support specialist, as well as your manager's

-- 
Mladen Gogala