帮忙分析原因：数据库宕了（急）

显示全部楼层 · 2009-7-22 09:30:00

alart.log内容下：
Thread 1 advanced to log sequence 8390
Current log# 1 seq# 8390 mem# 0: /u01/app/oracle/oradata/patrol/redo01.log
Tue Jul 21 11:29:37 2009
Thread 1 advanced to log sequence 8391
Current log# 2 seq# 8391 mem# 0: /u01/app/oracle/oradata/patrol/redo02.log
Tue Jul 21 11:54:52 2009
Thread 1 advanced to log sequence 8392
Current log# 3 seq# 8392 mem# 0: /u01/app/oracle/oradata/patrol/redo03.log
Tue Jul 21 12:18:25 2009
Thread 1 advanced to log sequence 8393
Current log# 1 seq# 8393 mem# 0: /u01/app/oracle/oradata/patrol/redo01.log
Tue Jul 21 12:48:21 2009
Thread 1 advanced to log sequence 8394
Current log# 2 seq# 8394 mem# 0: /u01/app/oracle/oradata/patrol/redo02.log
Tue Jul 21 13:14:44 2009
Thread 1 advanced to log sequence 8395
Current log# 3 seq# 8395 mem# 0: /u01/app/oracle/oradata/patrol/redo03.log
Tue Jul 21 13:44:49 2009
Thread 1 advanced to log sequence 8396
Current log# 1 seq# 8396 mem# 0: /u01/app/oracle/oradata/patrol/redo01.log
Tue Jul 21 13:57:35 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_ora_2403.trc:
ORA-00610: Internal error code
Tue Jul 21 13:58:01 2009
Process J005 died, see its trace file
Tue Jul 21 13:58:08 2009
kkjcre1p: unable to spawn jobq slave process
Tue Jul 21 13:58:08 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc:
Tue Jul 21 14:03:14 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Jul 21 14:17:46 2009
Process J000 died, see its trace file
Tue Jul 21 14:18:18 2009
kkjcre1p: unable to spawn jobq slave process
Tue Jul 21 14:18:42 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc:
Tue Jul 21 14:31:27 2009
ksvcreate: Process(q001) creation failed
Tue Jul 21 14:46:52 2009
kkjcre1p: unable to spawn jobq slave process
Tue Jul 21 14:46:28 2009
MMNL absent for 1243 secs; Foregrounds taking over
Tue Jul 21 14:50:20 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc:
Tue Jul 21 14:53:59 2009
Process q001 died, see its trace file
Tue Jul 21 14:54:09 2009
ksvcreate: Process(q001) creation failed
Tue Jul 21 15:06:19 2009
MMNL absent for 1290 secs; Foregrounds taking over
Tue Jul 21 15:06:51 2009
Process J000 died, see its trace file
Tue Jul 21 15:06:43 2009
MMNL absent for 1290 secs; Foregrounds taking over
Tue Jul 21 15:09:12 2009
kkjcre1p: unable to spawn jobq slave process
Tue Jul 21 15:09:20 2009
MMNL absent for 1300 secs; Foregrounds taking over
Tue Jul 21 15:14:10 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc:
Tue Jul 21 15:14:29 2009
Process startup failed, error stack:
Tue Jul 21 15:16:24 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_psp0_2703.trc:
ORA-27300: Message 27300 not found; No message file for product=RDBMS, facility=ORA; arguments: [fork] [11]
ORA-27301: Message 27301 not found; No message file for product=RDBMS, facility=ORA; arguments: [Resource temporarily unavailable]
ORA-27302: Message 27302 not found; No message file for product=RDBMS, facility=ORA; arguments: [skgpspawn5]
Tue Jul 21 15:16:38 2009
ksvcreate: Process(q001) creation failed
Tue Jul 21 15:16:43 2009
MMNL absent for 1768 secs; Foregrounds taking over
Tue Jul 21 15:26:48 2009
kkjcre1p: unable to spawn jobq slave process
Tue Jul 21 15:26:57 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc:
Tue Jul 21 15:45:12 2009
MMNL absent for 1778 secs; Foregrounds taking over
Tue Jul 21 15:51:58 2009
ksvcreate: Process(q001) creation failed
Tue Jul 21 15:53:42 2009
MMNL absent for 1475 secs; Foregrounds taking over
Tue Jul 21 15:54:38 2009
MMNL absent for 1577 secs; Foregrounds taking over
Tue Jul 21 16:01:21 2009
MMNL absent for 1738 secs; Foregrounds taking over
MMNL absent for 1738 secs; Foregrounds taking over
Tue Jul 21 16:09:13 2009
MMNL absent for 1449 secs; Foregrounds taking over
Tue Jul 21 16:17:08 2009
MMNL absent for 1549 secs; Foregrounds taking over
Tue Jul 21 16:16:54 2009
MMNL absent for 1549 secs; Foregrounds taking over
Tue Jul 21 16:25:54 2009
MMNL absent for 2500 secs; Foregrounds taking over
Tue Jul 21 16:30:47 2009
MMNL absent for 2633 secs; Foregrounds taking over
Tue Jul 21 16:35:21 2009
Process q001 died, see its trace file
Tue Jul 21 16:42:22 2009
ksvcreate: Process(q001) creation failed
Tue Jul 21 16:42:26 2009
MMNL absent for 2765 secs; Foregrounds taking over
Tue Jul 21 16:42:36 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_pmon_2701.trc:
ORA-00476: Message 476 not found; No message file for product=RDBMS, facility=ORA
Tue Jul 21 16:44:38 2009
PMON: terminating instance due to error 476
Tue Jul 21 16:44:39 2009
Errors in file /u01/app/oracle/admin/patrol/bdump/patrol_psp0_2703.trc:
ORA-00476: Message 476 not found; No message file for product=RDBMS, facility=ORA
Tue Jul 21 17:07:33 2009
Instance terminated by PMON, pid = 2701

