>
Oracle, Technical

LOCAL_LISTENER and ORA-12545

How ironic that just this afternoon I read James Morle’s recent whitepaper about Connection Management in an Oracle RAC Configuration. One of the first things that James unearthed during his testing was a bug in how Oracle’s assistants configured RAC networking settings – specifically in how they don’t correctly set the LOCAL_LISTENER parameter. Of course you don’t need to use DBCA or NETCA to have this problem – you can set it incorrectly or forget to set it yourself just as easily when manually creating a database.

The reason it’s ironic that I read his paper today is because about two hours later I ran into a very similar problem myself – I was receiving ORA-12545 from the client every time I tried to connect despite the fact that my TNSNAMES file was exactly correct. Although my problem was slightly different from James’ they both had the same solution: set the LOCAL_LISTENER correctly.

Let’s recap exactly how I received the ORA-12545 error.

Defining the Problem

My laptop was the client and it was connected over a VPN to a remote network where the database server was. I am using IP addresses because I don’t have name resolution over the VPN connection. (Can you guess right away what the problem was?) Here’s what my TNSNAMES looked like:

db2rac1 =
  (description =
    (address=(protocol=tcp)(host=10.9.8.65)(port=1521))
    (connect_data=(service_name=db2rac1.lab.ardentperf.com)
      (instance_name=db2rac11))
  )

db2svc2 =
  (description =
    (address_list=
      (failover=on)
      (load_balance=on)
      (address=(protocol=tcp)(host=10.9.8.65)(port=1521))
      (address=(protocol=tcp)(host=10.9.8.66)(port=1521)))
    (connect_data=(service_name=db2svc2.lab.ardentperf.com))
  )

And this is what happened when I tried to connect:

C:Documents and Settingsjschneider>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 18:01:48 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: tchr@db2svc2
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

And yet connecting to the database identifier works fine:

Enter user-name: tchr@db2rac1
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining

SQL>

Furthermore, I’m able to connect without any problems on the local server:

[oracle@rh4lab15 ~]$ sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 27 18:14:32 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter user-name: tchr@db2svc2
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining

SQL>

Investigation

So what’s going on here? Well the first place to start investigating any oracle error is always by asking, “what does this error mean?”

[oracle@rh4lab15 ~]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Looks like this error usually means that there’s a problem with your TNSNAMES. In fact I can demonstrate this quite easily by making a “bad” TNSNAMES entry – lets try putting some nonsense string into the hostname field:

db2rac1bad =
  (description =
    (address=(protocol=tcp)(host=nonsense_string)(port=1521))
    (connect_data=(service_name=db2rac1.lab.ardentperf.com)
      (instance_name=db2rac11))
  )

What happens when I try to connect with this string?

C:Documents and Settingsjschneider>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 18:17:09 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: tchr@db2rac1bad
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Gadzooks! The same error! Well that’s informative but I’m sure that I have the right ip address in my connect string. I can connect directly to either instance using these ip addresses. (I only showed one above but I did in fact test both.)

So what could it be? Well don’t forget that RAC is introducing a new intermediate step with it’s server-side connection load balancing. Remember how RAC instances register with remote listeners? On other nodes? Well I wonder if the problem could have something to do with this. Easy enough to test; we’ll just make another TNSNAMES entry to force a connection using a remote listener:

db2rac1remote =
  (description =
    (address=(protocol=tcp)(host=10.9.8.65)(port=1521))
    (connect_data=(service_name=db2rac1.lab.ardentperf.com)
      (instance_name=db2rac12))
  )

Note that the ONLY difference between this entry and the one that WORKS is that I’ve switched the instance I’m connecting to. This means that the listener will have to redirect me to the other node. Let’s see what happens:

C:Documents and Settingsjschneider>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 18:31:42 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: tchr@db2rac1remote
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

