bbed修改表记录内容系列二-CSDN博客

结论

1,可见如果我采用bbed修改表记录某列内容，且修改后的列宽度如果大于原来列宽度，会导致坏块出现报ora-01578
2,解决ora-01578的解决方案有如下几则方法：
a,如有rman备份，则直接恢复这个数据块即可,block recover
b,若无rman备份，且可以允许数据损失，采用10231 event暂时屏蔽SELECT查询错误
重建表或索引
c,ora-01578只会影响查询，不会影响其它的DML操作
3，当然也可能是我BBED使用不熟悉，我相信采用bbed是可以修改列内容，由原来的列宽度变成修改后更宽的宽度
此将在另一则文章进行测试
4, 通过BBED查看数据块内容其中 tailchk及chkval_kcbh的值不一样

测试

1，继续在前述文章
http://blog.itpub.net/9240380/viewspace-1816238/，进行测试；

2,修改前的表记录
SQL> select * from t_bbed_modify_row;

A
--------------------
mama

BBED> modify /c 'others' dba 4,99004 offset 8184
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99004 Offsets: 8184 to 8187 Dba:0x010182bc
------------------------------------------------------------------------
6f746865

<32 bytes per line>

BBED> dump /v dba 4,99004 offset 8184
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99004 Offsets: 8184 to 8187 Dba:0x010182bc
-------------------------------------------------------
6f746865 l othe

<16 bytes per line>

BBED> sum dba 4,99004 apply
Check value for File 4, Block 99004:
current = 0xec0f, required = 0xec0f

可见如果修改后的列宽度大于原有内容宽度，数据块就会报错
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from t_bbed_modify_row;
select * from t_bbed_modify_row
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 99004)
ORA-01110: data file 4: '/oracle/oradata/guowang/users01.dbf'

BBED> modify /c 'moto' dba 4,99004 offset 8184
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99004 Offsets: 8184 to 8187 Dba:0x010182bc
------------------------------------------------------------------------
6d6f746f

<32 bytes per line>

BBED> dump /v dba 4,99004 offset 8184
File: /oracle/oradata/guowang/users01.dbf (4)
Block: 99004 Offsets: 8184 to 8187 Dba:0x010182bc
-------------------------------------------------------
6d6f746f l moto

<16 bytes per line>

BBED> sum dba 4,99004 apply
Check value for File 4, Block 99004:
current = 0xfd11, required = 0xfd11

可见修改原来列的宽度，还是数据块报错
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from t_bbed_modify_row;
select * from t_bbed_modify_row
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 99004)

ORA-01110: data file 4: '/oracle/oradata/guowang/users01.dbf'

在bbed验证这个数据块
BBED> verify
Message 500 not found; No message file for product=RDBMS, facility=BBED
Message 501 not found; No message file for product=RDBMS, facility=BBED

Message 520 not found; No message file for product=RDBMS, facility=BBED
Corrupt block relative dba: 0x010182bc (file 0, block 99004)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010182bc
last change scn: 0x0000.02361010 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x10107372
check value in block header: 0xfd11
computed block checksum: 0x0

在bbed查看tailchk及chkval_kcbh的值
BBED> p tailchk
ub4 tailchk @8188 0x10107372

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010182bc
ub4 bas_kcbh @8 0x02361010
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0xfd11
ub2 spare3_kcbh @18 0x0000

哪么正常情况下，tailchk及chkval_kcbh的值是否相同呢，还是一个什么关系呢？
我们创建另一个测试表，并查它其值
SQL> create table t_same(a varchar2(10));

Table created.

SQL> insert into t_same values('abcd');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),dbms_rowid.rowid_row_number(rowid) from t_same;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
------------------------------------ ------------------------------------ ----------------------------------
4 99011 0

BBED> set file 4
FILE# 4

BBED> set block 99011
BLOCK# 99011

可见tailchk及chkval_kcbh的值不一样
BBED> p tailchk
ub4 tailchk @8188 0x26f50601

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010182c3
ub4 bas_kcbh @8 0x023b26f5
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0x4b20
ub2 spare3_kcbh @18 0x0000

换另一个角度，查看ORACLE报错
SQL> host oerr ora 1578
01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"
// *Cause: The data block indicated was corrupted, mostly due to software
// errors.
// *Action: Try to restore the segment containing the block indicated. This
// may involve dropping the segment and recreating it. If there
// is a trace file, report the errors in it to your ORACLE
// representative.

解决方案：重建表或者查看报错的TRC文件

可见ora-01578不会阻塞其它的DML操作，但会影响SELECT查询

SQL> insert into t_bbed_modify_row values('newly');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_bbed_modify_row where a='newly';
ERROR:
ORA-01578: ORACLE data block corrupted (file # 4, block # 99004)
ORA-01110: data file 4: '/oracle/oradata/guowang/users01.dbf'

no rows selected

可通过10231事件暂时屏蔽ora-01578错误，把坏块中未损坏的记录复制出来，然后重建表
SQL> alter session set events '10231 trace name context forever,level 10';

Session altered.

SQL> create table t_bak_corrupt as select * from t_bbed_modify_row;

Table created.

SQL> select * from t_bak_corrupt;

A
--------------------
newly

SQL> drop table t_bbed_modify_row purge;

Table dropped.

SQL> create table t_bbed_modify_row as select * from t_bak_corrupt;

Table created.

或者你如果有RMAN的备份，可以采用BLOCK RECOVER块恢复