www.dbaref.
com
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's >
DBA's Forum, Routine Tasks and Activities >
Database Hung! what to do?
posted Apr 25, 2012, 8:14 AM by Sachchida Ojha [ updated Apr 25, 2012, 9:08 AM ]
Development team reported that your oracle database is hung. Users can't
log in and exiting users can't complete their transaction. The DBA's with
SYSDBA privileges may also be unable to log into the database. Your
challenge is to troubleshoot the problem what is causing this database to
hang and then fix the problem ASAP.
Step 1: Check the alert log to see if database has reported any errors, which
may indicate why the database is hanging.
Step 2: See if you can get AWR or ASH report or query some of the ASH
views. You may notice events such as hard parses at the top of the load
profile section of the AWR report, indicating that this is what is slowing down
the database.
Step 3: As a DBA you might know that a single poorly written adhoc query
has potential to bring an entire database to its knees. See if you can identify
one or more poorly written SQL that may be leading to the hung database.
Step 4: See if large expdp/impdp operation running in parallel mode
consuming all database resources.
Step 5: Check the database for blocking locks and latch contention.
Step 6: Check the server memory usage and CPU usage. Make sure that the
sessions are not stalling because of low sized PGA. (not required if u
configured AMM)
Step 7:See if it is caused by filling up of all archive log destination. If Archive
destination is full, database will hand. Here in this case you can connect as
sys and make room for archive log. You can also changed the archive log
destination temporarily and then clean up the original destination and then
change the archive log destination pointing to original one.
Step 8: Check the FRA. A database hangs when it is unable to write
flashback database logs to the recovery area. You can fix this problem by
increasing the size of the FRA using the command,
SQL>alter system set db_recovery_file_dest_size=10GB;
If you are still unable to resolve the reasons for the hung database and you
are also unable to connect to the database as SYSDBA then here is another
option to use.
Use prelim option. The prelim option does not require a database
connection. You can not use prelim option if you are already connected to
the database.
[oracle@usha ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 25 11:54:02 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL>
You can also use
[oracle@usha ~]$ sqlplus -prelim "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 25 11:55:39 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Now you can use oradebug hanganalyze command to
analyze the hung database.
SQL> oradebug hanganalyze 3
in RAC env
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
You can repeat the oradebug hanganalyze command mutiple times to
generate dump files for varying process states.
When you call Oracle Support about this issue, they may ask you to attach
logs for process state dump also called systemstate dump to
analyze hung database.
systemstate dump report shows "what processes are
doing and the resource currently they are holding "
.
Commands to get systemstate dump (NON RAC ENV)
SQL>oradebug setmypid
SQL>oradebug dump systemstate 266
RAC ENV
SQL>oradebug setmypid --- You can aslo specify the process ID other than
yours oradebug setmypid <pid>
SQL>oradebug ulimit
SQL>oradebug -g all dump systemstate 266
If you try to issue this command without setting the PID you get ORA-00074.
Comments
You do not have permission to add comments.
View as Desktop My Sites
Powered By Google Sites
