LOCAL_LISTENER and ORA-12545

[출처] http://www.ardentperf.com/2007/04/02/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 Settings\jschneider>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 Settings\jschneider>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 Settings\jschneider>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 Settings\jschneider>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.