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))
)
The output of "lsnrctl service listener_R9201" is:
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
D3R is a database on 2 RAC nodes.
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'
....