Ora-12518:tns:listener could not hand off client connection

One,Ora-12518:tns:listener could not hand off client connection

When there are more team members, there are often issues such as "Unable to distribute client connections". After searching on the internet, finally conquered the problem, and now the solution is summarized such as the following, for reference and later backup.

Change the SPFILEORCL. Process in the Ora file

F:\oracle\product\10.2.0\db_1\database\SPFILEORCL. ORA

Cause: There are more team members, and the old database settings are not enough, which often throws the "unable to distribute client connection" exception when connecting Plsql and starting Tomcat.

How to resolve:

First step: Adjust the process and session values

1. Check that the process and session are adequate.

A) Use Plsql to connect to Oracle to see the number of process processes:

Select count (*) from V $process; -- gets the current number of processes in the database.
Select value from V $parameter where name = 'processes'; -- gets the maximum number of processes.

b) View Session sessions:

Select count (*) from V $session; -- gets the current number of sessions in the database.
Select value from V $parameter where name = 'sessions'; -- gets the maximum number of sessions.

Check to see if the current process and sessions are close to the upper value. If it is close, it can be enlarged.

2. Adjust the size of these two parameters.

Number of system processes process and system sessions there is a relationship between the session:

Process number =session number *1.1+5

We set this rule in the configuration.

A) change the process value

Alter system set processes=1000 Scope=spfile; --Change the process value to 1000

b) Change session value

Alter system set SESSIONS=1105 Scope=spfile; --Change the sessions value to 1105

3. Back up Pfile, restart Oracle

A) After the change is complete, backup pfile

Create Pfile from SPFile; --Create Pfile (System configuration) from SPFile (configuration at execution time)

c) Restart Oracle

There are many ways to restart the Oracle service, or to restart the database. You can restart the service directly under Windows.

Step Two: Change the number of dispatchers

Let's say the first step doesn't solve the problem. Be able to do the second step.

1. View the current number of dispatchers, and dispatchers usage

Select Name,busy,status,accept,idle from V$dispatcher; --View the current number of dispatchers and some information. There is only one library that is typically installed by default.

Select Name, (busy/(busy+idle)) *100 "Busy rate%" from V$dispatcher; --View dispatchers usage rate

Assuming that the usage rate is greater than 50%, consider adding the number of dispatchers.

2. Adjust the number of dispatchers

Alter system set dispatchers= ' (PROTOCOL=TCP) (dispatchers=3) (service=youroracleservicenameXDB) ';

--Change the number of dispatchers to 3.

3. Restart Oracle.

There is no listener error when restarting the service

Two.ora-12541:tns: No listening program

1: Add F:\oracle\product\10.2.0\db_1\network\admin\listener.ora such as the following code:

 (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )    

# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
	 (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
    (SID_NAME = orcl)
    ) 	
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )    
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = wl-fe65b9e18e24)(PORT = 1521))
    )
  )

Note: restartListener and service

This file may be lost due to the change of Spfileorcl.ora, which makes Oracle unusable

Third, Oralce not available

Create a Spfileorcl.ora file, note: SPFILEORCL,ORCL is the name of the instance

F:\oracle\product\10.2.0\db_1\database\SPFILEORCL. ORA

CMD in execution:

sqlplus /nolog
conn / as sysdba;
create spfile='F:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA' from pfile='F:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.7292014151052';

Therefore, when this error occurs, create the SPFILEORCL first. Ora file, and then change the listening error, restart monitoring and service to

Ora-12518:tns:listener could not hand off client connection