### 1. 內容概述
```c
   oracle segment header由於儲存、bug、人為誤操作等原因，造成段頭塊"Completely zero block"，存在業務資料丟失風險，手工修改資料字典會造成相關資料塊狀態異常，
影響資料庫備份等操作，本文透過bbed工具恢復oracle segment header "Completely zero block"塊。

```

### 2.環境準備
```c
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '/data2/enmo/hsql01.dbf' size 100M autoextend off;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql;

begin
for i in 1 .. 1000000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
end;
/

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1;
```

### 3.資料字典檢查
```c
set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='DROP_1';

OWNER	   SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
HSQL	   DROP_1		5	   130 TABLE

set linesize 200 pagesize 9999
col owner for a10
col object_name for a20
select owner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='DROP_1';

OWNER	   OBJECT_NAME		 OBJECT_ID DATA_OBJECT_ID
---------- -------------------- ---------- --------------
HSQL	   DROP_1		     13761	    13761

set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a60
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id;

OWNER	   SEGMENT_NAME 						 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- ------------------------------------------------------------ ---------- ---------- ---------- ----------
HSQL	   DROP_1								 0	    5	     128	  8
HSQL	   DROP_1								 1	    5	     136	  8
HSQL	   DROP_1								 2	    5	     144	  8
HSQL	   DROP_1								 3	    5	     152	  8
HSQL	   DROP_1								 4	    5	     160	  8
HSQL	   DROP_1								 5	    5	     168	  8
HSQL	   DROP_1								 6	    5	     176	  8
HSQL	   DROP_1								 7	    5	     184	  8
HSQL	   DROP_1								 8	    5	     192	  8
HSQL	   DROP_1								 9	    5	     200	  8
HSQL	   DROP_1								10	    5	     208	  8
HSQL	   DROP_1								11	    5	     216	  8
HSQL	   DROP_1								12	    5	     224	  8
HSQL	   DROP_1								13	    5	     232	  8
HSQL	   DROP_1								14	    5	     240	  8
HSQL	   DROP_1								15	    5	     248	  8
HSQL	   DROP_1								16	    5	     256	128
HSQL	   DROP_1								17	    5	     384	128
HSQL	   DROP_1								18	    5	     512	128
HSQL	   DROP_1								19	    5	     640	128
HSQL	   DROP_1								20	    5	     768	128
HSQL	   DROP_1								21	    5	     896	128
HSQL	   DROP_1								22	    5	    1024	128
HSQL	   DROP_1								23	    5	    1152	128
HSQL	   DROP_1								24	    5	    1280	128
HSQL	   DROP_1								25	    5	    1408	128
HSQL	   DROP_1								26	    5	    1536	128
HSQL	   DROP_1								27	    5	    1664	128
HSQL	   DROP_1								28	    5	    1792	128
HSQL	   DROP_1								29	    5	    1920	128
HSQL	   DROP_1								30	    5	    2048	128
HSQL	   DROP_1								31	    5	    2176	128
HSQL	   DROP_1								32	    5	    2304	128
HSQL	   DROP_1								33	    5	    2432	128
HSQL	   DROP_1								34	    5	    2560	128
HSQL	   DROP_1								35	    5	    2688	128
HSQL	   DROP_1								36	    5	    2816	128
HSQL	   DROP_1								37	    5	    2944	128
HSQL	   DROP_1								38	    5	    3072	128
HSQL	   DROP_1								39	    5	    3200	128
HSQL	   DROP_1								40	    5	    3328	128
HSQL	   DROP_1								41	    5	    3456	128
HSQL	   DROP_1								42	    5	    3584	128
HSQL	   DROP_1								43	    5	    3712	128
HSQL	   DROP_1								44	    5	    3840	128

45 rows selected.

SQL>
```

### 4. 破壞段頭塊
```c
dd if=/dev/zero of=/data2/enmo/hsql01.dbf seek=130 bs=8192 count=1 conv=notrunc

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1;

SQL> 
select count(1) from hsql.drop_1
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/data2/enmo/hsql01.dbf'

SQL>
```

### 5. dbv檢修塊狀態
```c
[oracle@sourcedb enmo]$ dbv file=/data2/enmo/hsql01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jul 8 03:33:09 2020

DBVERIFY - Verification starting : FILE = /data2/enmo/hsql01.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x01400082 (file 5, block 130)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 3758
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 194
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 8847
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 320271 (0.320271)
[oracle@sourcedb enmo]$ 
```

