Re: DB Unusable due to locking but not sure of the source

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 7 Dec 2015 16:18:16 +0700
Message-ID: <CAP50yQ-t9Oz56z=en=E3AiNUztW5cbJX-0z4+w6LttqtBBM2qQ_at_mail.gmail.com>

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_at_soocs.de> 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 will
> be 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: _at_OracleSK
>
>
> > Michael Cunningham <napacunningham_at_gmail.com> hat am 4. Dezember 2015
> um 18:46 geschrieben:
> >
> > 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 07 2015 - 10:18:16 CET