Connection to RAC database

Intermittent ORA-12545 error when trying to connect to RAC database.

In a multiple node RAC environment listeners do the load balancing between all the instances. So after receiving a connection request from the client, listener may send a redirect packet to the client to redirect it to the listener of another node. ORA-12545 error will be reported in the client if client is unable resolve the host name mentioned in the redirect packet.

The remote and local listener for the RAC database were not defined in full hostname.domain, thus instance remote registration uses short name instead of full host with domain name. When server side load balance is in place, it returns short hostname to client where client does not have domain searching ability, causing client lookup for short hostname failed with ora-12545 "Connect failed because target host or object does not exist".

example: in init.ora (or spfile): remote_listener=LISTENERS_R920 (local listener as well)

LISTENERS_R920 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aulnx1)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = aulnx2)(PORT = 1527))
)

Service "R920" has 2 instance(s).
   Instance "R9201", status READY, has 2 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready 
         REMOTE SERVER
          (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx1)(PORT=1527)) <<=
       "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
   Instance "R9202", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:blocked
         REMOTE SERVER
          (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx2)(PORT=1527)) <<=

To fix the problem:

• use full hostname.domain in the local and remote listener configuration
• restart the instance and listener
• check lsnrctl service output
• make sure full hostname.domain show up

example:

LISTENERS_R920 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aulnx1.au.oracle.com)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = aulnx2.au.oracle.com)(PORT = 1527))
)

Service "R920" has 2 instance(s).
   Instance "R9201", status READY, has 2 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
          (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx1.au.oracle.com)(PORT=1527)) <<=
       "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
   Instance "R9202", status READY, has 1 handler(s) for this service...
     Handler(s):
       "DEDICATED" established:0 refused:0 state:blocked
         REMOTE SERVER
          (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx2.au.oracle.com)(PORT=1527)) <<=

Listeners configuration example

tnsnames.ora file

#single listener with multiple listening addresses
LISTENERS_D3R =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = itrac37-v.cern.ch)(PORT = 1527))
    (ADDRESS = (PROTOCOL = TCP)(HOST = itrac38-v.cern.ch)(PORT = 1527))
  )

#local listeners (one per instance)
LISTENER_D3R1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = itrac37-v.cern.ch)(PORT = 1527))

LISTENER_D3R2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = itrac38-v.cern.ch)(PORT = 1527))

init.ora file

....
d3r1.local_listener='LISTENER_D3R1'
d3r2.local_listener='LISTENER_D3R2'
....
*.remote_listener='LISTENERS_D3R'
....