Q. My Oracle XE installation was working fine, but today I’m getting an error – which read as follows under Ubuntu Linux :
ORA-12154: TNS:could not resolve the connect identifier specified
How do I fix this problem without reinstalling Oracle?
A. Try following solutions:
Make sure DNS / host name resolution working fine
$ host oracle-server-ip
$ host oracle-server-hostname
Make sure /etc/resolv.conf and /etc/hosts and /etc/host.conf configured properly.
Make sure tnsnames.ora is configured properly:
Type the following command to login as oracle / dba user:
$ su - oracle
$ cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/
$ vi tnsnames.ora
Make sure hostname / IP is correctly defined and resolving perfectly:
# tnsnames.ora Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vivek-desktop)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Make sure the host, port and service name specified are correct in listener.ora and above file:
$ cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/
$ vi listener.ora
My sample working file:
# listener.ora Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = vivek-desktop)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Make sure firewall is not blocking TCP ports:
$ sudo iptables -L -n
If everything else failed try to trace problem by running strace:
$ su - oracle
$ strace -o /tmp/debug.sqlplus sqlplus user/password
(adsbygoogle = window.adsbygoogle || []).push({});
