BBED學習

參考

http://www.laoxiong.net/ora_8103_erro.html

http://www.eygle.com/archives/2005/09/how_to_repair_corruption_block_with_bbed.html

[oracle@RMAN ~]$ cat par.bbd

blocksize=8192

listfile=filelist.txt

mode=edit

[oracle@RMAN ~]$ cat filelist.txt

1 /oracle/oradata/logminer/system01.dbf

2 /oracle/oradata/logminer/sysaux01.dbf

3 /oracle/oradata/logminer/undotbs01.dbf

4 /oracle/oradata/logminer/users01.dbf

5 /oracle/oradata/logminer/TEST.dbf

6 /oracle/oradata/logminer/DFG01.dbf

7 /oracle/oradata/logminer/DFG02.dbf

[oracle@RMAN ~]$ $ORACLE_HOME/rdbms/lib/bbed parfile=par.bbd

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 26 12:47:56 2009

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

BBED> help all

SET DBA [ dba | file#, block# ]

SET FILENAME 'filename'

SET FILE file#

SET BLOCK [+/-]block#

SET OFFSET [ [+/-]byte offset | symbol | *symbol ]

SET BLOCKSIZE bytes

SET LIST[FILE] 'filename'

SET WIDTH character_count

SET COUNT bytes_to_display

SET IBASE [ HEX | OCT | DEC ]

SET OBASE [ HEX | OCT | DEC ]

SET MODE [ BROWSE | EDIT ]

SET SPOOL [ Y | N ]

SHOW [ | ALL ]

INFO

MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]

:

N - a number which specifies a repeat count.

u - a letter which specifies a unit size:

b - b1, ub1 (byte)

h - b2, ub2 (half-word)

w - b4, ub4(word)

r - Oracle table/index row

f - a letter which specifies a display format:

x - hexadecimal

d - decimal

u - unsigned decimal

o - octal

c - character (native)

n - Oracle number

t - Oracle date

i - Oracle rowid

FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]

MODIFY[/x|d|u|o|c] numeric/character string

ASSIGN[/x|d|u|o] =<source>

: [ value | ]<source>

SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]

PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]

POP [ALL]

REVERT [ DBA | FILE | FILENAME | BLOCK ]

UNDO

HELP [ | ALL ]

VERIFY [ DBA | FILE | FILENAME | BLOCK ]

CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

BBED> set file 4

FILE# 4

BBED> show

FILE# 4

BLOCK# 1

OFFSET 0

DBA 0x01000001 (16777217 4,1)

FILENAME /oracle/oradata/logminer/users01.dbf

BIFILE bifile.bbd

LISTFILE filelist.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED> set DBA 4,12

DBA 0x0100000c (16777228 4,12)

-------------

SQL> SELECT dbms_utility.data_block_address_file(16777228) file#,

2 dbms_utility.data_block_address_block(16777228) block# FROM dual;

FILE# BLOCK#

---------- ----------

4 12

SQL>

SQL> SELECT dbms_utility.make_data_block_address(4,12) FROM dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,12)

------------------------------------------

16777228

-------------

SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),

2 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

3 FROM CTAIS2.TEST

4 WHERE ROWNUM < 10;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

------------------------------------ ------------------------------------

5 33812

BBED> set DBA 5,33812

DBA 0x01408414 (21005332 5,33812)

BBED> dump count 8192

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 0 to 8191 Dba:0x01408414

------------------------------------------------------------------------

06a20000 14844001 cf9b0e00 00000104 88870000 01000000 8c2e0000 ca9b0e00

00000000 03003200 11844001 ffff0000 00000000 00000000 00000000 00800000

ca9b0e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

...............

3a31353a 31360556 414c4944 014e014e 014e2c00 0d035359 53054943 4f4c24ff

02c11502 c1030554 41424c45 07786d03 1a0f1011 07786d03 1a0f1011 13323030

392d3033 2d32363a 31343a31 353a3136 0556414c 4944014e 014e014e 0106cf9b

<32 bytes per line>

dd bs=8k if=/oracle/oradata/logminer/TEST.dbf skip=33812 count=1|od -x > /tmp/file.out

[root@RMAN ~]# dd bs=8k if=/oracle/oradata/logminer/TEST.dbf skip=33812 count=1|od -x > /tmp/file>

1+0 records in

1+0 records out

[root@RMAN ~]#

[root@RMAN ~]# cat /tmp/file.out

0000000 a206 0000 8414 0140 9bcf 000e 0000 0401

0000020 8788 0000 0001 0000 2e8c 0000 9bca 000e

.............

0017740 2d39 3330 322d 3a36 3431 313a 3a35 3631

0017760 5605 4c41 4449 4e01 4e01 4e01 0601 9bcf

BBED> verify

DBVERIFY - Verification starting

FILE = /oracle/oradata/logminer/TEST.dbf

BLOCK = 33812

