Analytics


Google

Wednesday, October 1, 2008

Connecting to RAC using SQLDeveloper

The following are two definition in tnsnames.ora pointing to the same RAC database. The first one uses name (racdb1), the second (racdb2) uses actual IP address:

racdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.blogspot.COM)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.blogspot.COM)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL.BLOGSPOT.COM)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)


racdb2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.155.1.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.155.1.2)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL.BLOGSPOT.COM)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

If you use SQL Developer to connect to racdb1 using the TNS, you will encounter the following error:

Status: Failure - Test failed: (null).

It works fine if you connect to racdb2. Apparently, it is not able to perform a nslookup to obtain the IP address when using TNS. It works fine if you use advance and use thin client to connect as in:

Strangely, if you use Advanced and put the following string, it works:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = db1.blogspot.COM)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = db2.blogspot.COM)(PORT = 1521))(LOAD_BALANCE = yes))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = ORCL.BLOGSPOT.COM)(FAILOVER_MODE=(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))

3 comments:

Anonymous said...

Very handy, Thank you

Anonymous said...

THanks for your post , very useful

Anonymous said...

Very useful post - clued me in on what could be the reason that a third party driver was unable to connect to RAC when using hostnames. Things started working when I used the IP addresses instead