ORA-12154: TNS: could not determine the specified connection identifier

Question

I am trying to connect to oracle 11g installed on Linux EL 5 and get 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 on the network / admin looks like this

 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) ) ) 

The status of MY lsnrctl is 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 

Answer 1

Is it possible to successfully execute ping ud06 (like ud06, not ud06.us.server.com)?

What does the team do

 lsnrctl services 

show?

EDIT: It seems to me that maybe the database instance name is actually not "orcl"? What the lsnrctl service outputs indicates that the orcl service, although it is defined in the listener.ora file, is not actually running.

Can you log in with a direct connection to the server? If so, what do you use as the value of the environment variable ORACLE_SID? Log in as a SYS user and run the command:

ALTER SYSTEM REGISTER;

Then run the lsnrctl services command again and see if an additional instance appears.

In addition, as Alex points out, the tnsping command reports the full name of the service. Edit the sqlnet.ora file and set the NAMES.DEFAULT_DOMAIN parameter to NULL if it has a value.

EDIT 2: Does tnsping ud06 work on the server? Or my assumption that the client and server on different systems are wrong?

Answer 2

The answer to this problem is very simple. Don't worry about .ora files or any other configuration. Oracle does it all just perfectly.

Only when connected via the command line does it get confused with passwords that have the @ symbol.

Therefore, when connecting via the SQL command line, do not use a password with "@" in it. Just use the web interface to create an account with a password without the "@" symbol.

It!! The problem is solved. I’ve been racking my brains for several days, and now my problem is solved.

Answer 3

SERVICE_NAME at the output of tnsping does not match the entry in tnsnames.ora ; Is this a file from a Windows or Linux window? It looks like you don't have a local (Windows) tnsnames.ora entry for u06 , and it guesses that the service name should expand. I think that means a link to the host name adapter.

Answer 4

I also came across ORA-12154: TNS: could not resolve the specified connection identifier and add a user trying to connect to the oinstall oracle group fixed by him.

Answer 5

first try running an instance of oracle:

for windows:

start-> configuration panel-> administration touls-> Services-> ORACLESERVICEORCL (my example, by the way) → start

Answer 6

Enter the connection string as follows:

ServerName: port / ServiceName; Username; Password

Answer 7

I also ran into the same error, try this code:

 SQL> conn hr/hr @pdborcl; 

and if you find the same error, it means that you have a different name for the pluggable database. Check the name of the pluggable database by simply entering the following command in sqlplus

 sql> SELECT name, con_id FROM v$pdbs; 

Answer 8

I fixed this problem using these steps.

First of all, this error occurred if you did not install the same directory or disk.

But the answer is here.

• Login window as administrator.
• Go to Control Panel.
• System Properties and click Enviroment

• Find the OS variable and change the name as "TNS_ADMIN"
  
  

• And change the value as "tnsnames directory address"

• Reboot the system.

• Congrulations.