本文主要是介绍ASM 异常 ORA-00020: maximum number of processes (100) exceeded，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
数据库alert 日志：Tue Jul 04 08:54:31 2017
NOTE: ASMB terminating
Errors in file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_asmb_62914802.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 138 Serial number: 335
Errors in file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_asmb_62914802.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 138 Serial number: 335
ASMB (ospid: 62914802): terminating the instance due to error 15064
Tue Jul 04 08:54:32 2017
System state dump requested by (instance=2, osid=62914802 (ASMB)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_diag_8454528.trc
Tue Jul 04 08:54:33 2017
ORA-1092 : opitsk aborting process
Tue Jul 04 08:54:34 2017
ORA-1092 : opitsk aborting process
Tue Jul 04 08:54:34 2017
License high water mark = 811
Instance terminated by ASMB, pid = 62914802
USER (ospid: 32112770): terminating the instance
Instance terminated by USER, pid = 32112770
Tue Jul 04 08:55:18 2017
Starting ORACLE instance (normal)
在alert 日志中看到数据库实例被Instance terminated by ASMB 进程关闭；
继续往上看看alert 日志发现数据库和Asm 实例通信故障。asm 实例
WARNING: ASM communication error: op 18 state 0x40 (20)
ERROR: slave communication error with ASM
NOTE: Deferred communication with ASM instance
Errors in file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_pmon_10551588.trc:
ORA-00020: maximum number of processes (100) exceeded
WARNING: ASM communication error: op 18 state 0x40 (20)
ERROR: slave communication error with ASM
NOTE: Deferred communication with ASM instance
Errors in file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_pmon_10551588.trc:
ORA-00020: maximum number of processes (100) exceeded
WARNING: ASM communication error: op 18 state 0x40 (20)
ERROR: slave communication error with ASM
NOTE: Deferred communication with ASM instance
Errors in file /u01/app/diag/rdbms/soarca/soarca2/trace/soarca2_pmon_10551588.trc:
ORA-00020: maximum number of processes (100) exceeded
SQL> select * from v$resource_limit where rownum< 3;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------- ------------------- --------------- -------------------- --------------------
processes 49 100 100 100
sessions 49 100 176 176
SQL> select program,count(*) from v$session group by program;
PROGRAM COUNT(*)
---------------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 18
oraagent.bin@SOAR-2 (TNS V1-V3) 3
oracle@SOAR-2 (ASMB) 1
oracle@SOAR-2 (CKPT) 1
oracle@SOAR-2 (DBW0) 1
oracle@SOAR-2 (DIA0) 1
oracle@SOAR-2 (DIAG) 1
oracle@SOAR-2 (GEN0) 1
oracle@SOAR-2 (GMON) 1
oracle@SOAR-2 (LCK0) 1
oracle@SOAR-2 (LGWR) 1
PROGRAM COUNT(*)
---------------------------------------- ----------
oracle@SOAR-2 (LMD0) 1
oracle@SOAR-2 (LMHB) 1
oracle@SOAR-2 (LMON) 1
oracle@SOAR-2 (LMS0) 1
oracle@SOAR-2 (MMAN) 1
oracle@SOAR-2 (MMNL) 1
oracle@SOAR-2 (MMON) 1
oracle@SOAR-2 (PING) 1
oracle@SOAR-2 (PMON) 1
oracle@SOAR-2 (PSP0) 1
oracle@SOAR-2 (RBAL) 1
PROGRAM COUNT(*)
---------------------------------------- ----------
oracle@SOAR-2 (SMON) 1
oracle@SOAR-2 (TNS V1-V3) 4
oracle@SOAR-2 (VKTM) 1
sqlplus@SOAR-2 (TNS V1-V3) 1
26 rows selected.
extract@SOAR-2 (TNS V1-V3)
这是一个ogg 的后台抽取进程。
SQL> select PROGRAM,count(*) from v$session group by PROGRAM;
PROGRAM COUNT(*)
------------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 34
oraagent.bin@SOAR-2 (TNS V1-V3) 3
oracle@SOAR-2 (ASMB) 1
oracle@SOAR-2 (CKPT) 1
oracle@SOAR-2 (DBW0) 1
oracle@SOAR-2 (DIA0) 1
oracle@SOAR-2 (DIAG) 1
oracle@SOAR-2 (GEN0) 1
oracle@SOAR-2 (GMON) 1
oracle@SOAR-2 (LCK0) 1
oracle@SOAR-2 (LGWR) 1
PROGRAM COUNT(*)
------------------------------------- ----------
oracle@SOAR-2 (LMD0) 1
oracle@SOAR-2 (LMHB) 1
oracle@SOAR-2 (LMON) 1
oracle@SOAR-2 (LMS0) 1
oracle@SOAR-2 (MMAN) 1
oracle@SOAR-2 (MMNL) 1
oracle@SOAR-2 (MMON) 1
oracle@SOAR-2 (PING) 1
oracle@SOAR-2 (PMON) 1
oracle@SOAR-2 (PSP0) 1
oracle@SOAR-2 (RBAL) 1
PROGRAM COUNT(*)
------------------------------------- ----------
oracle@SOAR-2 (SMON) 1
oracle@SOAR-2 (TNS V1-V3) 4
oracle@SOAR-2 (VKTM) 1
sqlplus@SOAR-2 (TNS V1-V3) 1
查询下 v$active_session_history 查看下历史会话信息
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.56.52.103 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.57.50.118 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.00.22.157 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.00.36.160 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.01.26.173 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.05.45.246 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.07.06.266 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.08.34.290 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.09.31.305 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.10.25.319 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.12.44.355 AM 17
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.12.48.356 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.13.06.360 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.14.09.377 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.14.51.388 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.14.57.390 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.09.392 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.27.397 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.16.14.407 AM 17
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.27.08.649 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.27.11.650 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.31.670 AM 18
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.43.673 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.46.674 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.49.675 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.52.676 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.28.53.676 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.29.20.683 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.29.21.683 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.29.41.688 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.31.17.712 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.33.09.741 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.33.21.745 AM 18
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.36.41.796 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.36.46.797 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.39.15.833 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.39.21.834 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.39.32.837 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.39.45.842 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.39.47.842 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.40.04.846 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.46.25.942 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.46.32.944 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.46.33.944 AM 18
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.47.46.963 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.51.25.017 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.54.04.058 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.54.41.068 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.56.23.095 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.56.55.103 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.57.36.114 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 09.59.13.139 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.00.10.153 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.00.13.153 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.00.25.157 AM 18
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.03.30.209 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.03.37.211 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.05.51.248 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.06.00.250 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.07.08.267 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.08.58.296 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.09.15.301 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.10.23.319 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.12.16.348 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.01.390 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.19.395 AM 18
PROGRAM SAMPLE_TIME COUNT(*)
-------------------------------- ------------------------------- ----------
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.20.395 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.15.41.399 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.16.01.404 AM 18
extract@SOAR-2 (TNS V1-V3) 04-JUL-17 10.16.05.405 AM 18
6285 rows selected.
根据上述信息基本可以确定是由于ogg 抽取进程导致asm 后台进程数满
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string +OCRVOTEDG/hzfgcluster/asmpara
meterfile/registry.253.7623713
85
SQL> show parameter proce
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
processes integer 100
根据当前系统环境，ogg 使用 。需要增加 asm 进程数
More details about this calculation are available in the NOTE 265633.1 "ASM Technical Best Practices"
For 11.2 set up, the formula is PROCESSES = 50 + 50*n
This is in the standard document http://docs.oracle.com/cd/E11882_01/server.112/e18951/asminst.htm#OSTMG94080
重启生效
SQL> alter system set processes=150 scope=spfile sid='*';
这篇关于ASM 异常 ORA-00020: maximum number of processes (100) exceeded的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
