ASM 异常 ORA-00020: maximum number of processes (100) exceeded

最新推荐文章于 2021-04-03 13:07:00 发布

lixora

最新推荐文章于 2021-04-03 13:07:00 发布

阅读量1.8k

点赞数

分类专栏： ASM troubleshooting

本文链接：https://blog.csdn.net/lixora/article/details/74331691

版权

troubleshooting 同时被 2 个专栏收录

110 篇文章 0 订阅

订阅专栏

ASM

8 篇文章 0 订阅

订阅专栏

数据库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

where n is the number database instances connecting to the Oracle ASM instance.
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='*';