Try thousand times could not resolve famous ORA-12514 error

Linux and Unix-Like Linux
1

It is related to Linux, Oracle 9.2.0. I am new to Oracle.

I am trying to connect to Oracle database say A, but as I am running this command dbstart the error is

�ORA-12514: TNS: listener could not resolve SERVICE_NAME given in connect descriptor�

I have successfully started lsnrctl. Here are respective ora files. Please tell me where I am wrong?

My ORACLE_HOME is set as:
/home/oracle/product/9.2.0

Here are my tnsnames.ora, listener.ora & sqlnet.ora files. For clarification database names are "Pana" & "Pana2", host name is "pana".

TNSNAMES.ORA::
PANA2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = pana)(PORT = 1521)

(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = Pana)
)
)

PANA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = pana)(PORT = 1521)
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = Pana)
)
)

LISTENER.ORA:

LISTENER =
(ADDRESS = (PROTOCOL = TCP) (HOST = pana)(PORT = 1521))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DES =
(GLOBAL_DBNAME = Pana2)
(ORACLE_HOME = /home/oracle/product/9.2.0)
(SID_NAME = Pana)
)
(SID_DES =
(GLOBAL_DBNAME = Pana)
(ORACLE_HOME = /home/oracle/product/9.2.0)
(SID_NAME = Pana)
)
)

SQLNET.ORA::
SQLNET.AUTHENICATION_SERVICE = (NONE)
NAMES.DEFAULT_DOMAIN = localadmin
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)
NAMES.PREFERRED_SERVERS =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(PORT = 1521)
(HOST = Pana2)
)
(ADDRESS =
(PROTOCOL = TCP)
(PORT = 1521)
(HOST = Pana)
)
)

2

This error is primarily caused by:

  1. Syntax error or other error in tnsnames.ora make sure parentheses match
  2. HOST string in TNSNAMES does not resolve. you have pana can you tnsping pana?
  3. CONNECT_DATA - I do not see that inyour tnsnames

Instead of me doing this by remote control, you do it using this guide:
ORA-12154 - Oracle FAQ

Check everything, assume nothing on your side is correct.

3

I have corrected the syntax error as:

TNSNAMES.ORA::
PANA2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = pana)(PORT = 1521)

(CONNECT_DATA = (SERVER = DEDICATED))
(SERVICE_NAME = Pana)
)
)

&

have changed SID_DES to SID_DESC in listener.ora.

Also tnsping host pana & the reply is OK.

There is CONNECT_DATA in tnsnames.

Does initorcl.ora has any thing to do with this problem, I have copied & rename "initorcl.ora" as "initPana.ora", will be of great help if you will help me in this regard, it is verry important for me to resove this issue ASAP.

4

Thanks for your cooperation, this problem is resolved. There is another necessary file which play an important role is names.ora which was not there in my machine. I created that file accordingly, & check the syntax in tnsnames.ora, listener.ora & sqlnet.ora.

Thank you all