早晨收到报警信息后，登陆数据库执行ps -ef查看oracle的后台进程都在，使用conn /as sysdba的方式登陆数据库，提示连接到空闲的实例，使用easy connect 方式连接则报oracle实例无法分配内存，从报错提示上看，就像oracle数据库实例未打开的状态！分析alert日志不断出现如下错误信息：
Process J002 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_18577.trc:
trace文件摘要信息如下：
*** 2012-07-26 10:20:31.068
Process J002 is dead (pid=13857 req_ver=1136 cur_ver=1136 state=KSOSP_SPAWNED).
*** 2012-07-26 10:20:32.069
Process J002 is dead (pid=13876 req_ver=1594 cur_ver=1594 state=KSOSP_SPAWNED).
google查询一番后，发现大部分描述和oracle的进程数设置有关,又或者是内存不足引起！于是在oracle 10g环境下测试，线上数据库环境为11.2.0.3
一：设置processes初始化参数值为20，重启数据库后，已经占用19个进程
SQL> select count(*) from v$process;
COUNT(*)
----------
19
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 20
新的会话连接，则报连接到空闲的实例，alert日志则出现相应的报错
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 13:50:22 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
[oracle@db1 dbs]$ tail -f /u01/app/oracle/admin/db1/bdump/alert_db1.log
Wed Jul 4 13:52:23 2012
ksvcreate: Process(q000) creation failed
Wed Jul 4 13:52:35 2012
Process q001 died, see its trace file
ksvcreate: Process(q001) creation failed
Wed Jul 4 13:52:37 2012
Process m000 died, see its trace file
ksvcreate: Process(m000) creation failed
二：于是想到监控oracle的进程和会话数来进一步确定问题
1：首先要对用户进行显示授权，否则后面创建存储过程编译将会报错
SQL> grant select on V_$SESSION to hr;
Grant succeeded.
SQL> grant select on V_$PROCESS to hr;
Grant succeeded.
2：建表，用来存储结果
SQL> create table session_monitor(time timestamp,session_count number,process_count number);
Table created.
3：创建存储过程，将数据插入表
SQL> create or replace procedure proc_session
2 is
3 v_session number(8);
4 v_process number(8);
5 begin
6 select count(*) into v_session from v$session;
7 select count(*) into v_process from v$process;
8 insert into session_monitor values (sysdate,v_session,v_process);
9 commit;
10 end proc_session;
Procedure created.
4：创建任务
SQL> var job number;
SQL> begin
2 sys.dbms_job.submit(job => :job,
3 what => 'proc_session();',
4 next_date => sysdate,
5 interval => 'sysdate+2/1440');
6* end;
PL/SQL procedure successfully completed.
5：测试效果
SQL> exec proc_session;
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='YYYY-MM-DD-HH24:MI:SS';
Session altered.
SQL> select * from session_monitor;
TIME SESSION_COUNT PROCESS_COUNT
---------------------------------------- ------------- -------------
26-JUL-12 03.02.12.000000 PM 140 155
26-JUL-12 03.02.14.000000 PM 141 157
SQL> select job,next_date from user_jobs where what='proc_session();';
JOB NEXT_DATE
---------- -------------------
145 2012-07-26-15:04:14
SQL> select * from session_monitor;
26-JUL-12 03.04.14.000000 PM 87 94
26-JUL-12 03.02.14.000000 PM 141 157
6：如果要删除任务，则运行下列的命令，145代表user_jobs视图中的job列
2 dbms_job.remove(145);
3 end;
三：使用nagios+fetion，定时去监控会话和进程数
1：创建监控脚本，该脚本放任务计划中运行，每2分钟自动执行
[root@server240 libexec]# cat session_oracle.sh
#!/bin/sh
rm -rf /tmp/session_oracle.log
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1
/u01/app/oracle/product/11.2.0/db1/bin/sqlplus hr/[email protected]:1521/orcl <<EOF
set echo off
set feedback off
spool /tmp/session_oracle.log
alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
select session_count from (select * from session_monitor order by time desc ) where rownum=1;
select process_count from (select * from session_monitor order by time desc ) where rownum=1;
spool off
set echo on
set feedback on EOF
2：创建第二脚本，用来处理前面监控脚本的日志输出，将结果返回给监控服务器
[root@server240 ~]# cat /tmp/session_oracle.log
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
SQL> select session_count from (select * from session_monitor order by time desc ) where rownum=1;
SESSION_COUNT
-------------
138
SQL> select process_count from (select * from session_monitor order by time desc ) where rownum=1;
PROCESS_COUNT
153
SQL> spool off
[root@server240 libexec]# cat check_oracle_session.sh
STATE_OK=0
STATE_CRITICAL=2
if [ -f /tmp/session_oracle.log ];then
SESSION=$(grep -A 2 'SESSION_COUNT' /tmp/session_oracle.log |tail -1|sed 's/[ ][ ]*//g')
PROCESS=$(grep -A 2 'PROCESS_COUNT' /tmp/session_oracle.log |tail -1|sed 's/[ ][ ]*//g')
else
echo "something wrong,please check monitor script"
exit $STATE_CRITICAL
fi
if [ $SESSION -gt 500 ] || [ $PROCESS -gt 500 ];then
echo "Current session is $SESSION,process is $PROCESS "
echo "Current session is $SESSION,process is $PROCESS "
exit $STATE_OK
fi
四：实际效果
[root@server198 ~]# /usr/local/nagios/libexec/check_nrpe -H 192.168.1.240 -c check_oracle_session
Current session is 138,process is 153
<a target="_blank" href="http://blog.51cto.com/attachment/201207/172435717.jpg"></a>
五：后期观察结果，估计和内存问题相关
[oracle@server240 ~]$ sar -r |grep 10:20
10:20:02 AM 3481492 21195164 85.89 675584 13661448 3683012 413552 10.10
[oracle@server240 ~]$ sar -r |grep 04:40
04:40:01 PM 2076748 22599908 91.58 734088 14581728 4048864 47700 1.16
<a target="_blank" href="http://blog.51cto.com/attachment/201207/172606316.jpg"></a>
本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/943377如需转载请自行联系原作者
ylw6006
![ACS基本配置-权限等级管理[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)