http://www-01.ibm.com/support/docview.wss?uid=swg21506481
I had to configure Cognos BI to connect to an Oracle RAC dataase and it says I should use the following connection string when using Service Name:
^User ID:^?Password:;LOCAL;OR;ORACLE@%s@<database_alias>/%s@COLSEQ=IBM_JD_CNX_STR:^User ID:^?Password:;LOCAL;JD-OR;URL=jdbc:oracle:thin:@//<database_host>:<database_port(e.g.1521)>/<database_service_name>;DRIVER_NAME=oracle.jdbc.driver.OracleDriver
The DBA provided me with the following Oracle RAC connection string:
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADB01.mydomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADB02.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB.mydomain.com)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
I was trying to figure out what <database_alias> means until it hit me and realized the answer is the alias I used in the TNSNAMES.ORA for the oracle client (instant client) I installed previously.
TNSNAMES.ORA
PROD_ORADB = (DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADB01.mydomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADB02.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB.mydomain.com)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
After setting PROD_ORADB to the <database_alias> parameter, it worked perfectly:
^User ID:^?Password:;LOCAL;OR;ORACLE@%s@PROD_ORADB/%s@COLSEQ=IBM_JD_CNX_STR:^User ID:^?Password:;LOCAL;JD-OR;URL=jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = ORADB01.mydomain.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ORADB02.mydomain.com)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORADB.mydomain.com) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))));DRIVER_NAME=oracle.jdbc.driver.OracleDriver