千问 · 2009-7-22 09:30:00

/u01/app/oracle/admin/patrol/bdump/patrol_ora_2403.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1
System name:
Linux
Node name:
FJWGXSEV-01
Release:
2.6.9-8.11smp
Version:
#1 SMP Wed Aug 2 18:08:57 CST 2006
Machine:
x86_64
Instance name: patrol
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 2403, image: oracle@FJWGXSEV-01
*** 2009-07-21 13:57:26.336
error 610 in job queue process
ORA-00610: Internal error code
Died during process startup with error 447 (seq=2117)
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process

千问 · 2009-7-22 09:30:00

/u01/app/oracle/admin/patrol/bdump/patrol_cjq0_2717.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1
System name:
Linux
Node name:
FJWGXSEV-01
Release:
2.6.9-8.11smp
Version:
#1 SMP Wed Aug 2 18:08:57 CST 2006
Machine:
x86_64
Instance name: patrol
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 2717, image: oracle@FJWGXSEV-01 (CJQ0)
*** 2009-07-21 13:55:58.665
*** SERVICE NAME

SYS$BACKGROUND) 2009-07-21 13:55:55.275
*** SESSION ID

162.1) 2009-07-21 13:55:55.275
Waited for process J005 to initialize for 60 seconds
*** 2009-07-21 13:55:58.665
Dumping diagnostic information for J005:
OS pid = 2403
loadavg : 29.97 11.86 5.57
memory info: free memory = 0.00M
swap info: free = 0.00M alloc = 0.00M total = 0.00M
F S UIDPIDPPIDC PRINI ADDR SZ WCHANSTIME TTY
TIME CMD
0 D oracle2403 1078 0 - 33559 lock_p 13:54 ?00:00:01 ora_j005_patrol
skgpgpstack: read() for cmd /usr/bin/gdb --batch -quiet -x /tmp/stackRVphLh /proc/2403/exe 2403 &1 timed out after 60 seconds
*** 2009-07-21 13:58:01.205
*** 2009-07-21 13:58:01.338
Process J005 is dead (pid=2403, state=5):
*** 2009-07-21 14:10:46.732
Waited for process J000 to initialize for 60 seconds
*** 2009-07-21 14:10:56.364
Dumping diagnostic information for J000:
OS pid = 24108
loadavg : 76.78 59.02 37.54
memory info: free memory = 0.00M
swap info: free = 0.00M alloc = 0.00M total = 0.00M
skgpgpstack: read() for cmd /bin/ps -elf | /bin/egrep 'PID | 24108' | /bin/grep -v grep timed out after 60 seconds
skgpgpstack: read() for cmd /usr/bin/gdb --batch -quiet -x /tmp/stack6ujhF1 /proc/24108/exe 24108 &1 timed out after 60 seconds
*** 2009-07-21 14:13:29.990
*** 2009-07-21 14:13:45.364
Waited for process J000 to initialize for 75 seconds
*** 2009-07-21 14:13:45.726
Dumping diagnostic information for J000:
OS pid = 24108
loadavg : 144.33 91.26 52.96
memory info: free memory = 0.00M
swap info: free = 0.00M alloc = 0.00M total = 0.00M
skgpgpstack: read() for cmd /bin/ps -elf | /bin/egrep 'PID | 24108' | /bin/grep -v grep timed out after 60 seconds
skgpgpstack: read() for cmd /usr/bin/gdb --batch -quiet -x /tmp/stackjhCzZf /proc/24108/exe 24108 &1 timed out after 60 seconds
*** 2009-07-21 14:17:31.888
*** 2009-07-21 14:17:32.242
Process J000 is dead (pid=24108, state=5):
*** 2009-07-21 14:18:23.123
*** 2009-07-21 14:32:00.812
Waited for process J000 to be spawned for 61 seconds
*** 2009-07-21 14:32:33.831
Waited for process J000 to be spawned for 76 seconds
*** 2009-07-21 14:33:59.620
Waited for process J000 to be spawned for 87 seconds
*** 2009-07-21 14:37:24.667
Waited for process J000 to be spawned for 97 seconds
*** 2009-07-21 14:37:43.178
Waited for process J000 to be spawned for 109 seconds
*** 2009-07-21 14:38:13.168
Waited for process J000 to be spawned for 120 seconds
*** 2009-07-21 14:48:46.833
*** 2009-07-21 14:47:33.766
*** 2009-07-21 14:55:15.156
Waited for process J000 to be spawned for 61 seconds
*** 2009-07-21 14:56:10.044
Waited for process J000 to be spawned for 73 seconds
*** 2009-07-21 14:59:27.428
*** 2009-07-21 14:56:26.675
Waited for process J000 to be spawned for 84 seconds
*** 2009-07-21 15:05:58.984
Waited for process J000 to be spawned for 94 seconds
*** 2009-07-21 15:06:37.305
Process J000 is dead (pid=24960, state=3):
*** 2009-07-21 15:14:10.305
*** 2009-07-21 15:17:50.175
Waited for process J000 to be spawned for 60 seconds
*** 2009-07-21 15:20:52.399
Waited for process J000 to be spawned for 171 seconds
*** 2009-07-21 15:26:56.563
*** 2009-07-21 16:44:19.133
ktsmgtur(): TUR was not tuned for 2873 secs
ktsmg_advance_slot(): MMNL advances slot after 1202 seconds

