A-A+
UNDO块损坏恢复 ORA-00600 KSLFDNG2
该篇文章模拟用户活动事物上的UNDO块损坏恢复
数据库版本
SQL> select * from gv$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1 PL/SQL Release 11.2.0.3.0 - Production
1 CORE 11.2.0.3.0 Production
1 TNS for Linux: Version 11.2.0.3.0 - Production
1 NLSRTL Version 11.2.0.3.0 - Production
会话1
SQL> sho user USER is "YALLONKING" SQL> create table yallonking ( 2 id number, 3 name varchar2(1000), 4 constraint yallonking_pk primary key (id) 5 ) 6 ; Table created. SQL> insert into yallonking values(1,'yallonking'); 1 row created. SQL> commit; Commit complete.
会话2（该会话不提交）
SQL> sho user USER is "YALLONKING" SQL> update yallonking set name='ORAKING'; 1 row updated.
会话3
查看与活动会话2相关的UNDO信息
SQL> sho user
USER is "SYS"
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS,START_SCNB from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_SCNB
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
5 9 713 3 14581 158 30 ACTIVE 810587
或者
SQL> select INDX,KTUXESTA,KTUXECFL,KTUXEPSQN,KTUXESCNW,KTUXESCNB,KTUXERDBF,KTUXERDBB,KTUXESIZ from x$ktuxe where KTUXESTA='ACTIVE';
INDX KTUXESTA KTUXECFL KTUXEPSQN KTUXESCNW KTUXESCNB KTUXERDBF KTUXERDBB KTUXESIZ
---------- ---------------- ------------------------ ---------- ---------- ---------- ---------- ---------- ----------
243 ACTIVE NONE 0 0 810587 3 14581 1
转储对应UNDO块
SQL> oradebug setmypid Statement processed. SQL> alter system dump datafile 3 block 14581; System altered. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_12457.trc
trace文件部分内容如下：
UNDO BLK: xid: 0x0005.009.000002c9 seq: 0x9e cnt: 0x1e irb: 0x1e icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1fac 0x02 0x1f08 0x03 0x1ec0 0x04 0x1e90 0x05 0x1e50 0x06 0x1e2c 0x07 0x1c9c 0x08 0x1b70 0x09 0x1ab0 0x0a 0x1a00 0x0b 0x193c 0x0c 0x18d0 0x0d 0x1874 0x0e 0x17c0 0x0f 0x1764 0x10 0x1708 0x11 0x1680 0x12 0x1620 0x13 0x1554 0x14 0x14a4 0x15 0x1428 0x16 0x1378 0x17 0x12cc 0x18 0x121c 0x19 0x1148 0x1a 0x10c0 0x1b 0x1054 0x1c 0x0f80 0x1d 0x0f1c 0x1e 0x0e8c *----------------------------- * Rec #0x1e slt: 0x09 objn: 71482(0x0001173a) objd: 71482 tblspc: 5(0x00000005) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c038f5.009e.1c ctl max scn: 0x0000.000c5504 prv tx scn: 0x0000.000c55ef txn start scn: scn: 0x0000.000c5dec logon user: 80 prev brb: 12597488 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0140004f hdba: 0x0140004a itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 9 ncol: 2 nnew: 1 size: 3 col 1: [10] 79 61 6c 6c 6f 6e 6b 69 6e 67
确认该UNDO块
SQL> set line 50
SQL> select utl_raw.cast_to_varchar2('79616c6c6f6e6b696e67') from dual;
UTL_RAW.CAST_TO_VARCHAR2('79616C6C6F6E6B696E67')
--------------------------------------------------
yallonking
转储UNDO段头
SQL> oradebug setmypid Statement processed. SQL> alter system dump undo header '_SYSSMU5_1228822706$'; System altered. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_16827.trc SQL> oradebug close_trace Statement processed.
trace文件中也可看见有一个活动会话：
TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x02cc 0x0001 0x0000.000c59ea 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387333397 0x01 9 0x00 0x02cf 0x0006 0x0000.000c5a25 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387333533 0x02 9 0x00 0x02ce 0x0010 0x0000.000c578b 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332033 0x03 9 0x00 0x02cf 0xffff 0x0000.000c5df9 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335346 0x04 9 0x00 0x02ce 0x0007 0x0000.000c5890 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x05 9 0x00 0x02bf 0x0011 0x0000.000c5d3b 0x00c038f7 0x0000.000.00000000 0x00000003 0x00000000 1387335318 0x06 9 0x00 0x02ce 0x001b 0x0000.000c5a4e 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387333623 0x07 9 0x00 0x02cf 0x0015 0x0000.000c5899 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x08 9 0x00 0x02ce 0x000f 0x0000.000c5833 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332415 0x09 10 0x90 0x02c9 0x0002 0x0000.000c5e5b 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 0 0x0a 9 0x00 0x02ce 0x000d 0x0000.000c5c99 0x00c038ed 0x0000.000.00000000 0x00000001 0x00000000 1387335128 0x0b 9 0x00 0x02cd 0x0002 0x0000.000c5763 0x00c038f0 0x0000.000.00000000 0x00000001 0x00000000 1387332032 0x0c 9 0x00 0x02ce 0x0016 0x0000.000c58ab 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x0d 9 0x00 0x02ce 0x001d 0x0000.000c5ca2 0x00c038f5 0x0000.000.00000000 0x00000003 0x00000000 1387335128 0x0e 9 0x00 0x02cf 0x001c 0x0000.000c5d98 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334 0x0f 9 0x00 0x02cd 0x0004 0x0000.000c5887 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x10 9 0x00 0x02ce 0x0008 0x0000.000c579f 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332033 0x11 9 0x00 0x02cb 0x0021 0x0000.000c5d65 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334 0x12 9 0x00 0x02cf 0x000e 0x0000.000c5d8d 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334 0x13 9 0x00 0x02ce 0x000a 0x0000.000c5c90 0x00c038ed 0x0000.000.00000000 0x00000001 0x00000000 1387335128 0x14 9 0x00 0x02cd 0x0012 0x0000.000c5d83 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334 0x15 9 0x00 0x02cb 0x000c 0x0000.000c58a2 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x16 9 0x00 0x02cd 0x001e 0x0000.000c58b4 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x17 9 0x00 0x02ce 0x0013 0x0000.000c5bf0 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387334732 0x18 9 0x00 0x02cb 0x0000 0x0000.000c58c6 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x19 9 0x00 0x02ce 0x0014 0x0000.000c5d79 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334 0x1a 9 0x00 0x02ce 0x0003 0x0000.000c5ddc 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335345 0x1b 9 0x00 0x02cc 0x0017 0x0000.000c5be6 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387334732 0x1c 9 0x00 0x02cc 0x001a 0x0000.000c5dcd 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1387335345 0x1d 9 0x00 0x02cd 0x0005 0x0000.000c5d31 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335318 0x1e 9 0x00 0x02cb 0x0018 0x0000.000c58bd 0x00c038f3 0x0000.000.00000000 0x00000001 0x00000000 1387332632 0x1f 9 0x00 0x02cc 0x0020 0x0000.000c56ce 0x00c038f0 0x0000.000.00000000 0x00000001 0x00000000 1387331730 0x20 9 0x00 0x02cd 0x000b 0x0000.000c56f6 0x00c038f0 0x0000.000.00000000 0x00000001 0x00000000 1387331815 0x21 9 0x00 0x02cd 0x0019 0x0000.000c5d6f 0x00c038f5 0x0000.000.00000000 0x00000001 0x00000000 1387335334
注：9-inactive 10-active
下边使用bbed将会话2中未提交事务产生的UNDO人为损坏
BBED> set dba 3,14581
DBA 0x00c038f5 (12597493 3,14581)
BBED> find /c yallonking
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 14581 Offsets: 3876 to 4387 Dba:0x00c038f5
------------------------------------------------------------------------
79616c6c 6f6e6b69 6e671835 0c001800 20001400 03000600 25180000 25180000
00000000 00000000 0a16031c 0000cf02 040d0000 00000000 04000800 b9020000
5603c000 c1000500 00800000 f75d0c00 05020100 b8324000 b9324000 00000000
00000709 02c15430 004032b1 00000200 12004c00 20003100 02000200 0e000200
020006c5 24180000 24180000 00000000 00000000 0b010300 080c0100 00000000
f538c000 9e001a00 f7540c00 00000000 04550c00 00000000 00000000 f85d0c00
0000a50e f038c000 00000000 00000000 040d0000 00000000 02000b00 d9020000
9300c000 d5001700 00800000 f55d0c00 b1324000 b0324000 ff120201 01000000
2c000597 00000000 f02a3c97 00000000 01000000 01000000 1e000000 00600000
a84b2a63 c1542100 c10480ea 53595324 4241434b 47524f55 4e44c102 c1020e1d
c1026302 0a001800 20001400 120006c5 fc190000 fc190000 01000000 00000000
0a161a1a 0000ce02 040d1400 4c002000 06001600 62030000 fc0ac000 ae003100
00800000 d99c0b00 0302018d ca198000 cb198000 00000000 00000000 06c50214
352c5c02 c1020778 710c120b 382f06c5 08004c00 20001400 fb190000 fb190000
01000000 00000000 0b011a00 080c0100 00000000 f838c000 9e000100 eb540c00
00000000 f7540c00 00000000 00000000 db5d0c00 00000000 f038c000 00000000
<32 bytes per line>
BBED> dump /v dba 3,14581 offset 3876 count 10
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 14581 Offsets: 3876 to 3885 Dba:0x00c038f5
-------------------------------------------------------
79616c6c 6f6e6b69 6e67 l yallonking
<16 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x02
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00c038f5
ub4 bas_kcbh @8 0x000c5e5e
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xdb3b
ub2 spare3_kcbh @18 0x0000
BBED> set mode edit
MODE Edit
BBED> modify /x ff dba 3,14581 offset 14
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 14581 Offsets: 14 to 23 Dba:0x00c038f5
------------------------------------------------------------------------
ff043bdb 00000500 0900
<32 bytes per line>
BBED> sum dba 3,14581 apply;
Check value for File 3, Block 14581:
current = 0xdbc5, required = 0xdbc5
验证坏块
BBED> VERIFY dba 3,14581 DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf BLOCK = 14581 Block 14581 is corrupt Corrupt block relative dba: 0x00c038f5 (file 0, block 14581) Fractured block found during verification Data in bad block: type: 2 format: 2 rdba: 0x00c038f5 last change scn: 0x0000.000c5e5e seq: 0xff flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x5e5e0201 check value in block header: 0xdbc5 computed block checksum: 0x0 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 1 Total Blocks Influx : 2 Message 531 not found; product=RDBMS; facility=BBED [oracle@rhl6 ~]$ dbv file='/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf' DBVERIFY: Release 11.2.0.3.0 - Production on Wed Dec 18 11:46:53 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf Page 14581 is influx - most likely media corrupt Corrupt block relative dba: 0x00c038f5 (file 3, block 14581) Fractured block found during dbv: Data in bad block: type: 2 format: 2 rdba: 0x00c038f5 last change scn: 0x0000.000c5e5e seq: 0xff flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x5e5e0201 check value in block header: 0xdbc5 computed block checksum: 0x0 DBVERIFY - Verification complete Total Pages Examined : 39680 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 39678 Total Pages Processed (Seg) : 10 Total Pages Failing (Seg) : 0 Total Pages Empty : 1 Total Pages Marked Corrupt : 1 Total Pages Influx : 1 Total Pages Encrypted : 0 Highest block SCN : 812058 (0.812058)
查看报错信息
SQL> select * from yallonking;
select * from yallonking
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 14581)
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf'
ALERT日志中报错：(截取部分)
ORACLE Instance ora11gr2 (pid = 13) - Error 600 encountered while recovering transaction (5, 9). Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_smon_14261.trc: ORA-00600: internal error code, arguments: [KSLFDNG2], [0x097892340], [0], [0x0968BBCD0], [0x000000000], [], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_mmon_14265.trc (incident=8554): ORA-01578: ORACLE data block corrupted (file # 3, block # 14581) ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf' Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_mmon_14265.trc (incident=9635): ORA-01578: ORACLE data block corrupted (file # 3, block # 14581) ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf' minact-scn: got error during useg scan e:1578 usn:5 minact-scn: useg scan erroring out with error e:1578 Instance terminated by PMON, pid = 14236
随后数据库down掉
修复
尝试_offline_rollback_segments和_corrupted_rollback_segments来跳过坏块，均失败
下边用bbed修改回原来状态
BBED> set dba 1,512
DBA 0x00400200 (4194816 1,512)
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x10
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00400200
ub4 bas_kcbh @8 0x000add35
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xd217
ub2 spare3_kcbh @18 0x0000
BBED> set dba 3,14581
DBA 0x00c038f5 (12597493 3,14581)
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x02
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00c038f5
ub4 bas_kcbh @8 0x000c5e5e
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0xff
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xdbc5
ub2 spare3_kcbh @18 0x0000
BBED> set mode edit
MODE Edit
BBED> modify /x 01 dba 3,14581 offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 14581 Offsets: 14 to 525 Dba:0x00c038f5
------------------------------------------------------------------------
0104c5db 00000500 0900c902 00009e00 1e1e0000 e81fac1f 081fc01e 901e501e
2c1e9c1c 701bb01a 001a3c19 d0187418 c0176417 08178016 20165415 a4142814
7813cc12 1c124811 c0105410 800f1c0f 8c0e0500 1e00c402 0000f238 c0009b00
03000100 00000000 00000500 0100c802 0000f438 c0009b00 06008c00 00000000
00000e00 000077ea 80000224 80000001 85050100 0000f000 04020602 02000000
00000000 000076ea 80000600 0000601f 0000411f 551c741c 931cb21c d11cf11c
111d311d 511d701d 8f1dae1d cd1dec1d 0b1e2a1e 491e681e 871ea61e c51ee41e
031f221f 25134413 63138313 a313c313 e3130314 23144314 62148114 a014bf14
df14ff14 1f153e15 5d157c15 9b15ba15 d915f915 19163916 59167916 9816b716
d616f516 14173317 52177217 9117b017 cf17ee17 0d182d18 4d186d18 8d18ac18
cb18ea18 09192819 47196619 8519a419 c319e319 031a231a 421a611a 801a9f1a
be1add1a fc1a1c1b 3c1b5d1b 7c1b9b1b ba1bd91b f81b171c 361c0602 24024202
60027f02 9d02bb02 d902f802 17033603 55037403 9303b203 d103f003 0f042f04
4f046f04 8e04ad04 cc04eb04 0a052905 48056705 8605a505 c405e305 02062106
40065f06 7e069d06 bc06db06 fa061a07 3a075907 79079807 b707d607 f5071408
33085208 72089208 b108d008 ef080e09 2e094d09 6d098d09 ad09cd09 ec090b0a
<32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x02
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00c038f5
ub4 bas_kcbh @8 0x000c5e5e
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xdbc5
ub2 spare3_kcbh @18 0x0000
BBED> sum dba 3,14581 apply;
Check value for File 3, Block 14581:
current = 0xdb3b, required = 0xdb3b
BBED> VERIFY dba 3,14581
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
BLOCK = 14581
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 549456280 bytes
Database Buffers 381681664 bytes
Redo Buffers 6123520 bytes
Database mounted.
Database opened.
SQL> col NAME for a30
SQL> select * from yallonking.yallonking;
ID NAME
---------- ------------------------------
1 yallonking