### 6. copy 段頭塊結構
```c
set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where TABLESPACE_NAME='SYSAUX' and rownum=1;

OWNER	   SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
SYS	   SMB$CONFIG		2	   138 TABLE

[oracle@sourcedb bbed]$ ./l_bbed.sh

BBED: Release 2.0.0.0.0 - Limited Production on Wed Jul 8 03:36:50 2020

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

BBED> set dba 2,138
	DBA            	0x0080008a (8388746 2,138)

BBED> d offset 0 count 12
 File: /data2/enmo/sysaux01.dbf (2)
 Block: 138              Offsets:    0 to   11           Dba:0x0080008a
------------------------------------------------------------------------
 23a20000 8a008000 56080000

<32 bytes per line>

BBED> copy dba 2,138 to dba 5,130
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:    0 to   11           Dba:0x01400082
------------------------------------------------------------------------
 23a20000 8a008000 56080000

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 130:
current = 0xab35, required = 0xab35

BBED>

[oracle@sourcedb bbed]$ dbv file=/data2/enmo/hsql01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jul 8 03:37:36 2020

DBVERIFY - Verification starting : FILE = /data2/enmo/hsql01.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x01400082 (file 5, block 130)
Bad header found during dbv: 
Data in bad block:
 type: 35 format: 2 rdba: 0x0080008a
 last change scn: 0x0000.00000856 seq: 0x3 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x08562303
 check value in block header: 0xab35
 computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 3758
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 194
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 8847
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 320271 (0.320271)
[oracle@sourcedb bbed]$
```

### 7. 修改rdba地址
```c
[oracle@sourcedb bbed]$ ./l_bbed.sh

BBED: Release 2.0.0.0.0 - Limited Production on Wed Jul 8 03:38:02 2020

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

BBED> p dba 5,129 kcbh
BBED-00400: invalid blocktype (33)

BBED> p dba 5,131 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        0x01400083
   ub4 bas_kcbh                             @8        0x0004e30f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xf7c2
   ub2 spare3_kcbh                          @18       0x0000

BBED> set dba 5,130
	DBA            	0x01400082 (20971650 5,130)

BBED> d offset 4 count 12
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:    4 to   15           Dba:0x01400082
------------------------------------------------------------------------
 8a008000 56080000 00000304

<32 bytes per line>

BBED> m /x 82004001 offset 4
BBED-00209: invalid number (82004001)

BBED> m /x 8200 offset 4
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:    4 to   15           Dba:0x01400082
------------------------------------------------------------------------
 82008000 56080000 00000304

<32 bytes per line>

BBED> m /x 4001 offset 6
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:    6 to   17           Dba:0x01400082
------------------------------------------------------------------------
 40015608 00000000 030435ab

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 130:
current = 0xaafd, required = 0xaafd

BBED> d offset 4 count 12
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:    4 to   15           Dba:0x01400082
------------------------------------------------------------------------
 82004001 56080000 00000304

<32 bytes per line>

BBED> v
DBVERIFY - Verification starting
FILE = /data2/enmo/hsql01.dbf
BLOCK = 130

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
Message 531 not found;  product=RDBMS; facility=BBED

BBED>
```

### 8.透過L2/L1 構造extent map
```c
SQL> alter system dump datafile 5 block 129;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';
Default Trace File
/u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_3849.trc

grep -w 'Inst' /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_3849.trc|awk '{print $1}'|xargs -n 1 ora_rdba|grep command|sed 's/dump command://g'
select value from v$diag_info where name='Default Trace File';
--outfile: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_3977.trc
grep -i 'Length:' /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_3977.trc|grep -in 'Offset:'|sed 's/://g'|sed 's/0x//g'|awk '{print "select "$1 " as n_num,\47" $2 "\47 as n_dba," $4 " as n_len from dual union all"}'
```

