MMNL Issue

[former_member186066]

Hi Guys,

We have this chronic issue of "db hang" on our production database.

It all starts with MMNL running and performing "db file sequential read", which in turn,makes background jobs, check db to hang.

And, within no time, a situation arrives where our production system can be considered dead.

Workaround:

The moment we kill MMNL process from O.S level, things get back to normal.

I am trying to find a permanent fix to this problem but falling short of ideas.

Please suggest on how to troubleshoot.

Thanks and Regards,
Ankit Mishra

[stefan_koehler · Accepted solution]

Hi Ankit,

> Please suggest on how to troubleshoot.

MMNL is part of the AWR framework.However a system state dump is the way to go in case of a stuck Oracle database / instance. I have written a blog post about system state dumps here: and we already used it several times on SCN like here .

If it is completely stuck and you  can not logon with sqlplus the usual way, you can use the "prelim" option to dump the system state.

Regards

Stefan

[former_member186066 · Accepted solution]

Thanks Stefan.

Will go thorugh the blog and share my findings.

[former_member186066 · Accepted solution]

Hi,

Would like to highlight one more point.

There are a lot of inactive sessions in the system.

Screen for reference.

Regards,

Ankit

[Former Member · Accepted solution]

They are ideal and this mean these sessions are waiting for request from SAP to process. If the status is active then query is being processed.

[former_member186066 · Accepted solution]

ok..so you mean to say no issues with that.

[stefan_koehler · Accepted solution]

Hi Ankit,

"SQL*Net message from client" is considered as an idle wait event, which is obviously not true in several cases, but you can ignore it and consider it as "application think time". Everything is alright with it - just go on with the system state dump (level 266) and you will find the reason for the hanging almost certainly (and maybe already the root cause).

Regards

Stefan

[former_member186066 · Accepted solution]

Hi Stefan,

I took statedump(level 266) for the database. Under MMNL process, we see the below:

SO: 0x16eea33c8, type: 78, owner: 0x1fafaec48, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0x1fac0a210, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0

      LibraryObjectLock:  Address=0x16eea33c8 Handle=0x1eff6c248 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=520

Any clue?

Thanks and Regards,

Ankit Mishra

[stefan_koehler · Accepted solution]

Hi Ankit,

sounds great that you were able to create a system state dump, but we need the whole dump not just that snippet to interpret it correctly.

Can you please zip and attach it to this SCN thread? You can also drop me an e-mail (check my SCN profile) with this dump, if you don't want to make the whole dump public.

Regards

Stefan

[Former Member]

have you gone through the note  960417

[former_member186066]

Our's db is Oracle 11g on Linux box.