1.1 环境:
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 7)
Oracle:9.2.0.4
1.2 现象:
1.2.1 16:18开始半分钟内最大进程数;
当时我正在检查216的硬件运行状况,突然发现进程一个劲的往上窜,20几秒进程数有250个迅速窜到900多个,发现问题后,我马上看是哪个账号引起的,执行了“select username,count(*) from v$session group by username”,发现不断上升的进程是后台进程,上述这个SQL执行了4次,已经堕机,速度太快了!立马startup,但报:
Errors in file /data/oracle9/admin/DEVDB/udump/jdb_ora_30391.trc:
ORA-00600: internal error code, arguments: [ktsiseginfo1], [122], [2], [65], []
, [], [], []
Shutdown immediate,再报上述错误,反复了很多次,查看MOS发现很可能是undo数据文件坏了,于是先设置undo_tablespace=’’,然后立马建新的undo,数据库在16:40分正常打开,整个过程惊心动魄。
1.2.2 Alert警告日志:
在迅速达到最大进程数前数据库非常正常,负载一般,没有任何警告日志:
Fri Jan 13 16:14:02 2012
Created Undo Segment _SYSSMU1$
Undo Segment 1 Onlined
Fri Jan 13 16:14:44 2012
ARC0: Completed archiving log 28 thread 1 sequence 129039
Fri Jan 13 16:18:18 2012
Errors in file /data/oracle9/admin/DEVDB/bdump/jdb_j007_5912.trc:
1.2.3 日志过大截取了部分:
1.2.3.1 jdb_ora_30391.trc:
Dump file /data/oracle9/admin/DEVDB/udump/jdb_ora_30391.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /data/oracle9/product/9.2.0
System name: Linux
Node name: FDB01
Release: 2.6.9-78.ELsmp
Version: #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine: x86_64
Instance name: jdb
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 30391, image: oracle@FDB01 (TNS V1-V3)
*** SESSION ID:(11.35) 2012-01-13 16:19:00.456
Thread checkpoint rba:0x01f80f.00000002.0010 scn:0x002e.5a760f02
On-disk rba:0x01f810.000784a0.0000 scn:0x002e.5a7df0b5
Use incremental checkpoint cache-low RBA
Thread 1 recovery from rba:0x01f80f.000914b3.0000 scn:0x0000.00000000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 460789Kb in 2.78s => 161.51 Mb/sec
Longest record: 16Kb, moves: 98/2306561 (0%)
Change moves: 171/207 (82%), moved: 0Mb
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 3
Average hash chain = 28624/27766 = 1.0
Max compares per lookup = 3
Avg compares per lookup = 3282529/3335157 = 1.0
----------------------------------------------
*** 2012-01-13 16:19:03.247
KCRA: start recovery claims for 28624 data blocks
*** 2012-01-13 16:19:46.011
KCRA: buffers claimed = 28624/28624, eliminated = 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 3
Average hash chain = 28624/27766 = 1.0
Max compares per lookup = 3
Avg compares per lookup = 3110379/3305747 = 0.9
----------------------------------------------
Dumping current redo log in thread 1
DUMP OF REDO FROM FILE '/dev2/oradata/log31.ora'
Opcodes *.*
DBA's: (file # 2, block # 65) thru (file # 2, block # 65)
RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCN's scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
Db ID=1943074356=0x73d0f634, Db Name='DEVDB'
Activation ID=2014002543=0x780b3d6f
Control Seq=3594868=0x36da74, File size=1024000=0xfa000
File Number=31, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000129041, SCN 0x002e5a7e3ed6-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x0001f811 hws: 0x2 eot: 1 dis: 0
reset logs count: 0x27f245f4 scn: 0x0000.00026cb4
Low scn: 0x002e.5a7e3ed6 01/13/2012 16:19:56
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0026.5558ba0d 01/22/2011 09:22:57
Thread closed scn: 0x002e.5a7e3ed6 01/13/2012 16:19:56
Log format vsn: 0x8000000 Disk cksum: 0x7c39 Calc cksum: 0x7c39
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 12Kb in 0.07s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/27 (0%)
Change moves: 12/63 (19%), moved: 0Mb
----------------------------------------------
Dumping next redo log in thread 1
DUMP OF REDO FROM FILE '/dev2/oradata/log30.ora'
Opcodes *.*
DBA's: (file # 2, block # 65) thru (file # 2, block # 65)
RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCN's scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
Db ID=1943074356=0x73d0f634, Db Name='DEVDB'
Activation ID=2014002543=0x780b3d6f
Control Seq=3594867=0x36da73, File size=1024000=0xfa000
File Number=30, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000129040, SCN 0x002e5a7a4e4a-0x002e5a7e3ed6"
thread: 1 nab: 0x784a0 seq: 0x0001f810 hws: 0x4 eot: 0 dis: 0
reset logs count: 0x27f245f4 scn: 0x0000.00026cb4
Low scn: 0x002e.5a7a4e4a 01/13/2012 16:13:34
Next scn: 0x002e.5a7e3ed6 01/13/2012 16:19:56
Enabled scn: 0x0026.5558ba0d 01/22/2011 09:22:57
Thread closed scn: 0x002e.5a7e3ed5 01/13/2012 16:18:24
Log format vsn: 0x8000000 Disk cksum: 0x3ef0 Calc cksum: 0x3ef0
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
1.3 网上查询:
1.3.1 网络参考http://www.itpub.net/thread-902983-1-1.html
1.3.2 解决方案
(1) startup mount;
(2) alter system set undo_tablespace=’’ scope=both;
(3) alter databae open;
(4) create undo tablespace undo03 datafile ‘/u02/oradata/undo03.dbf’ size 16m auto extend next 16m;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751051/viewspace-731721/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/751051/viewspace-731721/