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;