Purchase your Section 508 Compliance Support guide now!

Purchase your Section 508 Compliance Support guide now!

Cognos 8 -- Oracle connection errors

The error messages noted below may occur when using or creating an Oracle connection from Cognos Connection or from within Framework Manager. The root causes of the problem are similar although the means of generating the message may differ.

Potential Error Messages:

In a Windows environment:

QE-DEF-0285 Logon failure.
QE-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
testDataSourceConnection

UDA-SQL-0432 Unable to locate the gateway "cogudaor".

In a UNIX environment:

QE-DEF-0285 Logon failure.
QE-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
testDataSourceConnection

UDA-SQL-0432 Unable to locate the gateway "libcogudaor".

For Data Manager the following message may also be displayed:

DS-DBMS-E402: UDA driver reported the following:
UDA-SQL-0432 Unable to locate the gateway "libcogudaor9".
DS-DBMS-E306: UDA driver error connecting to 'Alias Name'.

Depending on the Oracle version the message may also list "libcogudaor10"

Environment:

The "cogudaor" is the Windows gateway for establishing connections to an Oracle data source. On UNIX the gateway will be listed as "libcogudaor".

Root Cause:

Incomplete configuration of the Oracle client. There are many ways that this issue may manifest itself. The items listed below are a means of identifying which areas are causing the connection difficulties.

Solution:

1. Identify that the Oracle Client drivers have been installed on all related Cognos systems. Make sure that the systems are restarted after the Oracle Client drivers have been installed. For Cognos 8 the client driver is required on both the Cognos 8 server and on systems running Framework Manager. Framework Manager establishes it's own connections to isolate resource consumption and prevent latencies on the Cognos 8 server due to long-running queries that may be executed from Framework Manager during model development and testing.
2. Compare the version of the Oracle client driver to those listed for the supported environments of the product version that you are using. Note that Cognos 8 increases the minimum driver version to 9.2.0.1 (Oracle 9i Release 2). Earlier releases of Cognos ReportNet supported older Oracle client drivers. Refer to the following resource to identify the supported driver versions for your Cognos products.

http://support.cognos.com/en/support/products/environments.html
3. Search the system for multiple tnsnames.ora files. It is possible that multiple Oracle clients or tnsnames.ora files exist and the connection string has not been defined within the "active" copy of the tnsnames.ora. If multiple files are located then ensure that the expected connection alias is defined within each tnsnames.ora to eliminate any possible inconsistencies.

The Oracle "tnsping" command can be used to test a given connection alias. Running "tnsping ALIAS_NAME" from a command window will obtain the client driver connection information from the current environment settings and return a success or failure message indicating if the ALIAS_NAME defines a valid Oracle connection. When successful the return message should look like the following:

Attempting to contact (ADDRESS=(PROTOCOL=TCP)
(HOST=yourhostname)(PORT=1521)) OK (50 msec)

Similarly, if the connection can be established on one system in the Cognos 8 environment but fails when tested on another system then ensure that the tnsnames.ora on the failing system contains the same connection string as on a system where the connection is successful. Copying the file or entry within the file to the failing environment will ensure that the connection information is the same in both environments. Following an update to the tnsnames.ora file, test the connection using tnsping on the problem systems to validate the copied connection string.

In some cases a server identified by name may not be resolvable on secondary systems (i.e. if name resolution is not configured or the secondary system is in a secured environment such as a DMZ). In such cases it may be necessary to update the copied tnsnames.ora entry to refer to the Oracle database server using an IP address instead of the server name.
4. Once the tnsnames.ora aliases have been defined and tested, ensure that the alias in the tnsnames.ora file matches the alias in the Cognos 8 connection string. Oracle's SQLPlus will allow you to establish connections based on unqualified aliases while Cognos 8 requires the exact alias name to make the appropriate connection.

For example, if an alias is defined as NAME.DOMAIN.COM then SQLPlus will allow connections using just the NAME portion of the alias. Cognos 8 would require the full NAME.DOMAIN.COM alias to be defined in the connection string.
5. Attempt to use the Oracle client tools to establish a connection to the Oracle database. Using SQLPlus to establish a connection may provide additional details regarding the failures.

For example, attempting to connect to an Oracle 10G database using a 9i client driver when the 10G client is required may result in the following error message: ORA-12638: Credential retrieval failed
6. If the error message above is occuring in Framework Manager then ensure that the Cognos 8 server is started, accessible, and fully configured. If the Cognos 8 server is unreachable then the connection string information will not be accessible to Framework Manager.
7. If the Oracle client libraries are missing or not accessible then a connection may fail with the above message. For example, if for any reason the oci.dll (located in the Oracle bin directory) is unavailable then the connection will fail.

Ensure that the environment variables for the system are configured to access the Oracle client. For full details on configuration requirements please refer to your Oracle documentation. As a quick reference, on Windows, the Oracle bin directory must be located in the PATH environment variable. On UNIX the Oracle bin must be located in the PATH variable and the lib (or lib32 in when using 32-bit libraries along side of the 64-bit client installation) directory must be added to the library path.

