Failure to Start Oracle Listener after applying Oracle Patch

[Former Member]

Hi all ,

I'm having problem after upgrading to 10.2, patch level 15 .

Before applying the patch everything went well. The server is hosting 2 instance D02-ECC6 and BID- BI .

I'm a newbie in oracle , hope someone that faced this issue before and managed to solve this issue before can shed some light how to solve it .

TNS-12518: TNS:listener could not hand off client connection

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

64-bit Windows Error: 2: No such file or directory

07-SEP-2007 20:37:19 * (CONNECT_DATA=(SID=D02)(GLOBAL_NAME=D02.WORLD)(CID=(PROGRAM=D:\usr\sap\D02\DVEBMGS00\exe\disp+work.EXE)(HOST=ACMSAPD02)(USER=OraUser))

For BID - BI Instance

tarted with pid=3596

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=
.\pipe\BID.WORLDipc)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=
.\pipe\BIDipc)))

Error listening on: (ADDRESS=(COMMUNITY=SAP.WORLD)(PROTOCOL=TCP)(HOST=ACMSAPD02)(PORT=1528))

TNS-12542: TNS:address already in use

TNS-12560: TNS:protocol adapter error

TNS-00512: Address already in use

64-bit Windows Error: 48: Unknown error

No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=
.\pipe\BID.WORLDipc)))

No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=
.\pipe\BIDipc)))

TNSLSNR for 64-bit Windows: Version 10.2.0.2.0 - Production on 26-SEP-2007 02:04:37

Thank You

[Former Member · Accepted solution]

Hi Sallehudin,

This is clearly a problem of Port Conflict for listner service.

while checking output of tnsping command port shown is 1521

and in your error log shows it is attempting to start listner on port 1528.

Change the port for your listner service.

Hope this is useful

Regards

Umesh

[Former Member · Accepted solution]

Umesh is right. The port number in the tnsnames.ora and listener.ora for a database should match with each other.

[Former Member · Accepted solution]

Hi Guys ,

The port number for listener and tnsname is the same and I still cannot start the listener...hmmmm....

Weird ..weird ..

Below is the listener and tnsnames file

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

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 $

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

LISTENER =

(ADDRESS_LIST =

(ADDRESS=

(PROTOCOL=IPC)

(KEY= D02.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= D02)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = ACMSAPD02)

<b>(PORT = 1528)</b>

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = D02)

(ORACLE_HOME = D:\oracle\D02\102)

(PRESPAWN_MAX = 10)

)

(SID_DESC =

(SDU = 32768)

(SID_NAME = BID)

(ORACLE_HOME = D:\oracle\BID\102)

(PRESPAWN_MAX = 10)

)

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

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= D02.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= D02)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = ACMSAPD02)

<b>(PORT = 1528)</b>

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = D02)

(ORACLE_HOME = D:\oracle\D02\102)

(PRESPAWN_MAX = 10)

)

(SID_DESC =

(SDU = 32768)

(SID_NAME = BID)

(ORACLE_HOME = D:\oracle\BID\102)

(PRESPAWN_MAX = 10)

)

)

[Former Member · Accepted solution]

Did you restart the listener?

Could you log on to the database as sysdba like that?

sqlplus /nolog

connect / as sysdba

How ist the PATH Variable set?

Do you have any Oracle clients on this OS?

Best regards

[Former Member]

The above output refer to BID

[Former Member]

I am working with sallehuddin on this

After change tnsname.ora listener.ora and sqlnet.ora it still pointing to host 202.75.47.122 port 1521 instead of 192.168.4.7 port 1528

BELOW WAS THE OUTPUT

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 679477248 bytes

Fixed Size 2056000 bytes

Variable Size 348127424 bytes

Database Buffers 322961408 bytes

Redo Buffers 6332416 bytes

Database mounted.

Database opened.

SQL> select status from v$instance;

STATUS

-

---

OPEN

SQL>

<b>LISTENER OUTPUT</b>