#### 9. bbed 恢復extent map
```c
set linesize 200 pagesize 9999
col dba_em_1 for a20
col dba_em_2 for a20
col n_len for a30
with new_ext as (
select 1 as n_num,'01400080' as n_dba,8 as n_len from dual union all
select 2 as n_num,'01400088' as n_dba,8 as n_len from dual union all
select 3 as n_num,'01400090' as n_dba,8 as n_len from dual union all
select 4 as n_num,'01400098' as n_dba,8 as n_len from dual union all
select 5 as n_num,'014000a0' as n_dba,8 as n_len from dual union all
select 6 as n_num,'014000a8' as n_dba,8 as n_len from dual union all
select 7 as n_num,'014000b0' as n_dba,8 as n_len from dual union all
select 8 as n_num,'014000b8' as n_dba,8 as n_len from dual union all
select 9 as n_num,'014000c0' as n_dba,8 as n_len from dual union all
select 10 as n_num,'014000c8' as n_dba,8 as n_len from dual union all
select 11 as n_num,'014000d0' as n_dba,8 as n_len from dual union all
select 12 as n_num,'014000d8' as n_dba,8 as n_len from dual union all
select 13 as n_num,'014000e0' as n_dba,8 as n_len from dual union all
select 14 as n_num,'014000e8' as n_dba,8 as n_len from dual union all
select 15 as n_num,'014000f0' as n_dba,8 as n_len from dual union all
select 16 as n_num,'014000f8' as n_dba,8 as n_len from dual union all
select 17 as n_num,'01400100' as n_dba,64 as n_len from dual union all
select 18 as n_num,'01400140' as n_dba,64 as n_len from dual union all
select 19 as n_num,'01400180' as n_dba,64 as n_len from dual union all
select 20 as n_num,'014001c0' as n_dba,64 as n_len from dual union all
select 21 as n_num,'01400200' as n_dba,64 as n_len from dual union all
select 22 as n_num,'01400240' as n_dba,64 as n_len from dual union all
select 23 as n_num,'01400280' as n_dba,64 as n_len from dual union all
select 24 as n_num,'014002c0' as n_dba,64 as n_len from dual union all
select 25 as n_num,'01400300' as n_dba,64 as n_len from dual union all
select 26 as n_num,'01400340' as n_dba,64 as n_len from dual union all
select 27 as n_num,'01400380' as n_dba,64 as n_len from dual union all
select 28 as n_num,'014003c0' as n_dba,64 as n_len from dual union all
select 29 as n_num,'01400400' as n_dba,64 as n_len from dual union all
select 30 as n_num,'01400440' as n_dba,64 as n_len from dual union all
select 31 as n_num,'01400480' as n_dba,64 as n_len from dual union all
select 32 as n_num,'014004c0' as n_dba,64 as n_len from dual union all
select 33 as n_num,'01400500' as n_dba,64 as n_len from dual union all
select 34 as n_num,'01400540' as n_dba,64 as n_len from dual union all
select 35 as n_num,'01400580' as n_dba,64 as n_len from dual union all
select 36 as n_num,'014005c0' as n_dba,64 as n_len from dual union all
select 37 as n_num,'01400600' as n_dba,64 as n_len from dual union all
select 38 as n_num,'01400640' as n_dba,64 as n_len from dual union all
select 39 as n_num,'01400680' as n_dba,64 as n_len from dual union all
select 40 as n_num,'014006c0' as n_dba,64 as n_len from dual union all
select 41 as n_num,'01400700' as n_dba,64 as n_len from dual union all
select 42 as n_num,'01400740' as n_dba,64 as n_len from dual union all
select 43 as n_num,'01400780' as n_dba,64 as n_len from dual union all
select 44 as n_num,'014007c0' as n_dba,64 as n_len from dual union all
select 45 as n_num,'01400800' as n_dba,64 as n_len from dual union all
select 46 as n_num,'01400840' as n_dba,64 as n_len from dual union all
select 47 as n_num,'01400880' as n_dba,64 as n_len from dual union all
select 48 as n_num,'014008c0' as n_dba,64 as n_len from dual union all
select 49 as n_num,'01400900' as n_dba,64 as n_len from dual union all
select 50 as n_num,'01400940' as n_dba,64 as n_len from dual union all
select 51 as n_num,'01400980' as n_dba,64 as n_len from dual union all
select 52 as n_num,'014009c0' as n_dba,64 as n_len from dual union all
select 53 as n_num,'01400a00' as n_dba,64 as n_len from dual union all
select 54 as n_num,'01400a40' as n_dba,64 as n_len from dual union all
select 55 as n_num,'01400a80' as n_dba,64 as n_len from dual union all
select 56 as n_num,'01400ac0' as n_dba,64 as n_len from dual union all
select 57 as n_num,'01400b00' as n_dba,64 as n_len from dual union all
select 58 as n_num,'01400b40' as n_dba,64 as n_len from dual union all
select 59 as n_num,'01400b80' as n_dba,64 as n_len from dual union all
select 60 as n_num,'01400bc0' as n_dba,64 as n_len from dual union all
select 61 as n_num,'01400c00' as n_dba,64 as n_len from dual union all
select 62 as n_num,'01400c40' as n_dba,64 as n_len from dual union all
select 63 as n_num,'01400c80' as n_dba,64 as n_len from dual union all
select 64 as n_num,'01400cc0' as n_dba,64 as n_len from dual union all
select 65 as n_num,'01400d00' as n_dba,64 as n_len from dual union all
select 66 as n_num,'01400d40' as n_dba,64 as n_len from dual union all
select 67 as n_num,'01400d80' as n_dba,64 as n_len from dual union all
select 68 as n_num,'01400dc0' as n_dba,64 as n_len from dual union all
select 69 as n_num,'01400e00' as n_dba,64 as n_len from dual union all
select 70 as n_num,'01400e40' as n_dba,64 as n_len from dual union all
select 71 as n_num,'01400e80' as n_dba,64 as n_len from dual union all
select 72 as n_num,'01400ec0' as n_dba,64 as n_len from dual union all
select 73 as n_num,'01400f00' as n_dba,64 as n_len from dual union all
select 74 as n_num,'01400f40' as n_dba,64 as n_len from dual
),
new_ext_temp as(
select 
 n_num,
 dbms_utility.data_block_address_file(TO_NUMBER(n_dba, 'XXXXXXXX')) file_id,
 dbms_utility.data_block_address_block(TO_NUMBER(n_dba,'XXXXXXXX')) block_id,
 'm /x '||substr(n_dba,7,2)||substr(n_dba,5,2) ||' offset '||to_char(280+(n_num-1)*8) dba_em_1,  
 'm /x '||substr(n_dba,3,2)||substr(n_dba,1,2) ||' offset '||to_char(280+(n_num-1)*8+2) dba_em_2,
 case when n_num<=16                        then  'm /x '||'0800'||' offset '||to_char(280+(n_num-1)*8+4)
      when n_num>16 and n_num<=16+63        then  'm /x '||'8000'||' offset '||to_char(280+(n_num-1)*8+4)
      when n_num>16+63 and n_num<=16+63+120 then  'm /x '||'0004'||' offset '||to_char(280+(n_num-1)*8+4)
      when n_num>16+63+120                  then  'm /x '||'0020'||' offset '||to_char(280+(n_num-1)*8+4)
      else '0'
 end as n_len
 from (select rownum n_num,n_dba from new_ext where n_num<=16 or mod(n_num,2)=1)
 )
select dba_em_1 from new_ext_temp
union all
select dba_em_2 from new_ext_temp
union all
select n_len from new_ext_temp
;

m /x 8000 offset 280
m /x 8800 offset 288
m /x 9000 offset 296
m /x 9800 offset 304
m /x a000 offset 312
m /x a800 offset 320
m /x b000 offset 328
m /x b800 offset 336
m /x c000 offset 344
m /x c800 offset 352
m /x d000 offset 360
m /x d800 offset 368
m /x e000 offset 376
m /x e800 offset 384
m /x f000 offset 392
m /x f800 offset 400
m /x 0001 offset 408
m /x 8001 offset 416
m /x 0002 offset 424
m /x 8002 offset 432
m /x 0003 offset 440
m /x 8003 offset 448
m /x 0004 offset 456
m /x 8004 offset 464
m /x 0005 offset 472
m /x 8005 offset 480
m /x 0006 offset 488
m /x 8006 offset 496
m /x 0007 offset 504
m /x 8007 offset 512
m /x 0008 offset 520
m /x 8008 offset 528
m /x 0009 offset 536
m /x 8009 offset 544
m /x 000a offset 552
m /x 800a offset 560
m /x 000b offset 568
m /x 800b offset 576
m /x 000c offset 584
m /x 800c offset 592
m /x 000d offset 600
m /x 800d offset 608
m /x 000e offset 616
m /x 800e offset 624
m /x 000f offset 632
m /x 4001 offset 282
m /x 4001 offset 290
m /x 4001 offset 298
m /x 4001 offset 306
m /x 4001 offset 314
m /x 4001 offset 322
m /x 4001 offset 330
m /x 4001 offset 338
m /x 4001 offset 346
m /x 4001 offset 354
m /x 4001 offset 362
m /x 4001 offset 370
m /x 4001 offset 378
m /x 4001 offset 386
m /x 4001 offset 394
m /x 4001 offset 402
m /x 4001 offset 410
m /x 4001 offset 418
m /x 4001 offset 426
m /x 4001 offset 434
m /x 4001 offset 442
m /x 4001 offset 450
m /x 4001 offset 458
m /x 4001 offset 466
m /x 4001 offset 474
m /x 4001 offset 482
m /x 4001 offset 490
m /x 4001 offset 498
m /x 4001 offset 506
m /x 4001 offset 514
m /x 4001 offset 522
m /x 4001 offset 530
m /x 4001 offset 538
m /x 4001 offset 546
m /x 4001 offset 554
m /x 4001 offset 562
m /x 4001 offset 570
m /x 4001 offset 578
m /x 4001 offset 586
m /x 4001 offset 594
m /x 4001 offset 602
m /x 4001 offset 610
m /x 4001 offset 618
m /x 4001 offset 626
m /x 4001 offset 634
m /x 0800 offset 284
m /x 0800 offset 292
m /x 0800 offset 300
m /x 0800 offset 308
m /x 0800 offset 316
m /x 0800 offset 324
m /x 0800 offset 332
m /x 0800 offset 340
m /x 0800 offset 348
m /x 0800 offset 356
m /x 0800 offset 364
m /x 0800 offset 372
m /x 0800 offset 380
m /x 0800 offset 388
m /x 0800 offset 396
m /x 0800 offset 404
m /x 8000 offset 412
m /x 8000 offset 420
m /x 8000 offset 428
m /x 8000 offset 436
m /x 8000 offset 444
m /x 8000 offset 452
m /x 8000 offset 460
m /x 8000 offset 468
m /x 8000 offset 476
m /x 8000 offset 484
m /x 8000 offset 492
m /x 8000 offset 500
m /x 8000 offset 508
m /x 8000 offset 516
m /x 8000 offset 524
m /x 8000 offset 532
m /x 8000 offset 540
m /x 8000 offset 548
m /x 8000 offset 556
m /x 8000 offset 564
m /x 8000 offset 572
m /x 8000 offset 580
m /x 8000 offset 588
m /x 8000 offset 596
m /x 8000 offset 604
m /x 8000 offset 612
m /x 8000 offset 620
m /x 8000 offset 628
m /x 8000 offset 636
```

