Connection test problem from DBCON to non-sap Orac... - SAP Community

Hi,

We need some data from external oracle (non-sap db), all settings are implemented but return two ORA -XXXX error from DBCON.

I have successfully accessed and tested from SQLPLUS(in sap server) is not a problem !.. but when connection test from DBCON ORA-XXX getting the error.

We use, Windows 2008 R2 Ent. SP1 + SAP-Oracle 11.2.0.2 64bit, Remote Oracle DB 11.2.0.1 64bit...

Our SAP Servers, access to Remote DB from Site-to-Site VPN ( Firewall Access any-to-any not block data or port )

I tried some settings in sqlnet.ora&tnsnames.ora (my side and remote site) is not solved problem all timeout and authentication settings tried... how to solve those problems?

-- ERROR Database connection RIGA: SQL error 3135 con=1(GSRTTST.WORLD): ORA-03135: connection lost contact

-- ERROR Database connection RIGA: SQL error 28547 con=1(GSRTTST.WORLD): ORA-28547: connection to server failed, probable Oracle Net admin error

last settings;

TRIED....

GSRTTST.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = 10.2.7.4)

(PORT = 1521)

)

)

(CONNECT_DATA =

(SID = GSRTTST)

)

)

OR -

TRIED....

GSRTTST.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = 10.2.7.4)

(PORT = 1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = GSRTTST)

)

)

OR -

TRIED....

GSRTTST.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = 10.2.7.4)

(PORT = 1521)

)

)

(CONNECT_DATA =

(SID = GSRTTST)

(GLOBAL_NAME = GSRTTST.WORLD)

)

)

SQLNET.EXPIRE_TIME = 5 (tried 10)

SQLNET.INBOUND_CONNECT_TIMEOUT = 300 (tried some values)

SQLNET.SEND_TIMEOUT = 300

SQLNET.RECV_TIMEOUT = 300

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT) (tried one of one)

SQLNET.AUTHENTICATION_SERVICES = (NONE, NTS) (tried only NONE or NTS)

SAP Managed Tags:
Oracle Database

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (4)

Answers (4)

Hi Mutlu,

sorry I forgot some information:

in the Scripts

<DB-User> means the user of remote db

<passwd> means password of this remote-db-user

<SID> means the connection as mentioned in tnsnames.ora

for example scott/tiger@remote_db

Regards

Stephan

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Stephan,

Thank you for sharing good idea, i tested good example.

I opened a case for support, I'm waiting for sap consultants about the problem. if i have found another solution or When the problem is resolved, i write here.

thank you for your help.

hi,

we normally use oracle client 10.2.0.4 v5, sap consultant says; please try 10.2.0.5 and 11.2.0.2 client version. I tried two options but problem continues.

what is your SAP-BASIS patch version ? we currently use SAPKB70020, from sap site i have found SAPKB70025 (5 New Patch)

I don't think the problem is from Oracle Client... i think about the problem from basis components...

Hello Mutlu,

the problem was solved applying SAP NOTE 998399. The lite version of the Oracle client can only deal with

character sets WE8DEC and UTF8. If your target db uses another character set you will get ORA-28547.

The connection will work from sqlplus though.

Hope this helps,

Thomas

Hi,

I already tried this note with the three different client version 10.2.0.4 v5, 10.2.0.5 and 11.2.0.2 but problem is not solved...

I think found the source of the problem. but how to solve this i don't know !

when connect from sqlplus in oracle installed directory ( D:\oracle\HVQ\11202>sqlplus sap/XXXX@GSRTTST ) return to success !!!

but when connect from sqlplus in sap DIR_CT_RUN directory ( D:\usr\sap\HVQ\SYS\exe\uc\NTAMD64>sqlplus sap/XXXX@GSRTTST ) return to fail ORA-03135 or ORA-28547

by the way, i tried three version of sap oracle instant client is the same problem... (10.2.0.4 v5; 10.2.0.5; 11.2.0.2)

Hi,

Problem resolved with the SAP NOTE : 998399, i'm sorry skip the one step...

Thank you for all support

Hi Mutlu,

as SAP Note 1478060 mentioned I took Oracle-Client 10.2.0.4 Material-No 51039412 for the Installation/Migration.

DBSL Patchnumber 148 was incluede in Kernel 7.01 PL150.

So now I've updated to Oracle-Client 11.2 Material-No 51038966 and DBSL Patchnumber 165 which I got from ServiceMarketplace (Kernel) as described in notes 998004 and 1261329.

This didn't solve the problem, but nevertheless it's a good idea to stay up-to-date. I'm getting crazy that SAP tells you in one note to take client 10.2.0.4 and in another that 10.2.0.4 client is out of date

SAP is still working on my problem, but I found the solution after a night out of sleep.

Do the following:

Create two litlle scripts at OS-Level

test.bat

whoami > <PATH>werbinich.log

set > <PATH>check_env.log

sqlplus /nolog @<PATH>test_connection.sql

and

test_connection.sql

connect <DB_USER>/<passwd>@<SID>

select instance_name from V$instance;

exit;

where

<PATH> = Path to your scripts test.bat and test_connection.sql

Now create a new external command "ZTEST" in SAP via sm49 where command is "<PATH>test.bat

Save this command and execute it.

You will now see that sqlplus-connect will not work.

check check_env.log

I'm quite sure you will find several entries in PATH Variable for the SAP-Kernel-Directory. Copy the output of PATH-Variable

Now edit test.bat

insert following command before the sqlplus command:

