Friday, August 30, 2013

(BAM) Using Oracle RAC Connection String in Cognos BI DataSource

I was configuring Cognos BI Data Source for IBM Business Monitor (BAM) and followed the technote:

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


No comments: