Solved

ORA-12203: TNS:unable to connect to destination

Posted on 2006-07-10
32
4,634 Views
Last Modified: 2008-01-09
Hello Oracle Experts:

I'm having trouble connecting to an Oracle database.

The error I'm getting is "ORA-12203: TNS:unable to connect to destination".

This thread is pretty exhaustive in what needs to be done for this error:
   http://www.experts-exchange.com/Databases/Oracle/Q_10109822.html

But I've still had no luck.  

The following command line script works:
> tnsping80 [service_name]

This leads me to believe that my TNSNAMES.ORA entry is okay.

Any ideas?

Thanks!
Phil / peh803
0
Comment
Question by:peh803
  • 15
  • 12
  • 4
  • +1
32 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076401
How are you connecting to the DB? Sqlplus or ??

Make sure that your ORACLE_HOME is set to the right value in the registry especially if you have multiple installations of oracle.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076437
If you have multiple instances of Oracle, check if the right tnsnames.ora file being used.

Also if your password has special strings like @ you might have a problem.

Alsp try sqlplus /nolog from cmd prompt
Then conn userid@hostname
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076498
Also if you have a sqlnet.ora file under your ORACLE_HOME\admin directory, try renaming it sqlnet_bkp.ora and try connecting
0
 
LVL 19

Author Comment

by:peh803
ID: 17076704
Thanks for the quick help!  

I'm trying multiple ways to connect -- SQLPlus, Toad, ODBC / Access, etc.  All result in the same error.  I will try looking in the registry, and renaming the sqlnet.ora file.  There are no special characters in teh password -- only letters and numbers.

Thanks again!
Phil
0
 
LVL 19

Author Comment

by:peh803
ID: 17076716
Renaming the SQLNET.ORA file to SQLNET_BKP.ORA didn't work ... trying other recommendations now...

Thanks!
Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076744
Do a search for tnsnames.ora on your m/c and see if you get multiple hits.

Decide on which one should be used for your connection and set the registry variable ORACL_HOME accordingly to the respective bin folder
0
 
LVL 19

Author Comment

by:peh803
ID: 17076748
Okay:

I do this:

C:\orant\BIN>sqlplus /nolog
SQL> conn uid@host.domain.suffix:port

And I get this:

ERROR:
ORA-06401: NETCMN: invalid driver designator

Does that help?

Thanks!
Phil / peh803
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076775
Just use
 conn uid@host

Hostname is your tnsnames entry for the db

and not
conn uid@host.domain.suffix:port
0
 
LVL 19

Author Comment

by:peh803
ID: 17076787
ORACLE_HOME on my PC is just c:\orant

Should it be C:\orant\bin ?

Thanks!
Phil
0
 
LVL 19

Author Comment

by:peh803
ID: 17076801
>>Hostname is your tnsnames entry for the db

Ah, okay -- I see.  When I do this, I get
ERROR:
ORA-12154: TNS:could not resolve service name

However, as I said, tnsping80 SID returns "OK".

Regards,
Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076827
No it should be just C:\orant

Ok, now whe you do a search for tnsnames.ora on your m/c, how many hits do u have?
0
 
LVL 19

Author Comment

by:peh803
ID: 17076833
Okay -- now I'm back to the same error in the title of this thread:

ORA-12203: TNS:unable to connect to destination

Before when I got "could not resolve service name", it was because I had tried conn UID@SID instead of conn UID@SID.world.

Thanks!
Phil
0
 
LVL 19

Author Comment

by:peh803
ID: 17076850
I have 3:

C:\orant\NET80\TNSAPI\TNSNAMES.ORA
C:\orant\NET80\ADMIN\TNSNAMES.ORA
C:\orant\NET80\ADMIN\SAMPLE\TNSNAMES.ORA

Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076862
Also try this, go to c:\orant\bin and then type sqlplus /nolog and then use
conn uid@SID
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076879
Which one of it has the entry for your DB?

Also if you have NetManager, try configuring your tnsnames again.
0
 
LVL 19

Author Comment

by:peh803
ID: 17076890
>>Also try this, go to c:\orant\bin and then type sqlplus /nolog and then use
conn uid@SID

I get the same error:
ORA-12203: TNS:unable to connect to destination

However, when I run the initial line, conn UID@sid.world, I am prompted for a password...It is only after I enter the pwd that it presents the error.

Thanks again,
Phil
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17076897
One more suggestion is add the entry for your DB in to both the files

C:\orant\NET80\TNSAPI\TNSNAMES.ORA
C:\orant\NET80\ADMIN\TNSNAMES.ORA

just to see if it's a issue with your tnsnames.ora file. But be sure to keep a back up copy of the original tnsnames.ora
0
 
