on 09-27-2007 7:03 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
################
Filename......: tnsnames.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $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)
)
################
Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $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)
)
)
The above output refer to BID
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
################
Filename......: tnsnames.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $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)))
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
05.01.06 unsorported parameter now
#NAME.DEFAULT_ZONE = WORLD
05.01.06 set the default to 10
SQLNET.EXPIRE_TIME = 10
SQLNET.AUTHENTICATION_SERVICES = (NTS)
05.01.06 set to default
#TCP.NODELAY=YES
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
Hi Yusuf,
Have you rebooted the OS after patch install.?
Hari.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
################
Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $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)
)
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
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
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
Heres the result of r3trans x, theres 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).
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.