tricky error ora-12154

Oracle Database installation looks pretty simple but we feel frustrated when we have this error popping up while we connect to the DB

ora-12154: tns could not resolve the connect identifier specified

in some versions this can also be in this way ora-12154 : tns could not resolve the service name

the only way you will get this error is because of our great TNSNAMES.ORA and Listener.ORA files

four reasons behind this

• The entry is missing from tnsnames.ora
• The entry in tnsnames.ora is malformed
• The program is using tnsnames.ora from the wrong ORACLE_HOME
• The program is not using a fully-qualified service name, but no default domain is enabled in sqlnet.ora.

i have a simple solution for this . i will give you the sample contents ( major content of ORCL database) for windows platform

Check sqlnet.ora file

must look like this :

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

___________________________________________________________________

check tnsnames.ora file

must look like this:

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = Your_PC_NAME)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

)

(CONNECT_DATA =

(SID = ORCL)

(PRESENTATION = RO)

)

)

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

)

(CONNECT_DATA =

(SID = ORCL)

(PRESENTATION = RO)

)

)

________________________________________________________________

check listener.ora file

Must look like this:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR_PC_NAME)(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (ORCL)

____________________________________________________________________________

if any of your file doesn't match the criteria . please edit it carefully and then save it as .ora file

if you don't find any of these files after installation :) . please create three files using notepad and save it as .ora files with the same names as above .

then login into sqlplus or sql developer . try your luck . believe me you will succeed this time

Regards,

Naga