I want to talk about a situation that happened to me a few days ago. I Logged in to one of my databases and my connection was hanged. My database was not accepting new connections. Now bound to the database server and I checked the status of the listener.
# lsnrctl status
Everything was normal. The listener is listening to my database. So I didn’t get any error from listener. I tried to log in with “sqlplus / as sysdba” but I failed. Connections on the server was also hanging. I checked the background processes.
# ps -ef | grep ora_
Everything was normal. And I learned something I did not know until that day. The backdoor entry of the Oracle database! You can enter to the database through the back door using SQL * Plus tool with “Prelim” parameter:) Prelim, directly connects to the SGA but it does not open a session.
You can connect to the database with Prelim as following
# sqlplus -prelim / as sysdba
# sqlplus /nolog
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
Now, you can analyze the SGA using oradebug command.
SQL> oradebug setmypid
SQL> oradebug hanganalyze 12
A trace file will be produced in the directory configured in user_dump_dest parameter. If we are to sort the files according to time we can get the most recent trace file.
# ls -ltrh
You can also get the trace file name with oradebug command.
SQL> oradebug TRACEFILE_NAME
When we examine the trace file, we see that lines beginning as follows.
*** 2012-06-11 12:14:02.870
instances (db_name.oracle_sid): dbtalip.talipdb
analysis initiated by oradebug
If we continue to examine the trace file We can see the details of the session that caused the problem.
os id: 981
process id: 29, oracle@dbtalip (TNS V1-V3)
session id: 74
session serial #: 47681
When we kill that session from OS, everything will be OK.
# kill -9 981
Talip Hakan Öztürk