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>
