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

2008-06-23 08:57

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.

First try connect/as sysdba, see if it works, if it does, then try connect/@as sysdba again to see if there is a problem with the listening configuration.

Then try connect/ @as sysdba to see if there is a problem with the listening configuration.

If there is a problem with this step, it means that there is a problem with the listening piece.

1.cmd

2.sqlpls /nolog

3.connect sys/test as sysdba

no problem

1.cmd

2.sqlplus /nolog

3.connectsys/test@testas sysdba

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

Explain that there is a problem with listening

The relevant information was found as follows.

http://www.dba-oracle.com/sf_ora_12518_tns_listener_could_not_hand_off_client_connection.htm

The reason ORA-12518 is being throw may be because of DEDICATED connections because Oracle 10g has a value of PROCESSES is defaulted at 150 which can be lower than necessary in a production system. Also, in pre-9i MTS, ORA-12518 may be thrown in SHARED SERVER because the dispatcher may have reached the maximum connection value, so it denies all other.

There are two solutions for ORA-12518 depending on which symptom you may be experiencing.

a.For the DEDICATED occurrence of ORA-12518, you would need to try increasing the PROCESSES parameter so that it can handle the needed number of processes. You can ensure that you have the needed value by monitoring the listener log for ORA-12518. Also, note that because the PROCESSES parameter is static, the database will need to be bounced.

b.If you are experiencing ORA-12518 because of a shared server issue, you first would need to use the command below to shutdown the dispatcher:

SQL> alter system shutdown immediate ‘D001’;

Then, add on new dispatchers:

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

The analysis of the above information shows that there are two possible reasons for the problem: one is that the value of processes is set too small, and the other is that the parameter DISPATCHERS needs to be increased.

Considering that the problem of ora-00020 exceeding the maximum number of processes was often encountered before when the database was installed, the processes were set to 800, so for the sake of insurance, show parameter processes again.

SQL> show parameter process;

NAMETYPEVALUE

———————————— ———– —–

aq_tm_processesinteger0

db_writer_processesinteger1

gcs_server_processesinteger0

job_queue_processesinteger10

log_archive_max_processesinteger2

processesinteger1000

Re-check the number of processes

SQL> select count(*) from v$session;

COUNT(*)

———-

224

So the final conclusion should not be the processes problem, should use the b method to solve

1.connect sys/test as sysdba

2.show parameters dispatchers;

NAME TYPEVALUE

dispatchersstring(protocol=tcp)(service=oracle10xdb)

max_dispatchersinteger

NAME TYPE VALUE

———————————— ———– ——————————

dispatchers string (PROTOCOL=TCP) (SERVICE=smsvrXDB)

max_dispatchers integer

5.SQL>alter system set dispatchers = ‘(protocol=tcp)(dispatchers=3)(service=oracle10xdb)’;

system altered

The problem is solved.

Of course, depending on the situation, you can also use the a method to solve the problem.

You can use show parameters processes;

command to check the value of processes, the database is 150 by default when installed, for large applications, 32-bit oracle must pay attention to this problem