#### 10. bbed 恢復aux extmap
```c
set linesize 200 pagesize 9999
col dba_em_1 for a20
col dba_em_2 for a20
col n_len for a30
with all_ext as (
select 1 as n_num,'01400080' as n_dba,8 as n_len from dual union all
select 2 as n_num,'01400088' as n_dba,8 as n_len from dual union all
select 3 as n_num,'01400090' as n_dba,8 as n_len from dual union all
select 4 as n_num,'01400098' as n_dba,8 as n_len from dual union all
select 5 as n_num,'014000a0' as n_dba,8 as n_len from dual union all
select 6 as n_num,'014000a8' as n_dba,8 as n_len from dual union all
select 7 as n_num,'014000b0' as n_dba,8 as n_len from dual union all
select 8 as n_num,'014000b8' as n_dba,8 as n_len from dual union all
select 9 as n_num,'014000c0' as n_dba,8 as n_len from dual union all
select 10 as n_num,'014000c8' as n_dba,8 as n_len from dual union all
select 11 as n_num,'014000d0' as n_dba,8 as n_len from dual union all
select 12 as n_num,'014000d8' as n_dba,8 as n_len from dual union all
select 13 as n_num,'014000e0' as n_dba,8 as n_len from dual union all
select 14 as n_num,'014000e8' as n_dba,8 as n_len from dual union all
select 15 as n_num,'014000f0' as n_dba,8 as n_len from dual union all
select 16 as n_num,'014000f8' as n_dba,8 as n_len from dual union all
select 17 as n_num,'01400100' as n_dba,64 as n_len from dual union all
select 18 as n_num,'01400140' as n_dba,64 as n_len from dual union all
select 19 as n_num,'01400180' as n_dba,64 as n_len from dual union all
select 20 as n_num,'014001c0' as n_dba,64 as n_len from dual union all
select 21 as n_num,'01400200' as n_dba,64 as n_len from dual union all
select 22 as n_num,'01400240' as n_dba,64 as n_len from dual union all
select 23 as n_num,'01400280' as n_dba,64 as n_len from dual union all
select 24 as n_num,'014002c0' as n_dba,64 as n_len from dual union all
select 25 as n_num,'01400300' as n_dba,64 as n_len from dual union all
select 26 as n_num,'01400340' as n_dba,64 as n_len from dual union all
select 27 as n_num,'01400380' as n_dba,64 as n_len from dual union all
select 28 as n_num,'014003c0' as n_dba,64 as n_len from dual union all
select 29 as n_num,'01400400' as n_dba,64 as n_len from dual union all
select 30 as n_num,'01400440' as n_dba,64 as n_len from dual union all
select 31 as n_num,'01400480' as n_dba,64 as n_len from dual union all
select 32 as n_num,'014004c0' as n_dba,64 as n_len from dual union all
select 33 as n_num,'01400500' as n_dba,64 as n_len from dual union all
select 34 as n_num,'01400540' as n_dba,64 as n_len from dual union all
select 35 as n_num,'01400580' as n_dba,64 as n_len from dual union all
select 36 as n_num,'014005c0' as n_dba,64 as n_len from dual union all
select 37 as n_num,'01400600' as n_dba,64 as n_len from dual union all
select 38 as n_num,'01400640' as n_dba,64 as n_len from dual union all
select 39 as n_num,'01400680' as n_dba,64 as n_len from dual union all
select 40 as n_num,'014006c0' as n_dba,64 as n_len from dual union all
select 41 as n_num,'01400700' as n_dba,64 as n_len from dual union all
select 42 as n_num,'01400740' as n_dba,64 as n_len from dual union all
select 43 as n_num,'01400780' as n_dba,64 as n_len from dual union all
select 44 as n_num,'014007c0' as n_dba,64 as n_len from dual union all
select 45 as n_num,'01400800' as n_dba,64 as n_len from dual union all
select 46 as n_num,'01400840' as n_dba,64 as n_len from dual union all
select 47 as n_num,'01400880' as n_dba,64 as n_len from dual union all
select 48 as n_num,'014008c0' as n_dba,64 as n_len from dual union all
select 49 as n_num,'01400900' as n_dba,64 as n_len from dual union all
select 50 as n_num,'01400940' as n_dba,64 as n_len from dual union all
select 51 as n_num,'01400980' as n_dba,64 as n_len from dual union all
select 52 as n_num,'014009c0' as n_dba,64 as n_len from dual union all
select 53 as n_num,'01400a00' as n_dba,64 as n_len from dual union all
select 54 as n_num,'01400a40' as n_dba,64 as n_len from dual union all
select 55 as n_num,'01400a80' as n_dba,64 as n_len from dual union all
select 56 as n_num,'01400ac0' as n_dba,64 as n_len from dual union all
select 57 as n_num,'01400b00' as n_dba,64 as n_len from dual union all
select 58 as n_num,'01400b40' as n_dba,64 as n_len from dual union all
select 59 as n_num,'01400b80' as n_dba,64 as n_len from dual union all
select 60 as n_num,'01400bc0' as n_dba,64 as n_len from dual union all
select 61 as n_num,'01400c00' as n_dba,64 as n_len from dual union all
select 62 as n_num,'01400c40' as n_dba,64 as n_len from dual union all
select 63 as n_num,'01400c80' as n_dba,64 as n_len from dual union all
select 64 as n_num,'01400cc0' as n_dba,64 as n_len from dual union all
select 65 as n_num,'01400d00' as n_dba,64 as n_len from dual union all
select 66 as n_num,'01400d40' as n_dba,64 as n_len from dual union all
select 67 as n_num,'01400d80' as n_dba,64 as n_len from dual union all
select 68 as n_num,'01400dc0' as n_dba,64 as n_len from dual union all
select 69 as n_num,'01400e00' as n_dba,64 as n_len from dual union all
select 70 as n_num,'01400e40' as n_dba,64 as n_len from dual union all
select 71 as n_num,'01400e80' as n_dba,64 as n_len from dual union all
select 72 as n_num,'01400ec0' as n_dba,64 as n_len from dual union all
select 73 as n_num,'01400f00' as n_dba,64 as n_len from dual union all
select 74 as n_num,'01400f40' as n_dba,64 as n_len from dual
),
aux_temp as(
select rownum n_num,n_dba L1_dba,n_dba data_dba from all_ext where n_num<=16 or mod(n_num,2)=1
),
aux_temp2 as(
select n_num,L1_dba,
lpad(replace(lower(to_char(to_number(data_dba,'XXXXXXXX')+1,'XXXXXXXX')),' ',''),8,'0') d_dba 
from aux_temp where n_num<=16 and mod(n_num,2)=1
union all
select n_num,(select L1_dba from aux_temp t where t.n_num=a.n_num-1) L1_dba,L1_dba d_dba
from aux_temp a where n_num<=16 and mod(n_num,2)=0
union all
select n_num,L1_dba,lpad(replace(lower(to_char(to_number(data_dba,'XXXXXXXX')+2,'XXXXXXXX')),' ',''),8,'0')
from aux_temp a where n_num>16
order by 1
)
select 'm /x '||substr(L1_dba,7,2)||substr(L1_dba,5,2) ||' offset '||to_char(2736+(n_num-1)*8) from aux_temp2
union all
select 'm /x '||substr(L1_dba,3,2)||substr(L1_dba,1,2) ||' offset '||to_char(2736+(n_num-1)*8+2) from aux_temp2
union all
select 'm /x '||substr(d_dba,7,2)||substr(d_dba,5,2) ||' offset '||to_char(2736+(n_num-1)*8+4) from aux_temp2
union all
select 'm /x '||substr(d_dba,3,2)||substr(d_dba,1,2) ||' offset '||to_char(2736+(n_num-1)*8+6) from aux_temp2;

m /x 8000 offset 2736
m /x 8000 offset 2744
m /x 9000 offset 2752
m /x 9000 offset 2760
m /x a000 offset 2768
m /x a000 offset 2776
m /x b000 offset 2784
m /x b000 offset 2792
m /x c000 offset 2800
m /x c000 offset 2808
m /x d000 offset 2816
m /x d000 offset 2824
m /x e000 offset 2832
m /x e000 offset 2840
m /x f000 offset 2848
m /x f000 offset 2856
m /x 0001 offset 2864
m /x 8001 offset 2872
m /x 0002 offset 2880
m /x 8002 offset 2888
m /x 0003 offset 2896
m /x 8003 offset 2904
m /x 0004 offset 2912
m /x 8004 offset 2920
m /x 0005 offset 2928
m /x 8005 offset 2936
m /x 0006 offset 2944
m /x 8006 offset 2952
m /x 0007 offset 2960
m /x 8007 offset 2968
m /x 0008 offset 2976
m /x 8008 offset 2984
m /x 0009 offset 2992
m /x 8009 offset 3000
m /x 000a offset 3008
m /x 800a offset 3016
m /x 000b offset 3024
m /x 800b offset 3032
m /x 000c offset 3040
m /x 800c offset 3048
m /x 000d offset 3056
m /x 800d offset 3064
m /x 000e offset 3072
m /x 800e offset 3080
m /x 000f offset 3088
m /x 4001 offset 2738
m /x 4001 offset 2746
m /x 4001 offset 2754
m /x 4001 offset 2762
m /x 4001 offset 2770
m /x 4001 offset 2778
m /x 4001 offset 2786
m /x 4001 offset 2794
m /x 4001 offset 2802
m /x 4001 offset 2810
m /x 4001 offset 2818
m /x 4001 offset 2826
m /x 4001 offset 2834
m /x 4001 offset 2842
m /x 4001 offset 2850
m /x 4001 offset 2858
m /x 4001 offset 2866
m /x 4001 offset 2874
m /x 4001 offset 2882
m /x 4001 offset 2890
m /x 4001 offset 2898
m /x 4001 offset 2906
m /x 4001 offset 2914
m /x 4001 offset 2922
m /x 4001 offset 2930
m /x 4001 offset 2938
m /x 4001 offset 2946
m /x 4001 offset 2954
m /x 4001 offset 2962
m /x 4001 offset 2970
m /x 4001 offset 2978
m /x 4001 offset 2986
m /x 4001 offset 2994
m /x 4001 offset 3002
m /x 4001 offset 3010
m /x 4001 offset 3018
m /x 4001 offset 3026
m /x 4001 offset 3034
m /x 4001 offset 3042
m /x 4001 offset 3050
m /x 4001 offset 3058
m /x 4001 offset 3066
m /x 4001 offset 3074
m /x 4001 offset 3082
m /x 4001 offset 3090
m /x 8100 offset 2740
m /x 8800 offset 2748
m /x 9100 offset 2756
m /x 9800 offset 2764
m /x a100 offset 2772
m /x a800 offset 2780
m /x b100 offset 2788
m /x b800 offset 2796
m /x c100 offset 2804
m /x c800 offset 2812
m /x d100 offset 2820
m /x d800 offset 2828
m /x e100 offset 2836
m /x e800 offset 2844
m /x f100 offset 2852
m /x f800 offset 2860
m /x 0201 offset 2868
m /x 8201 offset 2876
m /x 0202 offset 2884
m /x 8202 offset 2892
m /x 0203 offset 2900
m /x 8203 offset 2908
m /x 0204 offset 2916
m /x 8204 offset 2924
m /x 0205 offset 2932
m /x 8205 offset 2940
m /x 0206 offset 2948
m /x 8206 offset 2956
m /x 0207 offset 2964
m /x 8207 offset 2972
m /x 0208 offset 2980
m /x 8208 offset 2988
m /x 0209 offset 2996
m /x 8209 offset 3004
m /x 020a offset 3012
m /x 820a offset 3020
m /x 020b offset 3028
m /x 820b offset 3036
m /x 020c offset 3044
m /x 820c offset 3052
m /x 020d offset 3060
m /x 820d offset 3068
m /x 020e offset 3076
m /x 820e offset 3084
m /x 020f offset 3092
m /x 4001 offset 2742
m /x 4001 offset 2750
m /x 4001 offset 2758
m /x 4001 offset 2766
m /x 4001 offset 2774
m /x 4001 offset 2782
m /x 4001 offset 2790
m /x 4001 offset 2798
m /x 4001 offset 2806
m /x 4001 offset 2814
m /x 4001 offset 2822
m /x 4001 offset 2830
m /x 4001 offset 2838
m /x 4001 offset 2846
m /x 4001 offset 2854
m /x 4001 offset 2862
m /x 4001 offset 2870
m /x 4001 offset 2878
m /x 4001 offset 2886
m /x 4001 offset 2894
m /x 4001 offset 2902
m /x 4001 offset 2910
m /x 4001 offset 2918
m /x 4001 offset 2926
m /x 4001 offset 2934
m /x 4001 offset 2942
m /x 4001 offset 2950
m /x 4001 offset 2958
m /x 4001 offset 2966
m /x 4001 offset 2974
m /x 4001 offset 2982
m /x 4001 offset 2990
m /x 4001 offset 2998
m /x 4001 offset 3006
m /x 4001 offset 3014
m /x 4001 offset 3022
m /x 4001 offset 3030
m /x 4001 offset 3038
m /x 4001 offset 3046
m /x 4001 offset 3054
m /x 4001 offset 3062
m /x 4001 offset 3070
m /x 4001 offset 3078
m /x 4001 offset 3086
m /x 4001 offset 3094
```

