Wednesday, June 11, 2014

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



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>

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>