Ora 12518 ошибка - Решение и исправление самых разных ошибок на TopOshibok.ru

I am using ORACLE database in a windows environment and running a JSP/servlet web application in tomcat. After I do some operations with the application it gives me the following error.

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

can any one help me to identify the reason for this problem and propose me a solution?

asked Nov 29, 2012 at 11:11

Dinidu HewageDinidu Hewage

2,1697 gold badges40 silver badges51 bronze badges

The solution to this question is to increase the number of processes :

1. Open command prompt 
2. sqlplus / as sysdba; //login  sysdba user
3. startup force;
4. show parameter processes;  // This shows 150(some default) processes allocated, then increase the count to 800
5. alter system set processes=800 scope=spfile;

As Tried and tested.

answered Jan 7, 2015 at 12:56

coretechiecoretechie

1,06016 silver badges38 bronze badges

In my case I found that it is because I haven’t closed the database connections properly in my application. Too many connections are open and Oracle can not make more connections. This is a resource limitation. Later when I check with oracle forum I could see some reasons that have mentioned there about this problem. Some of them are.

In most cases this happens due to a network problem.
Your server is probably running out of memory and need to swap memory to disk.One cause can be an Oracle process consuming too much memory.

if it is the second one, please verify large_pool_size or check dispatcher were enough for all connection.

You can refer bellow link for further details.
https://community.oracle.com/message/1874842#1874842

answered Jul 8, 2015 at 1:38

Dinidu HewageDinidu Hewage

2,1697 gold badges40 silver badges51 bronze badges

I ran across the same problem, in my case it was a new install of the Oracle client on a new desktop that was giving the error, other clients were working so I knew it wouldn’t be a fix to the database configuration. tnsping worked properly but sqlplus failed with the ora-12518 listener error.

I had the tnsnames.ora entry with a SID instead of a service_name, then once I fixed that, still the same error and found I had the wrong service_name as well. Once I fixed that, the error went away.

answered May 15, 2015 at 15:53

If from one day to another the issue shows for no apparent reasons, add these following lines at the bottom of the listner.ora file. If your oracle_home environment variable is set like this:

(ORACLE_HOME = C:\oracle11\app\oracle\product\11.2.0\server)

ADR_BASE_LISTENER = C:\oracle11\app\oracle\

DIRECT_HANDOFF_TTC_LISTENER=OFF

answered Feb 27, 2014 at 10:32

FrancescoFrancesco

5228 silver badges16 bronze badges

I had the same problem when executing queries in my application. I’m using Oracle client with Ruby on Rails.

The problem started when I accidentally started several connections with the DB and didn’t close them.

When I fixed this, everything started to work fine again.

Hope this helps another one with the same problem.

answered Jul 7, 2015 at 19:12

LaerteLaerte

7,0233 gold badges32 silver badges50 bronze badges

I experienced the same error after upgrading to Windows 10. I solved it by starting services for Oracle which are stopped.

Start all the services as shown in the following image:

answered Oct 26, 2016 at 6:58

I had the same issue. After restarting all Oracle services it worked again.

answered May 2, 2018 at 7:37

same problem encountered for me.
And from oracle server listener log, can see more information.
and I found that the SERVICE_NAME is not match the tnsnames.ora configured Service name. so I changed the application’s data source configuration from SID value to Service_NAME value and it fixed.

23-MAY-2019 02:44:21 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=XXXXXX$))(SERVICE_NAME=orclaic)) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=50818)) * establish * orclaic * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   64-bit Windows Error: 203: Unknown error

answered May 23, 2019 at 4:53

I had the same issue in real time application and the issue gone by itself next day. upon checking, it was found that server ran out of memory due to additional processes running.

So in my case, the reason was server run out of memory

Zoe♦

27.1k21 gold badges119 silver badges148 bronze badges

answered May 24, 2019 at 19:55

first of all
check the listener log
check the show parameter processes vs select count(*) from v$processes;
increase the process, it may require SGA increase
;

answered Dec 17, 2022 at 17:46

I faced this issue in newly created oracle DB hosted on windows server. TNSNAMES, LISTENER were fine, TNSping was working. still TNS-12518: TNS:listener could not hand off client connection error used to reflect while making connection through service name.

Along with all the above suggestions do follow the changes I made.
hostname was 16 characters, i changed hostname to less than 16 characters. It worked perfect.

Thanks.

answered Mar 29 at 9:59

Источник

Соединения не могут быть установлены из-за ошибки:

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

Часто сопровождается ошибками:

ORA-12547 — TNS-12547 = lost contact
ORA-12537 — TNS-12537 = connection closed
ORA-03135 — TNS-03135 = connection lost contact
ORA-03113 — TNS-03113 = end-of-file on communication channel
ORA-03106 — TNS-03106 = fatal two-task communication protocol error
ORA-03136 — TNS-03136 = WARNING inbound connection timed out
ORA-12535 — TNS-12535 = TNS:operation timed out
ORA-12170 — TNS-12170 = Connect timeout occurred
ORA-12637 — TNS-12637 = Text: Packet receive failed