### 11. dump 相關block
```c
SQL> 
alter system dump datafile 5 block 130;
alter system dump datafile 5 block 3712;
alter system dump datafile 5 block 3713;
alter system dump datafile 5 block 3840;
alter system dump datafile 5 block 3841;
select value from v$diag_info where name='Default Trace File';
--/u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_8375.trc

#### alter system dump datafile 5 block 3712;
   0x01400e80  Length: 64     Offset: 0

0:Metadata   1:Metadata   2:FULL   3:FULL
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
   16:FULL   17:FULL   18:FULL   19:FULL
   20:FULL   21:FULL   22:FULL   23:FULL
   24:FULL   25:FULL   26:FULL   27:FULL
   28:FULL   29:FULL   30:FULL   31:FULL
   32:FULL   33:FULL   34:FULL   35:FULL
   36:FULL   37:FULL   38:FULL   39:FULL
   40:FULL   41:FULL   42:FULL   43:FULL
   44:FULL   45:FULL   46:FULL   47:FULL
   48:FULL   49:FULL   50:FULL   51:FULL
   52:FULL   53:FULL   54:FULL   55:FULL
   56:FULL   57:FULL   58:FULL   59:FULL
   60:FULL   61:FULL   62:FULL   63:FULL

#### alter system dump datafile 5 block 3713;
   0x01400ec0  Length: 64     Offset: 0

0:FULL   1:FULL   2:FULL   3:FULL
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
   16:FULL   17:FULL   18:FULL   19:FULL
   20:FULL   21:FULL   22:FULL   23:FULL
   24:FULL   25:FULL   26:FULL   27:FULL
   28:FULL   29:FULL   30:FULL   31:FULL
   32:FULL   33:FULL   34:FULL   35:FULL
   36:FULL   37:FULL   38:FULL   39:FULL
   40:FULL   41:FULL   42:FULL   43:FULL
   44:FULL   45:FULL   46:FULL   47:FULL
   48:FULL   49:FULL   50:FULL   51:FULL
   52:FULL   53:FULL   54:FULL   55:FULL
   56:FULL   57:FULL   58:FULL   59:FULL
   60:FULL   61:FULL   62:FULL   63:FULL

#### alter system dump datafile 5 block 3840;

0x01400f00  Length: 64     Offset: 0

0:Metadata   1:Metadata   2:unformatted   3:unformatted           F00                           |    m /x 000f offset 40
	ub4 offset_ech			@44      	  0x00000a9c
	ub4 ext_ech			@48      	  0x00000000                 80              |   m /x 80 offset 52                               
	ub4 extsize_ech			@56      	  0x00000008             80          |  m /x 80 offset 100
	ub4 lhwm_ech			@104      	  0x00800090            
```

