Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Identifying Oracle Homes for OCI Drivers

Posted by Venkatakrishnan J on October 3, 2007

I have been trying to figure out how OBI EE finds ORACLE_HOME in Windows machines. I do not have ORACLE_HOME set on my machine. And i have 2 instances of Oracle DB running. But whenever i try to connect to a database using OCI in BI Administrator, it always picks up the one that i have installed as the latest. In Solaris and UNIX we do not have the problem since we explicitly have to specify the ORACLE_HOME in the user.sh or system.sh files. And it looks like, it depends on how your PATH variable has been constructed. For example, let me take a sample PATH variable. My PATH variable looks like this

PATH=D:\oracle\product\10.2.0\db_1\bin;D:\oracle\BISE1\db\bin

As you see above, the path variable has the link to both the Oracle Homes. But it looks like BI Administrator would always pick the one that is entered first(or the latest install among the 2 databases). In the case above, the Oracle Home used would be D:\oracle\product\10.2.0\db_1\bin. This can be verified in command prompt using the tnsping utility.

Lets first try to ping ORCL (an entry in my tnsnames.ora file in D:\oracle\product\10.2.0\db_1\network\admin).

As you see above, the sqlnet.ora file used is the one that comes from D:\oracle\product\10.2.0\db_1\network\admin. Now lets change the PATH variable in such a way as to have the 2 database home as the first one.

PATH=D:\oracle\BISE1\db\bin;D:\oracle\product\10.2.0\db_1\bin

Now try pinging ORCL(an entry in my tnsnames.ora file in D:\oracle\BISE1\db\network\admin).

As you see above, the sqlnet.ora file used is the one that comes from D:\oracle\BISE1\db\network\admin which means that our OCI path has changed and i can start using my tnsnames entries in this path. So simple but still took me some time to figure out. But the lingering question still remains. Can we effectively use only one Oracle Home instance? What if we have different tnsnames entries in different Oracle Homes. Well, looks like the only way as of now is to have all the entries in the same tnsnames.ora file and use that Oracle Home’s OCI.

About these ads

