Oracle会话和进程数的监控

背景：先前在生产库上配置了oracle监控，每5分钟尝试连接一次数据库，若连接失败则通过nagios+fetion自动报警,此配置参考文章：http://ylw6006.blog.51cto.com/470441/787496

早晨收到报警信息后，登陆数据库执行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个进程

1. SQL> select count(*) from v$process;  
2.  
3.   COUNT(*)  
4. ----------  
5.         19  
6.  
7. SQL> show parameter process;  
8.  
9. NAME                                 TYPE        VALUE  
10. ------------------------------------ ----------- ------------------------------  
11. aq_tm_processes                      integer     0  
12. db_writer_processes                  integer     1  
13. gcs_server_processes                 integer     0  
14. job_queue_processes                  integer     10  
15. log_archive_max_processes            integer     2  
16. processes                            integer     20 

新的会话连接，则报连接到空闲的实例，alert日志则出现相应的报错

1. [root@db1 ~]# su - oracle  
2. [oracle@db1 ~]$ sqlplus /nolog  
3. SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 13:50:22 2012  
4. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
5.  
6. SQL> conn /as sysdba  
7. Connected to an idle instance.  
8.  
9. [oracle@db1 dbs]$ tail -f /u01/app/oracle/admin/db1/bdump/alert_db1.log   
10. Wed Jul  4 13:52:23 2012  
11. ksvcreate: Process(q000) creation failed  
12. Wed Jul  4 13:52:35 2012  
13. Process q001 died, see its trace file  
14. Wed Jul  4 13:52:35 2012  
15. ksvcreate: Process(q001) creation failed  
16. Wed Jul  4 13:52:37 2012  
17. Process m000 died, see its trace file  
18. Wed Jul  4 13:52:37 2012  
19. ksvcreate: Process(m000) creation failed 

refer: http://www.dba001.com/space.php?uid=854&do=blog&id=84

二：于是想到监控oracle的进程和会话数来进一步确定问题
1：首先要对用户进行显示授权，否则后面创建存储过程编译将会报错

1. SQL> grant select on V_$SESSION to hr;  
2. Grant succeeded.  
3.  
4. SQL> grant select on V_$PROCESS to hr;  
5. Grant succeeded. 

2：建表，用来存储结果

1. SQL> create table session_monitor(time timestamp,session_count number,process_count number);  
2. Table created. 

3：创建存储过程，将数据插入表

1. SQL> create or replace procedure proc_session  
2.   2  is  
3.   3  v_session number(8);  
4.   4  v_process number(8);  
5.   5  begin  
6.   6   select count(*) into v_session from v$session;  
7.   7   select count(*) into v_process from v$process;  
8.   8   insert into session_monitor values (sysdate,v_session,v_process);  
9.   9   commit;  
10.  10  end proc_session;  
11. Procedure created. 

4：创建任务

1. SQL> var job number;  
2. SQL> begin  
3.   2    sys.dbms_job.submit(job => :job,  
4.   3                        what => 'proc_session();',  
5.   4                        next_date => sysdate,  
6.   5                        interval => 'sysdate+2/1440');  
7.   6* end;  
8. PL/SQL procedure successfully completed. 

5：测试效果

1. SQL> exec proc_session;  
2. PL/SQL procedure successfully completed.  
3.  
4. SQL> alter session set nls_date_format='YYYY-MM-DD-HH24:MI:SS';  
5. Session altered.  
6.  
7. SQL>  select * from session_monitor;  
8.  
9. TIME                                     SESSION_COUNT PROCESS_COUNT  
10. ---------------------------------------- ------------- -------------  
11. 26-JUL-12 03.02.12.000000 PM                       140           155  
12. 26-JUL-12 03.02.14.000000 PM                       141           157  
13.  
14. SQL> select job,next_date from user_jobs where what='proc_session();';  
15.  
16.        JOB NEXT_DATE  
17. ---------- -------------------  
18.        145 2012-07-26-15:04:14  
19.  
20. SQL> select * from session_monitor;  
21.  
22. TIME                                     SESSION_COUNT PROCESS_COUNT  
23. ---------------------------------------- ------------- -------------  
24. 26-JUL-12 03.04.14.000000 PM                        87            94  
25. 26-JUL-12 03.02.12.000000 PM                       140           155  
26. 26-JUL-12 03.02.14.000000 PM                       141           157 

6：如果要删除任务，则运行下列的命令，145代表user_jobs视图中的job列

1. SQL> begin  
2.   2  dbms_job.remove(145);  
3.   3  end;  
4. PL/SQL procedure successfully completed. 

三：使用nagios+fetion，定时去监控会话和进程数
1：创建监控脚本，该脚本放任务计划中运行，每2分钟自动执行

1. [root@server240 libexec]# cat session_oracle.sh   
2. #!/bin/sh  
3. rm -rf /tmp/session_oracle.log  
4. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1  
5. /u01/app/oracle/product/11.2.0/db1/bin/sqlplus hr/hr@192.168.1.240:1521/orcl <<EOF 
6. set echo off  
7. set feedback off  
8. spool /tmp/session_oracle.log  
9. alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';  
10. select session_count from (select * from session_monitor order by time desc ) where  rownum=1;  
11. select process_count from (select * from session_monitor order by time desc ) where  rownum=1;  
12. spool off  
13. set echo on  
14. set feedback on  EOF

2：创建第二脚本，用来处理前面监控脚本的日志输出，将结果返回给监控服务器

1. [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;
2. SESSION_COUNT                                                                  
   -------------                                                                  
             138                                                                  
   SQL> select process_count from (select * from session_monitor order by time desc ) where  rownum=1;
3. PROCESS_COUNT                                                                  
   -------------                                                                  
             153                                                                  
   SQL> spool off
4.  
5. [root@server240 libexec]# cat check_oracle_session.sh   
6. #!/bin/sh  
7. STATE_OK=0 
8. STATE_CRITICAL=2 
9.  
10. if  [ -f /tmp/session_oracle.log  ];then  
11.      SESSION=$(grep -A 2 'SESSION_COUNT'  /tmp/session_oracle.log |tail -1|sed 's/[ ][ ]*//g')  
12.      PROCESS=$(grep -A 2 'PROCESS_COUNT'  /tmp/session_oracle.log |tail -1|sed 's/[ ][ ]*//g')  
13.      else   
14.      echo "something wrong,please check monitor script"  
15.      exit $STATE_CRITICAL  
16. fi  
17.  
18. if   [ $SESSION -gt 500 ] || [ $PROCESS -gt 500 ];then   
19.      echo "Current session is $SESSION,process is $PROCESS "  
20.      exit $STATE_CRITICAL  
21.      else   
22.        echo "Current session is $SESSION,process is $PROCESS "  
23.        exit $STATE_OK  
24. 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

五：后期观察结果，估计和内存问题相关
[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

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/943377如需转载请自行联系原作者

ylw6006