Question

ORA-12560: TNS: protocol adaptor error

Asked by: ijeevan

Hi  Am new to Oracle.
Ive installed and configured Oracle 11g Server in Oracle Enterprise Server 5.
Ive started necessary services and I was able to log in to SQL Plus from the server.
Now, Ive installed Oracle client in Windows XP Prof with SP2.
Ive configured tnsnames.ora and listener.ora.
I can connect to database in the server using SQL Developer. But when I am trying to connect via SQL PLUS and am getting

ORA-12560: TNS: protocol adaptor error

I can make a tnsping to the server from client.

My tnsnames.ora in client

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OraSrv1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

My listner.ora in client


SID_LIST_ORCL =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_g)
      (SID_NAME = ORCL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclient)(PORT = 1521))
  )


Any help in resolving this issue will be greatly appreciated. Thank you in advance.

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-01-12 at 02:18:00ID24043576
Tags

Oracle 11g Client error

Topic

Oracle 11.x Database

Participating Experts
2
Points
500
Comments
30

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Oracle TNS troubles
    Hi - I'm installing Oracle 8 on NT 4.0 and have TNS troubles, as well as some general questions ... When I try: sqlplus scott/tiger it works fine. But when I try: sqlplus scott/tiger@ORCL I get: ERROR: ORA-12154: TNS:could not resolve service name Questions ... 1. H...
  2. TNS
    How do I trace TNS on client. What does this error message mean. ora-12571 : TNS Packet Writer Failure. I get this message when there is no activity for a couple of minutes. I checked profiles, listener_timeouts. I think it could be the line to the server which is causing th...
  3. oracle error of ORA-12538 and ORA-12514  erro…
    hello friends, iam getting an oracle error of ORA-12538 and ORA-12514 errors of (TNS PROTOCOL ADAPTOR ERROR). please send the exact formats of tnsnames.ora file and listener.ora file formats. information about my database ourdominname is aperc servername is apecdev oralce...
  4. TNS
    ADO Error #1: ORA-12571: TNS Packet writer Failure We get the error when trying to log in. It was working fine on this pc until about 3 weeks ago. The user remembers getting a strange error during a standard virus scan, and after that was never able to log into the applicati...
  5. TNS Listener
    I have installed ther Oracle 8.1 and when I tired to launch the application its keep giving me error "TNS Listerner could not resolve the service name".. Any thoughts ? Thanks

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: MilleniumairePosted on 2009-01-12 at 02:34:27ID: 23352169

This issue may be caused because you have multiple Oracle Homes on your PC.  SQL Developer may be using one Oracle Home and Sql*Plus another.

Each Oracle Home will have a Network\Admin directory which will contain a tnsnames.ora file.

Try comparing these tnsnames.ora files across Oracle Homes and you'll probably find that the one used by SQL*Plus does not contain the entry for your 11g database.

You can fix this by simply ensuring ALL tnsnames.ora files on your PC are the same.  Alternatively you can define a Windows environment variable to identify which Network\Admin directory structure to use.

Check out the following for more details:
http://www.experts-exchange.com/Database/Oracle/Q_20333603.html?sfQueryTermInfo=1+%22tn+admin%22

 

by: sdstuberPosted on 2009-01-12 at 04:20:48ID: 23352616

you don't need a listener.ora on your client,

what does the listener.ora on your server look like?

 

by: ijeevanPosted on 2009-01-12 at 04:45:04ID: 23352740

Here is the server Listener.ora

 


