I second what Stefan has already said. I'd personally use hanganalyze first
and review those tracefiles, and only if that doesn't show me what's going
on, or if I suspect we're dealing with a bug and I'll be filing an SR, I'd
also take a system state dump. System states are intrusive, and can crash
processes. Plus, they can also take a long time to complete; particularly
at higher levels.
Stefan
On Sat, Dec 5, 2015 at 3:59 AM, Stefan Koehler <contact@xxxxxxxx> wrote:
Hi Michael,
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 willbe appreciated.
Sure, just create several hanganalyze and systemstate dumps in a row (with
some wait time in-between). This is also possible, if you can not login
anymore the classical way (keyword prelim option). Afterwards you can
bounce your instance and you still got all the needed information for later
on.
MOS ID #452358.1 describes anything in detail.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Michael Cunningham <napacunningham@xxxxxxxxx> hat am 4. Dezember 2015um 18:46 geschrieben:
morning to solve a locking issue. Truth is I was unable to find the source
Hello all, I cannot figure out why we had to bounce 3 databases this
of
the locking problem, but here is what I'm asking for.... just in case this happens again is there another way for me to query for
I could not query v$locked_object or v$lock to try and investigate. So
locks? This is what I am really asking for help with, but anything willbe appreciated.
far.
Just in case you are curious, here is what I know about the issue so
Oracle 12.1.0.1 running on Redhat Linux 6.5assumed it was blocked for some reason.
1. top showed ctwr (change tracking writer) process at 100% so I
2. I could not find anything in any trace file indicating a problem.DML.
3. v$sqlarea and v$session showed many sessions waiting to complete
4. ASH data was not written during the time of the locking so Ican't find anything in DBA_HIST. Is there another way for me to find this
data
after a bounce?directories in a trace file for the ctwr process.
After the db was restarted this showed up in all 3 of the trace
process=128 pending aio=0
WARNING:io_submit failed due to kernel limitations MAXAIO for
WARNING:asynch I/O kernel limits is set at AIO-MAX-NR=2097152AIO-NR=504314
--
--
Michael Cunningham
//www.freelists.org/webpage/oracle-l