1

I'm starting to become very very frustrated by Oracle. So, I have Oracle XE 11.2 64 bit and ODAC121012_x64 installed.

I can start listener; I can do tnsping xe; but when I try to connect as via sqlplus as system I'm getting:

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

Here are my config files:

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
                           (SID_NAME = XE)
       (ORACLE_HOME =  C:\oraclexe\app\oracle\product\11.2.0\server)
     )

    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

sqlnet.ora:

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Help is very much needed and will be even more appreciated!

9
  • Can you connect as sysdba? > connect / as sysdba Jun 27, 2014 at 10:01
  • Can you include the output of "lsnrctl status"? Jun 27, 2014 at 10:15
  • is your database open? what is the value of the processes parameter?
    – ik_zelf
    Jun 27, 2014 at 10:43
  • >sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 27 15:21:58 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12560: TNS:protocol adapter error
    – greengold
    Jun 27, 2014 at 13:23
  • this is to lsnctl status: pastebin.com/QDe4m33V
    – greengold
    Jun 27, 2014 at 13:26

3 Answers 3

2

I has this problem in Windows (with oracle 11g) because the user under which the listener service was running was not part of the administrators group... once added to the administrator group all was fine.

4
  • This is legit one of the answers. if the database and listener service are running as a user which is not part of the administrators group, setting up a static connection doesn't work properly
    – Worthy7
    Jun 6, 2017 at 1:53
  • +1 this solved an additional problem with Oracle 18c in Windows for me: Oracle services had to be started manually, they didn't start automatically. I added all NT_SERVICE/Oracle* service users to Administrators group. Jan 11, 2020 at 9:38
  • I'm facing the same issue. I believe this could be the cause of my 12518 after trying everything. However, when the users were created, I can see that they appear correctly in the task administrator as the owner of the Oracle process, but they don't appear in the account management menu in the control panel. Is there something I should worry? Their folders are created correctly under the User folder
    – Alex
    Dec 29, 2021 at 17:32
  • This is hell of an answer. Needs more recognition. For this change the user of listener service from properties. Sep 30 at 9:32
2

To understand the problem and try to fix it you need to check related logs: listener.log, alert log, trace file, etc

possible causes and solutions:

  • listener problem:

Make sure there is no error message and listener is up and running by execute the following command:

lsnrclt status

if listener is down check the following it may help you troubleshoot this issue:

  1. could be network error: check the even log for any network error and fix them.

  2. service stopped or it has failure when trying to start: start--->run--->services.msc the search for oracle listener service should be like OracleInstancename try to start it, if it failed to start it will throw an exception inside listener error and windows error number. please provide that error

listener workaround:

add the following parameter to your listener:

DIRECT_HANDOFF_TTC_LISTENER=OFF

  • database problem:

1.database server memory were enough for new connection

  1. database connection parameters such as: dedicated server, please checked processes parameter or query from v$resource_limit where resource_name=’processes’; checked max_utilization must less than Limit_value or shared server, verify large_pool_size or check dispatcher were enough for all connection.
1
  • +1 for new connection memory limit. In my case a leaking app was making my oracle to reject any new connection attempt, with this message.
    – vassilag
    Nov 28, 2017 at 9:00
0

ORA-12518 or TNS-12518 can occur, if you have a Linux system with systemd 228 or newer. Systemd by default limits the number of threads to 512 per cgroup. This limit can easily been hit, if you have many connections or more than one instance or RAC or ....

Easiest fix: Edit /etc/systemd/system.conf and set

DefaultTasksMax=infinity

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.