Вот цитата из документации (никакой полезной информации тут нет 🙂 ):

ORA-12518: TNS:listener could not hand off client connection Cause: The process of handing off a client connection to another process failed. Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support.

Причина:

Основная причина — это нехватка ресурсов на сервере где работает Listener. Ошибка обычно возникает при высокой загруженности сервера или большом количестве сессий.

Решения:

1. Проверить с помощью запроса параметры processes и sessions. Если нужно увеличить их (Как узнать значения и изменить параметры processes и sessions в Oracle). В примере видно что максимальное количество prosesses достигло лимита — т.е. нужно увеличивать processes и соответственно sessions.

select * from v$resource_limit;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE processes 137 150 150 150 sessions 141 159 170 170

2. Увеличить количество оперативной памяти и файла подкачки ОС (swap). Если возможно — сделать это физически, т.е. добавить на сервер модули RAM (если ОС 32-битная и памяти уже > 3Gb то это не поможет — нужно переходить на 64-битную ОС и Oracle). Можно попытаться остановить все ненужные службы (демоны) и приложения.
©Bobrovsky Dmitry
3. На 32-битных ОС Windows — установить параметр /3GB (Как установить ключ /3GB /PAE в Windows Vista 7 2008).
©Bobrovsky Dmitry
4. Уменьшить потребление памяти Oracle,т.е. уменьшить SGA, т.е. уменьшить один из параметров PGA_AGGREGATE_TARGET или SGA_MAX_SIZE или оба вместе.
Dmitry Bobrovsky
5. Перейти на Shared Server (Managing Processes in Oracle).
Dmitry Bobrovsky
6. Если у вас 32-битная система — перейти на 64-битную систему!

Остальные решения не рассматриваю, т.к. если пункты 1-5 не помогли, то это экзотический случай и лучше обратиться в тех.поддержку или начать поиск в MOS. Начать можно отсюда — ORA-12518 / TNS-12518 Troubleshooting

Запись ORA-12518 TNS-12518 впервые появилась Dmitry Bobrovsky Blog

Источник

You try to connect with Oracle database and get Ora-12518 listener could not hand off client connection error.

Now what action is to be taken to get rid of above error? Follow below steps to resolve the issue.

Connect with oracle database:

C:\>sqlplus / as sysdba

SQL>conn testuser/test@mydb

And you got the ERROR: ORA-12518: TNS: listener could not hand off client connection

Solution-1:

First check your database whether it is in start mode or not.

SQL> shutdown immediate;

SQL> Startup;

Solution-2: If first is not working

You have to increase PROCESSES initialization parameter in Oracle database.

First use the following command to check the existing value of the PROCESSES.

show parameter processes

As we have already started our database normally or using spfile, then run following command to alter system processes to 450.

alter system set processes=450 scope=spfile;

If you have started your database using pfile, process parameter must be set in pfile.

Solution-3: If both the above solutions are not working.

If you are getting ORA-12518 because of a shared server issue then you first need to use the below command to shutdown the dispatcher.

SQL> alter system shutdown immediate 'D001';

Then add on new dispatchers.

SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';

Resolving ORA-12518 requires you to evaluate the syntax depending on your dispatcher value in the Spfile.ora or init.ora files. When you increase DISPATCERS to resolve ORA-12518 you should also keep an eye on the shared server ratio.

Resolved ORA-12541: TNS no listener error

ORA-28001: The Password Has Expired

I hope you have successfully resolved your Ora-12518 issue.

Источник

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!

Источник

May 5, 2020

Hi,

Sometimes You can get “ORA-12518: TNS:listener could not hand off client connection ” error.

ORA-12518 TNS listener could not hand off client connection

Details of error are as follows.

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

Cause: The process of handing off a client connection to another process failed.

Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database

instance are properly configured for direct handoff. If problem persists, call Oracle Support.

Edit /etc/systemd/system.conf file and Set DefaultTasksMax to ‘infinity’.

Or This error may be related with out of available Oracle processes parameter.

If processes parameters are insufficient, then you should increase the PROCESSES parameter as follows.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1500
SQL>
SQL>
SQL> alter system set processes=2000 scope=spfile;

System altered.

SQL>

Restart database after this operation.

If it is not related with processes parameter, then it may be related with dispatcher.

Firstly Shutdown the dispatcher and add new dispatchers as follows.

SQL> show parameter dispatchers
SQL> select name from v$dispatcher;
SQL> alter system shutdown immediate 'D001';
Add new dispatcher
SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=4)';

Or you should check ‘max user processes’ etc. with ‘ulimit -a‘ command, and if it is insufficient, then increase it.

For windows environment.

set logging_listener_name=on in the listener.ora and reload the listener.

Or restart all Oracle services, it will be fine.

Do you want to learn more details about RMAN, then read the following articles.

RMAN Tutorial | Backup, Restore and Recovery Tutorials For Beginner Oracle DBA

Источник