Case of ORA-00020: No more process state objects available fault

Case of ORA-00020: No more process state objects available fault

Today, a user consulted about database downtime. At that time, the database showed symptoms that users could not log on. To learn the cause, he checked the warning Log alert of the user. A large number of ORA-00020: No more process state objects available errors were found around, the specific error log is as follows:

Fatal NI connect error 12170.

Version information:
TNS for Linux: Version 11.2.0.2.0-Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0-Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0-Production
Time: 11-MAY-2015 14:28:28
Tracing not turned on.
Tns error struct:
Ns main err code: 12535

TNS-12535: TNS: operation timed out
Ns secondary err code: 12560
Nt main err code: 505

TNS-00505: Operation timed out.
Nt secondary err code: 110
Nt OS err code: 0
Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.102.150) (PORT = 50069 ))
Mon May 11 16:21:14 2015
Thread 1 cannot allocate new log, sequence 1728
Private strand flush not complete
Current log #4 seq #1727 mem #0:/oradata/orcl/redo4.log
Thread 1 advanced to log sequence 1728 (LGWR switch)
Current log #3 seq #1728 mem #0:/oradata/orcl/redo3.log
Mon May 11 22:00:00 2015
Setting Resource Manager plan SCHEDULER [0x3107]: DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon May 11 22:00:00 2015
Starting background process VKRM
Mon May 11 22:00:00 2015
VKRM started with pid = 83, OS id = 27195
Tue May 12 00:56:23 2015
Thread 1 cannot allocate new log, sequence 1729
Private strand flush not complete
Current log #3 seq #1728 mem #0:/oradata/orcl/redo3.log
Thread 1 advanced to log sequence 1729 (LGWR switch)
Current log #1 seq #1729 mem #0:/oradata/orcl/redo01.log
Tue May 12 02:00:00 2015
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Tue May 12 10:46:48 2015
Thread 1 cannot allocate new log, sequence 1730
Private strand flush not complete
Current log #1 seq #1729 mem #0:/oradata/orcl/redo01.log
Thread 1 advanced to log sequence 1730 (LGWR switch)
Current log #2 seq #1730 mem #0:/oradata/orcl/redo2.log
Tue May 12 14:36:13 2015
Thread 1 cannot allocate new log, sequence 1731
Private strand flush not complete
Current log #2 seq #1730 mem #0:/oradata/orcl/redo2.log
Thread 1 advanced to log sequence 1731 (LGWR switch)
Current log #4 seq #1731 mem #0:/oradata/orcl/redo4.log
Tue May 12 16:27:07 2015
Thread 1 cannot allocate new log, sequence 1732
Private strand flush not complete
Current log #4 seq #1731 mem #0:/oradata/orcl/redo4.log
Thread 1 advanced to log sequence 1732 (LGWR switch)
Current log #3 seq #1732 mem #0:/oradata/orcl/redo3.log
Tue May 12 22:00:00 2015
Setting Resource Manager plan SCHEDULER [0x3108]: DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Tue May 12 22:00:00 2015
Starting background process VKRM
Tue May 12 22:00:00 2015
VKRM started with pid = 174, OS id = 25804
Wed May 13 02:00:00 2015
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Wed May 13 02:45:10 2015
Thread 1 cannot allocate new log, sequence 1733
Private strand flush not complete
Current log #3 seq #1732 mem #0:/oradata/orcl/redo3.log
Thread 1 advanced to log sequence 1733 (LGWR switch)
Current log #1 seq #1733 mem #0:/oradata/orcl/redo01.log
Wed May 13 09:05:36 2015
Time drift detected. Please check VKTM trace file for more details.
Wed May 13 09:15:55 2015
DM00 started with pid = 352, OS id = 4784, job SYSTEM. SYS_EXPORT_SCHEMA_01
Wed May 13 09:15:56 2015
DW00 started with pid = 353, OS id = 4786, wid = 1, job SYSTEM. SYS_EXPORT_SCHEMA_01
Wed May 13 10:11:52 2015
Thread 1 cannot allocate new log, sequence 1734
Private strand flush not complete
Current log #1 seq #1733 mem #0:/oradata/orcl/redo01.log
Thread 1 advanced to log sequence 1734 (LGWR switch)
Current log #2 seq #1734 mem #0:/oradata/orcl/redo2.log
Wed May 13 13:15:38 2015
Thread 1 cannot allocate new log, sequence 1735
Private strand flush not complete
Current log #2 seq #1734 mem #0:/oradata/orcl/redo2.log
Thread 1 advanced to log sequence 1735 (LGWR switch)
Current log #4 seq #1735 mem #0:/oradata/orcl/redo4.log
Wed May 13 14:54:18 2015
Thread 1 cannot allocate new log, sequence 1736
Private strand flush not complete
Current log #4 seq #1735 mem #0:/oradata/orcl/redo4.log
Thread 1 advanced to log sequence 1736 (LGWR switch)
Current log #3 seq #1736 mem #0:/oradata/orcl/redo3.log
Wed May 13 17:39:31 2015
Thread 1 cannot allocate new log, sequence 1737
Private strand flush not complete
Current log #3 seq #1736 mem #0:/oradata/orcl/redo3.log
Thread 1 advanced to log sequence 1737 (LGWR switch)
Current log #1 seq #1737 mem #0:/oradata/orcl/redo01.log
Wed May 13 20:47:10 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Wed May 13 20:52:37 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Wed May 13 20:57:38 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Wed May 13 21:01:35 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process W001 submission failed with error = 20
Wed May 13 21:02:38 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m001 submission failed with error = 20
Wed May 13 21:05:44 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process W001 submission failed with error = 20
Wed May 13 21:12:38 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Wed May 13 21:42:39 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Wed May 13 21:43:00 2015
Process W001 submission failed with error = 20
Wed May 13 21:51:13 2015
Adjusting the default value of parameter parallel_max_servers
From 1280 to 385 due to the value of parameter processes (400)
Starting ORACLE instance (normal)
Wed May 13 21:52:40 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Wed May 13 21:53:33 2015
The error message is usually displayed when an Oracle instance fails to start a secondary background process (such as the m00x sub-process of mmon or W00x sub-process, there are many possibilities for this error, including insufficient Oracle instance resources and insufficient operating system resources. Generally, the process usage of an instance reaches the upper limit. You can query the v $ resource_limit view to check whether the total number of processes in the instance life cycle has reached the upper limit:

We can see that the maximum usage of MAX_UTILIZATION in processes has reached 400 limited by LIMIT_VALUE,
Sessions reaches 640.

From the output of the above V $ resource_limit view, it is very likely that the total number of processes has reached the upper limit, resulting in the creation of new background auxiliary processes failed. In fact, we can easily verify this point:

[Oracle @ bntjftest ~] $ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Thu May 14 15:28:15 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
PL/SQL Release 11.2.0.4.0-Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0-Production
NLSRTL Version 11.2.0.4.0-Production

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 500
SQL> alter system set processes = 40 scope = spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1987563520 bytes
Fixed Size 2254584 bytes
Variable Size 1224739080 bytes
Database Buffers 754974720 bytes
Redo Buffers 5595136 bytes
Database mounted.
Database opened.
SQL> select resource_name, MAX_UTILIZATION, LIMIT_VALUE from v $ resource_limit where resource_name in ('processs', 'session ');

RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
-----------------------------------------------------------------
Processes 24 40
Sessions 25 556

SQL> show parameter proce

NAME TYPE VALUE
-----------------------------------------------------------------------------
Aq_tm_processes integer 1
Cell_offload_processing boolean TRUE
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 40
Processor_group_name string

SQL> select resource_name, MAX_UTILIZATION, LIMIT_VALUE from v $ resource_limit where resource_name in ('processs', 'session ');

RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
-----------------------------------------------------------------
Processes 40 40
Sessions 45 556

SQL>
The following error is reported when the client is connected.

The warning information in the alter file is as follows:
Thu May 14 15:39:32 2015
SMCO started with pid = 37, OS id = 19445
Thu May 14 15:44:30 2015
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Thu May 14 15:49:30 2015
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Thu May 14 15:59:30 2015
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Thu May 14 16:00:30 2015
Process m000 submission failed with error = 20
Thu May 14 16:01:30 2015
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Thu May 14 16:03:31 2015
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Process m000 submission failed with error = 20
Thu May 14 16:04:31 2015
Process m000 submission failed with error = 20
In this example, we can clearly understand that the problem occurs because the total number of processes in the database has reached the parameter setting limit during actual operation, you can reasonably increase the initialization parameter processes or solve this problem by correcting abnormal client logins.