SQL> select count(*) from v$session;
COUNT(*)
----------
98
SQL> select count(*) from v$process;
COUNT(*)
----------
99
more odsprod.log
目前系统正常
检查结果如下:
系统的进程树最大150.
当前系统连接数目如下:
SQL> select count(*) from v$session;
COUNT(*)
----------
98
SQL> select count(*) from v$process;
COUNT(*)
----------
99
问题时间段发起的新连接检查数据库监听日志:
发现问题时间段以下ip 发起了大量连接。
07-JUN-2017 08:23:04 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=einvuat))(SID=odsprod)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.63.68)(PORT=13923)) * establish * odsprod * 0
07-JUN-2017 08:23:04 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=einvprod))(SID=odsprod)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.127)(PORT=23356)) * establish * odsprod * 0
请应用也检查下。
可能原因1 .
密码即将过期，导致帐号登陆有问题。不断重连。
需要检查profile 文件，检查设置
############
可能原因2：
ps -ef|grep oracledbprod
oracle 5861 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5175 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5751 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 25819 1 0 Jun 29 ? 0:20 oracledbprod (LOCAL=NO)
oracle 20312 1 0 Jul 3 ? 1:53 oracledbprod (LOCAL=NO)
oracle 27998 1 0 Jul 1 ? 3:43 oracledbprod (LOCAL=NO)
oracle 18328 1 0 Jan 11 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5933 1 0 Jun 30 ? 8:35 oracledbprod (LOCAL=NO)
oracle 25377 1 0 Jun 30 ? 333:24 oracledbprod (LOCAL=NO)
oracle 18326 1 0 Jan 11 ? 0:53 oracledbprod (LOCAL=NO)
oracle 5755 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 26120 1 0 Jun 30 ? 3:21 oracledbprod (LOCAL=NO)
oracle 17508 1 0 Jun 25 ? 0:03 oracledbprod (LOCAL=NO)
oracle 18330 1 0 Jan 11 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5765 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5769 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8001 1 0 Jun 30 ? 2:14 oracledbprod (LOCAL=NO)
oracle 28350 1 0 Jul 1 ? 0:00 oracledbprod (LOCAL=NO)
oracle 2992 1 0 Jul 3 ? 0:02 oracledbprod (LOCAL=NO)
oracle 24181 1 0 17:04:52 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5179 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5759 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 20534 1 0 Jun 30 ? 0:56 oracledbprod (LOCAL=NO)
oracle 14752 1 0 Jun 29 ? 1:09 oracledbprod (LOCAL=NO)
oracle 18324 1 0 Jan 11 ? 0:52 oracledbprod (LOCAL=NO)
oracle 7720 1 0 May 22 ? 157:56 oracledbprod (LOCAL=NO)
oracle 5169 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 7995 1 0 Jun 30 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5937 1 0 Jun 30 ? 10:20 oracledbprod (LOCAL=NO)
oracle 21718 1 0 Jun 30 ? 1:33 oracledbprod (LOCAL=NO)
oracle 5877 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 6156 1 0 Jun 30 ? 3:19 oracledbprod (LOCAL=NO)
oracle 5869 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8003 1 247 Jun 30 ? 23:06 oracledbprod (LOCAL=NO)
oracle 20310 1 0 Jul 3 ? 6:24 oracledbprod (LOCAL=NO)
oracle 26825 1 0 Jun 28 ? 10:08 oracledbprod (LOCAL=NO)
oracle 24289 1 0 Jul 1 ? 0:01 oracledbprod (LOCAL=NO)
oracle 3574 1 0 May 19 ? 97:26 oracledbprod (LOCAL=NO)
oracle 9577 1 0 Jul 3 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5873 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5939 1 0 Jun 30 ? 4:18 oracledbprod (LOCAL=NO)
oracle 8017 1 0 Jun 30 ? 16:33 oracledbprod (LOCAL=NO)
oracle 5753 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 11659 1 0 16:00:49 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5386 1 0 Jun 28 ? 0:02 oracledbprod (LOCAL=NO)
oracle 5865 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 22762 1 0 Jul 3 ? 1:28 oracledbprod (LOCAL=NO)
oracle 20536 1 0 Jun 30 ? 1:13 oracledbprod (LOCAL=NO)
oracle 5193 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8011 1 0 Jun 30 ? 3:32 oracledbprod (LOCAL=NO)
oracle 17506 1 0 Jun 25 ? 0:02 oracledbprod (LOCAL=NO)
oracle 5191 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8015 1 0 Jun 30 ? 4:09 oracledbprod (LOCAL=NO)
oracle 5177 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5747 1 0 13:01:02 ? 0:00 oracledbprod (LOCAL=NO)
oracle 4309 1 0 Jun 27 ? 0:01 oracledbprod (LOCAL=NO)
oracle 27348 1 0 Jun 28 ? 0:01 oracledbprod (LOCAL=NO)
oracle 5871 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5187 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5767 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 20532 1 0 Jun 30 ? 0:42 oracledbprod (LOCAL=NO)
oracle 24080 1 0 Jun 26 ? 0:05 oracledbprod (LOCAL=NO)
oracle 8007 1 0 Jun 30 ? 7:25 oracledbprod (LOCAL=NO)
oracle 18322 1 0 Jan 11 ? 0:00 oracledbprod (LOCAL=NO)
oracle 14552 1 0 Jul 4 ? 0:05 oracledbprod (LOCAL=NO)
oracle 5881 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5189 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5757 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5853 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
oracle 6253 1 0 Jun 29 ? 0:27 oracledbprod (LOCAL=NO)
oracle 2384 1 0 10:15:25 ? 0:20 oracledbprod (LOCAL=NO)
oracle 5144 1 0 Jun 30 ? 8:51 oracledbprod (LOCAL=NO)
oracle 8005 1 0 Jun 30 ? 10:06 oracledbprod (LOCAL=NO)
oracle 5745 1 0 13:01:02 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8023 1 0 Jun 30 ? 11:22 oracledbprod (LOCAL=NO)
oracle 1162 1 0 Jul 3 ? 0:23 oracledbprod (LOCAL=NO)
oracle 8019 1 0 Jun 30 ? 22:10 oracledbprod (LOCAL=NO)
oracle 5941 1 0 Jun 30 ? 17:44 oracledbprod (LOCAL=NO)
oracle 5171 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5771 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 8009 1 0 Jun 30 ? 16:14 oracledbprod (LOCAL=NO)
oracle 8013 1 0 Jun 30 ? 23:01 oracledbprod (LOCAL=NO)
oracle 5761 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 26703 1 0 Jun 30 ? 0:08 oracledbprod (LOCAL=NO)
oracle 5763 1 0 13:01:03 ? 0:00 oracledbprod (LOCAL=NO)
oracle 26967 1 0 Jul 2 ? 3:05 oracledbprod (LOCAL=NO)
oracle 29683 1 0 Jun 27 ? 0:01 oracledbprod (LOCAL=NO)
oracle 5855 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5875 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5181 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 20538 1 0 Jun 30 ? 2:07 oracledbprod (LOCAL=NO)
oracle 5173 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5195 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5749 1 0 13:01:02 ? 0:00 oracledbprod (LOCAL=NO)
oracle 4868 1 0 Jun 30 ? 7:34 oracledbprod (LOCAL=NO)
oracle 8025 1 0 Jun 30 ? 9:55 oracledbprod (LOCAL=NO)
oracle 21447 1 0 Jun 29 ? 0:01 oracledbprod (LOCAL=NO)
oracle 11226 1 0 16:00:00 ? 0:20 oracledbprod (LOCAL=NO)
oracle 5185 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 2371 1 0 Jun 27 ? 0:02 oracledbprod (LOCAL=NO)
oracle 8021 1 0 Jun 30 ? 20:07 oracledbprod (LOCAL=NO)
oracle 5743 1 0 13:01:02 ? 0:00 oracledbprod (LOCAL=NO)
oracle 21716 1 0 Jun 30 ? 0:40 oracledbprod (LOCAL=NO)
oracle 5935 1 0 Jun 30 ? 7:02 oracledbprod (LOCAL=NO)
oracle 5859 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
oracle 16658 1 0 Jun 30 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5197 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5867 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5183 1 0 12:59:44 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5879 1 0 13:01:39 ? 0:00 oracledbprod (LOCAL=NO) <- 13:01:39 means db connection begin at time
oracle 5863 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
oracle 5857 1 0 13:01:38 ? 0:00 oracledbprod (LOCAL=NO)
dbmon 14965 14587 1 01:37:27 pts/3 0:00 grep oracledbprod
oracle 1795 1 0 10:12:15 ? 77:50 oracledbprod (LOCAL=NO)
结论：
1.今天消耗主要是以192.168.4.33为主，从12点到13点这个时间段，是busr 用户访问。
it consume 45 process, thre app server ,every app server config 15 process, normal ausr only consume 10 user.
SQL> select count(*) from v$session where USERNAME='bUSR';
COUNT(*)
----------
45
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
SQL> show parameter session
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 256
2.first kill session:
spool /tmp/1.sql
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='bUSR';
spool off
3.long time:
step 1: we will try to add process from 150 to 250, session from 256 to 356.
and restart dbprod
step 2: app turn down 15 process/per server to 7 process/per server , decrease process from 45 to 21.
建议：
新建用户，要看process 和session 数目定义够不够用，先跟应用一起评估。以免上线造成问题。
#############2018
step 1: 预处理
cd /app/product/database/diag/tnslsnr/pscfdb01/lsnr_scf/trace
sed -n '/07-MAR-2018 2/p' lsnr_db.log |grep establish > p.log
####
step 2:
＃开始处理：
#!/bin/bash
i=1
while(( i <= 60 ))
do
s=`printf "%02d\n" $i`
echo "$1""-MAR-2018 22:""$s"
#echo "07-MAR-2018 21:""$s"
grep "$1""-MAR-2018 22:""$s" $2 |wc -l
#grep "07-MAR-2018 21:""$s" p.log |wc -l
let "i += 1"
done
usage: 06 日期，查询16日 22点每分钟的连接数
sh 1.sh 06 p4.log