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.