ORA-27300: OS system dependent operation:fork failed with status: 2、ORA-27301、ORA-27302

问题描述

业务人员反映应用程序无法登陆数据库，用PL/SQL登录时报错ORA-12537：

• 查看告警日志：

Mon Aug 03 14:12:19 2020
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_psp0_44053.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
Mon Aug 03 14:12:20 2020
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_44290.trc:
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_psp0_44053.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_44290.trc:
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_psp0_44053.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_44290.trc:

出现很多ORA-27300、ORA-27301、ORA-27302错误

• 切到oracle用户，会发生等待

[root@web ~]# su - oracle
-bash: fork: retry: Resource temporarily unavailable
-bash: fork: retry: Resource temporarily unavailable
-bash: fork: retry: Resource temporarily unavailable
-bash: fork: retry: Resource temporarily unavailable

• 用sqlplus登录报错:

ORA-12536: TNS: 操作可能阻塞

报错原因

The error messages indicating that oracle has problem in forking more process, the maximum number of PROCESSES allowed per user could be too low.
This is configured by the following kernel settings:
AIX: maxuproc
HP and Solaris: maxuprc
Solaris 10/11: project.max-lwps
Linux: nproc (/etc/security/limits.conf)

主要原因就是oracle的用户进程数量已经几乎接近oracle用户的user processes数量，所以数据库日志里面频繁出现大量的ORA-27300,ORA-27301,ORA-27302，根据MOS:392006.1建议修改/etc/security/limits.conf文件里的oracle的 nproc值为更大的值

问题解决

• 查看当前：

[oracle@web ~]$ ulimit -u
2047
[oracle@web ~]$ cat /etc/security/limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type>  <item>  <value>
#
#Where:
#<domain> can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#<domain>      <type>  <item>         <value>
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

# End of file
weblogic soft nproc 20470
weblogic hard nproc 16384
weblogic soft nofile 10240
weblogic hard nofile 65536

oracle    soft    nproc    2047                                                   
oracle    hard    nproc    16384                                              
oracle    soft    nofile    1024                                                          
oracle    hard    nofile    65536                                                   
oracle    soft    stack    10240         

• 修改参数

[oracle@web ~]$ cat /etc/security/limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type>  <item>  <value>
#
#Where:
#<domain> can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#<domain>      <type>  <item>         <value>
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

# End of file
weblogic soft nproc 20470
weblogic hard nproc 16384
weblogic soft nofile 10240
weblogic hard nofile 65536

oracle    soft    nproc    10240                                                   
oracle    hard    nproc    16384                                              
oracle    soft    nofile    10240                                                          
oracle    hard    nofile    65536                                                   
oracle    soft    stack    10240          

生效：

[oracle@web ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 515685
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 40960
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 10240
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[oracle@web ~]$ ulimit -u
10240

但是依旧报错，又看了下内存
free已经是0了，cached占了113

手动释放缓存

而后恢复正常