[Oracle] bbed (block edit) ORA-01578: ORACLE data block corrupted

// ORA-01578: ORACLE data block corrupted (file # 4, block # 140)
// ORA-01110: data file 4: '/u01/app/oracle/oradata/ORADB/users01.dbf'

// bbed 명령어는 10.2.0.5 에 생겼던 기능으로 버전이 상향되며 오라클에서 더 이상 지원하지 않는다. 따라서, 해당 명령어를 사용 후 연쇄적으로 발생하는 에러에 대해서는 오라클에서 지원받을 수 없다.

//해당 명령어를 사용하고 싶다면 개인적으로 아래 파일을 구해야한다. 구한 파일의 지원os와 설치한(적용할) os가 다르면 명령어 컴파일이 안될 수 있음. 

 

// bbed file (linux_x64) https://chess-drive.tistory.com/103

$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/lib/ssbbed.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb
$ORACLE_HOME/rdbms/mesg/bbedus.msg

 

// before bbed (compile bbed command)
[oracle@orcl11gR2 bbed]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

// before bbed (check block)

[oracle@orcl11gR2 bbed]$ dbv file=/u01/app/oracle/oradata/ORADB/users01.dbf

// 확인 한 블록넘버로 덤프를 뜨고 덤프의 내용 중 obj 넘버를 찾아 어떤 객체인지 조회한다.

SQL> alter system dump datafile 4 block 140;

[oracle@orcl11gR2 bbed]$ cd /u01/app/oracle/diag/rdbms/oradb/ORADB/trace

[oracle@orcl11gR2 trace]$ ls -alt ORADB_ora* |head -n 10;

[oracle@orcl11gR2 trace]$ vi ORADB_ora_81734.trc

SQL> select owner,object_type,object_name from dba_objects where object_id = 80567;

OWNER            OBJECT_TYPE        OBJECT_NAME
-------------- ------------------- --------------------
SCOTT             TABLE                       T1

 

// bbed
[oracle@orcl11gR2 bbed]$ cat bbed.par
blocksize=8192
listfile=/home/oracle/bbed/filelist.log
mode=edit
[oracle@orcl11gR2 bbed]$ cat filelist.log
1 /u01/app/oracle/oradata/ORADB/system01.dbf 734003200
2 /u01/app/oracle/oradata/ORADB/sysaux01.dbf 629145600
3 /u01/app/oracle/oradata/ORADB/undotbs01.dbf 519045120
4 /u01/app/oracle/oradata/ORADB/users01.dbf 5242880

 

// bbed 처음 암호는 blockedit
[oracle@orcl11gR2 bbed]$ bbed parfile=bbed.par

// 작업할 파일과 블록을 설정
BBED> set filename '/u01/app/oracle/oradata/ORADB/users01.dbf'

BBED> set dba 4,140

// 작업 설정 확인

BBED> show all

// map
BBED> map

// print (corrupt 전의 block? offset? 상태 확인)
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        0x0100008c
   ub4 bas_kcbh                             @8        0x000c0d3f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x8b8c
   ub2 spare3_kcbh                          @18       0x0000

BBED> p tailchk

ub4 tailchk                                 @8188     0x0d3f0601

// dump (블럭의 내용 확인)
BBED> dump /v dba 4,140 offset 0 count 9000

// 문자열 찾기 (if you need)

BBED> f /c [찾을 문자]

// corrupt block 
BBED> corrupt block 140

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

// print (corrupt 후의 block? offset? 상태 확인)
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        0x0100008c
   ub4 bas_kcbh                             @8        0x000c0d3f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x66f4
   ub2 spare3_kcbh                          @18       0x0000

BBED> p tailchk

ub4 tailchk                                 @8188     0x0d3f06ff

 

// 변경을 적용?
BBED> sum dba 4,140 apply

 

// rman backup 본이 있을 시

RMAN > backup check logical validate datafile 4;
SQL >  select  *  from v$database_block_corruption where  file #=4;

RMAN > blockrecover datafile 4 block 140 from backupset ;
or 
RMAN > recover corruption list ;

RMAN > validate datafile 4 block 140 ;

 

// rman backup 본이 없을 시 (bad block skip)

SQL> Exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( 'SCOTT' , 'T1' ) ;
SQL> select skip_corrupt from dba_tables where owner = 'SCOTT' and table_name = 'T1' ;

SKIP_CORRUPT
------------------
ENABLED

 

// block edit 후 buffercache를 flush 해준다.

SQL> alter system flush buffer_cache;

 

// 확인

SQL> select * from SCOTT.T1;

 

 

// bbed 명령어 설명 : https://thankyeon.tistory.com/62

// 참고 : https://blog.actorsfit.com/a?ID=00850-789ddc8d-4331-4012-b378-256f9ec2fefe