今天巡检oracle日志发现报错
Tue Apr 28 01:04:07 CDT 2015
Thread 1 advanced to log sequence 8191 (LGWR switch)
Current log# 1 seq# 8191 mem# 0: +DATA/sglk/onlinelog/group_1.268.765387663
Current log# 1 seq# 8191 mem# 1: +DATA/sglk/onlinelog/group_1.269.765387673
Wed Apr 29 01:11:54 CDT 2015
Thread 1 advanced to log sequence 8192 (LGWR switch)
Current log# 2 seq# 8192 mem# 0: +DATA/sglk/onlinelog/group_2.270.765387683
Current log# 2 seq# 8192 mem# 1: +DATA/sglk/onlinelog/group_2.256.765387693
Wed Apr 29 22:56:43 CDT 2015
Process J007 died, see its trace file
Wed Apr 29 22:56:43 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 22:56:43 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:21:40 CDT 2015
Process J003 died, see its trace file
Wed Apr 29 23:21:40 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:21:40 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:39:51 CDT 2015
Process J002 died, see its trace file
Wed Apr 29 23:39:51 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:39:51 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:41:53 CDT 2015
Process J014 died, see its trace file
Wed Apr 29 23:41:53 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:41:53 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:42:44 CDT 2015
Process J017 died, see its trace file
Wed Apr 29 23:42:44 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:42:44 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:47:45 CDT 2015
Process J013 died, see its trace file
Wed Apr 29 23:47:45 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:47:45 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:55:46 CDT 2015
Process J003 died, see its trace file
Wed Apr 29 23:55:46 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Wed Apr 29 23:55:46 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Wed Apr 29 23:56:48 CDT 2015
Thread 1 advanced to log sequence 8193 (LGWR switch)
Current log# 1 seq# 8193 mem# 0: +DATA/sglk/onlinelog/group_1.268.765387663
Current log# 1 seq# 8193 mem# 1: +DATA/sglk/onlinelog/group_1.269.765387673
Thu Apr 30 00:01:38 CDT 2015
Process J003 died, see its trace file
Thu Apr 30 00:01:38 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:01:38 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Process J003 died, see its trace file
Thu Apr 30 00:01:40 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:01:40 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:02:47 CDT 2015
Process J004 died, see its trace file
Thu Apr 30 00:02:47 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:02:47 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:03:28 CDT 2015
Process J004 died, see its trace file
Thu Apr 30 00:03:28 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:03:28 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:06:44 CDT 2015
Process J009 died, see its trace file
Thu Apr 30 00:06:44 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:06:44 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:35:41 CDT 2015
Process J006 died, see its trace file
Thu Apr 30 00:35:41 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:35:41 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:35:52 CDT 2015
Process J006 died, see its trace file
Thu Apr 30 00:35:52 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:35:52 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 00:38:33 CDT 2015
Process J002 died, see its trace file
Thu Apr 30 00:38:33 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 00:38:33 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 01:26:21 CDT 2015
Process m000 died, see its trace file
Thu Apr 30 01:26:21 CDT 2015
ksvcreate: Process(m000) creation failed
Thu Apr 30 02:52:49 CDT 2015
WARNING: db_recovery_file_dest is same as db_create_file_dest
Thu Apr 30 08:54:54 CDT 2015
Process J004 died, see its trace file
Thu Apr 30 08:54:54 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 08:54:54 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
Thu Apr 30 08:55:20 CDT 2015
Process J006 died, see its trace file
Thu Apr 30 08:55:20 CDT 2015
kkjcre1p: unable to spawn jobq slave process
Thu Apr 30 08:55:20 CDT 2015
Errors in file /oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc:
打开日志记录
/oracle/admin/sglk/bdump/sglk1_cjq0_11141232.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/db
System name:AIX
Node name:rac1
Release:1
Version:6
Machine:00F6D1F94C00
Instance name: sglk1
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 11141232, image: oracle@rac1 (CJQ0)
*** 2015-03-26 13:53:24.255
*** SERVICE NAME:(SYS$BACKGROUND) 2015-03-26 13:53:24.248
*** SESSION ID:(1075.1) 2015-03-26 13:53:24.248
SKGXPIWAIT: keepalive_reset elapsed 5584319 ts 472862190 last ping 467277871 check 600000
PING HISTORY for CONTEXT Time Stampe 0x1c2f4dee
MSGs Time Stamp
--- ----------
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
0 0x1bda182f
SKGXP_KEEPALIVE_RESET: alarm unblocked already
SKGXP_KEEPALIVE_RESET: restting shared signal for keep alive messages
SKGXP_KEEPALIVE_RESET: re-initing shared signal for keep alive messages
SKGXP_KEEPALIVE_RESET: setting alarm for keep alive messages
*** 2015-04-29 22:56:43.303
Process J007 is dead (pid=6226264, state=3):
*** 2015-04-29 23:21:40.272
Process J003 is dead (pid=66060304, state=3):
*** 2015-04-29 23:39:51.804
Process J002 is dead (pid=9306374, state=3):
*** 2015-04-29 23:41:53.008
Process J014 is dead (pid=262560, state=3):
*** 2015-04-29 23:42:44.085
Process J017 is dead (pid=59310118, state=3):
*** 2015-04-29 23:47:45.488
Process J013 is dead (pid=57934032, state=3):
*** 2015-04-29 23:55:46.843
网上百度后，发现可能会话和进程可能达到临界值
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 4
gcs_server_processes integer 8
job_queue_processes integer 20
log_archive_max_processes integer 2
processes integer 1000
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 1105
shared_server_sessions integer
查看process,session
select count(1) from v$session;
数据值为998
select count(1) from v$process;
数据值为967
接近临界值
那么首先查看会话数
select t.username,count(1) from v$session t where t.username is not null group by t.username
XXX 867
重启XXX的应用释放会话数和进程数
只能说这段时间就会出现问题，
1、等下次重启数据库时，需要重新设置会话数和进程数
ALTER SYSTEM SET PROCESS=XXX SCOPE=SPFILE;
SESSIONS=1.1*RPOCESS +5
