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)

(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)) 

Fix Use full hostname.domain_name in remote_listener configuration in tnsnames.ora. Restart the instance and listener, check lsnrctl service output, make sure full hostname.domain_name show up.

eg: in init.ora (or spfile) remote_listener=LISTENERS_R920

in tnsnames.ora: 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)) <<