Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 12C: Odbc driver returned an error (SQLExecDirectW). ORA-12154: TNS:could not resolve the conn
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.
Answers
-
Which version of OBIEE 12c are you running? tnsnames.ora file is in a different location in 12.2.1.4 & higher
0 -
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!
0 -
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/oracledbThis 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)
0 -
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.
0 -
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*!
0 -
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 :-)
0 -
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;
]]
0
