SAP NWSR1 connect to external database fails - tnsping error

[Former Member]

Hi,

I am attempting to connect to an external DB from XI. I have attempted to setup jdbc connection. I attempted a tsnping to the external oracle database and it failed

with the following information:

Why is the tns ping failing when the listener if up and running on donner and comet host...I can do a simple host ping fine but tnsping fails....and there is not apparent reason based on my configuration. Any ideas why it would attempt to connect on 1521 when tnsnames has 1527???

Doesn't make sense and I can't find where 1521 comes from....

Mikie B.

tnsnames on the SAP oracle 10.2:

"tnsnames.ora" [Read only] 40 lines, 821 characters

################

1. Filename......: tnsnames.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........:

5. @(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/TNSNAMES.ORA#4 $

################

XII.WORLD=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = comet)

(PORT = 1527)

)

)

(CONNECT_DATA =

(SID = XII)

(GLOBAL_NAME = XII.WORLD)

)

)

HOMER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = donner)

(PORT = 1527)

)

)

(CONNECT_DATA =

(SID = HOMER)

"tnsnames.ora" 37 lines, 781 characters

Now after setting up the tnsnames to external oracle database called Donner:

26> tnsping donner

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production on 17-NOV-2006 18:44:07

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

Used parameter files:

/oracle/XII/102_64/network/admin/sqlnet.ora

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=donner))(ADDRESS=(PROTOCOL=TCP)(HOST=donner)(PORT=1521)))

TNS-12541: TNS:no listener

oraXII 27> ls

libnk510.imp listener.ora sqlnet.log

libnrad10.exp samples sqlnet.ora

libnrad10.imp shrept.lst tnsnames.ora

comet:oraXII 28> view sqlnet.log

"sqlnet.log" [Read only] 58 lines, 2461 characters

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

Fatal NI connect error 12541, connecting to:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=donner)(CID=(PROGRAM=sqlplus)(HOST=

comet)(USER=oraXII)))(ADDRESS=(PROTOCOL=TCP)(HOST=donner)(PORT=1521))

)

VERSION INFORMATION:

TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production

TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.

2.0 - Production

Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Versio

n 10.2.0.2.0 - Production

Time: 16-NOV-2006 11:51:18

Tracing not turned on.

Tns error struct:

ns main err code: 12541

TNS-12541: TNS:no listener

ns secondary err code: 12560

nt main err code: 511

TNS-00511: No listener

nt secondary err code: 79

[former_member453631 · Accepted solution]

Hi,

This is probebly because of youre settings in the sqlnet.ora file. The default zone in this file is probebly not set to world. Ypu can try

(GLOBAL_NAME = HOMER.WORLD)

In youre tnsfile like the XII entry or try to tnsping HOMER.WORLD. I gues this will help. If not please show the listener.ora config file and the sqlnet.ora file ?!

Lando

P.S don't forget the points when this answer is helpfull

[Former Member]

Lando,

The tnsping XXXX.WORLD is working. I got a an OK code.

Thanks and points awarded......

Mikie

[former_member453631]

Hi mikie,

Good to here the problem is solved. I ues remove of the # before

#NAME.DEFAULT_ZONE = WORLD would have worked as well the defualt zone will the be world and tnisping will automaticly ad world when pinging

Lando

[Former Member]

here is my sqlnet:

################

1. Filename......: sqlnet.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........:

5. @(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/SQLNET.ORA#4 $

################

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

1. 05.01.06 unsorported parameter now

#NAME.DEFAULT_ZONE = WORLD

1. 05.01.06 set the default to 10

SQLNET.EXPIRE_TIME = 10

1. 05.01.06 set to default

#TCP.NODELAY=YES

1. 05.01.06 set to 32768

DEFAULT_SDU_SIZE=32768

and listener:

################

1. Filename......: listener.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........:

5. @(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#4 $

################

ADMIN_RESTRICTIONS_LISTENER = on

LISTENER =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = IPC)

(KEY = XII.WORLD)

)

(ADDRESS=

(PROTOCOL = IPC)

(KEY = XII)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = comet)

(PORT = 1527)

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = XII)

(ORACLE_HOME = /oracle/XII/102_64)

)

)

Thanks Mikie