【Oracle数据恢复】通过BBED修复ORA-600[4193]和ORA-600[4194]的例子

ORA-600[4193]的成因与ORA-600[4194]类似, 是ORACLE发现redo重做记录与rollback/undo 回滚/撤销数据不匹配。 当进程在校验undo block中的undo block sequence number以及对应的redo block sequence number时发现不一致,则会报该错误。称为 ORA-600 [4193] “seq# mismatch while adding undo record”。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

ORA-600[4913]错误的2个argument的含义, 该ORA-600[4913]属于内核undo事务模块

[a] Undo record seq number

[b] Redo record seq number

 

ORA-600[4913]相关的bug如下:

14034244 11.2.0.3.BP09,  12.1.0.0 Lost write type corruption using ASM in 11.2.0.3

8240762 10.2.0.5,11.1.0.7.10,11.2.0.1 Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] /SMON may spin to recover transaction

如果4193/4194 相关的undo/rollback block在system rollback segment,则不可以使用隐藏参数来绕过该问题_CORRUPTED_ROLLBACK_SEGMENTS隐藏参数

而需要通过手动BBED 来修复,下面为一个具体的例子:

 

 

下面是system 回滚段段头 位置为file 1 block# 9的部分转储信息
  TRN CTL:: seq: 0x003a chd: 0x0017 ctl: 0x0052 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400197.003a.02 scn: 0x0000.004fbbf0
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400197.003a.02 ext: 0x4  spc: 0x1dd2
    uba: 0x00000000.0037.05 ext: 0x1  spc: 0x1d6c
    uba: 0x00000000.0035.37 ext: 0x5  spc: 0x538
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0

1. 首先我们要给构造出BBED来使用:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk `pwd`/bbed
mv bbed $ORACLE_HOME/bin

2. 需要构建system表空间所在的数据文件的信息存放在file.lis中:

示例的file.lis 

1 /oradata/s102/system01.dbf 524288000

3. 创建bbed的参数文件bbed.par

bbed.par 内容为:

MODE=EDIT
LISTFILE=
BLOCKSIZE=<db_block_size>

在本例中bbed.par的内容

MODE=EDIT
LISTFILE=file.lis
BLOCKSIZE=8192

4. 执行bbed,其密码为blockedit 

$ bbed parfile=bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 27 10:06:25 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

5. 将当前块设置为system rollback segment的header block 

BBED> set block 9
        BLOCK#          9

6. Run map to see the C structures for the block and the DBA:

BBED> map
 File: /oradata/s102/system01.dbf (1)
 Block: 9                                     Dba:0x00400009
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0

 struct ktech, 72 bytes                     @20

 struct ktemh, 16 bytes                     @92

 struct ktetb[6], 48 bytes                  @108

 struct ktuxc, 104 bytes                    @4148

 struct ktuxe[255], 10200 bytes             @4252

 ub4 tailchk                                @8188

Note that dba=0x00400009 is file 1 block 9, so we are positioned in the correct block.

7. Print the structure ktuxc:

BBED> print ktuxc
struct ktuxc, 104 bytes                     @4148
   struct ktuxcscn, 8 bytes                 @4148
      ub4 kscnbas                           @4148     0x004fbbf1
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156
      ub4 kubadba                           @4156     0x00400197
      ub2 kubaseq                           @4160     0x003a
      ub1 kubarec                           @4162     0x03
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x003a
   sb2 ktuxcnfb                             @4168     1
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     6
   sb2 ktuxcctl                             @4178     23
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192
      struct ktufbuba, 8 bytes              @4192
         ub4 kubadba                        @4192     0x00400197
         ub2 kubaseq                        @4196     0x003a
         ub1 kubarec                        @4198     0x0c
      sb2 ktufbext                          @4200     4
      sb2 ktufbspc                          @4202     5630

8. Modify ktuxc.ktuxcnfb to 0x0000

BBED> set offset ktuxc.ktuxcnfb
        OFFSET          4168

BBED> print
ktuxc.ktuxcnfb
--------------
sb2 ktuxcnfb                                @4168     1

BBED> modify 0x0000
 File: /oradata/s102/system01.dbf (1)
 Block: 9                Offsets: 4168 to 4679           Dba:0x00400009
------------------------------------------------------------------------
 00000000 00000000 06001700 02800100 68000000 feffff7f 97014000 3a000c00
 0400fe15 00000000 37000500 01006c1d 00000000 35003700 05003805 00000000
 00000000 00000000 00000000 00000000 00000000 30000000 93014000 191f5300
 00000000 09005f00 00000000 00000000 00000000 01000000 00000000 31000000
 96014000 a03e5b00 00000000 09005c00 00000000 00000000 00000000 01000000
 00000000 31000000 96014000 9e3e5b00 00000000 09000e00 00000000 00000000
 00000000 01000000 00000000 30000000 93014000 f4bb4f00 00000000 09001600
 00000000 00000000 00000000 01000000 00000000 31000000 96014000 c13a5b00
 00000000 09004800 00000000 00000000 00000000 01000000 00000000 31000000
 96014000 983e5b00 00000000 09006000 00000000 00000000 00000000 01000000
 00000000 30000000 93014000 f2bb4f00 00000000 09001400 00000000 00000000
 00000000 01000000 00000000 31000000 96014000 933e5b00 00000000 09006100
 00000000 00000000 00000000 01000000 00000000 31000000 96014000 8d3e5b00
 00000000 09004700 00000000 00000000 00000000 01000000 00000000 30000000
 94014000 87d15900 00000000 09002100 00000000 00000000 00000000 01000000
 00000000 30000000 94014000 211f5300 00000000 09001d00 00000000 00000000

9. Modify ktuxc.ktuxcfbp[0].ktufbuba to 0x00000000

BBED> set offset ktuxc.ktuxcfbp[0].ktufbuba
        OFFSET          4192

BBED> print
ktuxc.ktuxcfbp[0].ktufbuba.kubadba
----------------------------------
ub4 kubadba                                 @4192     0x00400197

BBED> modify 0x00000000
 File: /oradata/s102/system01.dbf (1)
 Block: 9                Offsets: 4192 to 4703           Dba:0x00400009
------------------------------------------------------------------------
 00000000 3a000c00 0400fe15 00000000 37000500 01006c1d 00000000 35003700
 05003805 00000000 00000000 00000000 00000000 00000000 00000000 30000000
 93014000 191f5300 00000000 09005f00 00000000 00000000 00000000 01000000
 00000000 31000000 96014000 a03e5b00 00000000 09005c00 00000000 00000000
 00000000 01000000 00000000 31000000 96014000 9e3e5b00 00000000 09000e00
 00000000 00000000 00000000 01000000 00000000 30000000 93014000 f4bb4f00
 00000000 09001600 00000000 00000000 00000000 01000000 00000000 31000000
 96014000 c13a5b00 00000000 09004800 00000000 00000000 00000000 01000000
 00000000 31000000 96014000 983e5b00 00000000 09006000 00000000 00000000
 00000000 01000000 00000000 30000000 93014000 f2bb4f00 00000000 09001400
 00000000 00000000 00000000 01000000 00000000 31000000 96014000 933e5b00
 00000000 09006100 00000000 00000000 00000000 01000000 00000000 31000000
 96014000 8d3e5b00 00000000 09004700 00000000 00000000 00000000 01000000
 00000000 30000000 94014000 87d15900 00000000 09002100 00000000 00000000
 00000000 01000000 00000000 30000000 94014000 211f5300 00000000 09001d00
 00000000 00000000 00000000 01000000 00000000 30000000 93014000 0d1f5300

BBED>

10.  Disable the block Checksum by changing the kcbh.flg_kcbh-4 and kcbh.chkval_kcbh to 0x0000:

BBED> map
 File: /oradata/s102/system01.dbf (1)
 Block: 9                                     Dba:0x00400009
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0

 struct ktech, 72 bytes                     @20

 struct ktemh, 16 bytes                     @92

 struct ktetb[6], 48 bytes                  @108

 struct ktuxc, 104 bytes                    @4148

 struct ktuxe[255], 10200 bytes             @4252

 ub4 tailchk                                @8188

BBED> print kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400009
   ub4 bas_kcbh                             @8        0x005b3f76
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe264
   ub2 spare3_kcbh                          @18       0x0000

BBED> set offset kcbh.flg_kcbh
        OFFSET          15

BBED> print
kcbh.flg_kcbh
-------------
ub1 flg_kcbh                                @15       0x04 (KCBHFCKV)

BBED> modify 0x00
 File: /oradata/s102/system01.dbf (1)
 Block: 9                Offsets:   15 to  526           Dba:0x00400009
------------------------------------------------------------------------
 0064e200 00000000 00000000 00000000 00000000 00060000 002f0000 00201000
 00040000 00060000 00080000 00970140 00000000 00040000 00000000 00000000
 00000000 00000000 00000000 00060000 00000000 00000000 00000000 400a0040
 00070000 00110040 00080000 00810140 00080000 00890140 00080000 00910140
 00080000 00990140 00080000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> set offset kcbh.chkval_kcbh
        OFFSET          16

BBED> print
kcbh.chkval_kcbh
----------------
ub2 chkval_kcbh                             @16       0xe264

BBED> modify 0x0000
 File: /oradata/s102/system01.dbf (1)
 Block: 9                Offsets:   16 to  527           Dba:0x00400009
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 06000000 2f000000 20100000
 04000000 06000000 08000000 97014000 00000000 04000000 00000000 00000000
 00000000 00000000 00000000 06000000 00000000 00000000 00000040 0a004000
 07000000 11004000 08000000 81014000 08000000 89014000 08000000 91014000
 08000000 99014000 08000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

11.  Verify the the block has no corruptions:

BBED> verify
DBVERIFY - Verification starting
FILE = /oradata/s102/system01.dbf
BLOCK = 9

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

12. exit, open the database and shrink the system rollback segment:

BBED> exit

[oracle@arem example]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 27 10:28:00 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1260696 bytes
Variable Size              62915432 bytes
Database Buffers          100663296 bytes
Redo Buffers                2932736 bytes
Database mounted.
Database opened.

SQL> alter rollback segment system shrink;

Rollback segment altered.

SQL>

Comments

  1. maclean says

    Fri Dec 06 14:14:33 2013Errors in file d:oracleproduct10.2.0adminorcludumporcl_ora_4008.trc:ORA-00600: 内部错误代码, 参数: [4193], [170], [184], [], [], [], [], []Fri Dec 06 14:14:34 2013Doing block recovery for file 1 block 403Block recovery from logseq 9, block 3 to scn 10226837381Fri Dec 06 14:14:34 2013Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0 Mem# 0: D:ORACLEPRODUCT10.2.0ORADATAorclREDO03.LOGBlock recovery stopped at EOT rba 9.5.16Block recovery completed at rba 9.5.16, scn 2.1636902788Doing block recovery for file 1 block 9Block recovery from logseq 9, block 3 to scn 10226837379Fri Dec 06 14:14:34 2013Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0 Mem# 0: D:ORACLEPRODUCT10.2.0ORADATAorclREDO03.LOGBlock recovery completed at rba 9.5.16, scn 2.1636902788Fri Dec 06 14:14:34 2013Errors in file d:oracleproduct10.2.0adminorcludumporcl_ora_4008.trc:ORA-00604: 递归 SQL 级别 1 出现错误ORA-00607: 当更改数据块时出现内部错误ORA-00600: 内部错误代码, 参数: [4193], [170], [184], [], [], [], [], []

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号