LSNRCTL> STATUS BID

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=BID.com.my))(ADDRESS=(PRO

TOCOL=TCP)(HOST=202.75.47.122)(PORT=1521)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

64-bit Windows Error: 61: Unknown error

<b>TNSPING OUTPUT</b>

D:\oracle\BID\102\BIN>tnsping BID

TNS Ping Utility for 64-bit Windows: Version 10.2.0.2.0 - Production on 28-SEP-2

007 09:24:29

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

Used parameter files:

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

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP

.WORLD) (PROTOCOL = TCP) (HOST = ACMSAPD02) (PORT = 1528))) (CONNECT_DATA = (CON

NECT_DATA = (SERVICE_NAME=BID) (ADDRESS = (PROTOCOL = TCP) (HOST = ACMSAPD02) (P

ORT = 1528)))

TNS-12533: TNS:illegal ADDRESS parameters

D:\oracle\BID\102\BIN>

<b>Profile parameter setting</b>

TNSNAMES.ORA

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

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 $

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

BID.WORLD=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = ACMSAPD02)

(PORT = 1528)

)

)

(CONNECT_DATA =

(CONNECT_DATA =

(SERVICE_NAME=BID)

(ADDRESS =

(PROTOCOL = TCP)

(HOST = ACMSAPD02)

(PORT = 1528)

)

)

LISTENER.ORA

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=ACMSAPDEV02)(PORT=1528))

(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

1. SID_LIST_<lsnr>

List of services the listener knows about and can connect

clients to. There is no default. See the Net8 Administrator's

Guide for more information.

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

#BEQUEATH CONFIG

(GLOBAL_DBNAME=BID.SAP)

(SID_NAME=BID)

(ORACLE_HOME=D:\oracle\BID\102)

#PRESPAWN CONFIG

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

)

)

SQLNET.ORA

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

SQLNET.AUTHENTICATION_SERVICES = (NTS)

1. 05.01.06 set to default

#TCP.NODELAY=YES

1. 05.01.06 set to 32768

DEFAULT_SDU_SIZE=32768

Remarks: TNS_ADMIN point to D:\usr\sap\BID\SYS\profile\oracle<u></u><u></u><u></u>

[Former Member]

your listener.ora and tnsnames.ora don't match.

see please example tnsnames.ora and listener.ora in the SAP Note 562403 - FAQ: Oracle Net

You have two times connect_data and two times TCP Protocol in the same tnsnames.ora for the same database.

On which host is database BID <b>ACMSAPD02</b> or <b>ACMSAPDEV02</b>? And which port?

Here you have to give the name of listener, not the name of database.

<b><i>LSNRCTL> STATUS <listener_name></i></b>

Something is wrong in tnsnames.ora, maybe because of two connect_data and two times TCP for the same database.

<b><i>TNS-12533: TNS:illegal ADDRESS parameters</i></b>

Check the parameters and parenthesis within the ADDRESS and CONNECT_DATA section of TNSNAMES.ORA

best regards

[Former Member]

Hi Baran ,

I'll check it out... thanks for the suggestion

[Former Member]

where are your tnsnames.ora and listener.ora? D:\usr\sap\BID\SYS\profile\oracle or D:\oracle\BID\102\network\admin. And for D02? Do you have one listener for all databases on this host? How is the PATH set?

You can fix your listener.ora and tnsnames.ora according to the SAP note 562403 - FAQ: Oracle Net

Best regards

[Former Member]

Hi Baran ,

The listener.ora and tnsnames.ora are located at D:\oracle\D02\102\network\admin and D:\oracle\BID\102\network\admin

As for the listener .. There's 2 listener on this host ... one for BID and one for D02

As for the PATH

For D02ADM

D:\usr\sap\D02\SYS\exe\uc\NTAMD64

For BIDADM

D:\usr\sap\D02\SYS\exe\uc\NTAMD64

Thanks

[Former Member]

you can unset TNS_ADMIN and try again to start the listener for BID?

Before starting the listener, rename the listener.log and create a new listener.log (D:\oracle\BID\102\network\log)

Do you have a sqlnet.ora D:\oracle\BID\102\network\admin

check please environment settings according to the SAP 556232 - Environment settings for R/3/Oracle on Windows

best regards

[Former Member]

Hi Yusuf,

Have you rebooted the OS after patch install.?

Hari.

[Former Member]

Yes .. server has been rebooted after patch

[Former Member]

Hello Sallehudin,

Could you please check if the OracleService<DBSID> is started?