DBVERIFY - Verification complete

Total Blocks Examined : 1

Total Blocks Processed (Data) : 1

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

BBED> sum apply

Check value for File 5, Block 33812:

current = 0x8788, required = 0x8788

--修改

BBED> dump count 100

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 0 to 99 Dba:0x01408414

------------------------------------------------------------------------

06a20000 14844001 cf9b0e00 00000104 88870000 01000000 8c2e0000 ca9b0e00

00000000 03003200 11844001 ffff0000 00000000 00000000 00000000 00800000

ca9b0e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> modify /x 0b

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 0 to 99 Dba:0x01408414

------------------------------------------------------------------------

0ba20000 14844001 cf9b0e00 00000104 88870000 01000000 8c2e0000 ca9b0e00

00000000 03003200 11844001 ffff0000 00000000 00000000 00000000 00800000

ca9b0e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> revert

All changes made in this session will be rolled back. Proceed? (Y/N) y

Reverted file '/oracle/oradata/logminer/TEST.dbf', block 33812

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

BBED> dump count 100

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 0 to 99 Dba:0x01408414

------------------------------------------------------------------------

06a20000 14844001 cf9b0e00 00000104 88870000 01000000 8c2e0000 ca9b0e00

00000000 03003200 11844001 ffff0000 00000000 00000000 00000000 00800000

ca9b0e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> set offset 16 （偏移為當前減1）

OFFSET 16

BBED> dump count 100

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 16 to 115 Dba:0x01408414

------------------------------------------------------------------------

88870000 01000000 8c2e0000 ca9b0e00 00000000 03003200 11844001 ffff0000

00000000 00000000 00000000 00800000 ca9b0e00 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> sum

Check value for File 5, Block 33812:

current = 0x8788, required = 0x8788

BBED> modify /x 0b

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 16 to 115 Dba:0x01408414

------------------------------------------------------------------------

0b870000 01000000 8c2e0000 ca9b0e00 00000000 03003200 11844001 ffff0000

00000000 00000000 00000000 00800000 ca9b0e00 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> sum

Check value for File 5, Block 33812:

current = 0x870b, required = 0x8788

--alert:

Corrupt block relative dba: 0x01408414 (file 5, block 33812)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x01408414

last change scn: 0x0000.000e9bcf seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x9bcf0601

check value in block header: 0x870b

computed block checksum: 0x83

Reread of rdba: 0x01408414 (file 5, block 33812) found same corrupted data

Sun Apr 26 14:08:56 2009

Corrupt Block Found

TSN = 5, TSNAME = TEST

RFN = 5, BLK = 33812, RDBA = 21005332

OBJN = 11916, BJD = 11916, BJECT = TEST, SUBOBJECT =

SEGMENT WNER = CTAIS2, SEGMENT TYPE = Table Segment

--backup

RMAN> backup validate check logical datafile 5

2> ;

Starting backup at 26-APR-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/oracle/oradata/logminer/TEST.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 26-APR-09

--select

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION

2 ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

5 33812 1 0 CHECKSUM

BBED> sum apply

Check value for File 5, Block 33812:

current = 0x8788, required = 0x8788

BBED> dump

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 16 to 115 Dba:0x01408414

------------------------------------------------------------------------

88870000 01000000 8c2e0000 ca9b0e00 00000000 03003200 11844001 ffff0000

00000000 00000000 00000000 00800000 ca9b0e00 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

-------------------------------------------------------------

BBED> set mode browse

MODE Browse

BBED>

BBED> dump

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33812 Offsets: 16 to 115 Dba:0x01408414

------------------------------------------------------------------------

88870000 01000000 8c2e0000 ca9b0e00 00000000 03003200 11844001 ffff0000

00000000 00000000 00000000 00800000 ca9b0e00 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000

<32 bytes per line>

BBED> modify /x 10

BBED-00215: editing not allowed in BROWSE mode

--------------------------------------------------------

[oracle@RMAN ~]$ cat filelist.txt

1 /oracle/oradata/logminer/system01.dbf

2 /oracle/oradata/logminer/sysaux01.dbf

3 /oracle/oradata/logminer/undotbs01.dbf

4 /oracle/oradata/logminer/users01.dbf

5 /oracle/oradata/logminer/TEST.dbf

6 /oracle/oradata/logminerbak/TEST.dbf

--檔案6為以前的備份，沒TEST的表

BBED> copy file 6 block 33846 to file 5 block 33846;

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Offsets: 0 to 511 Dba:0x01408436

------------------------------------------------------------------------

06a20000 36844001 ed480d00 00000104 f39d0000 01000000 782e0000 dc480d00

00000000 03003200 31844001 ffff0000 00000000 00000000 00000000 00800000

dc480d00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00014f00

ffffb000 21047103 71030000 4f00221f d11e7a1e 281ed01d 7f1d281d cf1c701c

