Email: service@parnassusdata.com 7 x 24 online support!

简体中文
English
日本語

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

Posted by PDSERVICE on Jan 16, 2016 In

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

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

まずはBBEDで使う：

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk `pwd`/bbed

mv bbed $ORACLE_HOME/bin

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

例のfile.lis

1 /oradata/s102/system01.dbf 524288000

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

bbed.par 内容は:

MODE=EDIT

LISTFILE=

BLOCKSIZE=<db_block_size>

この例のbbed.parの内容

MODE=EDIT

LISTFILE=file.lis

BLOCKSIZE=8192

bbedを実行　する。そのパスワードはblockedit

$ bbed parfile=bbed.par

Password:

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

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

BBED>

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

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.

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

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

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>

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

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

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

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

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>

You are here

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

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

プロのOracle Databaseの復旧サービスを提供携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com