LISTENER =
 (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
 )  

 SID_LIST_LISTENER=
  (SID_LIST=
(SID_DESC=
         (GLOBAL_DBNAME=orcl)
         (SID_NAME=orcl)  
          (ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1/)
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
   )

 

by: sdstuberPosted on 2009-01-12 at 05:22:47ID: 23352949

I reread the question and want to confirm, you used sqldeveloper from the server?  or from the client?

If from the server, that doesn't help the diagnosis.
If from the client, then, as suggested above, check for multiple oracle homes and check you used the same connection information for sql developer as you are using for sql plus.

 

by: ijeevanPosted on 2009-01-12 at 05:49:11ID: 23353112


Ive searched, but there is only tnsnames.ora. What I could notice is there is SQL PLUS inside SQL DEVELOPER application and I am able to log-in to the database using that SQL PLUS but not the one from Start-->Programs-->Oracle home-->Application Development-->SQL PLUS.
 
Will this help to resolve the problem?
Thank you in advance.

 

by: sdstuberPosted on 2009-01-12 at 05:52:38ID: 23353143

that seems to support the theory that you have more than one oracle home.

what is your PATH?

where is the sqlplus you are invoking? (check both the command line and what is invoked via sqldeveloper)

 

by: MilleniumairePosted on 2009-01-12 at 05:54:14ID: 23353156

If you right click and chose properties for the Start-->Programs-->Oracle home-->Application Development-->SQL PLUS this should tell you where it is running SQL*Plus from, which will identify the directory structure under which SQL*Plus is installed.  Is this a different directory structure to which SQL DEVELOPER is installed?  At the top level of this structure is there a Network\Admin directory and if so is there a tnsnames.ora file in it?  If not, try copying your other tnsnames.ora into this Admin directory.

Can you connect to any databases from SQL*Plus?

 

by: ijeevanPosted on 2009-01-12 at 05:58:21ID: 23353180

When I invoke SQL PLUS from Start-->Programs-->Oracle home-->Application Development-->SQL PLUS, it gives me the TNS error.
Where as if I invoke from Start-->Programs-->Oracle home-->Application Development-->SQL Developer
then Tools-->SQL PLUS, it works.
Where am I standing in this error!
Thank You

 

by: ijeevanPosted on 2009-01-12 at 06:02:37ID: 23353220

Milleniumaire: Both SQL PLUS are launching from same D:/app/product/11.1.0/client_1/....
There only 2 tnsnames.ora in my enitre workstation. One is in D:/app/product/11.1.0/client_1/network/admin and another is in D:/app/product/11.1.0/client_1/network/admin/sample.
Thank You.

 

by: ijeevanPosted on 2009-01-12 at 06:31:04ID: 23353448

To add more, I dont have TNS_Admin entry in my registry. Will this help me to get on to this error?

 

by: MilleniumairePosted on 2009-01-12 at 06:36:49ID: 23353497

Are the two copies of SQL*Plus the same i.e. are the menu shortcuts pointing to the same executable?

 

by: MilleniumairePosted on 2009-01-12 at 06:38:28ID: 23353508

As you only have one Oracle Home containing one copy of tnsnames.ora, then setting up the TNS_ADMIN environment variable probably won't make any difference.

 

by: sdstuberPosted on 2009-01-12 at 06:41:04ID: 23353533

what is the PATH?

 

by: ijeevanPosted on 2009-01-12 at 06:42:00ID: 23353539

Yes, they are same. Both are in D:/app/product/11.1.0/client_1/BIN.

Hope this is driving strange for you people. I can understand there is a thin line.

 

by: MilleniumairePosted on 2009-01-12 at 06:53:44ID: 23353637

It doesn't make sense that two different options on the start menu run exactly the same code, but one gives an error!  Are there any differences between the properties of these two menu items?

 

by: sdstuberPosted on 2009-01-12 at 07:04:46ID: 23353725

how are you connecting in sqlplus (stand alone) vs sql developer?

Are you using TNS in sql developer or "Basic" or "Advanced"  connection type?

 

by: ijeevanPosted on 2009-01-12 at 07:05:48ID: 23353731

:-)

No there are no difference.

 

by: ijeevanPosted on 2009-01-12 at 07:06:45ID: 23353741

I am using Basic in Connection type.

 

by: sdstuberPosted on 2009-01-12 at 07:09:20ID: 23353763

ah!!  basic bypasses tnsnames.ora lookup and goes directly to the server.

is the information in the prompts the same as in your tnsnames.ora?

 

by: ijeevanPosted on 2009-01-12 at 07:13:42ID: 23353807

My tnsnames.ora in client;-

ORCL =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = OraSrv1)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orcl)
   )
 )

I used Basic while I connected sqldeveloper. Do I need to change anything on the tnsnames.ora?

 

by: sdstuberPosted on 2009-01-12 at 07:15:16ID: 23353819

what are the "Hostname"  "Port" and "SID/Service name"  fields in sqldeveloper set to for your connection?

 

by: ijeevanPosted on 2009-01-12 at 07:23:42ID: 23353898

Hostname: OraSrv1

Port: 1521

SID/Service Name: orcl

 

by: sdstuberPosted on 2009-01-12 at 07:33:33ID: 23353999

what does your sqlnet.ora look like?

sqldeveloper isn't using your tnsnames.ora because it doesn't need to
but maybe sqlplus isn't either because sqlnet.ora is redirecting to ldap or onames.

what do you get if you "tnsping orcl"  from the command line?

 

by: ijeevanPosted on 2009-01-12 at 07:37:35ID: 23354049

sqlner.ora:-


SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

tnsping result:-


D:\>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 12-JAN-2
009 21:10:21
Copyright (c) 1997, 2007, Oracle.  All rights reserved.
Used parameter files:
D:\app\product\11.1.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = OraSrv1)(PORT = 1521))) (CONNECT_DATA = (SID = orcl) (SERVER = DEDICATED
)))
OK (100 msec)

 

 

 

by: sdstuberPosted on 2009-01-12 at 07:45:22ID: 23354129

when you connect with sqlplus you should get something like the snippet below
what are your typing at the prompt that generates the error?

Can you cut and paste your screeen text?


SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 12 10:43:16 2009
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Enter user-name:

                                              
1:
2:
3:
4:
5:

Select allOpen in new window

 

by: ijeevanPosted on 2009-01-12 at 07:57:35ID: 23354276

yah sure,

 

 

by: sdstuberPosted on 2009-01-12 at 08:00:40ID: 23354321

that's the problem,  you're not specifying the database you want to connect to

try

"system@orcl"

 

by: ijeevanPosted on 2009-01-12 at 08:03:17ID: 23354356

Wow... It worked :-)

Thanks a lot.

 

by: ijeevanPosted on 2009-01-12 at 08:06:18ID: 31533486

The answer was perfect.

 

by: sdstuberPosted on 2009-01-12 at 08:08:07ID: 23354405

glad I could help

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...