Re: DB Unusable due to locking but not sure of the source
Date: Mon, 7 Dec 2015 08:06:29 -0800
Message-ID: <CAPt39tuG44NzwNLYu_C4ArYpPFPzMsdqbdLmG1=JpH=yJ7=PNg@mail.gmail.com>
Thanks to both of you. I've used this info to create a doc to use in the future if it happens again. It's just what I needed.
Michael
On Mon, Dec 7, 2015 at 1:18 AM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
> 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
>>
>>
>>
>
-- Michael Cunningham -- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 07 2015 - 17:06:29 CET