Sunday, 24 November 2013

Oracle database hang new connections not allowed

what is sqlplus -prelim : This is the backdoor entry method to access database (only SGA) in a hung situation 
 where no other methods are allowing to create new sessions in database.
 This will not create any session in DB rather connect to SGA directly ...

#lsnrctl status  >> Fine

#ps -ef | grep ora_   >> Fine

# sqlplus / as sysdba  >> database not accepting

# sqlplus /nolog
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
SQL> oradebug setmypid
SQL> oradebug hanganalyze 12


cat /oracle/diag/rdbms/dbtalip/TALIPDB/trace/TALIPDB_ora_32239.trc 
*** 2012-06-11 12:14:02.870
  instances (db_name.oracle_sid): dbtalip.talipdb
  oradebug_node_dump_level: 12
  analysis initiated by oradebug

Search inside the trace file you will find the process number which hanged the database:
os id: 1981process id: 129, oracle@fmsdb (TNS V1-V3)
session id: 174
session serial #: 147681

# kill -9 1981

Done .....................

No comments:

Post a Comment

Number of Visitors