• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4739
  • Last Modified:

ORA-12203: TNS:unable to connect to destination

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
peh803
Asked:
peh803
  • 15
  • 12
  • 4
  • +1
3 Solutions
 
sathyagiriCommented:
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
 
sathyagiriCommented:
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
 
sathyagiriCommented:
Also if you have a sqlnet.ora file under your ORACLE_HOME\admin directory, try renaming it sqlnet_bkp.ora and try connecting
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
peh803Author Commented:
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
 
peh803Author Commented:
Renaming the SQLNET.ORA file to SQLNET_BKP.ORA didn't work ... trying other recommendations now...

Thanks!
Phil
0
 
sathyagiriCommented:
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
 
peh803Author Commented:
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
 
sathyagiriCommented:
Just use
 conn uid@host

Hostname is your tnsnames entry for the db

and not
conn uid@host.domain.suffix:port
0
 
peh803Author Commented:
ORACLE_HOME on my PC is just c:\orant

Should it be C:\orant\bin ?

Thanks!
Phil
0
 
peh803Author Commented:
>>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
 
sathyagiriCommented:
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
 
peh803Author Commented:
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
 
peh803Author Commented:
I have 3:

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

Phil
0
 
sathyagiriCommented:
Also try this, go to c:\orant\bin and then type sqlplus /nolog and then use
conn uid@SID
0
 
sathyagiriCommented:
Which one of it has the entry for your DB?

Also if you have NetManager, try configuring your tnsnames again.
0
 
peh803Author Commented:
>>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
 
sathyagiriCommented:
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
 
peh803Author Commented:
>>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
 
sathyagiriCommented:
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
 
sathyagiriCommented:
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
 
mohammadzahidCommented:
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
 
actonwangCommented:
"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
 
actonwangCommented:
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
 
actonwangCommented:
that should take you to the right direction. let me know if you have more questions.
0
 
actonwangCommented:
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
 
peh803Author Commented:
@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
 
sathyagiriCommented:
Did you try to configure your TNSNAMES.ora using Oracle Net8 Easy Config?
0
 
sathyagiriCommented:
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
 
peh803Author Commented:
>>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
 
sathyagiriCommented:
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
 
sathyagiriCommented:
0
 
peh803Author Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 15
  • 12
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now