"ORA-12154:
TNS:could not resolve the connect identifier specified" is a very generic
and common error message; faced while connecting to an Oracle database. If you
"google" this error, you would find a lot of different reasons and
solutions for this error message;
and I will not repeat any of them, rather I would explain a different problem (which I faced recently), and the solution for it.
Problem
On my 2 nodes (10.2.0.4) RAC, everything was working fine until I enabled the server side load balancing for this RAC. After enabling server side load balancing (to enable oracle listener to forward the clients connections to the least loaded RAC node, to keep the load balanced), I started receiving "ORA-12154: TNS:could not resolve the connect identifier specified" on my application server. But this error message was very random. This was quite surprising that why my connections are failing randomly, not always. My TNS entries were as follows. First TNS entry which you can see is the actual TNS entry used. Rest of the entries were working fine because these were making connection directly to required instance mentioned in INSTANCE_NAME parameter and hence server side load balancing does not work for these entries
######################################
PRODDB.WORLD, PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.11)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
and I will not repeat any of them, rather I would explain a different problem (which I faced recently), and the solution for it.
Problem
On my 2 nodes (10.2.0.4) RAC, everything was working fine until I enabled the server side load balancing for this RAC. After enabling server side load balancing (to enable oracle listener to forward the clients connections to the least loaded RAC node, to keep the load balanced), I started receiving "ORA-12154: TNS:could not resolve the connect identifier specified" on my application server. But this error message was very random. This was quite surprising that why my connections are failing randomly, not always. My TNS entries were as follows. First TNS entry which you can see is the actual TNS entry used. Rest of the entries were working fine because these were making connection directly to required instance mentioned in INSTANCE_NAME parameter and hence server side load balancing does not work for these entries
######################################
PRODDB.WORLD, PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.11)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
PRODDB1.WORLD,
PRODDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.10)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
(INSTANCE_NAME = proddb1)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.10)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
(INSTANCE_NAME = proddb1)
)
)
PRODDB2.WORLD,
PRODDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.11)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
(INSTANCE_NAME = proddb2)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XX.11)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
(INSTANCE_NAME = proddb2)
)
)
#####################################
Root Cause
Investigation revealed that I did not have the entries of my both RAC nodes in the host file of my application server. After adding the entries of both RAC nodes in the host file of my application server, my problem was resolved (There wasn't any DNS server in my environment).
How the Entries in the HOST file Made the Difference
In RAC environment when server side load balancing is on, when a connection request comes to a RAC node (let say node1), and oracle listener plans to forward this connection to any other node in the RAC (let say node2), it would pass the hostname of node2 back to the client for further processing of the connection request so that client can connect to node2. Now if client system can resolve the hostname of node2 either through DNS or host file, connection would be successful, otherwise it would return "ORA-12154".
Moral of the Story
Always make sure that all systems (servers) involved with your oracle database environment, have each other's entries either in their host file or are resolvable through DNS. As a best practice, ALWAYS add entries in the host file. This is also true for client side – if you want to connect to the database server from a client, it is recommended to add the entries in the host file of the client.
Root Cause
Investigation revealed that I did not have the entries of my both RAC nodes in the host file of my application server. After adding the entries of both RAC nodes in the host file of my application server, my problem was resolved (There wasn't any DNS server in my environment).
How the Entries in the HOST file Made the Difference
In RAC environment when server side load balancing is on, when a connection request comes to a RAC node (let say node1), and oracle listener plans to forward this connection to any other node in the RAC (let say node2), it would pass the hostname of node2 back to the client for further processing of the connection request so that client can connect to node2. Now if client system can resolve the hostname of node2 either through DNS or host file, connection would be successful, otherwise it would return "ORA-12154".
Moral of the Story
Always make sure that all systems (servers) involved with your oracle database environment, have each other's entries either in their host file or are resolvable through DNS. As a best practice, ALWAYS add entries in the host file. This is also true for client side – if you want to connect to the database server from a client, it is recommended to add the entries in the host file of the client.
No comments:
Post a Comment