1 2 Previous Next 17 Replies Latest reply: Jun 16, 2011 10:21 AM by 860321 RSS

    After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW

    860321
      Hi all,

      for an OBI upgrade from 10g to 11g I used a RPD and Webcatalog based on a remote machine other than my local machine. Firstable I am making the tests on a Linux Virtual machine, which uses the Sample Lite version from Oracle.
      I made the following steps:

      1. I upgraded the RPD and Webcatalog. It worked without any errors.
      2. I set the static variable BI_EE_HOME via Administration Tool to the path in the Linux machine.
      --> Here I am not sure if the set path is correct. It begins with: \bi\ ...\instances\...\coreapplication_obis1 (... : I can't mention here the whole path name).
      --> When the OBI Server is installed in Windows, the mentioned static variable points to something like 'C:\<install folder>\...\coreapplication_obis1'
      --> Question: is the begin of my set path correct?. I set that pointing to the Linux OBI Server, because the OBI Server is installed on Linux.

      Problem:
      After login as the user in RPD: Administrator/<Password for my upgraded RPD> I can see the links in the Catalog and the Dashboards links. But I can not see any values, any Reports when going through the links.
      For example when trying to see a Dashboard with date 12.02.2011it happens:

      Odbc driver returned an error (SQLExecDirectW).
      Status: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] general error.
      [nQSError: 43113] Message returned from OBIS.
      [nQSError: 43119] Query Failed:
      [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
      at OCI call OCIServerAttach.
      [nQSError: 17014] Could not connect to Oracle database. (HY000)
      SQL Issued: SELECT "Dimension_-_My Dimension"."MyDimension" saw_0 FROM "Accounting (ACC)" WHERE "Dimension_-_My Dimension"."MyDimension" LIKE 'J%' ORDER BY saw_0

      I think its not retrieving the data from the remote database, because I don't have the data of the remote database in my local. I think the OBI Server still is pointing to the local virtual machine Oracle database.

      Can anybody tell me what happens here and how can I solve this problem?.

      I would appreciate any help....
        • 2. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
          gerardnico
          This is not the good forum.

          Try this one:
          Business Intelligence Suite Enterprise Edition

          Your problem is:
          ORA-12154: TNS:could not resolve the connect identifier specified
          OBIEE can not connect to the database.

          Which "Data Source Name" parameter did you enter ?

          Normal, if you have the good parameter, you may connect without problem with the:
          - easy connect method
          - or the connect descriptor
          http://gerardnico.com/wiki/dat/obiee/oci?&#easy_connect_method

          Cheers
          Nico
          • 3. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
            860321
            thanks for the answer.
            Yes, you are right. This is not the good forum. I was searching for other Oracle Forum, but you gave me the answer.

            As I wrote in my post, I upgraded the RPD and the Web Catalog but these belong to an OBI Server from a remote Linux machine, which is not installed in my computer. The remote Linux machine has its own database. The remote Linux machine is a machine at the customer, where I work.

            In my local machine I have a Linux virtual machine and there runs an OBI Server too for tests. It also has its own database. A Sample database by Oracle.
            So I think as an additional help to solve this problem I need the an import of the remote database on my machine. Because I need the data, which will be shown on the dashboards. Without data I can't see anything.

            Am I right concerning the above statement?.

            My tnsnames.ora is not filled. The only information there is the port 1521.
            Do you mean the DNS in the connection pool in the Administration Tool?, the parameters are filled automatically after the upgrade with the RPD data. The DNS points to the remote database, password and user as well.

            What do you mean with easy connect method?
            and connect descriptor?
            • 4. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
              gerardnico
              To know all about the oracle connection, check first this article:
              http://gerardnico.com/wiki/database/oracle/connect_identifier

              In OBIEE, you can give one of this possibilities ie:
              - tns name
              - easy connect method
              - of a connect descriptor

              I give an example of each in my article:
              http://gerardnico.com/wiki/dat/obiee/oci#easy_connect_method

              What is:
              - the host name of the database
              - the port of the database
              - the service name of the database

              Cheers
              Nico
              • 5. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                860321
                Ok, I will read the articles you recommend.
                But one thing first: I have to give the host, the port and the service name of the database of the remote machine, right?. If I gave these I couldn't access to that database, because the database doesn't exist on my local machine. First I have to import the remote database, can you give me a feedback regarding what I think?

                I just would like understand.
                • 6. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                  gerardnico
                  You don't need to have your database on your local machine or to import it.
                  Just change the parameters.

                  OBIEE make a connection to your database and send query.

                  A local or remote database doesn't change anything.

                  Cheers
                  Nico
                  • 7. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                    860321
                    sorry if I ask and ask. I hope you don't feel bothered about that.
                    If I have the RPD which has data which point to the remote database, should I perhaps set the parameters of the local database and not of the remote database?.

                    I will try that tomorrow in the morning...
                    • 8. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                      860321
                      Just change the parameters

                      do you mean also the parameters in the connection Pool in Administration Tool? remember it has the parameters of the remote database...
                      • 9. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                        gerardnico
                        Yes, the parameters in the connection pool. You need to enter the parameters for the database where your table and data are.

                        Cheers
                        Nico
                        • 10. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                          860321
                          Hi gerardnico,

                          I've tried many things and did as in your articles, but I don't have success with that.
                          These are the things I did:

                          1. I searched for the file tnsnames.ora in my Linux virtual machine. I could find it in many paths:

                          /.../Oracle_BI1/network/admin/samples/tnsnames.ora
                          /.../oracle/xe/app/oracle/product/10.2.0/server/hs/admin/tnsnames.ora.sample
                          /.../oracle/xe/app/oracle/product/10.2.0/server/network/admin/tnsnames.ora
                          /.../oracle/xe/app/oracle/product/10.2.0/server/network/admin/samples/tnsnames.ora
                          /.../oracle/xe/app/oracle/product/10.2.0/server/network/admin/temp/tnsnames.ora
                          /.../usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin

                          in the last path I found the listener.ora as well.

                          2. I took the tnsnames.ora from the last path, because the ORACLE_HOME points to /.../usr/lib/oracle/xe/app/oracle/product/10.2.0/server

                          3. I edited the tnsnames.ora this way:

                          # tnsnames.ora Network Configuration File:

                          XE =
                          (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = host_of_the_remote_machine)(PORT = 1521))
                          (CONNECT_DATA =
                          (SERVER = DEDICATED)
                          (SERVICE_NAME = database_name_of_the_remote_machine)
                          )
                          )

                          EXTPROC_CONNECTION_DATA =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
                          )
                          (CONNECT_DATA =
                          (SID = database_SID_of_the_remote_machine)
                          (PRESENTATION = RO)
                          )
                          )

                          4. I made a backup of the tnsnames.ora in path /.../Oracle_BI1/network/admin/samples/tnsnames.ora
                          and copied the tnsnames.ora from /.../usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin to the Oracle_BI1 path

                          5. I set the TNS_ADMIN to /.../usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin

                          6. I restarted the BI Server

                          No success in what I did. The error is still there.

                          7. I've tried also: sqlplus database_user_remote_machine/database_password_remote_machine@database_SID_remote_machine

                          I didn't get the connection:


                          SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 16 04:36:10 2011

                          Copyright (c) 1982, 2005, Oracle. All rights reserved.

                          ERROR:
                          ORA-12154: TNS:could not resolve the connect identifier specified


                          Enter user-name: database_user_remote_machine
                          Enter password:
                          ERROR:
                          ORA-01034: ORACLE not available
                          ORA-27101: shared memory realm does not exist
                          Linux Error: 2: No such file or directory


                          What am I doing wrong here?. I am still thinking that I need the import of the database.
                          Additional SID=Service-Name=Database-name in the remote machine.
                          • 11. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                            gerardnico
                            You don't read what I'm writing.

                            I try to explain better.

                            First, verify your connection parameter.
                            Try to make a connection with the user who start OBI with for instance the easy connect method
                            http://gerardnico.com/wiki/database/oracle/easy_connect_method
                            sqlplus Username/Password@//host_of_the_remote_machine:1521/service_name
                            This method (and also the connect descriptor) by pass the tnsnames.ora. You don't need it.

                            If you succeed, enter in the Data source Name of the connection pool
                            //host_of_the_remote_machine:1521/service_name
                            Check here an example
                            http://gerardnico.com/wiki/_detail/dat/obiee/obiee_oci.jpg?id=dat%3Aobiee%3Aoci

                            When I read you, I can see
                            ORA-01034: ORACLE not available
                            ORA-27101: shared memory realm does not exist
                            This message says that the instance of the database is not started. Problem with the database ?

                            Cheers
                            Nico
                            • 12. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                              860321
                              You don't read what I'm writing
                              I do read your articles and I am trying to execute and trying to understand them.

                              First, verify your connection parameter.
                              Try to make a connection with the user who start OBI with for instance the easy connect method
                              http://gerardnico.com/wiki/database/oracle/easy_connect_method

                              sqlplus Username/Password@//host_of_the_remote_machine:1521/service_name


                              This method (and also the connect descriptor) by pass the tnsnames.ora. You don't need it.

                              I also tried with the line above, but I gave the name of the host, not the IP address for example:

                              sqlplus Username/Password@//name_of_the_host_server:1521/service_name

                              and the connection doesn't work because I already explained the database is located in the remote machine. I don't have any connection to that host of the remote machine or to that database of the remote machine. I neither have network connection with the remote machine.

                              Again: the RPD and Webcatalog I upgraded are part of the OBI Server of a remote machine, for which I don't have any connection. No network connection. The remote machine belongs to the customer company and is located at the customer company. I don't have any data, any database of them in my local. As far I read from you. It doesn't matter if I don't have the remote database. I don't need the import. But I am still thinking Yes I do.

                              Check here an example
                              http://gerardnico.com/wiki/_detail/dat/obiee/obiee_oci.jpg?id=dat%3Aobiee%3Aoci

                              When I read you, I can see


                              ORA-01034: ORACLE not available
                              ORA-27101: shared memory realm does not exist


                              This message says that the instance of the database is not started. Problem with the database ?

                              the instance of the XE Database in my local Linux Virtual machine where my local OBI Server is located, is started. It runs. But I don't habe any instance of the database of the remote machine in the physical form.

                              So, because the connection via SQL Plus didn't work I try with the tns connection parameters. I gave port, SID, service_name, hostname in the file. Unfortunately the environment variables ORACLE_HOME, ORACLE_SID, TNS_ADMIN are not set permanently. By restart of my local machine they are deleted automatically...I love Oracle BI and Linux, really...

                              Edited by: 857318 on 16.06.2011 06:15

                              Edited by: 857318 on 16.06.2011 06:19
                              • 13. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                                gerardnico
                                When you write:
                                The database doesn't exist on my local machine. First I have to import the remote database.
                                It doesn't mean that you didn't have any access to your database. I understand, I don't have installed a database on my latptop, it's on a remote computer on the local network.

                                You write:
                                The remote machine belongs to the customer company and is located at the customer company. I don't have any data, any database of them in my local. 
                                Then to be able to test your repository, of course you have:
                                - to make a clone of the database on your local network
                                - or to create a network connection via router in order to access your database

                                Success
                                Nico
                                • 14. Re: After Upgrade OBI 10g to 11g:Odbc driver returned an error - SQLExecDirectW
                                  860321
                                  Maybe I have to write more specifical ;-).
                                  I have already a XP database in my local machine. But as said I don't have any network connection, no database of the remote machine, so that I can import in my local database.

                                  Then to be able to test your repository, of course you have:
                                  - to make a clone of the database on your local network

                                  I agree. I have to make a clone of the database of the remote machine on my local.

                                  - or to create a network connection via router in order to access your database

                                  I think you mean here not for access my database. I think you mean for access to the remote database.

                                  Thanks very much!
                                  1 2 Previous Next