16 Responses to “Oracle BI EE 10.1.3.3/2 – Identifying Oracle Homes for OCI Drivers”

  1. [...] still getting this error, then if you are on windows check the Path variable. Check my blog entry here to know what your path should have. If you are Linux/Solaris check whether you have ORACLE_HOME, [...]

  2. Raymond de Vries said

    Hi Venkatakrishnan,

    I am having problems getting my OBI-EE working using the Oracle InstantClient to connect to the Oracle database. In Windows it worked fine but now in a Linux I keep getting the error that it cannot find the DLL /server/Bin/libnqsdbgatewayoci10g.so and when I check the file does exist. I have set my LD_LIBRARY_PATH to point to the Oracle InstantClient. When I use the Instant Client SQLPLUS I can connect to the database. I know that OBI-EE does work with Oracle InstantClient but I cannot get it to work. Have you got any ideas?

    Regards
    Raymond

  3. Raymond de Vries said

    Venkatakrishnan,

    I found the problem. The instantClient that I downloaded was for Oracle 11g and OBI-EE 10.1.3.3.1 does not support 11g. When you select the database version in the connection pool, 10g / 11g uses the 10g OCI.

    Raymond

  4. Venkatakrishnan J said

    @Raymond – Are you sure it is because of the 11g OCI drivers. I think it does not work because of your LD_LIBRARY_PATH is not set properly. Search for libnqsdbgatewayoci10g.so in your linux db client and include that path to be a part of your LD_LIBRARY_PATH. That should make it work.

  5. Joel N said

    Venkatakrishnan, I am having the exact same failure to load the DLL issue here on a Windows machine.
    Some configuration background:
    - Oracle Client is loaded on machine
    - PATH contains D:\oracle\product\10.2.0\client\bin; and the OracleBI server & web entries
    - tsnsnames.ora and sqlnet.ora files are located in d:\oracle\..\client\network\admin folder
    - I can tnsping all six of my tsnames (DSCF1 is the one I am using in BI)
    - I can access all of these service names using both SQL Plus and SQL Developer
    - I even built an ODBC entry using the service name DSCF1 with success

    In my physical layer connection pool, I have 10g/11g OCI selected and my service DSCF1 coded. I included the correct UserID and pwd in the configuration as well.

    When I try to “view data” on a table, I get the dreaded nQSError:46029 Failed to load the DLL D:\OracleBI\server\bin\nqsdbgatewayoci10g.dll.
    I am at a total loss here as I believe everything is set up properly since the service names are working in other utilities.
    One caveat, I installed Oracle BI prior to installing the Oracle Client. But, a co-worker installed Oracle Client then Oracle BI with unfortunately the same results.

    I can map to and use this same repository on a Unix machine with no issues. My problem is with my Windows install.

    Any words of wisdom for the weary here?
    Thank you,
    Joel
    a.k.a. “Really Stumped”

  6. Joel N said

    Sorry to bother. Not sure how or why, but I think it seems to be working correctly now.

  7. Dan Sielicki said

    Oracle InstantClient is NOT supported (will not work) for OBIEE period!

    Never install any Oracle Client after installing OBIEE.

    Always reinstall JDK/sdk after installing / updating any oracle client.
    Resolves nqsdbgatewayoci10g.dll bug

  8. Kurt Wolff said

    I’m running into the same issue. My path is correct — it points to the 11g database (fed up with the issues that seemed to result with both 10g and 11g installed on the same Win box, I deinstalled all Oracle DB products and then re-installed 11g). Tnsping brings back the right location of the sqlnet.ora file. Dan, you say re-install the JDK/sdk to resolve the “nqsdbgatewayoci10g.dll bug”. Can you provide any more details? Is there an issue with which version to install, any sequence of events that should be followed?

  9. ck said

    I 2 am looking for this solution, anyone?

  10. Tommy65 said

    Hi, unfortunately same problem…

    Reinstalled JDK but nothing.

    Server: Oracle 11g, Client 11g: not working

    Server: Oracle 11g, Client 10g: not working

    Please notice that when I installed the clients I didn’t select “Install Client” but “Administrator”.

    New ideas?

  11. Richard said

    Hi

    im having the same problem. I had a fully functional version of BIEE (Windows 2003 R” – x64) using the 10g client. I upgraded from biee 10.1.3 to 10.1.4. All good. I then installed the 11g client. Biee is still trying to use the 10g client. If i remove the 10g tnsnames.ora i get the ‘nqsdbgatewayoci10g.dll’ error. if i reinstate the file its fine but biee is still using the 10g client.

    Oracle suggested to reinstall the jdk after the client. This solved the dll issue but i now get a ‘ora-12154 tns could not resolve connect identifier’.

    Anyone had any joy with this?

    Regards
    Richard

  12. Ken said

    Greetings,

    As stated from the above threads, I too have received the following error.
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. ld.so.1: nqsserver: fatal: libclntsh.so.10.1: open failed: No such file or directory [nQSError: 46029] Failed to load the DLL /usr/local/OracleBI/server/Bin/libnqsdbgatewayoci10g.so. Check if ‘Oracle OCI 10G’ database client is installed. (HY000)

    We are on Sun Solaris. The Admins state the ODCB client is there. We can access the service name via SQL+ and through the Connection Pool within the Admin Tools.

  13. jhall_uk said

    Hello,

    I’m struggling with this nqsdbgatewayoci10g.dll issue too when trying to do an import from database via the 10g/11g OCI drivers in BI Administrator (10.1.3.4.0):

    0xc1
    nQSError:46029 Failed to load the DLL
    C:\OracleBI\server\Bin\nqsdbgatewayoci10g.dll. Check if ‘Oracle OCI
    10G’ database client is installed.

    I’m running Vista 64 bit with 11g db installed first (and all working) via tns etc.

    OBIEE installed next on c: drive (I read somewhere that the c: drive was recommended for this).

    No other oracle products installed (apart from SQL Developer).
    The dll exists but it is not found by BI administrator.

    ORACLE_HOME set to my 11g home.

    Have tried setting a JAVA_HOME and moved the %ORACLE_HOME%/bin path to the start of the windows PATH env variable. Nothing has worked.

    I had no problems with getting this working on XP Pro.

    Not sure what to look for next.

    cheers,
    John

    • Anand said

      Hi John,

      I’ve a similar situation. Please let know if u have found a solution for this.

      Thanks in advance.

      Regards,
      Anand

  14. OBIEE guy said

    I re-install the java again and it works!

    Here is the order:

    1.Java
    2.OBIEE
    3.Oracle Client
    4.Java

    Thanks.

  15. DonnaF said

    Do you mean that you reinstalled all of those, in the order shown, including Java twice? Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: