【Oracle坏块】坏块类型：CORRUPT
一、模拟CORRUPT
模拟环境参考文章【Oracle坏块】Oracle坏块介绍 & 坏块类型：ALL ZERO
模拟CORRUPT
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> p type_kcbh ub1 type_kcbh @0 0x06 BBED> d /v offset 0 count 30 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 0 to 29 Dba:0x05800083 ------------------------------------------------------- 06a20000 83008005 09d52d00 00000104 l .¢.......ӭ..... a7390000 01000000 a71d0100 09d5 l §9......§.... <16 bytes per line> BBED> modify /x ff offset 0 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 0 to 29 Dba:0x05800083 ------------------------------------------------------------------------ ffa20000 83008005 09d52d00 00000104 a7390000 01000000 a71d0100 09d5 <32 bytes per line> BBED> sum apply Check value for File 22, Block 131: current = 0x395e, required = 0x395e
查看是否出现坏块
SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from test; select count(*) from test * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 22, block # 131) ORA-01110: data file 22: '/u01/app/oracle/oradata/T1/pdb/test.dbf' SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 22 131 1 0 CORRUPT 3
二、恢复方法
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> set offset 0 OFFSET 0 BBED> map /v File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Dba:0x05800083 ------------------------------------------------------------ BBED-00400: invalid blocktype (255) BBED> p type_kcbh BBED-00400: invalid blocktype (255)
使用BEED命令会报错无效的块类型，因为坏块是由于块类型错误造成的
利用dump查找出type_kcbh的值，type_kcbh位于块头offset 0，第一行前两位字节
BBED> d /v offset 0 count 32 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 0 to 31 Dba:0x05800083 ------------------------------------------------------- ffa20000 83008005 09d52d00 00000104 l .¢.......ӭ..... 5e390000 01000000 a71d0100 09d52d00 l ^9......§....ӭ. <16 bytes per line>
至于ff要修改成什么值，需要查看到底损坏的是什么类型的数据
SQL> select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=&fileid and &blockid between block_id and block_id + blocks -1; Enter value for fileid: 22 Enter value for blockid: 131 old 1: select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=&fileid and &blockid between block_id and block_id + blocks -1 new 1: select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=22 and 131 between block_id and block_id + blocks -1 TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME ------------------------------ ------------------ ------------------------------ ------------------------------------------------------------ TEST TABLE SYS TEST
损坏的为表数据而非索引数据（索引的话可以删除重建索引），根据下列表格，可以判断TYPE为06是表格。
| ID | Type |
| 01 | Undo segment block |
| 02 | Undo data block |
| 03 | Save undo header |
| 04 | Save undo data block |
| 05 | Data segment header(temp,index,data and so on) |
| 06 | KTB managed data block (with ITL) --正常表数据 |
| 07 | Temp table data block (no ITL) --临时表数据 |
| 08 | Sort Key |
| 09 | Sort Run |
| 10 | Segment free list block |
| 11 | Data file header |
修改TYPE为06
BBED> modify /x 06 offset 0 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 0 to 31 Dba:0x05800083 ------------------------------------------------------------------------ 06a20000 83008005 09d52d00 00000104 5e390000 01000000 a71d0100 09d52d00 <32 bytes per line> BBED> sum apply Check value for File 22, Block 131: current = 0x39a7, required = 0x39a7
刷新数据库缓存并查看数据
SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from test; COUNT(*) ---------- 20000 SQL> select * from v$database_block_corruption; no rows selected
2、备份恢复
3、DBMS_REPAIR包（丢数据）
4、CTAS复制方法（丢数据）
