cancel
Showing results for 
Search instead for 
Did you mean: 

Connect to External DB failed

Former Member
0 Kudos

Hi,

While trying to connect BI system on windows/oracle to external oracle DB, we are getting following errors,

After entering Details and on saving in DBCON,

Error: Password invalid (choose another password)

Executing Program ADBC_TEST_CONNECTION gives,

Testing a Database Connection Defined in DBCON

Could not open connection PMOORACLNT .

sql error 12.154 occured:

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

dev_w1 trace:

Mon Nov 16 18:04:40 2009

B Connect to PMOORACLNT as PMOUSER with PMOUAT

C Client NLS settings:

C Connecting as PMOUSER/<pwd>@PMOUAT on connection 1 (nls_hdl 0) ... (dbsl 700 250407)

C Nls CharacterSet NationalCharSet C EnvHp ErrHp ErrHpBatch

C 0 UTF8 1 05CCF118 05CD46B4 05CD3F3C

C Detaching from DB Server (con_hdl=1,svchp=1FC964A8,srvhp=217D2484)

C Attaching to DB Server PMOUAT (con_hdl=1,svchp=1FC964A8,srvhp=217D2484)

C *** ERROR => OCI-call 'OCIServerAttach' failed with rc=12154

[dboci.c 4313]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 11044]

B ***LOG BY2=> sql error 12154 performing CON [dbds#1 @ 1044] [dbds 1044 ]

B ***LOG BY0=> ORA-12154: TNS:could not resolve the connect identifier specified [dbds#1 @ 1044] [dbds 1044 ]

---

TNSNAMES.ORA Content

TD1.WORLD=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = sapcpg01)

(PORT = 1527)

)

)

(CONNECT_DATA =

(SID = TD1)

(GLOBAL_NAME = TD1.WORLD)

)

)

PMOUAT=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PMOUAT)

)

)

-


SQLNET.ORA file

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

SQLNET.EXPIRE_TIME = 10

DEFAULT_SDU_SIZE=32768

NAMES.DIRECTORY_PATH = (TNSNAMES)

-


I was able to ping and connect using following commands at os level,

tnsping PMOUAT

sqlplus pmouser/<pwd>@PMOUAT

Please suggest what can be done further to avoid error

Regards,

Ramesh R

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member204746
Active Contributor
0 Kudos

you might have multiple tnsnames.oar files. check that out.

basically, Oracle client cannot find connection identifier PMOUAT. remember that SPA use SAPserviceSID user ID. so, check paths or if you have set TNS_ADMIN.

Former Member
0 Kudos

Hi Eric

yes, we do have 2 tnsnames.ora files,

E:\usr\sap\TD1\SYS\profile\oracle\tnsnames.ora

E:\oracle\TD1\102\NETWORK\ADMIN\tnsnames.ora

Which file we need to modify, I tried to change both files, still did not work

for SIDadm user,

TNS_ADMIN=E:\usr\sap\TD1\SYS\profile\oracle\tnsnames.ora

do we need to add the same env variable to SAPServiceSID user? What other env variables to be added for the user SAPServiceSID?

Thanks,

former_member204746
Active Contributor
0 Kudos

you need to change E:\usr\sap\TD1\SYS\profile\oracle\tnsnames.ora

in doubt, change both files.

Former Member
0 Kudos

Hi,

I tried to update both files. Still no use. Can u clarify following things.

As per note : 518241 - DB Connect in BW for an external Oracle database

The source DB must have at least the release version of the BW DB. Our BW DB version is 10.2.0.1, Source DB is Oracle 9G, Will it be any issue.

Can we install another client libraries along with exising one. Will it create any problems with original BW system.

Also, Is it necessery to have <sid>adm user in source system? Please confirm

Ramesh

former_member204746
Active Contributor
0 Kudos

to start out, Oarcle 10.2.0.1 is not supported. upgrade to at least 10.2.0.2,. I recommend using 10.2.0.4.

also, in file sqlnet.ora (both locations), add this line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

good luck.

Former Member
0 Kudos

Hi Eric

DB version is 10.2.0.2, I changed to sqlnet files too. Still it is not recognizing the PMOUAT DB. Do we need to install lower version DB libraries as source system is on 9.2. But i can able to connect from OS level to source DB.Any other settings required?

******************************************

C:\Documents and Settings\<SID>adm>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 23 12:01:53 2009

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

******************************************

C:\Documents and Settings\<SID>adm> sqlplus pmouser/pmouser@pmouat

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 23 12:01:53 2009

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

*****************************************

Thanks,

former_member204746
Active Contributor
0 Kudos

at OS level, login as SAPservice[SID] and do the same tests.

if this does not work, add an system environement variable:

TNS_ADMIN=E:\usr\sap\TD1\SYS\profile\oracle\tnsnames.ora

Former Member
0 Kudos

Hi Eric,

Issue solved now. Problem was with my tnsnames,sqlnet files. Both entries are in different domain. After adujusting the files dbconnect working fine. thanks for the help

Ramesh

Former Member
0 Kudos

Hi Ramesh,

I have the same problem to connect to external Oracle DB.

Could you please share your SQLNET.ORA and TNSNAMES.ORA.

Please provide your Email ID too.

Regards,

Sendil

Former Member
0 Kudos

Hi Senthil,

I have sent a mail to your ID on SDN. Please let me know in case of queries.

Thanks,

0 Kudos

This message was moderated.