This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 31, 2011 12:45 AM by kedjo RSS

Cannot connect to Database from the OBIEE 11g upgraded RPD

793276 Newbie
Currently Being Moderated
Hello,

I am facing issues on the upgraded RPD - 11G. I used the ua batch program (Upgrade Assistant) to upgrade the RPD and Catalog. The upgrade was successful and the new set of upgraded RPD and Catalog got loaded into the servers.

When I try to view the data from the Physical layer I am getting the error message "The Connection has failed."

I also noticed that the Database Type in Physical Layer (Right-click on the Database Object) got changed to Oracle 10gR2. In the RPD prior to upgrade it was Oracle 10gR2/11g. My Backend is on 11G.

I went ahead and changed the database type to Oracle11g in the upgraded RPD. Now when I click on "View Data" , I am getting a different error

"NQS 46028 Unable to get the DLL path for the CLI 10g/11g from the NQSConfig.ini file."

In the Config File I have the below entries:
-----
[DB_DYNAMIC_LIBRARY]

ODBC200 = nqsdbgatewayodbc;
ODBC350 = nqsdbgatewayodbc35;
OCI8 = nqsdbgatewayoci8;
OCI8i = nqsdbgatewayoci8i;
OCI10g = nqsdbgatewayoci10g;
DB2CLI = nqsdbgatewaydb2cli;
DB2CLI35 = nqsdbgatewaydb2cli35;
NQSXML = nqsdbgatewayxml;
XMLA = nqsdbgatewayxmla;
ESSBASE = nqsdbgatewayessbasecapi;
OracleADF = nqsdbgatewayoracleadf;
OracleADF_HTTP = nqsdbgatewayoracleadf;
HyperionADM = nqsdbgatewayadm;
-----
All the above referenced DLLs are present under C:\Middleware\Oracle_BI1\bifoundation\server\bin

I noticed that there is no OCI11g entry either in the NQSConfig or in the C:\Middleware\Oracle_BI1\bifoundation\server\bin folder.

Is this the issue?. If so how can I get the DLL file for 11G.

Did any of you face a similar issue?

Any help would be higly appreciated

