I have many monitoring tools implemented here which keep bugging my application instances. We have one external careers site too which too increments some orphan processes in my Oracle database. Although we have script in place to take of them and kick them out frequently, but still I can see my total number of process reached close to my max process limit. Here what I have to do in that situation:
1. Run this query to find out them:
SELECT spid FROM v$process WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);
2. Grep what are they and what are they doing :
SELECT '!ps -ef : grep ' spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);
3. Kill them, if they are not required:
SELECT '!kill -9 ' :: spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);
Happy Troubleshooting !!!
Note: Use Pipe sign in place of colon which I have mentioned in statments
Subscribe to:
Post Comments (Atom)
3 comments:
Oracle Certifications (Newbie)
http://career-assessments.blogspot.com/2008/01/oracle-certifications-newbies.html
For Oracle 11g DBA OCA, OCP, DBA OCM Certification Aspirants
http://career-assessments.blogspot.com/2008/01/oracle-certifications-aspirants.html
This query is not OK (if we forget missing "||" sign)
sys@PROD> SELECT '!ps -ef|grep ' || spid FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr); 2
'!PS-EF|GREP'||SPID
-------------------------
!ps -ef|grep 9814
!ps -ef|grep 9816
!ps -ef|grep 9810
!ps -ef|grep
!ps -ef|grep 9818
!ps -ef|grep 9812
!ps -ef|grep 9822
!ps -ef|grep 9824
!ps -ef|grep 9820
9 rows selected.
Elapsed: 00:00:00.05
sys@PROD> !ps -ef|grep 9814
prod 9814 1 0 08:04 ? 00:00:40 ora_p002_PROD
prod 1949 14684 0 15:16 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9814
prod 1962 1949 0 15:16 pts/4 00:00:00 grep 9814
sys@PROD> !ps -ef|grep 9816
prod 9816 1 0 08:04 ? 00:00:38 ora_p003_PROD
prod 2169 14684 0 15:16 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9816
prod 2180 2169 0 15:16 pts/4 00:00:00 grep 9816
sys@PROD> !ps -ef|grep 9810
prod 9810 1 0 08:04 ? 00:00:49 ora_p000_PROD
prod 2410 14684 0 15:17 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9810
prod 2428 2410 0 15:17 pts/4 00:00:00 grep 9810
sys@PROD> !ps -ef|grep 9818
prod 9818 1 0 08:04 ? 00:01:04 ora_p004_PROD
prod 2560 14684 0 15:17 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9818
prod 2564 2560 0 15:17 pts/4 00:00:00 grep 9818
sys@PROD> !ps -ef|grep 9812
prod 9812 1 0 08:04 ? 00:00:37 ora_p001_PROD
prod 2705 14684 0 15:17 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9812
prod 2707 2705 0 15:17 pts/4 00:00:00 grep 9812
sys@PROD> !ps -ef|grep 9822
prod 9822 1 0 08:04 ? 00:01:01 ora_p006_PROD
prod 2823 14684 0 15:17 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9822
prod 2825 2823 0 15:17 pts/4 00:00:00 grep 9822
sys@PROD> !ps -ef|grep 9824
prod 9824 1 0 08:04 ? 00:01:02 ora_p007_PROD
prod 4298 14684 0 15:18 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9824
prod 4300 4298 0 15:18 pts/4 00:00:00 grep 9824
sys@PROD> !ps -ef|grep 9820
prod 9820 1 0 08:04 ? 00:01:12 ora_p005_PROD
prod 4415 14684 0 15:18 pts/4 00:00:00 /bin/bash -c ps -ef|grep 9820
prod 4417 4415 0 15:18 pts/4 00:00:00 grep 9820
sys@PROD> !ps -ef|grep ora_
prod 8521 1 0 Apr06 ? 00:00:14 ora_pmon_PROD
prod 8523 1 0 Apr06 ? 00:00:00 ora_psp0_PROD
prod 8525 1 0 Apr06 ? 00:00:29 ora_mman_PROD
prod 8527 1 0 Apr06 ? 00:02:33 ora_dbw0_PROD
prod 8529 1 0 Apr06 ? 00:02:16 ora_dbw1_PROD
prod 8531 1 1 Apr06 ? 00:13:03 ora_lgwr_PROD
prod 8533 1 0 Apr06 ? 00:00:45 ora_ckpt_PROD
prod 8535 1 0 Apr06 ? 00:01:04 ora_smon_PROD
prod 8537 1 0 Apr06 ? 00:00:00 ora_reco_PROD
prod 8539 1 0 Apr06 ? 00:00:12 ora_cjq0_PROD
prod 8541 1 0 Apr06 ? 00:00:24 ora_mmon_PROD
prod 8543 1 0 Apr06 ? 00:01:29 ora_mmnl_PROD
prod 8549 1 0 Apr06 ? 00:03:51 ora_arc0_PROD
prod 8551 1 0 Apr06 ? 00:03:47 ora_arc1_PROD
prod 8799 1 0 Apr06 ? 00:00:02 ora_qmnc_PROD
prod 9464 1 0 Apr06 ? 00:00:21 ora_q000_PROD
prod 9466 1 0 Apr06 ? 00:00:21 ora_q001_PROD
prod 9468 1 0 Apr06 ? 00:00:18 ora_q002_PROD
prod 9470 1 0 Apr06 ? 00:00:24 ora_q003_PROD
prod 9472 1 0 Apr06 ? 00:00:20 ora_q004_PROD
prod 14631 1 0 06:53 ? 00:00:03 ora_q006_PROD
prod 9810 1 0 08:04 ? 00:00:49 ora_p000_PROD
prod 9812 1 0 08:04 ? 00:00:37 ora_p001_PROD
prod 9814 1 0 08:04 ? 00:00:40 ora_p002_PROD
prod 9816 1 0 08:04 ? 00:00:38 ora_p003_PROD
prod 9818 1 0 08:04 ? 00:01:05 ora_p004_PROD
prod 9820 1 0 08:04 ? 00:01:13 ora_p005_PROD
prod 9822 1 0 08:04 ? 00:01:02 ora_p006_PROD
prod 9824 1 0 08:04 ? 00:01:02 ora_p007_PROD
prod 7578 1 0 14:55 ? 00:00:09 ora_j000_PROD
prod 4865 14684 0 15:18 pts/4 00:00:00 /bin/bash -c ps -ef|grep ora_
prod 4869 4865 0 15:18 pts/4 00:00:00 grep ora_
sys@PROD>
Damir,
I didn't forgot those, but Blogger has some restrictions and its hiding pipe sign. so I tried to give colon now.
Anyways Thanks
Sundeep
Post a Comment