Troubleshooting
Problem
Using or creating an Oracle connection from Cognos BI Connection or Framework Manager might result in similar errors. The root cause of the problem is similar although the means of generating the message can differ.
Symptom
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 in:
testDataSourceConnection
UDA-SQL-0432 Unable to locate the gateway "cogudaor".
- QE-DEF-0285 Logon failure.
QE-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database in:
testDataSourceConnection
UDA-SQL-0432 Unable to locate the gateway "libcogudaor".
Depending on the Oracle version, the message might also list "libcogudaor10"
Cause
Incomplete configuration of the Oracle client. There are many ways that this issue may manifest itself. The following items are a means to identify which areas are causing the connection difficulties.
Environment
Oracle data source
The "cogudaor" is the Windows gateway for establishing connections to an Oracle data source. In UNIX, the gateway is listed as "libcogudaor".
Resolving The Problem
*** HINT for compatibility with an earlier version that uses Classic Query Mode (CQM) data source connections. IBM Cognos supports 32-bit Oracle client drivers only. Even on 64-bit Operating Systems, and even with a 64-bit installation of IBM Cognos. This requirement exists because the BIBusTKServerMain process, which handles database connectivity, is written in 32-bit code, and so cannot use 64-bit database clients. ***
1. Identify that the Oracle Client drivers are installed on all related IBM Cognos systems. With the Oracle Client drivers installed, restart the system. In IBM Cognos , the client driver is required on both the IBM Cognos server and on any system that runs Framework Manager. Framework Manager establishes its own connections to isolate resource consumption and prevent latencies on the IBM Cognos server due to long-running queries that might be run from Framework Manager during model development and testing.
On Unix/AIX/Linux/Solaris/HP-UX system, you can run the following command, which verifies you whether the Oracle Client is 32-bit or 64-bit
cd <oracle bin folder>
File sqlplus
cd <oracle lib folder>
... run the file command on files that contain the string "oci" in the name
For reference, the following snapshot are taken from RHEL test system.
2. Compare the version of the Oracle client driver to any listed for the supported environments of the product version that you are using. It is highly suggested that the Oracle client software version matches the Oracle database version for compatibility reasons.
http://www-01.ibm.com/support/docview.wss?uid=swg27037784
3. Search the system for multiple tnsnames.ora files. It is possible that multiple Oracle clients or tnsnames.ora files exist and that the connection string was not defined within the "active" copy of the tnsnames.ora. If multiple files are located, then you can set the TNS_ADMIN environment variable to specify the directory that contains the version of the tnsnames.ora file you want to use. Alternately, ensure 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 connection alias. Running "tnsping ALIAS_NAME" from a command window obtains the client driver connection information from the current environment settings and then returns a success or failure message that indicates whether or not the ALIAS_NAME defines a valid Oracle connection. When successful the returned message will 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 IBM Cognos environment but fails when tested on another system, then ensure that the tnsnames.ora on the failing system contains the same connection string as the successfully connected system. Copying the file or entry within the file to the failing environment ensures that the connection information is the same in both environments. Following an update to the tnsnames.ora file, test the connection by using tnsping on the problem systems to validate the copied connection string.
In some cases, a server that is identified by name might not be resolvable on secondary systems (that is, if name resolution is not configured or the secondary system is in a secured environment such as a DMZ). In such cases, it might be necessary to update the copied tnsnames.ora entry to refer to the Oracle database server by using an IP address instead of the server name.
4. After the tnsnames.ora aliases are defined and tested, ensure that the alias in the tnsnames.ora file matches the alias in the IBM Cognos connection string. Oracle's SQLPlus allows connections based on unqualified aliases while IBM Cognos requires the exact alias name to make the appropriate connection.
For example, if an alias is defined as NAME.DOMAIN.COM then SQLPlus allows connections by using just the NAME portion of the alias. IBM Cognos 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 can provide more detail regarding the failures.
For example, attempting to connect to an Oracle 10G database that uses a 9i client driver when the 10G client is required, might result in the following error message: ORA-12638: Credential retrieval failed
6. If the error message that is referenced is occurring in Framework Manager, then ensure that the IBM Cognos server is started, accessible, and fully configured. If the IBM Cognos server is unreachable, then the connection string information is not accessible to Framework Manager.
7. If the Oracle client libraries are missing or not accessible, then a connection might fail with the message referenced. For example, if for any reason the oci.dll (located in the Oracle bin directory) is not available then the connection fails.
Ensure that the environment variables for the system are configured to access the Oracle client. For full details on configuration requirements, refer to your Oracle documentation. As a quick reference, on Windows, the Oracle bin directory must be located in the PATH environment variable. In UNIX, the Oracle bin must be located in the PATH variable and the lib (or lib32 that uses 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 it might not be necessary for later versions of the Oracle client)
TNS_ADMIN: Directory containing the tnsnames.ora file. Define the Oracle instance in the file. (The following might be optional. If it is not defined, the tnsnames.ora file can be obtained from the NETWORK\ADMIN location within the Oracle client installation location or ORACLE_HOME)
UNIX library path references:
Solaris: LD_LIBRARY_PATH (Example: LD_LIBRARY_PATH=Oracle_Home/lib32...)
HPUX: SHLIB_PATH
AIX: LIBPATH
If a third party application server (such as WebSphere, WebLogic, Oracle Application Server, SAP NetWeaver, or JBOSS) is being used for your Cognos environment, then it might also be necessary to review the startup scripts and configuration settings of your application server. Ensure the path settings to access the Oracle client libraries are not being reset by startup scripts or defined incorrectly within the third party products. Refer to your application server documentation for details on identifying custom environment settings.
For WebSphere, ensure that you define ORACLE_HOME, TNS_ADMIN, and the appropriate PATH variables within the Process Definition screen demonstrated in the following example:
After setting the environment variables, make sure you restart Cognos services before you test the data source connection.
8. On Windows the third party tools, Dependency Walker, RegMon, and FileMon (RegMon and FileMon are available from SysInternals) can help 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.
In UNIX, the ldd command can be used to identify the dependencies of the libcogudaor library (located in the Cognos bin directory).
ldd libcogudaor<oracle version number>.(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 identify scenarios where the libraries are being sourced from a location other than the expected Oracle bin directory. That is, other software or other oracle client installations can be identified in the system PATH environment variable, and are being sourced inappropriately for the 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 . 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 might not be immediately accessible to applications that are running as a service. In such scenarios a restart of the system might be required to finalize the configuration of the newly installed Oracle client software.
On rare occasions, issues were reported where an Oracle client installation on UNIX does not copy the libclntsh.so to the lib32 directory of the Oracle installation. It is suggested 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 by using the following
Steps:
1. Locate the file libclntsh.a from the temporary installation directory (in /tmp/OraInstall2005-03-22_08-49-34AM/oui/bin/aix/
2. Extract the file shr.o from libclntsh.a archive by 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 must be followed by a restart of the Cognos product to ensure the new settings are applied.
9. The connection for Cognos can be defined without enabling a user ID or password. If these options are not enabled, then the user ID and password are not included in the connection string when establishing the Oracle connection . Also Oracle might reject the attempt as unauthorized. These properties can be located by
1. Go into Cognos Administration in Cognos Connection.
2. On the Configuration tab, select data sources and click the Oracle data source that you are using.
3. With the data source selected, you are then shown the list of connections defined for the data source. For the connection you want to use, select the Set Properties action and then click the Connection tab.
4. Click on the Pencil icon to edit the connection string. You are then presented with the options to enable the use of a User ID and Password for the connection. Ensure they are checked and then click OK twice to commit the changes.
5. With the User ID and Password are enabled, it is also necessary to check the credentials defined for the Signon object. To do so, click the listed Connection (accomplished by following steps 1 and 2).
6. The Signon objects are displayed. If no signons are available, then you might need to create one using the New Signon option from the toolbar at the upper right of the page. If a signon exists, click the Set Properties action for the signon and then click the Signon tab.
g. Click the Edit the Signon... link to view the assigned credentials. Retype the User ID and Password by using a known valid combination that works in SQLPlus to ensure that the credentials are valid and then click OK twice to commit the changes.
h. 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. On UNIX systems, the Unable to locate the gateway "libcogudaor" error can also be the result of resource limitations of the user profile that started IBM Cognos In AIX environments, change the settings for ulimits and LDR_CNTR=MAXDATA to as high of values as possible to test if its what is causing the error. Setting these to the following is a good test:
ulimit -d unlimited
ulimit -m unlimited
LDR_CNTRL=MAXDATA=0x80000000
11. More trace information can be obtained through detailed tracing mechanisms available within Cognos. To enable this tracing, rename the CQEconfig.xml.samples file (located in the Cognos configuration directory) to CQEconfig.xml and restart Cognos . This change directs more detailed error messages to the cogserver.log file (located in the Cognos logs directory) and might include additional information not reported in the messages presented through the product user interfaces.
HINT: the CQEconfig file returns to the original file name (with the ".sample" extension) and Cognos restarted when the tracing is complete to disable the detailed logging mechanisms as the detailed logging might impact product performance.
12. As a last resort, copy both the files <Oracle Client Install Dir>/lib/libclntsh.so.10.1 and <Oracle Client Install Dir>/lib/libnnz10.so to the <Cognos Install Dir>/bin directory and change its permissions that use chmod 755. If possible restart the server and then start Cognos service. If you cannot restart the server, just restarting the Cognos service will suffice. The libclntsh.so.10.1 and libnnz10.so are specific to Oracle 10, Similar files can be found with respective version of Oracle.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21341734