| |
Today, a user consultation database downtime, then the user can not log on the database symptoms, in order to understand the cause, to view the alarm log user alert.log, found in the evening around 10:00 a lot of ORA-00020: No more process State objects available error, 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 can not 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 can not 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 can not 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 can not 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 can not 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 can not 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 can not 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 can not 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 can not 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 can not 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 for
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 for
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 for
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 for
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 for
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 for
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 for
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 for
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 for
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 caused when the Oracle instance fails to start the process when creating some secondary background process (such as mmon's child process m00x or child process W00x). There are many possibilities of this error, including the lack of Oracle instance resources, Operating system resources and so on. One of the more common instance of the instance of the process to reach the upper limit, you can query the v $ resource_limit view to understand whether the instance life cycle of the total number of process overflow
We can see the MAX_UTILIZATION processes the maximum number of used LIMIT_VALUE has been to limit the 400,
Sessions reached 640.
From the above V $ resource_limit view of the output point of view, it is very likely that the total number of processes reached the ceiling led to the creation of a new background process failure, in fact, we can easily verify this:
[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, 2013, Oracle. All rights reserved.
Connected to:
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 ( 'processes', 'sessions');
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 ( 'processes', 'sessions');
RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
------------------------------ --------------- ----- ---------------
Processes 40 40
Sessions 45 556
SQL>
Client connection, such as the error reported.
The warning messages in the alter file are 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 for
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 for
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 for
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 for
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 for
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
Example, we can clearly understand that the database is running in the actual process of the total number of processes to reach the upper limit of the parameter set to cause problems, then you can reasonably increase the initialization parameters processes or by modifying the frequent program client login to solve the problem. |
|
|
|