DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, May 9, 2013

OBIEE 11g Client Admin Tool Connection To Datasource In Offline Mode Fails With Error "The Connection Has Failed"

OBIEE 11g Client Admin Tool Connection To Datasource In Offline Mode Fails With Error "The Connection Has Failed"

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.

You may also notice all or some of the following errors in 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 Oracle Client installation and make sure the correct version of OCI libraries are 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 OCISessionBegin
...

 The issue is not reproducible while in Online mode.

This issue would be due to Multiple Oracle Homes exist which can cause confusion to the OBIEE Admin Tool for where to reference OCI libraries, NLS and the tnsnames.ora entries.
Additional configuration steps are required to ensure the desired files are used.

To prevent this error, you need to complete the following configuration tasks:

NOTE: In all cases, the OBIEE Admin Tool should be started by using the Start -> Programs -> Oracle Business Intelligence -> 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 be 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\ to make sure NLS_LANG key is set to a valid 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;%OBIEE_HOME%\bifoundation\server\bin;%OBIEE_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 tnsadmin %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\ to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.
 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. Please 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


NOTE:  While connecting from your standalone BI Admin Tool client in online mode, the Admin Tool will use the TNSNAMES.ORA file on the BI server.  Ensure that your connection pool has the correct entries for your Oracle DB and that the TNSNAMES.ORA file on the BI server has the corresponding TNS entry.
You may instead use the short format for database connection string in the Data Source Name field e.g. dbservername:1521/orcl

Reference Metalink Note : 1296370.1

6 comments:

Anonymous said...

awful piece of information, I had come to know about your blog from my friend vimal, mumbai,i have read atleast 13 posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, obiee training in hyderebad

Anonymous said...

Really an Important Information I got from your Blog.
Oracle DBA is what i am searching for Trouble shooting the Software.
Once again Thanks for the NewsQlikView Training in Hyderabad BI TOOL

Víctor Manuel Blázquez said...

For Oracle DB 12c this information didn't work. I am not able to connect an 12c DB in the Admin Tool.

Joseph A. Wallace said...

Thanks for InformationFlax IT online training began online training for Software program and SAP courses. Online training is the better option to study software products as well as programs, which are SAP modules, like SAP HR, SAP FSCM, SAP BASIS, SAP ABAP, SAP BW, SAP ED ADMIN, SAP SECURITY, SAP SRM, SAP SCM, SAP GRC etc., and Software Courses like JAVA-J2EE, PeopleSoft, Tibco courses, Microsoft Courses, Network and System Admin, Oracle Courses, SAS, Testingtools, PMP, Cognos, AB Initio, ORACLE and Database etc..

keylabs Training said...

Amazing post thanks for sharing OBIEE

Nasreen Basu said...

Very good idea you've shared here, from here I can be a very valuable new experience. all things that are here will I make the source of reference, ,servicenow training in hyderabad