Scenario:
When
trying to connection with SID/service name getting error message
Enter user-name: PS C:\Users\Administrator>
sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.3.0 - Production on
Mon Jun 9 16:37:16 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12518: TNS:listener could not hand off
client connection
But
without service name / SID can able to connect successfully.
PS C:\Users\Administrator> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 9 18:16:11 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 9 18:16:11 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
Tried with
many options editing listener.ora and tnsnames.ora file but can’t get success.
PS C:\Users\Administrator> lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.3.0
- Production on 09-JUN-2014 16:36:52
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.3.0
- Production
System parameter file is
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to
C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tstpip)(PORT=1521)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version TNSLSNR for 32-bit Windows:
Version 10.2.0.3.0 - Production
Start Date 09-JUN-2014 16:36:58
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File
C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tstpip)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this
service...
Service "PLSExtProc" has 1
instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for
this service...
Service "onlinepnet" has 1
instance(s).
Instance "onlinepnet", status UNKNOWN, has 1 handler(s) for
this service...
The command completed successfully
PS C:\Users\Administrator> tnsping orcl
TNS Ping Utility for 32-bit Windows: Version
10.2.0.3.0 - Production on 09-JUN-2014 16:37:08
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = TSTPIP)(PORT = 1521))) (CONNECT_
DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
PS C:\Users\Administrator> sqlplus
scott/tiger@ORCL
SQL*Plus: Release 10.2.0.3.0 - Production on
Mon Jun 9 16:37:11 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12518: TNS:listener could not hand off
client connection
Enter user-name: PS C:\Users\Administrator>
sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.3.0 - Production on
Mon Jun 9 16:37:16 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12518: TNS:listener could not hand off
client connection
Finally edited
listener.ora file with login mode on parameter.
logging_listener_name=on
When
checked C:\oracle\product\10.2.0\db_1\network\log\ listener.log file found below
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] *
EVENT [* SID] * RETURN CODE
09-JUN-2014 17:36:33 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870080))
* status * 0
09-JUN-2014 17:36:35 *
(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=TSTPIP)(USER=Administrator)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=10.16.2.20)(PORT=53125)) * establish * ORCL *
12518
TNS-12518:
TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00534: Failed to grant connection
ownership to child
32-bit
Windows Error: 10022: Unknown error
09-JUN-2014 17:37:24 * service_register * orcl
* 0
09-JUN-2014 17:45:19 * service_update * orcl *
0
09-JUN-2014 17:45:27 * service_died * orcl *
12537
Out of
all above three error have different reason.
Finally
TNS-00534: Failed to grant connection ownership to child error
helped me to resolve issue.
Issue is
my listener service started with local admin user while orcl service started as
local admin
Must
start both service should be started with common method.
Changed
both service started with .\Administrator resolved my issue.
Enter
user-name: PS C:\Users\Administrator> sqlplus sys as sysdba
SQL*Plus:
Release 10.2.0.3.0 - Production on Mon Jun 9 17:46:07 2014
Copyright
(c) 1982, 2006, Oracle. All Rights
Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup;
ORACLE
instance started.
Total
System Global Area 612368384 bytes
Fixed
Size 1292036 bytes
Variable
Size 335546620 bytes
Database
Buffers 268435456 bytes
Redo
Buffers 7094272 bytes
Database
mounted.
Database
opened.
SQL>
exit
Disconnected
from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the
Partitioning, OLAP and Data Mining options
PS
C:\Users\Administrator> sqlplus scott/tiger@orcl
SQL*Plus:
Release 10.2.0.3.0 - Production on Mon Jun 9 17:46:31 2014
Copyright
(c) 1982, 2006, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the
Partitioning, OLAP and Data Mining options
SQL>
exit
Disconnected
from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the
Partitioning, OLAP and Data Mining options
PS
C:\Users\Administrator>
5 comments:
Thank you!
This was helpful to me during a migration from 11gR2 to 12c on Windows. I was getting the "ORA-12518: TNS:listener could not hand off client connection" error as well on a 11gR2 database using a 12c listener. It turned out the 12c listener service was owned by the new Oracle Home User "orcsvc" but the 11gR2 database service was "Local System". Once I changed the service owner on the 12c listener to "Local Admin" everything was fine!
Keeping the 12c listener service as "Local Service" still was ok after the database was migrated to 12c and the db service was started by "orcsvc".
Chris
Thank you very much helped me a lot .
This was a big help. My problem occurred after a Window Security Update on an Oracle Enterprise Manager Cloud Control server. Changing the listener service to start with the Local User account which was the account that started the OEM database solved the problem. Thanks.
Thank you. This post from six years ago is still helpful.
My issue wasn't exactly the same, but this helped me discover my root cause. In my situation, the listener service and the database service were both running under the windows user account that the Oracle installation automatically created.
However, that account did not have enough permissions. Temporarily adding that account to the local administrators group and restarting both services allowed it to work. As an alternative, changing both services to run under the local system account and restarting them allowed it to work too. I probably could have dug deeper to determine what permissions were missing with the account would have eventually allowed it to work too.
Many many thanks - struggled for hours for this, this solved issue.
Post a Comment