【Oracleデータリカバリ】BBEDでORA-600[4193]とORA-600[4194]をリカバリした例

プロのOracle Databaseの復旧サービスを提供
携帯番号: +86 13764045638 メール:[email protected]

 

ORA-600[4193]の原因はORA-600[4194]の場合に似ている。Oracleがredo記録とrollback/undoデータが一致していないから。プロセスundo blocのundo block sequence number及び該当するredo block sequence numberをテストするときに一致していないと気づき、エラになる。 ORA-600 [4193] “seq# mismatch while adding undo record”と呼ばれている。

ORA-600[4913]エラの二つの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がシステムセグメントをロールバックするときに、隠しバラメタを使ってもこのトラブルを避けられない。_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

 

  1. システムテーブルスペースを含むデータファイルの情報をfile.lisに格納する:

 

例のfile.lis

 

1 /oradata/s102/system01.dbf 524288000

 

  1. bbedのバラメタファイルbbed.parを作成する

 

bbed.par 内容は:

 

MODE=EDIT

LISTFILE=

BLOCKSIZE=<db_block_size>

 

この例のbbed.parの内容

 

MODE=EDIT

LISTFILE=file.lis

BLOCKSIZE=8192

 

  1. 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>

 

  1. 今のブロックをsystem rollback segmentのheader block と設定してください。

 

  1. 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.

 

  1. 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

 

  1. 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

 

  1. 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>

 

  1. 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

 

  1. 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

 

  1. 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>

 


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *