Skip to Main Content
  • Questions

  • Oracle is hanging after few days (intermittent) and cannot connect as user sys even with prelim option and lsnrctl do not work too

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

Asked: May 09, 2017 - 3:28 pm UTC

Last updated: May 11, 2017 - 3:29 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

I have several clients that is having this intermittent issue where Oracle just hang.

Only solution is stop and start the win DB service and sometimes it needs to be done twice.

When the DB is hanging both CPU and memory are good.

I tried connecting below but none of them work

connect / as sysdba

sqlplus -prelim “/as sysdba”

After restating the service and connection is ok again.

I can use the prelim option but when I tried below I get this error
oradebug setmypid
oradebug hanganalyze 12

ERROR: Can not perform hang analysis dump without a process
state object and a session state object.
( process=0x000007FC525D4348, sess=0x0000000000000000 )


This is a production server and client have been doing reboot at least once a week.

Is there a way to create a trace file so we can figure out at least why its hanging?

Thanks

and Connor said...

Check out the MOS note

Troubleshooting Database Hang Issues (Doc ID 1378583.1)

It is a comprehensive list of options you can explore to gather information about a system in a degraded/hung state.

Some of the options may not work depending on the severity of the problem but there's plenty of options covered.

ALso, perhaps look at increasing the frequency of AWR snapshots so at least if the hang analysis cant be done, then you get some information closer to the point of failure and see if any patterns can be found (resources being exhausted etc)


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.