OBIEE 11g BI Management tool connection OCI 10g/11g data source times wrong [connection failed] OBIEE 11g Client Admin tools Connection to Datasource in Offline Mode fails Wit H Error "The Connection has Failed
Using OBIEE 11g BI management tool to import metadata, when connecting the OCI 10g/11g data source, click Connect will error [connection failed], such as:
Reference the Oracle Notes
OBIEE 11g Client Admin Tool Connection to Datasource in Offline Mode fails with Error "The Connection have Failed" [ID 1296 370.1]
Here's how to fix it:
#Applies to
Business Intelligence Server Enterprise edition-version:11.1.1.3.0 [1905] and later [release:11g and later]
Generic Windows
#Symptoms
Created a new repository (RPD) using the Oracle business Intelligence Enterprise Edition (OBIEE) Admin Tool. When trying to import data in offline mode, the following error occurs with both ODBC and OCI connection:
#Error
The Connection has failed.
Also notice all or some of the following errors in NQSAdminTool.log:
[Path is:< installation path >\oraclebi\orainst\diagnostics\logs\oraclebiservercomponent\coreapplication\administrator_ NQSAdminTool.log]
[2011-02-07t11:26:19.000+00:00] [Oraclebiservercomponent] [nqserror:17003] Oracle Gateway Error:ocienvnlscreate or Ocienvinit failed to initialize environment. Please check your for Oracle Client installation and make sure the correct version of OCI libraries is in the library path.
...
[2011-02-08t15:42:58.000+00:00] [nqserror:17014] Could not connect to Oracle database. ［
[nqserror:17001] Oracle Error code:12154, message:ora-12154:tns:could not resolve the connect identifier specified
At OCI call Ociserverattach
...
］
[2011-02-08t15:46:08.000+00:00] [nqserror:17014] Could not connect to Oracle database. ［
[nqserror:17001] Oracle Error code:12705, message:ora-12705:cannot access NLS data files or invalid environment specified at OCI call OC Isessionbegin
...
The issue is not reproducible while in Online mode.
#Cause
Multiple Oracle Homes exist which can cause confusion to the OBIEE Admin Tool for where to reference OCI libraries, NLS an D the Tnsnames.ora entries.
Additional configuration steps is required to ensure the desired files is used.
Solutionto prevent this error, your need to complete the following configuration tasks:
Note:in all cases, the OBIEE Admin Tool should is started by using the Start-to-Programs Oracle business Intelli Gence-Administration Tool menu item and not by double-clicking the RPD file itself. Starting the Admin Tool via the menu item is required to initialize required environment variables.
Setting the TNSNames. ORA entry to use the same name as the Oracle Database SID is recommended. The TNS_ADMIN environment variable needs to point to the physical location of the tnsnames. ORA file in the local environment (whether this is the OBIEE server machine or Admin Tool machine).
# #1. For the Admin Tool installed with the complete OBIEE Server installation:
1.1. Edit registry under Hkey_local_machine\software\oracle\<oracle_home> to make sure Nls_lang key was set to a Vali D characterset for the Oracle Client used as the DSN.
1.2. Please edit the file%middleware_home%\instances\instance1\bifoundation\oraclebiapplication\coreapplication\ Setup\bi-init.cmd to set%oracle_home%\bin as first entry in the PATH.
e.g.
Set Path=%obiee_home%\bin;%o Biee_home%\bifoundation\server\bin;%o Biee_home%\bifoundation\web\bin; c:\oracle\biee_11g\jre\bin;%windir%;%windir%\system32;%path%
1.3. Please edit the file%middleware_home%\instances\instance1\bifoundation\oraclebiapplication\coreapplication\ Setup\user.cmd to set Tns_admin to your Oracle client Tnsnames.ora home directory path (in case exists) or to your OBIEE t NSAdmin%obiee_client_home%\oracle_bi1\network\admin.
e.g.
Set Tns_admin=c:\oracle\product\11.2.0\client_1\network\admin
~ OR ~
Set Tns_admin=c:\oracle\oraclebiee11g\oracle_bi1\network\admin
# #2. For the Admin Tool installed with the new standalone installer:
2.1. Edit registry under Hkey_local_machine\software\oracle\<oracle_home> to make sure Nls_lang key was set to a Vali D characterset for the Oracle Client used as the DSN.
The settings for this step refer to, for example, modifying na to simplified Chinese_china. Zhs16gbk
2.2. Please edit the file%obiee_client_home%\\oraclebi\orahome\bifoundation\server\bin\bi_init.bat to set%oracle_ Home%\bin as first entry in the PATH.
2.3. Add Tns_admin entry to the Bi_init.bat file to point to your Oracle client Tnsnames.ora home directory path.
e.g.
Set Tns_admin=c:\oracle\product\11.2.0\client_1\network\admin
Finally I set the file Bi_init.bat to:
===========================================
@echo off
REM Overwrite Java_home
Set JAVA_HOME=%~D0%~P0. \.. \.. \.. \.. \jre
If exist "%java_home%\bin\java.exe" Goto:donejavahome
REM Abort
Echo *******************
echo ****** ERROR ******
Echo *******************
set/p prompt=[invalid java_home:%java_home%] Enter return to abort
Exit 100
:d Onejavahome
Set Oracle_home=d:\devsuitehome
Set Tns_admin=d:\devsuitehome\network\admin
Set Oracle_instance=d:\biclient\oraclebi\orainst
Set Oracle_bi_application=coreapplication
REM Call%oracle_instance%\bifoundation\oraclebiapplication\%oracle_bi_application%\setup\user.cmd
If not "%1" = = "" Set component_name=%1
Set Path=%oracle_home%\bin;%o Racle_home%\bifoundation\server\bin;%o Racle_home%\bifoundation\web\bin;%o racle_home%\bin;d:\biclient\jre\bin;%windir%;%windir%\system32;%path%
If not "%2" = = ""%2
===========================================
Note:while connecting from your standalone BI Admin tool client in online mode, the Admin tool would use the tnsnames. ORA file on the BI server. Ensure that your connection pool have the correct entries for your Oracle DB and that the tnsnames. ORA file on the BI server has the corresponding TNS entry.
Instead use the short format for database connection string in the Data Source Name field e.g. dbservername:1521/o Rcl
Oracle BIEE 11g Client Connection OCI 10g/11g data source times wrong
