DB Unusable due to locking but not sure of the source

From: Michael Cunningham <napacunningham_at_gmail.com>
Date: Fri, 4 Dec 2015 09:46:56 -0800
Message-ID: <CAPt39tsQ8rTZ_tHc5OqZKbk2ws_wp1xkxWQFfDwK7=noNqfD9Q_at_mail.gmail.com>

Hello all, I cannot figure out why we had to bounce 3 databases this morning to solve a locking issue. Truth is I was unable to find the source of the locking problem, but here is what I'm asking for.

I could not query v$locked_object or v$lock to try and investigate. So ... just in case this happens again is there another way for me to query for locks? This is what I am really asking for help with, but anything will be appreciated.

Just in case you are curious, here is what I know about the issue so far. Oracle 12.1.0.1 running on Redhat Linux 6.5

1. top showed ctwr (change tracking writer) process at 100% so I assumed it was blocked for some reason.
2. I could not find anything in any trace file indicating a problem.
3. v$sqlarea and v$session showed many sessions waiting to complete DML.
4. ASH data was not written during the time of the locking so I can't find anything in DBA_HIST. Is there another way for me to find this data after a bounce?

After the db was restarted this showed up in all 3 of the trace directories in a trace file for the ctwr process.

WARNING:io_submit failed due to kernel limitations MAXAIO for process=128 pending aio=0
WARNING:asynch I/O kernel limits is set at AIO-MAX-NR=2097152 AIO-NR=504314

--

Michael Cunningham

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 04 2015 - 18:46:56 CET