### 13. 整理上面的指令碼
```c
m /x 2d offset 36                                                                       
m /x 000f offset 40                                                                   
m /x 2c offset 48                                                                       
m /x 80 offset 52                                                                      
m /x 80 offset 56                                                                      
m /x 800f offset 60      
m /x 4001 offset 62                                           
m /x 2c offset 68                                                                       
m /x 2c offset 92                                                                    
m /x 02 offset 96                                                                      
m /x 80 offset 100                                                                    
m /x 020f offset 104      
m /x 4001 offset 106     
m /x 2c offset 112                                                                  
m /x 000f offset 124    
m /x 4001 offset 126                                         
m /x 010f offset 128   
m /x 4001 offset 130                                         
m /x 8100 offset 228   
m /x 4001 offset 230                                         
m /x 010f offset 236    
m /x 4001 offset 238                                     
m /x 8100 offset 240   
m /x 4001 offset 242                                         
m /x 2d offset 264                                       
m /x  d135 offset 272                                                    ```

### 14. 根據exts計算blocks指令碼
```c
set serveroutput on
DECLARE
   exts number;
   blocks number;
  BEGIN
   exts:=45;
   blocks:=0;
  	for i in 1..exts loop
  		if(i<=16) then blocks := blocks + 8;
  		elsif(i<=63+16) then blocks := blocks + 128;
  		elsif(i<=120+63+16) then blocks := blocks + 1024;
  		else 
  			blocks := blocks + 8192;
  		end if;
  	end loop;
  dbms_output.put_line('Total blocks: '||to_char(blocks));
  END;
  /

Total blocks: 3840  --> F00
```

### 15. 修改ext_1,datadba值  b4 datadba_am[0]
```c
@2740      	  0x01400081
m /x 83 offset 2740
```

### 16. 檢查修復結果
```c
SQL> alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1;
System altered.

SQL> 
System altered.

SQL>

COUNT(1)
----------
   1000000

SQL>
```

深入解析：段頭塊損壞bbed異常恢復

相關文章