LVL 19

Author Comment

by:peh803
ID: 17076920
>>Which one of it has the entry for your DB?

I have put the TNSNAMES.ORA entry in all three.

>>if you have NetManager, try configuring your tnsnames again

Is this the same as "Oracle Net8 Easy Config"?  If so, I tried using this, but when I attempt to execute it, I get no response -- nothing happens!  The hourglass comes up for a second or two like it's trying, but nothing else happens.  No processes appear in windows task manager, etc.  Not sure why this is happening.  

I should mention that I have several other database references configured in these TNSNAMES.ORA files and I have no trouble connecting to them using SQL*Plus, Toad, or any other interface...

Thanks again, I really appreciate your quick help!!
Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17077009
Yes I meant  Oracle Net8 Easy Config.

Hmm can you check if the database server is accepting connections from any other m/c? Also check the listener status on the database server.
lsnrctl status

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17077029
One more suggestion, rename your tnsnames.ora to tnsnames.ora.bkp.
 Then create a new file tnsnames.ora, with an entry just for the DB you're having issue with. See if that works. I would recommend using "Oracle Net8 Easy Config", if that doesn't work, only then do it manually.
0
 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 50 total points
ID: 17077930
You can force operating system to use tnsnames.ora file by defining a variable in your environement
such as TNS_ADMIN and put value C:\orant\NET80\ADMIN\TNSNAMES.ORA

Please make sure that service name in your tnsnames.ora file contain correct hostname, sid, port no , etc.

From looking at the thread it appears you have mutliple Oracle homes. Try using Oracle home selector and choose the correct Oracle home or move that Oracle_home at the start of the path.




0
 
LVL 19

Expert Comment

by:actonwang
ID: 17078245
"tnsping <tnsname>" only tests if your listener is ok not your real database connection.

>>ORA-12203: TNS:unable to connect to destination

it  likely means that your database is not running or not running correctly. Check to see if the database this tnsname points to is up and running correctly and also register itself correctly with listener (use "lsnrctl services" command).


0
 
LVL 19

Expert Comment

by:actonwang
ID: 17078251
To support my point, here is an excerprt from oracle net service document:

/////////////////////////////
Using TNSPING

To invoke the TNSPING utility, enter the following:

tnsping net_service_name [count]

....

If the net service name specified is a database name, TNSPING attempts to contact the corresponding listener. It does not actually determine whether or not the database itself is running. Use SQL*Plus to attempt a connection to the database.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17078253
that should take you to the right direction. let me know if you have more questions.
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 50 total points
ID: 17078258
Just did a test in my local laptop, i shutdown my database but i still connect "tnsping <sid>" ok because my listener still running. but by issuing:

       lsnrctl services

I didn't see my instance up and running.
0
 
LVL 19

Author Comment

by:peh803
ID: 17081790
@actonwang:

Thanks for your help with this.  In this case, I have indeed been able to connect to the database using a java client (SELECT * FROM TABS works, etc.), but I cannot seem to connect to the database using an oracle client like TOAD, SQL*Plus, etc.  

I am in a huge corporate environment where our development team (us) does not have direct access to the database -- we have to go through a database team, and even so, I'm sure the db is up and running.  

This is why I'm having such trouble with this!  Anyways, thanks for the comments.  I'll keep reviewing the other comments written and see if I can try anything else.

Thanks again,
Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17094176
Did you try to configure your TNSNAMES.ora using Oracle Net8 Easy Config?
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
ID: 17095619
One more suggestion try this from command prompt try this

sqlplus userid@"userid@"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Server Name>PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <SID>)))"/password

And see if this works


0
 
LVL 19

Author Comment

by:peh803
ID: 17095623
>>Did you try to configure your TNSNAMES.ora using Oracle Net8 Easy Config?

Thanks for following-up.  I was not able to launch Oracle Net8 Easy Config -- any reason that you know of why this might happen?  

Thanks..
Phil
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17095674
Did this work?
sqlplus userid@"userid@"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Server Name>PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <SID>)))"/password

Not really sure what's happening wit your net* config. Will see if I can find something
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17095703
0
 
LVL 19

Author Comment

by:peh803
ID: 17107910
Thanks to all for the help with this.  

I was never able to connect to the database, but I believe that's due to some settings on the database server that does not allow connections from my machine.

As far as why net8 easy config isn't working, I'm not all that concerned with that.  I've consistently been able to make TNSNAMES.ORA entries manually, and I'll just go ahead and continue to do that.

I learned a lot from you guys, and I appreciate the quick responses and effort.  

Regards,
Phil / peh803
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now