Hangs

Connections to DataBase Hang Including “/ as sysdba”

Recently I faced one issue where all the connection to database hung and it was also not possible to login to database using “/ as sysdba”.
To get access of sqlplus I used the following syntax:

$ sqlplus -prelim / as sysdba

With “prelim” option we can run some commands which will help in collection useful information about the problem.

This will work only in Oracle 10g and higher version.

After successfully getting connected run the following commands to generate Hanganalyze and systemstate traces:

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 266

SQL> oradebug tracefile_name

— This will give you the name of the tracefile generated.

SQL > oradebug dump hanganalyze 2

SQL > oradebug tracefile_name

To analyze these trace files one should be aware of Metalink Note: 215858.1.

After analyzing these files I found that following event was active and causing the hang:

<span style="font-family: arial,helvetica,sans-serif;"><span style="font-size: small;">"resmgr:cpu quantum"
Cmd: PL/SQL Execute

It means that the sessions are waiting for their turn on CPU.

This event occurs when resource manage is active and controls the allocation of CPU to processes.

We can also see the command which is causing all this: i.e some PL/SQL code was executing and spnning on for CPU.

After finding out this, checked with “TOP” command, got the PID of the process consuming all the cpu and killed that process with “kill -9”

After killing that process the users were able to connect.

So the cause of the Hang was found i.e PL/SQL, but it is still unknown why  PL/SQL caused problems. 🙂

Cheers!!!

Saurabh Sood

7 April, 2009 by 10g 2

Resolving Shutdown Immediate Hang Situations

Many times Oracle DBA’s are faced with a situation where shutting down the instance with shutdown immediate seems to take infinite time and gives a impression that the session is hung. In most of the cases this is not a hang. Hang can be defined as a scenario when few sessions/processes are waiting on some action and in turn blocking other session/processes to complete their tasks. Now the original sessions could be waiting because of resource issues or Oracle Bugs.

Shutdown immediate can take long time to complete (appear to be hung) because of three reasons:

1. Uncommitted transactions are being rolled back.

2. SMON is cleaning temp segments or performing delayed block cleanouts.

3. Processes still continue to be connected to the database and do not terminate.

1. Uncommitted transactions are being rolled back:

This is the case when the message ‘Waiting for smon to disable tx recovery’ is posted in the alert log after we issue shutdown immediate.

There are two reasons for this:
– A large query was running at the time of shutdown immediate.
-A large transaction was running at the time of shutdown immediate.

For large queries:

SQL > select count(*) from v$session_longops where time_remaining>0;

If it returns a value > 0 then we can do a shutdown abort and then startup restrict and then again shutdown immediate.

For large transactions:

SQL > select sum(used_ublk) from v$transaction;

If it returns a large value then we have to wait for a long time for shutdowm to get completed.
If the large transaction is aborted and then shutdown is issued then we have to query v$fast_start_transactions and v$fast_start_server, we will not see anything in v$transaction at this time.

At this particular moment transaction recovery is going on and the count(*) will keep on decreasing:

SQL > select count(*) from v$fast_start_transaction;

Decreasing count will show that recovery is going on and when the recovery is completed the database will be shutdown.

But it is not desirable under some circumstances such as, when we have very short maintance window and we need to perform a shutdown immediate to do some work, in those cases we can use the following event and set in the init.ora file TEMPERORARLY To disable transaction recovery:

event=”10513 trace name context forever, level 2″

and bounce the instance and issue shutdown immediate to get complete without transaction recovery.SMON will not do a transaction recovery untill this event is set in the init.ora file so it is necessary to remove this event whenever you get a chance to shutdown the database again, this time shutdown immediate can even take 3-5 hours(Just remove this event from pfile).

2. SMON is cleaning temp segments or performing delayed block cleanouts:

During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON cleans up extents which are no longer needed and marking them as freed. It means that count from uet$ will decrease and count in fet$ will increase.

To verify that the temporary segments are decreasing have an active session available in SQL during the SHUTDOWN IMMEDIATE. Run query to ensure the database is not hanging, but is actually perform extent cleanup:

