0

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!

  • Can you connect as sysdba? > connect / as sysdba – Andrew Brennan Jun 27 '14 at 10:01
  • Can you include the output of "lsnrctl status"? – Phil Sumner Jun 27 '14 at 10:15
  • is your database open? what is the value of the processes parameter? – ik_zelf Jun 27 '14 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 '14 at 13:23
  • this is to lsnctl status: pastebin.com/QDe4m33V – greengold Jun 27 '14 at 13:26
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 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 '17 at 9:00
0

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.

  • 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 '17 at 1:53
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.