模擬損壞

#獲取表的ROWID_BLOCK_NUMBER和rowid_relative_fno

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from def.d;

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

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

                                 131                                    5

                                 131                                    5

                                 131                                    5

                                 131                                    5

                                 131                                    5

#透過dba_segments 查詢表的塊資訊

SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='DEF' and segment_name='D';

OWNER                          SEGMENT_NAME         HEADER_FILE HEADER_BLOCK     BLOCKS

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

DEF                            D                              5          130          8

RMAN> recover datafile 5 block 131 clear;

Starting recover at 06-MAY-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1154 device type=DISK

Finished recover at 06-MAY-22

重新整理oracle快取

SQL> alter system flush buffer_cache;

System altered.

測試

SQL> select * from def.d;

select * from def.d

                  *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 131)

ORA-01110: data file 5: '/tmp/soft/app/oracle/oradata/WLH/datafile/bb01.dbf'

方法一（blockrecover）（啟用歸檔的情況）（壞塊修復）

1.查詢壞塊

找壞塊方法一、

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

         5        131          1            1003772 CHECKSUM

根據file_id和block_id查詢壞塊資料那一張段（表）(根據file_id和block_id)(我個人沒查出來，發現表損壞後查不出來)

SQL> select tablespace_name,segment_type,owner,segment_name,BLOCKS,BLOCK_ID from dba_extents where file_id=5 and BLOCK_ID=131;

找壞塊方法二、

時間重組的情況先檢視全庫，時間不足針對性檢視資料檔案或者表空間

backup validate check logical database;（全部）

backup validate check logical datafile 1;（檔案號）

backup validate check logical tablespace TT;（表空間）

找壞塊方法三、

dbv file=/tmp/soft/app/oracle/oradata/WLH/datafile/bb01.dbf userid=system/oracle

2.blockrecover修復

RMAN> blockrecover datafile 5 block 131;

Starting recover at 06-MAY-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: reading from backup piece /oracle/bak/aaa_WLH_1_1_1103974269_20220506

channel ORA_DISK_1: piece handle=/oracle/bak/aaa_WLH_1_1_1103974269_20220506 tag=TAG20220506T113109

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 06-MAY-22

例子：

RMAN> blockrecover datafile 6 block 133;  -- 單塊修復

RMAN> blockrecover corruption list;  -- 對檢視中所有壞塊修復

3.測試，表可用

SQL> select * from def.d;

        ID NAME

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

         2 abc

         2 abc

         2 abc

         2 abc

         2 abc

方法二（未啟用歸檔）

檢查壞塊所在物件（用ROWID_BLOCK_NUMBER和rowid_relative_fno）

SELECT tablespace_name,  

       segment_type,  

       owner,  

       segment_name,  

       partition_name  

  FROM dba_extents  

 WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER                SEGMENT_NAME         PARTITION_NAME

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

BB                             TABLE              DEF                  D

 如果壞塊所在物件為索引，直接進行刪除重建可以解決；

 如果壞塊所在物件為表，檢查能否透過日常匯出備份或業務恢復。

如果是索引

1.rebuild索引 （方法一）

alter index IDX_YHQT6_ID rebuild;

不行的話再analyze分析

analyze table YHQTEST_6 validate structure cascade online;

2.刪除再建（方法二）

獲取ddl（索引）

'TABLE','表名','使用者名稱'

SET SERVEROUTPUT ON 

SET LINESIZE 1000 

SET FEEDBACK OFF 

set long 99999           

set pagesize 4000   

SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_YHQT6_ID','SYS') FROM DUAL;

刪索引，重建索引

drop index IDX_YHQT6_ID;

CREATE INDEX "SYS"."IDX_YHQT6_ID" ON "SYS"."YHQTEST_6" ("OBJECT_ID")

如果為表

1) 新建repair table

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => '&TABLESPACE_NAME');

END;

 

2) 檢查壞塊並記錄到repair table（不是分割槽表的話，那行刪掉）

set serveroutput on

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => '&OWNER',

OBJECT_NAME => '&TABLE',

partition_name => '&PARTITION_NAME',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

corrupt_count => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END; 

 

SQL> Select * from REPAIR_TABLE;

 OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID   BLOCK_ID CORRUPT_TYPE SCHEM OBJECT_NAM BASEOBJECT PARTITION_ CORRUPT_DE REPAIR_DESCRIPTION             MARKED_COR CHECK_TIM FIX_TIMES REFORMAT_

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

     87355             6                5        131         6148 DEF   D                                           mark block software corrupt    TRUE       06-MAY-22

3) 標記壞塊並設定為查詢跳過（不是分割槽表的話，那行刪掉）

DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => '&OWNER',

OBJECT_NAME=> '&TABLE',

partition_name => '&PARTITION_NAME',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

 

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => '&OWNER',

OBJECT_NAME => '&TABLE',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.skip_flag);

END;

/

4) 新建其他表，改名（CTAS）

create table new_table_name as select * from old_table_name