7 Replies Latest reply on Aug 24, 2018 4:18 PM by Shoeless Joe

    OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier

    Shoeless Joe

      Can someone help me figure out how to fix this TNS error I'm getting in OBIEE Server when trying to run a simple report?

       

      What I'm trying to do - a simple one column report

       

       

      What I get:

       

       

      Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

      State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

      State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach. (HY000)

      State: HY000. Code: 17014. [nQSError: 17014] Could not connect to Oracle database. (HY000)

       

      Server log file:

       

      [2018-08-21T22:28:15.394-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ssi] [tid: bfc89720] nqsserver:    Clustered Oracle BI Server (64-bit) started.  Version: 12.2.1.1.0.^M

      [2018-08-21T22:28:22.851-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-0000000d,0:2491:44:1:6] [sik: ssi] [tid: 6884b700] LCMAPIsUtil.GetSDD-> /app/oracle/biee/user_projects/domains/bi/bidata^M

      [2018-08-21T22:28:23.809-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-0000000d,0:2491:44:1:21] [sik: ssi] [tid: 6884b700] LCMAPIsUtil.GetSDD-> /app/oracle/biee/user_projects/domains/bi/bidata^M

      [2018-08-21T22:29:30.136-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-0000016a,0:1:1:6] [sik: ssi] [tid: 6884b700] LCMAPIsUtil.GetSDD-> /app/oracle/biee/user_projects/domains/bi/bidata^M

      [2018-08-21T22:29:30.828-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000171,0:1:6] [sik: ssi] [tid: 6884b700] LCMAPIsUtil.GetSDD-> /app/oracle/biee/user_projects/domains/bi/bidata^M

      [2018-08-21T22:30:11.392-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000355,0:1:17:5:1] [sik: ssi] [tid: 6b7f7700] New connection created for dsn pdborcl104, DB connection name is pdborcl104:Connection Pool, DB name is pdborcl104, Idle Timeout is Ĭ [[^M

      Properties: description=<<329>> DbGateway Exchange: pdborcl104.Connection Pool; producerID=0x69fd3ff8; requestID=0x2873000d; sessionID=0x28730000; userName=prodney; ^M

      ]]^M

      [2018-08-21T22:30:11.392-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000355,0:1:17:5:1] [sik: ssi] [tid: 6b7f7700] Connection Pool entry for DSN pdborcl104, DB name is , connection pool name is  [[^M

      Properties: description=<<329>> DbGateway Exchange: pdborcl104.Connection Pool; producerID=0x69fd3ff8; requestID=0x2873000d; sessionID=0x28730000; userName=prodney; ^M

      ]]^M

      [2018-08-21T22:30:11.641-05:00] [OBIS] [ERROR:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000355,0:1:17:5:1] [sik: ssi] [tid: 6b7f7700]  [nQSError: 17014] Could not connect to Oracle database. [[^M

      file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 411^M

      Properties: description=<<329>> DbGateway Exchange: pdborcl104.Connection Pool; producerID=0x69fd3ff8; requestID=0x2873000d; sessionID=0x28730000; userName=prodney; ^M

      [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified

      at OCI call OCIServerAttach.^M

      **********  Task: 1. Running for (mls): 245 **********

      Description: DB Connect

      DSN: pdborcl104; userName=bisample

       

       

      **********  Task: 2. Running for (mls): 293 **********

      Description: <<329>>DbGateway Prepare

      DSN:Connection Pool

      User Name:bisample

      SQL:WITH

      SAWITH0 AS (select distinct T220.TYPE as c1

      from

           SAMP_PRODUCTS_D T220 /* D2 Product */ )

      select D1.c1 as c1, D1.c2 as c2 from ( select 0 as c1,

           D1.c1 as c2

      from

           SAWITH0 D1

      order by c2 ) D1 where rownum <= 5000001

       

       

      **********  Task: 3. Running for (mls): 293 **********

      Description: Producer Executing Query

      Repository Name:ssi;Subject Area Name:Sample Sales;User Name:prodney

      Logical Hash of SQL: 0x5ad3c548SQL:SET VARIABLE QUERY_SRC_CD='Report';SELECT^M

         0 s_0,^M

         "Sample Sales"."Products"."Type" s_1^M

      FROM "Sample Sales"^M

      ORDER BY 2 ASC NULLS LAST^M

      FETCH FIRST 5000001 ROWS ONLY^M

      ]]^M

      [2018-08-21T22:30:11.649-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000355,0:1:17:5:1] [sik: ssi] [tid: 6b7f7700] Connection Pool exit for DSN pdborcl104, DB name is pdborcl104, connection pool name is Connection Pool [[^M

      Properties: description=<<329>> DbGateway Exchange: pdborcl104.Connection Pool; producerID=0x69fd3ff8; requestID=0x2873000d; sessionID=0x28730000; userName=prodney; ^M

      ]]^M

      [2018-08-21T22:30:11.649-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: d4df4cb0-fd59-4fc3-bbb1-7f32d6960fe3-00000355,0:1:17:5:1] [sik: ssi] [tid: 6b7f7700] Connection destroyed due to timeout. dsn is pdborcl104, DB name is pdborcl104 [[^M

      Properties: description=<<329>> DbGateway Exchange: pdborcl104.Connection Pool; producerID=0x69fd3ff8; requestID=0x2873000d; sessionID=0x28730000; userName=prodney; ^M

      ]]^M

      [2018-08-21T22:33:09.404-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 005SzrD9tC8Fw000jzwkno0006tm00007K,0] [sik: ssi] [tid: a9a9a700] LCMAPIsUtil.DoesSIMetadataNeedRefresh-> called with token 2016-07-12T03:59:39.081-05:00 : False^M

       

      Windows Connection pool

       

       

      upload succeeds

       

      tnsnames.ora (windows client)

      orcl102=

      (description=

           (address = (protocol = TCP)(host = oracle102)(port = 1521))

      (connect_data = (SERVICE_NAME=orcl))

      )

      pdborcl104=

      (description=

           (address = (protocol = TCP)(host = oracle104)(port = 1521))

      (connect_data = (SERVICE_NAME=pdborcl))

      )

      orcl104=

      (description=

           (address = (protocol = TCP)(host = oracle104)(port = 1521))

      (connect_data = (SERVICE_NAME=orcl))

      )

       

      tnsnames.ora (OBIEE server)

      LISTENER_ORCL =

        (ADDRESS = (PROTOCOL = TCP)(HOST = demo.us.oracle.com)(PORT = 1521))

       

      pdborcl104 =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = oracle104)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = pdborcl)

          )

        )

      orcl104 =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = oracle104)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = orcl)

          )

        )

       

      Same file in all three places):

      $TNS_ADMIN (/app/oracle/db/product/12c/dbhome_1/network/admin)

      /app/oracle/biee/user_projects/domains/bi2/config/fmwconfig/bienv/core/tnsnames.ora

      app/oracle/biee/user_projects/domains/bi/config/fmwconfig/bienv/core/tnsnames.ora

       

      sqlplus bisample/bisample@pdborcl104 successfully connects in both bi server and windows client.

        • 1. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
          Joel Acha

          Which version of OBIEE 12c are you running? tnsnames.ora file is in a different location in 12.2.1.4 & higher

          • 2. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
            Shoeless Joe

            Server (Oracle Enterprise Linux 6.7 x64 as guest on Windows 10 host)

            OBIEE 12.2.1.1

            Oracle Database 12.1.0.2 EE

             

            Client (windows 10 host)

            Oracle BI Administration Tool 12.2.1.3

             

            As far as i can tell, the tns connect issue is only with OBIEE on Linux/weblogic.  I can connect everywhere else.  It would be helpful to know how OBIEE is trying to connect...what tnsname is it using.  I've tried to get logging information to show me that, but everything i've tried (sqlnet.ora parameters, tnsnames.ora parameters, lsnrctl) gives me no valuable info.  I am looking in the wrong place or maybe there's something that needs to be bounced that i'm not bouncing ... allthough i've tried bouncing the listener, the database, the entire stack.

             

            Thanks for your help!

            • 3. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
              SteveF-Oracle

              Looks like you are using the SampleApp vm..

               

              Do you have sqlnet.ora?

               

              Do you have TNS_NAMES env var configured on obis.properties pointing somewhere else (where there is tnsnames.ora? sqlnet.ora?)

               

              If not, as Joel Acha mentioned, in 12.2.1.4, the location of the tnsnames.ora should be:

               

              [DOMAIN_HOME]/bidata/components/core/serviceinstances/ssi/oracledb

               

              This is a documentation bug and documented in My Oracle Support
              OBIEE 12.2.1.4.0: Can Not Connect to Oracle Database due to ORA-12154 with Tnsnames.ora (Doc ID 2408448.1)
              and the document Joel pointed you to.

               

              This is strictly the OBI Server(obis1 - nqsserver) connecting to the data source (i.e. - the only component to bounce in this scenario)

              • 4. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
                Shoeless Joe

                Thanks Steve!  That makes sense, and I will make note of that for future reference.  Although in my SampleApp image, I see a $TNS_ADMIN, not a $TNS_NAMES.

                 

                Yep, it was the SampleApp.  I must confess, I did not get a chance to try out your suggestion.  Trying too hard to fix the issue and making things worse by exploring every wrong direction, i ended up breaking the image and had to start over with new VM.  The good news is that this is working now and I learned a great deal in the process.

                • 5. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
                  Christian Berg

                  Shoeless Joe wrote:

                   

                  Trying too hard to fix the issue and making things worse by exploring every wrong direction, i ended up breaking the image and had to start over with new VM. The good news is that this is working now and I learned a great deal in the process.

                  ^-- Give this man a beer! *applause*!

                  • 6. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
                    SteveF-Oracle

                    Welcome.. sorry about the typo.. it is TNS_ADMIN, not TNS_NAMES.. brain moves faster than fingers.

                     

                    I suppose technically Joel's response is the first correct answer ;-)

                     

                    I learn by breaking things, too, no harm in that :-)

                    • 7. Re: OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the connect identifier
                      Shoeless Joe

                      Haha - yeah its a (painful) process.  My apologies to Joel.  I saw that he did some earlier work on that, and he deserves credit for it.  I'll mark Joel's answer as correct then

                       

                      I tried creating tnsnames.ora in the directory you suggested, but in the sampleapp image, it appears the directory structure does not quite match.

                       

                      Suggested structure: ...bidata/components/core/serviceinstances/ssi...

                      My image's structure:  ...bidata/service_instances/ssi...

                       

                      I tried creating the oracledb directory under ssi and putting a symbolic link there to the tnsnames.ora that lives under $ORACLE_HOME.  Here's the actual path:

                       

                      /app/oracle/biee/user_projects/domains/bi/bidata/service_instances/ssi/oracledb

                       

                      That did not work...same ORA-12154 that i've come to know too well.

                       

                      I ended up punting and using the long tns entry as the data source name in the admin tool. That finally worked, but its something of a hollow victory.  It's interesting that this is the same DSN used by the image example.  They do not use a tnsnames in their examples.

                       

                      (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl)))

                       

                      For anyone who'd like to look further into this-I'm burned out on it...here are some log details:

                       

                      What BAD looks like:

                      ]]

                      [2018-08-24T10:06:26.758-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000042c,0:1:24:5:1] [sik: ssi] [tid: 464fc700] New connection created for dsn pdborcl, DB connection name is pdborcl:Connection Pool, DB name is pdborcl, Idle Timeout is Ĭ [[

                      Properties: description=<<578>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0xd1f6000e; sessionID=0xd1f60000; userName=prodney;

                      ]]

                      [2018-08-24T10:06:26.758-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000042c,0:1:24:5:1] [sik: ssi] [tid: 464fc700] Connection Pool entry for DSN pdborcl, DB name is , connection pool name is  [[

                      Properties: description=<<578>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0xd1f6000e; sessionID=0xd1f60000; userName=prodney;

                      ]]

                      [2018-08-24T10:06:26.954-05:00] [OBIS] [ERROR:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000042c,0:1:24:5:1] [sik: ssi] [tid: 464fc700]  [nQSError: 17014] Could not connect to Oracle database. [[

                      file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 411

                      Properties: description=<<578>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0xd1f6000e; sessionID=0xd1f60000; userName=prodney;

                      [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified

                      at OCI call OCIServerAttach.

                       

                      **********  Task: 1. Running for (mls): 195 **********

                      Description: DB Connect

                      DSN: pdborcl; userName=bisample

                       

                      **********  Task: 2. Running for (mls): 211 **********

                      Description: <<527>>DbGateway Prepare

                      DSN:Connection Pool

                      User Name:bisample

                      SQL:WITH

                      SAWITH0 AS (select distinct T220.PROD_DSC as c1,

                           T220.TYPE as c2

                      from

                           SAMP_PRODUCTS_D T220 /* D2 Product */ )

                      select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,

                           D1.c1 as c2,

                           D1.c2 as c3

                      from

                           SAWITH0 D1

                      order by c3, c2 ) D1 where rownum <= 5000001

                       

                       

                      **********  Task: 3. Running for (mls): 212 **********

                      Description: Producer Executing Query

                      Repository Name:ssi;Subject Area Name:Sample Sales;User Name:prodney

                      Logical Hash of SQL: 0x9b67a91SQL:SET VARIABLE QUERY_SRC_CD='Report';SELECT

                         0 s_0,

                         "Sample Sales"."Products"."Product" s_1,

                         "Sample Sales"."Products"."Type" s_2

                      FROM "Sample Sales"

                      ORDER BY 3 ASC NULLS LAST, 2 ASC NULLS LAST

                      FETCH FIRST 5000001 ROWS ONLY

                      ]

                      [2018-08-24T10:06:26.958-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000042c,0:1:24:5:1] [sik: ssi] [tid: 464fc700] Connection Pool exit for DSN pdborcl, DB name is pdborcl, connection pool name is Connection Pool [[

                      Properties: description=<<578>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0xd1f6000e; sessionID=0xd1f60000; userName=prodney;

                      ]]

                      [2018-08-24T10:06:26.958-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000042c,0:1:24:5:1] [sik: ssi] [tid: 464fc700] Connection destroyed due to timeout. dsn is pdborcl, DB name is pdborcl [[

                      Properties: description=<<578>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0xd1f6000e; sessionID=0xd1f60000; userName=prodney;

                      ]]

                       

                       

                      And GOOD

                      [2018-08-24T10:35:29.06-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000065d,0:1:9:5:1] [sik: ssi] [tid: 454ec700] New connection created for dsn (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl))), DB connection name is pdborcl:Connection Pool, DB name is pdborcl, Idle Timeout is Ĭ

                      Properties: description=<<10352>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0x5802000e; sessionID=0x58020000; userName=prodney;

                      ]]

                      [2018-08-24T10:35:29.06-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000065d,0:1:9:5:1] [sik: ssi] [tid: 454ec700] Connection Pool entry for DSN (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl))), DB name is , connection pool name is  [[

                      Properties: description=<<10352>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0x5802000e; sessionID=0x58020000; userName=prodney;

                      ]]

                      [2018-08-24T10:35:29.87-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000065d,0:1:9:5:1] [sik: ssi] [tid: 454ec700]  [16020] Metadata Database Type: Oracle 11g [[

                      Properties: description=<<10352>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0x5802000e; sessionID=0x58020000; userName=prodney;

                      Data Source Name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl)))

                      Data Source Type: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64b

                      ]]

                      [2018-08-24T10:35:29.160-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000065d,0:1:9:5:1] [sik: ssi] [tid: 454ec700] Connection Pool exit for DSN (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl))), DB name is pdborcl, connection pool name is Connection Pool [[

                      Properties: description=<<10352>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0x5802000e; sessionID=0x58020000; userName=prodney;

                      ]]

                      [2018-08-24T10:35:29.160-05:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 45b94aeb-c51e-4a6e-ab42-691e3521d737-0000065d,0:1:9:5:1] [sik: ssi] [tid: 454ec700] Connection destroyed due to timeout. dsn is (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl))), DB name is pdborcl [[

                      Properties: description=<<10352>> DbGateway Exchange: pdborcl.Connection Pool; producerID=0xf83cff8; requestID=0x5802000e; sessionID=0x58020000; userName=prodney;

                      ]]