SQL> select count(block#) from fet$;
COUNT(BLOCK)
----------
115

SQL> select count(block#) from uet$;
COUNT(BLOCK)
----------
713

After some time, issue the query again and check the results:

SQL> select count(block#) from fet$;
COUNT(BLOCK)
----------
210

SQL > select count(block#) from uet$;
COUNT(BLOCK)
----------
512

If you do not have sufficient time to wait for this cleanup then you can set the following event and bounce the database and reissue shutdown immediate to skip this cleanup:

event=”10061 trace name context forever, level 10″

It allows you to prevent SMON from cleaning up temporary segments. Again it is not recommended to set this event event forever. Whenever you have large downtime remove this event and allow SMON to do its work.

3. Processes still continue to be connected to the database and do not terminate:

After issuing shutdown immediate, If we see entries in alert log file as:

Tue Jan  8 12:00:27 2008
Active call for process 10071 user 'oracle' program '[email protected] (J001)'
SHUTDOWN: waiting for active calls to complete.
Tue Jan  8 12:00:57 2008

SHUTDOWN: Active sessions prevent database close operation

It shows that there are some active calls at program ‘[email protected] (J001)’ which pmon is not able to clear up.This message is due to the fact that database is waiting for pmon to clean up processes, but pmon is unable to clean them. The client connections to the server are causing the shutdown immediate or normal to hang. Do the following in this case:

1. Before shutdown immediate, shutdown the listener:

$ lsnrctl stop

2. Now check if there are any connection present at the database as:

$ ps -eaf | grep LOCAL

It will give you the OSPIDs of the client connected to database.

3 Manually kill them as:

# Kill -9 <OSPID>

4. Issue shutdown immediate now.

Do not forget to bring up the listener after startup 🙂

In addition to this you can set 10046 event in the session used to shutdown the instance. This will help to tell the event on which session is waiting

SQL>alter session set events '10046 trace name context forever, level 12'

SQL>Shutdown immediate;

Look for the trace file in user_dump_dest location. Also look at the alert.log for any other messages. They might be helpful in case the shutdown is experiencing hang situation.

26 May, 2008 by Featured 7

Diagnostics For Database Hang

Many times Oracle DBA’s are in a situation when the database is hung and does not seem to be responding. In some scenarios, state is such that you cannot even connect to the sqlplus session. Majority people restart the database (Sometimes I wonder if this is due to the fact that most of us started working on Computer’s on Microsoft Windows 🙂 ) and then log a ticket with Oracle support . They inturn happily inform us that “They do not have any diagnostic information to diagnose and resolve the issue and we need to wait for next occurrence to collect some diagnostic information)

Based on my experiences , I am writing this article to assist my fellow Oracle DBA’s to diagnose the problem and collect the required information. So Let’s Start.

1) First of all we need to ensure that this is really a database hung situation and not a slow database condition. This can be done by asking some questions to users.

a) Is a particular user complaining of database hang or its the condition for all the users. If one or few user are reporting then are these users executing a batch job?

b)Are you able to make new connections to database?

c)Also check if any initialisation parameter has been changed recently?

d)Check if any resource manager plan is in effect.

One more way to establish if database is hung is t0 try to query v$session_wait view to find events being waited on

select sid,event,seq#,p1,p2,p3 from V$session_wait where wait_time=0 and event not like ‘%message%’;

This will give the events for all the waiting session. In case you see something like ‘log file switch (archiving required)’ then this problem is caused by archiving issue. See if there is free space in archiving destination.

Suppose this gives events like row cache enqueue or latches, then we need to gather Hanganalyze and Systemstate for the support.

Else it could be that you are experiencing a slow database. In this case use AWR or statspack to diagnose the issue. Look out for top timed events. In case you see Library Latch or shared pool latch consuming lot of time, then look at the Hard parses per sec section in Load profile.

2)Look at database alert log and see if any messages are present. In case you are facing Latching or Enqueue issues, then you might see errors like below

PMON failed to acquire latch, see PMON dump

Errors in file /u01/BDUMP/test10_pmon_12864.trc:

In this case you are required to upload tracefile (reported in alert log) to Oracle support.

Note : -Make sure that max_dump_file_size is set to unlimited so that the tracefile contains the complete data.

Coming back to Hanganalyze and Systemstate. Find details for them below

A)Hanganalyze

HANGANALYZE is used to determine if a session is waiting for a resource, and reports the relationships between blockers and waiters.

Use following syntax and take hanganalyze from two sessions at interval of 1 min

SQL>sqlplus “/ as sysdba”

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug hanganalyze 3

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support.

Alternatively you can use

SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3’;

In case you wish to understand how to interpret hanganalyze file, then use Metalink Note:215858.1: Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

B)Systemstate

Systemstate is used to dump the process information which is useful for Oracle support to diagnose why the sessions are waiting.

For 9.2.0.6 and above gather systemstate as below

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug dump systemstate 266

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support. Perform this 2-3 times at interval of 1 min.

Again you can use

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266’;

For Oracle 9.2.0.5 and less use level 10 instead of 266

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266’;

Level 266 includes short stacks (Oracle function calls) which are useful for Oracle Developers to determine which Oracle function’s are causing the problem. This is also helpful in matching existing bugs.

In case you are unable to connect to database then capture systemstate using below note

Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

Apart from this, following information can also be captured

a)Database alert log

b)AWR report /statspack report for 30-60 min during database hang

c)Output of OS tools to ensure that everything is fine at OS level.

E.g

$vmstat 2 20

This will capture 20 snapshots at 2 seconds interval. Look for CPU contention or swapping issues

In Addition to above , you can use utility called LTOM which has got some predefined rules based on which it determines that database is in Hung situation and takes systemstate and hanganalyze dump automatically .

Please refer to following Metalink note for more details

Note:352363.1: LTOM – The On-Board Monitor User Guide

In case you are able to narrow down to a blocking session manually, then you can very well take errorstack for the blocking process as below

connect / as sysdba

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

oradebug tracefile_name

* In case the ospid for blocking session is 1234

Last command will report the tracefile name which has to be uploaded to Oracle support.

In case you are able to capture the above information, you stand 99% chance of getting solution . I have kept 1 % for the cases when Oracle Support will ask for setting up some events and waiting for Next Hang Occurence for getting more information.

21 March, 2008 by Performance 6

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers