Application team reported that one of their application was having issues and they were not ablet to connect to Oracle database. When I checked the databases it was completely hung and it was not allowing any new connections. I was able to connect database as sysdba. But it was not allowing any queries. Even simple select * from dual got hung. Then I collected hanganalyze dump and reviewed trace file. In the trace file I found that gather stats job was blocking all other sessions. Issue resolved after killing blocker session using spid from OS level.
Hanganalyze
-- Source DB env
$ . oraenv
orcl
-- Connect to DB as sysdba
sqlplus / as sysdba
-- Take hanganalyze dump
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
-- Get trace file name
oradebug tracefile_name
exit
Useful Links:
How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)
No comments:
Post a Comment