千问 · 2009-7-22 09:30:00

感觉似乎是启动任务进程时没有可供分配的内存导致系统等待挂起。
把耗资源的进程杀掉看看是否可以。

千问 · 2009-7-22 09:30:00

帮忙分析一下啊，

谢谢大家了！！

千问 · 2009-7-22 09:30:00

ORA-00610: Internal error code
Died during process startup with error 447 (seq=2117)
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
从你给出的日志分析，你的数据库宕机很有可能是系统的错误导致ORACLE的一些后台进程失败而宕机的。让系统管理员检查一下系统资源的使用是否正常。
memory info: free memory = 0.00M
swap info: free = 0.00M alloc = 0.00M total = 0.00M

千问 · 2009-7-22 09:30:00

检查是不是有job执行的很频繁。
pga 的使用情况。

千问 · 2009-7-22 09:30:00

应该是内存不够的原因吧！现在数据库都挂了，操作系统给oracle自动杀掉了。
据说我们的oracle很吃内存，linux系统。
oracle占内存应该是按sga+pga算的吗？sga不变的话，如果出现oracle占内存越来越多的现象，应该是pga占内存越来越多，是不？
我有什么方法能得到在宕机之前的oracle进程的一些信息呢？宕机之后，pl/sql也登不了了，也看不到宕机时有什么进程占内存比较大。

千问 · 2009-7-22 09:30:00

数据库配置的问题比较大，把parameter贴过来,内存及swap配置贴过来。

千问 · 2009-7-22 09:30:00

show us the kernel set & db sga/pga set