For RAC databases with NAT or firewall, ORA-12545 connection Errors often occur when load balance is enabled on the server, because after the server forwards client connection requests to other nodes, the client uses the returned IP address to send a connection request again, resulting in an unrecognized IP address or host name. This article describes the problem and provides a solution.
For more information about RAC listener configurations, see
Oracle RAC listener Configuration (listener. ora tnsnames. ora)
Non-Default port listening configuration in oracle rac (listener. ora tnsnames. ora)
1. Description of NAT listeners
Ii. Configuration
1. Server Configuration
############### Server node 1 listener. ora ########################
LISTENER_VMDB01P =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb01pvip) (PORT = 1314) (IP = FIRST ))
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb01p) (PORT = 1314) (IP = FIRST ))
)
)
SID_LIST_LISTENER_VMDB01P =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/oracle/db)
(PROGRAM = extproc)
)
)
############### Server node 2 listener. ora ########################
LISTENER_VMDB02P =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb02pvip) (PORT = 1314) (IP = FIRST ))
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb02p) (PORT = 1314) (IP = FIRST ))
)
)
SID_LIST_LISTENER_VMDB02P =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/oracle/db)
(PROGRAM = extproc)
)
)
############### Server side tnsnames. ora, same for both nodes ########################
Remote_lsnr_lm5330 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb01pvip) (PORT = 1314 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb02pvip) (PORT = 1314 ))
)
Local_lsnr_lm5330a =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb01pvip) (PORT = 1314 ))
)
Local_lsnr_lm5330b =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = vmdb02pvip) (PORT = 1314 ))
)
############### Server side host table, same for both nodes ########################
127.0.0.1 localhost
10.200.48.17 vmdb01p.oradb.com vmdb01p
10.200.48.18 vmdb02p.oradb.com vmdb02p
10.200.48.15 vmdb01pvip.oradb.com vmdb01pvip
10.200.48.16 vmdb02pvip.oradb.com vmdb02pvip
192.168.48.17 vmdb01pph.oradb.com vmdb01pph
192.168.48.18 vmdb02pph.oradb.com vmdb02pph
SQL> show parameter instance_name
NAME TYPE VALUE
-------------------------------------------------------------------------
Instance_name string LM5330A
SQL> show parameter listener
NAME TYPE VALUE
--------------------------------------------------------------------------
Local_listener string local_lsnr_lm5330a
Remote_listener string remote_lsnr_lm5330
SQL> show parameter instance_name
NAME TYPE VALUE
-------------------------------------------------------------------------
Instance_name string LM5330B
SQL> show parameter listener
NAME TYPE VALUE
-------------------------------------------------------------------------
Local_listener string local_lsnr_lm5330b
Remote_listener string remote_lsnr_lm5330
2. Client tnsnames. ora Configuration
# For NAT client
LM5330TKO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.20.48.15) (PORT = 1314 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.20.48.16) (PORT = 1314 ))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LM5330)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
# For non NAT client
LM5330VIP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.200.48.15) (PORT = 1314 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.200.48.16) (PORT = 1314 ))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LM5330)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
3. Client hosts file configuration
C: \ windows \ system32 \ drivers \ etc \ hosts
10.200.48.15 vmdb01pvip.oradb.com vmdb01pvip
10.200.48.16 vmdb02pvip.oradb.com vmdb02pvip