set PATH=<Your PATH variable but with only one SAP-Kernel-Directory-entry>

save test.bat

Reexecute it in sm49

Sqlplus will now work! So root cause is the duplicate entries for KERNEL-DIR in PATH Variable.

So far so good, but at the moment I've no idea in which way SAPServiceW30 is getting it's environment. I've found Registry-Keys user variables, System_variables ...

I'll keep you informed.

Regards

Stephan

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Mutlu,

I've got the same problem.

We have migrated our System last weekend from HP-UX Oracle to Windows 2008R2 Ora11 last weekend.

Since the migration the external db-Connection does not work any longer.

Still the same connect via sqlplus works properly, via DBCON I got a ORA-28547.

SAP already asked me to update Oracle-Client to 11.2 as mentioned in note 998004 even when note 1478060 tell you that you should use Oracle-Client 10.2.0.4.

Have you already gone thrue SAP note 1261329?

From my point of view everthing looks fine on my side. (environment, .ora Files etc.)

Prio1 message is in processing by SAP.

I'll keep you informed.

Regards

Stephan

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi,

- I Already tried SAP note 1261329, problem is not solved

- we have made the same way (hp-ux to Windows 2008 R2) when tested from hp-ux platform, db connection success

- I have succefully tested Remote MSSQL ! db connection in a new platform (Windows) but ORACLE Remote db connection failed...

- Our Oracle version 11.2.0.2 and Oracle Client version is 10.2.0.4 - ver.5... our basis consultant says; recommended version your platform. (SAP Netweaver 7.0) please do not use 11.2 client, 11.2 client recommendation for SAP Netweaver 7.1 and later....

What is your platform Oracle Db Version, Oracle Client Version and SAP Version ?

Edited by: Mutlu OZEL on Jan 27, 2012 9:09 AM

Hi,

what are your settings in trx: DBCO (assuming ID Password is an account on the remote database)

Can you tnsping the remote server OK ?

Mark

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi,

Tnsping return success and sqlplus return success...

DBCO Parameters;

Connection Name: RIGA

Database Type: Oracle

Username: sap

Password: hl_5kld

TNS Name: GSRTTST

And tried alternative;

TNS Name: GSRTTST.WORLD

Hi,

Is this just a CI and DI system or have you apps?

Mark

Hi,

Sorry, I don't understand CI? And DI?

We need it connection for internal dev. report and programs... Our developers select data from remote oracle db... Into sap tables...

Hi,

when the server was built did you build it in a DOMAIN or local ?

Mark

Our sap server in the domain, the remote oracle db is not in our domain and operating system linux

Hi,

See if this helps you..

Regards

Mark

http://exploreoracle.blogspot.com/2009/01/ora-28547-connection-to-server-failed_19.html

Hi,

I Tried two ways but same problem occured !.

C:\Users\hvdadm>tnsping gsrttst

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 25-JAN-2012 18:50:26

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

D:\usr\sap\HVD\SYS\profile\oracle\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.2.7.4) (PORT = 1521))) (CONNECT_DATA = (SID = GSRTTST)))

OK (170 msec)

C:\Users\hvdadm>sqlplus sap/hl_5kld@GSRTTST

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 25 18:51:13 2012

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

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL>

When test from trans. DB02 DBConnections output;;

-- ERROR Database connection RIGA: SQL error 3135 con=1(GSRTTST.WORLD): ORA-03135: connection lost contact

-- ERROR Database connection RIGA: SQL error 28547 con=1(GSRTTST.WORLD): ORA-28547: connection to server failed, probable Oracle Net admin error

FROM REMOTE DB (ORACLE - LINUX) LISTENER LOG;

25-JAN-2012 14:50:01 * (CONNECT_DATA=(SID=GSRTTST)(CID=(PROGRAM=D:\oracle\HVD\11202\bin\sqlplus.exe)(HOST=TRGMDHVD)(USER=hvdadm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.58.224.145)(PORT=50170)) * establish * GSRTTST * 0 ...

25-JAN-2012 14:51:32 * (CONNECT_DATA=(SID=GSRTTST)(CID=(PROGRAM=D:\usr\sap\HVD\DVEBMGS00\exe\disp+work.EXE)(HOST=TRGMDHVD)(USER=SAPServiceHVD))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.58.224.145)(PORT=50241)) * establish * GSRTTST * 0

Hi,

have you tried to use a database link ?

Mark

hi,

i investigating about the SQLNET.AUTHENTICATION_SERVICES = (NTS) to (NONE)

i tried you published links;

When changed NTS to NONE, sqlplus / as sysdba is not running, i have connected sqlplus sys/<password> as sysdba and started db...

when start the sap instance SAPSR3 locked... (unlock account, tried again and account lock, can't started SAP)

by the way; we have a old sap server (hp-ux sap+oracle(10.2.0.4)) i tried from platform sap connected to remote db success...

I think, Authentication Type : NTS, but how to force this settings when connect remote oracle-db (linux).

We installed all sap servers on Windows Active Directory platform and integrated Active Directory Authentication... our prod. servers use Oracle Fail Safe, if change this parameter (NTS to NONE) clustered platform not running safely...

how to force NTS only for remote-db(Linux) i don't know help me please!

Hi,

Is your Oracle service user a member of the DOMAIN ?

Mark

Yes I tried, domain member user , doman admin user, local user and local system account with nts to NONE options

Sapsr3 locked all options...

Ask a Question

Top Q&A Solution Author

User

Count

84

10

10

9

7

6

6

5

4

4