Analysis and Solution of ORA-12516 errors

Analysis and Solution of ORA-12516 errors

A ORA-12516 error occurs when you use swingbench to perform a stress test on the Oracle database. This error is generally because the number of oracle sessions exceeds the limit, generally because multiple connect established multiple connection sessions caused, and finally caused oracle unable to respond to new requests, resulting in a ora-12516 error.

Solution:

First, check whether the number of existing processes in the database has reached the processes parameter size.

Select count (*) from v $ process;

Obtain the current number of processes in the database.

Select value from v $ parameter where name = 'processs ';

Obtain the maximum number of processes.

Check the current sessions value and find that the number of sessions is very close to the value of the processes parameter.

SQL> conn/as sysdba
Connected.

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

SQL> show parameter processes
NAME TYPE VALUE
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 150

SQL> show parameter sessions
NAME TYPE VALUE
Java_soft_sessionspace_limit integer 0l
Icense_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 175
Shared_server_sessions integer

Then modify the processes and sessions values.

SQL> alter system set processes = 500 scope = spfile;
The system has been changed.
SQL> alter system set sessions = 555 scope = spfile;
The system has been changed.

Note: As long as the number of Session connections exceeds the number of processes above 150 or the number of sessions is 170, A 12516 error will occur for the next session process. You can modify the value as follows:

Sessions = 1.1 * processes + 5; // This is the relationship between the sessions value and the processes value. It is best to follow this process,

Therefore, to set the number of processes to 500, the number of sessions must be 1.1*500 + 5 = 555

View the processes and sessions parameters, but the changes do not take effect.

SQL> show parameter processes
NAME TYPE VALUE
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 150
SQL> show parameter sessions
NAME TYPE VALUE
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 170
Shared_server_sessions integer

Restart the database for the change to take effect

SQL> shutdown immediate

SQL> startup
SQL> show parameter processes
NAME TYPE VALUE
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 500
SQL> show parameter sessions
NAME TYPE VALUE
Java_soft_sessionspace_limit integer 0
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 555
Shared_server_sessions integer

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian