processes参数太小导致的异常

新登陆的客户端无法登陆上数据库，抛了一堆堆栈和memory的异常。赶紧上os主机上，看看是否有告警信息。
查了一下告警日志很正常，没有特别的异样信息出现。查询后台进程信息，如下：
[oracle@linux3200 log]$ ps -ef |grep ora_

[@more@]

oracle 7704 1 0 Nov07 ? 00:00:44 ora_pmon_db3200
oracle 7706 1 0 Nov07 ? 00:00:06 ora_psp0_db3200
oracle 7708 1 0 Nov07 ? 00:00:04 ora_mman_db3200
oracle 7710 1 0 Nov07 ? 00:00:19 ora_dbw0_db3200
oracle 7712 1 0 Nov07 ? 00:03:44 ora_lgwr_db3200
oracle 7714 1 0 Nov07 ? 00:00:32 ora_ckpt_db3200
oracle 7716 1 0 Nov07 ? 00:00:16 ora_smon_db3200
oracle 7718 1 0 Nov07 ? 00:00:00 ora_reco_db3200
oracle 7720 1 0 Nov07 ? 00:00:41 ora_cjq0_db3200
oracle 7722 1 0 Nov07 ? 00:00:10 ora_mmon_db3200
oracle 7726 1 0 Nov07 ? 00:00:47 ora_mmnl_db3200
oracle 7728 1 0 Nov07 ? 00:00:00 ora_d000_db3200
oracle 7730 1 0 Nov07 ? 00:00:00 ora_s000_db3200
oracle 7742 1 0 Nov07 ? 00:00:00 ora_qmnc_db3200
oracle 7822 1 0 Nov07 ? 00:00:00 ora_q000_db3200
oracle 7824 1 0 Nov07 ? 00:00:00 ora_q001_db3200
oracle 18265 1 0 Nov09 ? 00:02:33 ora_j000_db3200
oracle 31106 1 0 11:50 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 31168 1 0 11:57 ? 00:00:08 oracledb3200 (LOCAL=NO)
oracle 31446 1 0 13:59 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 31450 1 1 13:59 ? 00:01:58 oracledb3200 (LOCAL=NO)
oracle 31452 1 0 13:59 ? 00:00:00 oracledb3200 (LOCAL=NO)
... ... ... ...
oracle 31831 1 0 15:54 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 31841 1 0 15:55 ? 00:00:01 oracledb3200 (LOCAL=NO)
oracle 32025 1 0 16:34 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32065 1 0 16:37 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32069 1 0 16:37 ? 00:00:00 oracledb3200 (LOCAL=NO)
root 32115 32070 0 16:38 pts/0 00:00:00 su - oracle
oracle 32116 32115 0 16:38 pts/0 00:00:00 -bash
oracle 32177 1 0 16:39 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32189 1 0 16:40 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32193 1 0 16:41 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32199 32116 0 16:42 pts/0 00:00:00 tail -f alert_db3200.log
root 32247 32202 0 16:42 pts/1 00:00:00 su - oracle
oracle 32248 32247 0 16:42 pts/1 00:00:00 -bash
oracle 32340 1 0 16:48 ? 00:00:00 oracledb3200 (LOCAL=NO)
oracle 32346 32248 0 16:49 pts/1 00:00:00 ps -ef
oracle 32347 32248 0 16:49 pts/1 00:00:00 grep ora
说明数据库运行是没问题的，并没有宕掉。
SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown immediate;
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-00020: maximum number of processes (%s) exceeded
SQL>
这里提示ORA-00020的异常，说明当前数据库连接已经满了，奇怪的是sysdba身份也不让登陆，并且显示的是idle的实例。
只好通过强制的手段了。
[oracle@linux3200 log]$
[oracle@linux3200 log]$ kill -9 31855
[oracle@linux3200 log]$ kill -9 31861
[oracle@linux3200 log]$ kill -9 31843
通过杀死几个进程后再登陆就没问题了。
SQL> show parameter session

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 20
session_max_open_files integer 10
sessions integer 170
shared_server_sessions integer
SQL> show parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/10g/dbs/sp
filedb3200.ora
果然这里processes和sessions的设置值都比较低。
SQL> alter system set processes=1000 scope=spfile;

System altered.

SQL> alter system set sessions=1000 scope=spfile;

System altered.

SQL>
重启后问题彻底解决！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-982039/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-982039/