Cognos Datasource connection failure with Oracle DB

[Sahithi]

Hi, I have a problem with datasource connection with Oracle DB.
Datasource connection with oracle type is successful with JDBC connection but failed with OCI without any error message.

I have installed Oracle 0.2.1 (32 bit) and cognos 10.1.1 (32 bit) on a 64 bit pc.

I copied both ojdbc14 and ojdbc14_g from oracle jdbc lib to cognos lib (C:\Program Files (x86)\ibm\cognos\c10\webapps\p2pd\WEB-INF\lib)

Below are listener.ora, sqlnet.ora, tnsnames.ora file contents. (There files are located in C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN)

***** Listener.ora*****

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
       (PROGRAM = extproc)
     )
     (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
       (PROGRAM = extproc)
     )
   )

 

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
       (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
     )
   )

 

DEFAULT_SERVICE_LISTENER = (XE)

*********************************************************

***********SQLNET.ORA******************************


# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

 

SQLNET.AUTHENTICATION_SERVICES = (NTS)

************************************************************

*******tnsnames.ora***********************************

XE =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = XE)
     )
   )

 

EXTPROC_CONNECTION_DATA =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
   )

 

ORACLR_CONNECTION_DATA =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
     )
     (CONNECT_DATA =
       (SID = CLRExtProc)
       (PRESENTATION = RO)
     )
   )

*********************************************************

For Information: I am able to access Oracle from other tools like Toad.

Kindly help me in fixing this issue. I spent 2 days on this issue and couldn't find root cause. 


Due to this datasource connection error my reports are not running with error as in attachment.
 

 

 

[MFGF]

Hi, I have a problem with datasource connection with Oracle DB.
Datasource connection with oracle type is successful with JDBC connection but failed with OCI without any error message.

I have installed Oracle 0.2.1 (32 bit) and cognos 10.1.1 (32 bit) on a 64 bit pc.

I copied both ojdbc14 and ojdbc14_g from oracle jdbc lib to cognos lib (C:\Program Files (x86)\ibm\cognos\c10\webapps\p2pd\WEB-INF\lib)

Below are listener.ora, sqlnet.ora, tnsnames.ora file contents. (There files are located in C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN)

***** Listener.ora*****

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
       (PROGRAM = extproc)
     )
     (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
       (PROGRAM = extproc)
     )
   )

 

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
       (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
     )
   )

 

DEFAULT_SERVICE_LISTENER = (XE)

*********************************************************

***********SQLNET.ORA******************************


# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

 

SQLNET.AUTHENTICATION_SERVICES = (NTS)

************************************************************

*******tnsnames.ora***********************************

XE =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = XE)
     )
   )

 

EXTPROC_CONNECTION_DATA =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
   )

 

ORACLR_CONNECTION_DATA =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
     )
     (CONNECT_DATA =
       (SID = CLRExtProc)
       (PRESENTATION = RO)
     )
   )

*********************************************************

For Information: I am able to access Oracle from other tools like Toad.

Kindly help me in fixing this issue. I spent 2 days on this issue and couldn't find root cause. 


Due to this datasource connection error my reports are not running with error as in attachment.
 

 

Hi,

The key clue in this error message is the part that says:

ORA-12154: TNS:could not resolve the connect identifier specified

This is Oracle telling you what it doesn't understand when Cognos is trying to connect.

Take a look at the following Oracle doc, with good suggestions on how to resolve this specific message:

http://docs.oracle.com/cd/B19306_01/server.102/b14219/net12150.htm

Cheers!

MF.

[sir_jeroen]

Hi,

First of all: What exactly do you mean with :Datasource connection with oracle type is successful with JDBC connection but failed with OCI
- Are you referring to DQM (jdbc) and CQM (when you say failed with OCI)?
- If both references are to DQM take a look at the DQM Cookbook by IBM. There's an Oracle section on setting up connections.

In regards to copying the jar files to WEB-INF\LIB: This is only necessary for the Contentstore connection.
When you're connection to an Oracle database for reporting AND using CQM then Cognos is using your installed 32-bit Oracle Client driver, not the copied jars.


Some tips from the trenches:
1. Go for the OJDBC5.jar (also: ignore _g.jar) (not relevant for your issue... just a side-tip)
2. Check your %PATH% statement: How many references to oracle drivers are there? If there's a 32 and a 64 bit switching both.
3. What happens if you do a TNSPING XE? What driver version is used. Is it 32-bit?
and

last but not least
Do a SERVER reboot (not a service reboot). (<-- Solved most of my issues after installing Oracle clients)

[Sahithi]

Thanks for your reply
yes I am referring to DQM and CQM.

1.For Oracle 10gR2 there is OJDBC14.jar (ignored *_g.jar) and I have copied the same in cognos WEB-INF\LIB directory
2.%PATH% refers to only one 32 bit oracle
3.tnsping XE is throwing error - TNS-03505: Failed to resolve name

I have tried many options but in vain :(

Attached datasource connection test screenshot.

[sdf]

server reboot so your changes with the environment variables will take effect.