I think that we might have just found the culprit. Could Oracle be sending back some “bad” TNSNAMES entry, perhaps with some nonsense string in the hostname field? Well lets find out. Seeing the TNSNAMES entry is easy enough; LSNRCTL will tell us that:

[oracle@rh4lab15 ~]$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 27-MAR-2007 18:34:12

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "db2svc2.lab.ardentperf.com" has 2 instance(s).
  Instance "db2rac11", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=rh4lab15.lab.ardentperf.com)(PORT=1521))
  Instance "db2rac12", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:8 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=rh4lab16.lab.ardentperf.com)(PORT=1521))

Remember how I said that I don’t have name resolution over my VPN connection? That means that “rh4lab16.lab.ardentperf.com” is a nonsense string to my laptop. I think we’ve found the problem. Now… on to the solution.

The Solution

It’s a pretty simple fix; just need to know how Oracle determines that address for remote connections. Oracle looks in a place that you might not immediately guess: LOCAL_LISTENER. (Well you might have guessed from the title of this post!)

James Morle mentioned metalink note 364855.1 which deals with remote connections being made on the wrong port. Note 311099.1 also deals with the ORA-12545 error. Both of these notes point to the same solution: setting LOCAL_LISTENER correctly. The latter note also gives a helpful rule for determining what the proper value is:

“So the rule is you should set the host field of the local_listener to a name which can be resolved by the clients in your environment.”

So in my case I need to use IP addresses since I don’t have name resolution over my VPN connection. Let’s try it out:

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.65)(PORT=1521))' sid='db2rac11';

System altered.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.66)(PORT=1521))' sid='db2rac12';

System altered.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rh4lab15 ~]$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 27-MAR-2007 21:37:24

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "db2svc2.lab.ardentperf.com" has 2 instance(s).
  Instance "db2rac11", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.65)(PORT=1521))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
  Instance "db2rac12", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.66)(PORT=1521))

That seemed to fix the address in the listener. All that’s left to do is repeat my tests from earlier and see if they work now.

C:Documents and Settingsjschneider>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 21:40:47 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: tchr@db2rac1remote
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining

SQL> connect tchr@db2svc2
Enter password:
Connected.

That was it! The connection is now working! And in the process of fixing it I hope that I’ve demonstrated a few useful concepts and troubleshooting techniques.

About these ads

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider

Discussion

15 thoughts on “LOCAL_LISTENER and ORA-12545

  1. What do you do, when such a stoke of luck doesnt happen? I mean what steps would you take to resolve the solution, if you hadnt read the James’s article?

    regards,

    Posted by Fahd Mirza | April 3, 2007, 2:10 am
  2. If it weren’t for that “stroke of luck” ..
    would you have tried client-side SQLNET
    tracing ?
    Have you done so ? Does the Trace file show
    you that the client is receiving a TNS
    connect-string from the DB Server ?
    Hemant

    Posted by Hemant | April 3, 2007, 8:24 am
  3. Client-side sqlnet trace is exactly what you would have to use. I’ve had this exact problem, and the trace showed what the problem was.
    The listener returned a hostname that my client couldnt (dns)resolve, and setting the local_listener with something the client could resolve, solved the problem.

    /P

    Posted by Peter | April 5, 2007, 2:20 am
  4. I have similar problem..
    Acually when I am making connection to server it is connecting properly.But,as I am querying database first 2-3 attempts fails.It gave me following error

    ORA-12545: Connect failed because target host or object does not exist.
    If still I try to query DB after 2-3 attempts,it works properly
    I am not getting what is the exact problem.

    Posted by Atul | June 28, 2007, 12:08 am
  5. That’s interesting Atul – I thought ORA-12545 had to do with establishing a connection but it sounds like you are getting the error while issuing a query? So you can connect and authenticate then you get the error when issuing a SELECT statement?

    If you are actually getting the error at connect-time then you might check your DNS configuration; a problem there could cause the symptoms that you’re describing. It sounds like there is misconfigured “load balancing” happening somewhere between the client and server; could be in DNS, in the listener itself (as I described in this article), or even in an external load balancer.

    Posted by Jeremy | June 28, 2007, 6:27 am
  6. Questions for Atul :
    1. Is this an RAC database ?
    2. Are you using and Oracle SQLPlus client
    (which version) or a non-oracle tool and Oracle Client libraries ?
    3. Is there a Firewall between the client and database server ?

    Posted by Hemant | June 29, 2007, 9:23 am
  7. What if you know the syntax for the address parameters are correct, I have tried diffrent configurations and ways of connecting but no luck. Is there anything on the server side that would cause someone not see the server? I can ping the server and it will respond back but as soon as I enter the hostname and other info I get the “ORA-12545: Connect failed because target host or object does not exist” message? What gives.

    Posted by WILL DC | May 21, 2008, 1:32 pm
  8. Can anybody tell me how to enable client level tracing also the logfiles where it will reside.

    Posted by Niranjan | July 31, 2008, 2:23 am
  9. thank you very much sir. your solution fit to our situation perfectly and our problem has got solved. We added hosts file to resolve the hostnames problme with name resolution in windows client.

    thanks a lot

    Posted by sudarshan | August 19, 2008, 12:20 am
  10. I’d like to say thanks, because your explanation has help me to find out the solution which was worried me, exactly in a moment of migration of single instance 9.0.2.5 to Oracle cluster 9.2.0.3 and database patch set 10.2.0.4. Every thing was good, fail node simulation , excellent performance with asm storage,etc. But the connections was refused 2 or 3 times (or more) until get connect to host server. In my place we use 3 kinds of applications (WEB-PHP, ,ERP and Delphi). The only application without ORA-12545 was WEB-PHP.
    My RAC enviroment is (2 nodes – intel quad-core), and NAS – OPENFILER 32 bits with asm storage using normal redudance).

    Posted by atila santos | December 18, 2008, 6:03 pm
  11. Thank you so much. I was facing the similar issue and this has helped me a lot. thanks

    Posted by saurabh | October 8, 2009, 12:12 am
  12. I don’t like the idea of “hardcoding” IP addresses in a RAC environment.

    How about adding entries to the client’s hosts file so that it can resolve?

    Posted by A.S. | June 9, 2010, 9:44 am
  13. Hi, Thanks for this article – found that there is old listener entry registered under this parameter. There is no issue in connection either from server and from client -a s using the Vitual IP address and $TNS_ADMIN entry in the client machine, however under my Oracle Fail Safe environment…
    DBCONSOLE services status shows “ONLINE-PENDING”

    This shows the listener working – probably taking from the listener.ora….

    Does this DBCONSOLE service pending due to the wrong listener entry?

    Can provide you full details….help will be much appreciared if possible ASAP -a s need to work to sort out this tomorrow in afternoon session.

    Best Regards,

    Posted by arvind | September 16, 2010, 4:23 am
  14. Jeremy,
    How do I identify in the listener logs a direct connection as opposed to one that was redirected from the other node?

    Thanks

    Posted by Pradeep | January 25, 2011, 8:25 am
  15. Hey
    Thank you so much!!!
    I had been struggling to connect to oracle database from my remote desktop for 2 days now.
    It’s 11gR2, Oracle has introduced new SCAN on top of node VIP’s.
    None of my ip’s were in DNS (i was doing a test setup using /etc/hosts).
    SCAN was sending back one of the node VIP name, my desktop does not know what ip to resolve that name to. I has only SCAN ip in my hosts files.
    ** I didn’t realize that SCAN is sending one of node VIP “name” back, and of course as it was not on DNS, my desktop has no idea what was returned from server.**
    Your blog was a nail on the head, all i had to do is add node VIPs to my hosts file, boom! connection went through.
    I can’t thank you enough!!!

    Posted by Deepa | June 15, 2011, 2:14 pm

Disclaimer

(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 926 other followers

%d bloggers like this: