Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORA-12154: TNS:could not resolve the connect identifier specified

I am trying to connect to oracle 11g installed on Linux EL 5 and and getting the following error

SQL> connect sys/password@ud06 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

my listener.ora under network/admin is as follows

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ud06)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (SID_NAME=orcl))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (PROGRAM=extproc)))

MY tnsnames.ora is as follows

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

UD06=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ud06)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

MY lsnrctl status shows as follows:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-FEB-2010 16:23:06
Uptime                    0 days 0 hr. 12 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11g/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/11g/log/diag/tnslsnr/ud06/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
like image 751
user275705 Avatar asked Feb 18 '10 00:02

user275705


People also ask

What is could not resolve the connect identifier specified?

This is one of the most common Oracle errors and it is basically telling you that you are trying to connect to a database name that is unknown to the system. The most common cause are typos in your connection string or network configuration files.

How do I resolve TNS issue?

Go to the $ORACLE_HOME/network/admin directory and open the tnsnames. ora file and look for the connect identifier, verify that it's correct. You could have a typo or be missing the connection details in the tnsnames. ora file (on windows %ORACLE_HOME%\network\admin).

Where is TNS ORA file located?

By default, the tnsnames. ora file is located in the ORACLE_HOME/network/admin directory.


1 Answers

The answer to this problem is very simple. Do not worry about the .ora files or any other configuration. Oracle does all these just perfect.

Only while connecting via command line, it gets confused with passwords that have a @ symbol in them.

Therefore while connecting through command line SQL, do not use a password with a '@' in it. Just use the web interface to create an account having a password without an '@' symbol in it.

That is!! Problem solved. I had been breaking my head for quite a few days, and now my problem is solved!!

like image 86
Arun Thundyill Saseendran Avatar answered Sep 25 '22 20:09

Arun Thundyill Saseendran