Which error do you get, if you execute this command?

LSNRCTL> services <listener name>

How is the DB Parameter processes set? Can you ping the database? tnsping <DBSID>

Best regards

Baran

[Former Member]

Hi Baran,

How to check the listener name ?

Yes , the oracle services are started

Result From TNS PING

D:\oracle\BID\102\BIN>tnsping BID

TNS Ping Utility for 64-bit Windows: Version 10.2.0.2.0 - Production on 27-SEP-2

007 16:07:54

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

Used parameter files:

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

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=BID))(ADDRESS=(PR

OTOCOL=TCP)(HOST=202.75.47.122)(PORT=1521)))

TNS-12541: TNS:no listener

D:\oracle\BID\102\BIN>tnsping BID

TNS Ping Utility for 64-bit Windows: Version 10.2.0.2.0 - Production on 27-SEP-2

007 16:07:54

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

Used parameter files:

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

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=BID))(ADDRESS=(PR

OTOCOL=TCP)(HOST=202.75.47.122)(PORT=1521)))

TNS-12541: TNS:no listener

TNS Ping Utility for 64-bit Windows: Version 10.2.0.2.0 - Production on 27-SEP-2

007 16:10:50

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

Used parameter files:

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

TNS-03505: Failed to resolve name

[Former Member]

Hi Sallehudin,

you can see the listener name in the file listener.ora under

/oracle/<SID>/102/network/admin/listener.ora

How is the environment variable TNS_ADMIN set? Do you have also a sqlnet.ora under /oracle/<SID>/102/network/admin except

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

see please the SAP Note: 562403 - FAQ: Oracle Net

best regards

[Former Member]

Hi Baran,

Thanks for your prompt replies ,

The TNS_admin system variable is configured as below:

D:\usr\sap\BID\SYS\profile\oracle

Here is the content of listener.ora ..funny thing is the Listener value there is empty ..Could this caused the problem..

Many thanks for your help

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

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= D02.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= D02)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= BID)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = ACMSAPD02)

(PORT = 1528)

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = D02)

(ORACLE_HOME = D:\oracle\D02\102)

(PRESPAWN_MAX = 10)

)

(SID_DESC =

(SDU = 32768)

(SID_NAME = BID)

(ORACLE_HOME = D:\oracle\BID\102)

(PRESPAWN_MAX = 10)

)

[Former Member]

Could you please rename the old listener.log ($ORACLE_HOME/network/log) and create a new listener.log and restart the listener, if it is possible?

The Listener will write entries in the new log file. Can you post the errors in the new listener.log?

Post please also the results of the following actions:

LSNRCTL> services listener

tnsping <DBSID>

best regards

Baran

[Former Member]

please provide the followings:

LSNRCTL> services <listener name>

How is the DB Parameter processes set?

Environment settings:

Are you sure that OracleService runs? If it is possible, please restart the database and try again.

Which status does the database have?

select status from v$instance;

do you have maybe two listener.ora? One is under $ORACLE_HOME/network/admin, one is where TNS_NAMES is set? Which one is used? tnsping <DBSID>.

How is sqlnet.ora?

what do you get back if you execute R3trans -x or R3trans -d?

Best regards

[Former Member]

please provide also following results:

tnsping D02

tnsping BID

try also this and put please the result:

sqlplus sys/pw@D02 as sysdba (shouldn't run, if there is no listener: remote_login_passwordfile should be set and there must be a pw file for remote login)

sqlplus sys/pw@BID as sysdba (shouldn't work, if there is no listener)

How is SQLNET.AUTHENTICATION_SERVICES set in sqlnet.ora?

Best regards

[Former Member]

Here’s the result of r3trans –x, there’s error when running the command..

For BIDADM

C:\Documents and Settings\bidadm>r3trans -x

This is r3trans version 6.14 (release 700 - 18.09.06 - 09:35:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_o

ra_tnsname = 'BID'"

r3trans finished (0012).

C:\Documents and Settings\bidadm>

For D02ADM

C:\Documents and Settings\d02adm>r3trans -x

This is r3trans version 6.14 (release 700 - 18.09.06 - 09:35:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_o

ra_tnsname = 'D02'"

r3trans finished (0012).