PATH : Include the location of the bin directory located within the Oracle client installation.
ORACLE_HOME : Location of the base directory of the Oracle client installation. (On Windows Systems this may not be necessary for later versions of the Oracle client)
TNS_ADMIN : Directory containing the tnsnames.ora file. The file must have the Oracle instance defined. (This may be optional. In the event that this is not defined the tnsnames.ora file should be obtained from the NETWORK\ADMIN location within the Oracle client install location or ORACLE_HOME)

UNIX library path references:
Solaris: LD_LIBRARY_PATH (Example: LD_LIBRARY_PATH=Oracle_Home/lib32 etc.)
HPUX: SHLIB_PATH
AIX: LIBPATH

If a 3rd party application server (such as WebSphere , WebLogic , Oracle Application Server , SAP NetWeaver , or JBOSS ) is being used for your Cognos 8 environment then it may also be necessary to review the startup scripts and configuration settings of your application server to ensure that the path settings to access the Oracle client libraries are not being reset by startup scripts or defined incorrectly within the 3rd party products. Refer to your application server documentation for details on identifying custom environment settings.

On Windows the 3rd party tools, Dependency Walker, RegMon, and FileMon (RegMon and FileMon are available from SysInternals) can assist in identifying missing or inaccessible files. Dependency Walker can be attached to cogudaor.dll (located in the Cognos bin directory) to identify any inaccessible libraries required for establishing the connection.

On UNIX the ldd command can be used to identify the dependencies of the libcogudaor library (located in the Cognos bin directory).

ldd libcogudaor.(so|a|o)** -- Solaris, HPUX and AIX (depending which utility is installed)
** File extensions depend on operating system.

dump -H libcogudaor.so -- AIX

Using these trace utilities will also identify scenarios where the libraries are being sourced from a location other than the expected Oracle bin directory (i.e. other software or other oracle client installations may be identified in the system PATH environment variable and are being sourced inappropriately for the required Oracle libraries). If multiple Oracle clients are installed on a system ensure that the proper client is listed first in the PATH variable.

Note that in some environments the Oracle client drivers are available in both 64-bit and 32-bit versions. The 32-bit client libraries are required for establishing a connection with Cognos 8. Refer to your Oracle product documentation for the details on installing and configuring your environment to use the 32-bit client libraries.

Also, in some Windows environments the environment variables set during the installation of the Oracle client software may not be immediately accessible to applications running as a service. In such scenarios a reboot of the system may be required to finalize the configuration of the newly-installed Oracle client software.

On rare occasions there have also been issues reported where a Oracle client installation on UNIX does not copy the libclntsh.so to the lib32 directory of the Oracle installation. It is recommended that you contact your software vendor for the appropriate steps to resolve this issue. A copy of the file can be obtained from the temporary files created during the Oracle client installation using the following steps:
1. Locate the file libclntsh.a from the temporary installation directory (i.e. located at /tmp/OraInstall2005-03-22_08-49-34AM/oui/bin/aix/
2. Extract the file shr.o from libclntsh.a archive using the command "ar -xv libclntsh.a"
3. Rename shr.o to libclntsh.so and copy this file to the 32-bit library directory of your Oracle client installation.

Changes to the environment should be followed by a restart of the Cognos products to ensure that the new settings are applied.
8. The connection for Cognos 8 may have been defined without a user ID or password enabled. If these options are not enabled then the user ID and password will not be included in the connection string when estabilishing the Oracle connection and Oracle may reject the attempt as unauthorized. These properties can be located by
9.
1. Selecting Diretory from the Tools menu in Cognos Connection.
2. From the Directory page, select Data Sources and click on the data source that you are using.
3. After clicking on the data source you will be shown the list of connections defined for the data source. For the connection you wish to use, select the Set Properties action and then click on the Connection tab.
4. After clicking on the Pencil icon to edit the connection string you will be presented with the options to enable the use of a User ID and Password for the connection. Ensure that these are checked and then click the OK button twice to commit the changes.
5. Once the User ID and Password are enabled it is also necessary to check the credentials defined for the Signon object. To do so, click on the listed Connection (this is reached by following steps 1 and 2).
6. The Signon objects will now be displayed. If no signons are available then you may need to create one using the New Signon button from the toolbar at the top right of the page. If a signon already exists then click the Set Properties action for the signon and then click the Signon tab.
7. Click the Edit the Signon... link to view the assigned credentials. Retype the User ID and Password using a known valid combination to ensure that the credentials are valid and then click the OK button twice to commit the changes.
8. The connection and signon can be tested by returning to the connection object (steps 1 and 2) and then selecting the Test the Connection action.
10. Additional trace information can be obtained through detailed tracing mechanisms available within Cognos 8. To enable this tracing, rename the CQEconfig.xml.samples file (located in the Cognos 8 configuration directory) to CQEconfig.xml and restart Cognos 8. This change will direct more detailed error messages to the cogserver.log file (located in the Cognos 8 logs directory) and may include additional information not reported in the messages presented through the product user interfaces.

Note: the CQEconfig file should be returned to the orginal file name (with the ".sample" extension) and Cognos 8 restarted when the tracing is complete to disable the detailed logging mechanisms as the detailed logging may impact product performance.