Email: service@parnassusdata.com 7 x 24 online support!
【Oracleデータリカバリ】BBEDでORA-600[4193]とORA-600[4194]をリカバリした例
プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com
ORA-00600: 内部エラー・コード, 引数: [4193]
ORA-00600: 内部エラー・コード, 引数: [4194]
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! 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
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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
- 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
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>