Oracle Database Connection Error

Cannot Connect to the Oracle Database:

1.Problem: The user cannot connect to the Oracle database, with either the OEM (Oracle Enterprise Manager) or SQL*PLUS. The user's database is in a hung state and he does not have any messages in the alert log.


2.Impact: Very high as the user's database is in a hung state and he cannot perform any functions with the Oracle Database.


3.Solution: The Oracle database can hang for a lot of reasons. Below are the solutions to the problem.

A. The user must check the alert logs and the log files of the listener.

B. Check the server side logs like /adm/syslog and /etc/syslog.

C. The lsnrctl utility can be used to check if the listener is running. If the listener is not up and running then the server cannot detect incoming connections.

D. The user can check the instance by running - ps -ef|grep ora|grep pmon if the database instance is unavailable due to a crash. This would check the process monitor because the user cannot see the alert log in this scenario.

E. In Oracle 11g, a new utility has been introduced in SQL*PLUS called - prelim. This can be used to run oradebug command and utilities which function without actually connecting to the database.

F. After running the oradebug command, the user can use the utility called hanganalyze to diagnose the problem in the hung database.

G. The user can also kill or bounce the instance.

H. The user should ensure that the Oracle environment variables are properly set by looking at the Oracle_SID and the PATH in $ORACLE_HOME.

I. It is possible that due to insufficient Random Access Memory(RAM), the users cannot perform another connection to Oracle. 

J. The users might be trying to connect to the Oracle Database using Background and server processes which are being used by another user.

K. Another possibility is that Oracle cannot find tnsnames.ora file or the tnsnames.ora file does not point to a valid database.

L. So, ensure that the user's path contains the correct path to the tnsnames.ora file and that the file points to the user's database on the specific host machine.