161cb61b 5d1bfe1a a31a421a e6198419 2919c818 6e180e18 b3175217 f9169916

4216e515 8f153315 dd148114 2814c913 71131313 bb125d12 0a12b111 5d110311

b0105710 0a10b70f 690f150f c80e750e 230ecb0d 720d130d bb0c5d0c 050cad0b

4f0bf60a 970a3f0a e1099009 3909eb08 97084708 f1079b07 3f07e506 85063106

d7058305 2905d404 79042104 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

SQL> select count(*) from ctais2.test;

select count(*) from ctais2.test

*

ERROR at line 1:

ORA-01410: invalid ROWID

DBV無壞塊，修改要小心，這種情況下又不是壞塊，只有將這個塊修改成壞塊來跳過

SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set db_file_multiblock_read_count=1;
--SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576 ';

--10g不需要這句

SQL> alter session set events '10200 trace name context forever, level 1';

SQL> select count(*) from ctais2.test;

[oracle@RMAN logminerbak]$ cat /oracle/admin/logminer/udump/logminer_ora_7813.trc|grep "Consistent read started for block"

Consistent read started for block 5 : 01400027

Consistent read started for block 5 : 01400030

Consistent read started for block 5 : 0140004d

………………..

Consistent read started for block 5 : 01408434

Consistent read started for block 5 : 01408435

Consistent read started for block 5 : 01408436 => 21005366

SQL> SELECT dbms_utility.data_block_address_file(21005366) file#,

2 dbms_utility.data_block_address_block(21005366) block# FROM dual;

FILE# BLOCK#

---------- ----------

5 33846

現在找到了出問題的塊

BBED> print struct kcbh中的type_kcbh

kcbh.type_kcbh

--------------

ub1 type_kcbh @0 0x06

BBED> map 塊結構

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Dba:0x01408436

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0 @ 偏移量

struct ktbbh, 96 bytes @20

struct kdbh, 14 bytes @124

struct kdbt[1], 4 bytes @138

sb2 kdbr[79] @142

ub1 freespace[881] @300

ub1 rowdata[7007] @1181

ub4 tailchk @8188

BBED> map /v

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Dba:0x01408436

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

ub1 frmt_kcbh @1

ub1 spare1_kcbh @2

ub1 spare2_kcbh @3

ub4 rdba_kcbh @4

ub4 bas_kcbh @8

ub2 wrp_kcbh @12

ub1 seq_kcbh @14

ub1 flg_kcbh @15

ub2 chkval_kcbh @16

ub2 spare3_kcbh @18

struct ktbbh, 96 bytes @20

ub1 ktbbhtyp @20

union ktbbhsid, 4 bytes @24

struct ktbbhcsc, 8 bytes @28

b2 ktbbhict @36

ub1 ktbbhflg @38

ub1 ktbbhfsl @39

ub4 ktbbhfnx @40

struct ktbbhitl[3], 72 bytes @44

struct kdbh, 14 bytes @124

ub1 kdbhflag @124

b1 kdbhntab @125

b2 kdbhnrow @126

sb2 kdbhfrre @128

sb2 kdbhfsbo @130

sb2 kdbhfseo @132

b2 kdbhavsp @134

b2 kdbhtosp @136

struct kdbt[1], 4 bytes @138

b2 kdbtoffs @138

b2 kdbtnrow @140

sb2 kdbr[79] @142

ub1 freespace[881] @300

ub1 rowdata[7007] @1181

ub4 tailchk @8188

BBED> examine

kcbh.type_kcbh @0

--------------

0x06

BBED> find /x 0d06 移動到查詢到位置

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Offsets: 1271 to 8191 Dba:0x01408436

------------------------------------------------------------------------

0d065055 424c4943 12555345 525f534f 55524345 5f544142 4c4553ff 03c21c37

ff075359 4e4f4e59 4d07786d 031a0f12 2107786d 031a0f33 0e133230 30392d30

……………

44014e01 4e014e2c 000d0650 55424c49 4315414c 4c5f4f50 45524154 4f525f43

4f4d4d45 4e5453ff 03c21b4e ff075359 4e4f4e59 4d07786d 031a0f12 1807786d

031a0f33 0c133230 30392d30 332d3236 3a31343a 35303a31 31055641 4c494401

4e014e01 4e0106db 9b

BBED> dump

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Offsets: 0 to 19 Dba:0x01408436

------------------------------------------------------------------------

06a20000 36844001 db9b0e00 00000104 049d0000

<32 bytes per line>

BBED> assign /x 02

ub1 spare1_kcbh @2 0x06

BBED> dump

File: /oracle/oradata/logminer/TEST.dbf (5)

Block: 33846 Offsets: 2 to 21 Dba:0x01408436

------------------------------------------------------------------------

06003684 4001db9b 0e000000 0104049d 00000100

<32 bytes per line>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-591856/，如需轉載，請註明出處，否則將追究法律責任。

BBED學習

相關文章