Thanks,
Joe
  • 1. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    793276 Newbie
    Currently Being Moderated
    Requesting answers...
  • 2. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    Turribeach Guru
    Currently Being Moderated
    Have you got the 11g Full Oracle Client installed on the same server where the BI Server process is running?
  • 3. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    793276 Newbie
    Currently Being Moderated
    Thanks for the Response.

    Yes I have 11g client and 11g DB server installed and the tnsnames.ora have the connection details for the 11g database. I am able to connect to Database through PLSQL Developer Tool. But RPD cannot connect
  • 4. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    770671 Newbie
    Currently Being Moderated
    This is a behaviour change when we move from 10g to 11g. In BI EE 10g, there was no Oracle Client bundled along with BI EE. So, BI EE will use your Oracle DB Home client to connect to the database. Generally no issues in that case. But in BI EE 11g, the software itself contains the 11.2 DB client. So, when you try to connect to the database through the Repository or BI Server, it will try to find the tnsnames entry in the Oracle Client of BI EE 11g instead of your database. There are 2 options to work around this

    1. Copy your tnsnames.ora to {Oracle_BI1}\network\admin directory & {oracle_common}\network\admin directory (only the former is needed as that is the ORACLE_HOME)

    2. Or instead of tnsnames.ora source entry, use the full expanded tnsnames as shown below

    (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.66.173)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))

    Both of the above will work.

    Thanks,
    Venkat
  • 5. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    Turribeach Guru
    Currently Being Moderated
    Another example of OBIEE 11g over engineering things or installing things not really required. In my view the installer should ask the user if it was to create a new Oracle client for OBIEE 11g or not rather than just installing a new one by default. A new installed client means another installation to patch once Oracle releases a new client. In most cases there will be an existing Oracle client where OBIEE is installed. Also another Oracle client to configure and maintain TNS files. On the positive side the Oracle client installation is tricky to get right on the Unix world due to need to have the 32bits libraries so the separate Oracle client installation for OBIEE 11g should cover that.
  • 6. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    793276 Newbie
    Currently Being Moderated
    Thankyou Venkat. This helped. The new ORACLE_HOME was not explicitly and clearly mentioned in theOBIEE11g Upgrade Instructions !
  • 7. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    794352 Newbie
    Currently Being Moderated
    Hi,

    i had this same 'connection failed' issue but even changing and adding copies of the tnsnames.ora i was still with the same issue i was only able to make it work changing the database name to the one set in the tnsnames.ora.

    May I ask if I understood the same thing as everyone did to make it works ?

    In the 10g in the physical layer whatever the name you set up for the database you are using the connection pool parameters and it's works perfectly. In the 11g is there anyone who was able to use a dummy database name like in the 10g ?

    If not, may I have to worry for 10g upgrade to 11g if the database name not the same from the tnsname.ora ?

    thxs in advance :)

    Kris
  • 8. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    619769 Newbie
    Currently Being Moderated
    Hello,

    FYI, I also had the same database connection issue, but I was not upgrading from 10 to 11. I just did a pure install on a new VM. I had an 11G oracle client installed on Windows Server 2003 (32 bit). After installing OBI 11G I had 2 more oracle homes, one pointing to Oracle_BI1 directory and the other pointing to the oracle_common directory.

    After a few tries (with the correct driver in the connection pool), copying the tnsnames.ora file to Oracle_BI1/network/admin and oracle_common/network resolved the issue. I was also able to put in the exact IP address or the server name inside the tnsnames.ora files, it worked either way.

    The oracle_common folder is not on my classpath and it doesn't have a /network/admin folder, only a /network and a /admin folder (individually, both live under oracle_common) so I am not convinced that this is doing much, but I'll leave it as is for now.

    Anyway, thank you for the help,
    Carmen
  • 9. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    702160 Newbie
    Currently Being Moderated
    Using the following for OCI 10g/11g connection type seems to work.

    (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ip_address)(PORT = 1521))) (CONNECT_DATA =(SID = dbname)))

    Edited by: user721272 on Sep 16, 2010 2:59 PM
  • 10. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    622702 Newbie
    Currently Being Moderated
    I didn't got it working with tnsnames - only with the really fully connect string mentioned above
  • 11. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    622702 Newbie
    Currently Being Moderated
    Yes - I got it!!

    In the directory where biadmin tool ist initialized
    f.e. <biee11>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

    there is a file named user.cmd resp. user.sh, there you can set the TNS_ADMIN - set it to an appropriate path such as <biee11>\Oracle_BI1\network\admin and you can choose the database aliases defined in tnsnames.ora!
  • 12. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    825914 Newbie
    Currently Being Moderated
    Hi,

    I also received same error 'connection failed' during viewing data in phisical layer. The resolution is adding an entry in 'tnsnames.ora' i.e., (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.66.173)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))

    Thanks,
    Naveen Korpole
  • 13. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    799608 Newbie
    Currently Being Moderated
    Did any one mange to connect to the SQL server database on OBIEE 11g? I was not able to import data from SQL server.
  • 14. Re: Cannot connect to Database from the OBIEE 11g upgraded RPD
    844241 Newbie
    Currently Being Moderated
    Hi All,

    I am able to connect SQL Server database with OBIEE 11g RPD offline and imported metadata and verified update row count/view data but when i was trying to publish the same RPD the data is not returning also "update row count/view data" is giving and error "The connection has failed."


    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 16001] ODBC error state: IM002 code: 0 message: [DataDirect][ODBC lib] Data source name not found and no default driver specified. (HY000)

    Environment:
    OBIEE 11g/WLS 10.3.3/Oracle database 11g installed on RHL 5
    Client tools are installed on local Vista 32 bit system.

    Oracle database is working fin, where as SQL Server is giving issues.

    You want me to create a ODBC connection in RHL 5 server to connect to SQL Server ? i am little bit confused here how to create !
    same time i have created ODBC for oracle apps database and imported and able to view data in RPD when i am in Online mode.

    When i open RPD in online mode and trying to import the data i dont have access to local machine ODBC's which is expected and if i change to "Remote Server" nothing is showing here.

    Appreciate your comments on the above.

    Thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points