Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Debugging a hung database in Oracle 11g

Debugging a hung database in Oracle 11g

Back in Oracle 10g a hung database was a real problem, especially if the DBA could not connect via SQL*Plus to release the source of the hanging.  In these cases, the DBA had few options other than to force-down the instance and warmstart it.

There is a new feature in Oracle 11g SQL*Plus called the “prelim” option. This option is very useful for running oradebug and other utilities that do not require a real connection to the database.

 sqlplus –prelim

SQL>

or

SQL> set  prelim on

SQL> connect / as sysdba

At this point you are free to run the oradebug commands to diagnose a hung database issue using the new hanganalyze utility:

SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
Open a separate SQL session and immediately generate a system state dump.
SQL> alter session set events ‘immediate trace name SYSTEMSTATE level 10′;
You can also gat a trace dump to identify hung session details in Oracle Real Application Clusters (RAC) as follows:

SQL> oradebug setmypid
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
… Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3
There are several other ways to find the root cause of an Oracle hanging issue:

Automatic Session History – Check ASH during the hang (if you have purchased the license):

Report – Run a couple of STATSPACK or AWR snapshot during the hang and get an elapsed time report.

Check for locking – Whenever Oracle is has a session hangs waiting on a resource, this information can be found in the v$session view in the row_wait_file# and row_wait_block#.

Column host format a6;
Column username format a10;
Column os_user format a8;
Column program format a30;
Column tsname format a12;

select
b.machine host,
b.username username,
b.server,
b.osuser os_user,
b.program program,
a.tablespace_name ts_name,
row_wait_file# file_nbr,
row_wait_block# block_nbr,
c.owner,
c.segment_name,
c.segment_type
from
dba_data_files a,
v$session      b,
dba_extents    c
where
b.row_wait_file# = a.file_id
and
c.file_id = row_wait_file#
and
row_wait_block# between c.block_id and c.block_id + c.blocks – 1
and
row_wait_file# <> 0
and
type=’USER’;

July 14, 2009 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: