Oracle資料庫壞塊的恢複

標籤：

類比資料塊壞塊：

    對於發生資料區塊不一致的資料區塊，如果當前資料庫有備份且處于歸檔模式，那麼就可以利用rman工具資料區塊恢複功能 對資料區塊進行恢複，這種方法最簡單有效，而且可以在資料檔案線上時進行，不會發生資料丟失。對於被有備份的資料庫 發生資料區塊損壞，可能會發生資料的丟失或資料不丟失，這要根據發生壞塊的所在的對象決定的，如索引塊發生損壞，資料 就不會丟失，重建索引就可以了，發生資料丟失的多發生在表或分區表資料區塊上。

 

1.不丟資料的恢複方法

---使用rman工具的blockrecover blockrecover datafile xx block xx;--修複單個壞塊blockrecover corruption list;--修複全部壞塊SQL> select * from livan.test;select * from livan.test                    *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 12)ORA-01110: data file 6: ‘/u02/app/oradata/PSDB/livan_tbs01.dbf‘

[[email protected] u02]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 5 17:02:23 2015Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: PSDB (DBID=1410134833)RMAN> blockrecover datafile 6 block 12;Starting blockrecover at 05-FEB-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=142 devtype=DISKchannel ORA_DISK_1: restoring block(s)channel ORA_DISK_1: specifying block(s) to restore from backup setrestoring blocks of datafile 00006channel ORA_DISK_1: reading from backup piece /u02/PSDB_BACKUP/full_PSDB_870868610channel ORA_DISK_1: restored block(s) from backup piece 1piece handle=/u02/PSDB_BACKUP/full_PSDB_870868610 tag=TAG20150205T115650channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01starting media recoverymedia recovery complete, elapsed time: 00:00:03Finished blockrecover at 05-FEB-15

[[email protected] u02]$ sqlplus ‘/as sysdba‘SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 17:04:15 2015Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from livan.test;        ID NAME---------- ------------------------------         1 beijing         2 shanghai         3 shandong

如果壞塊上的表最近都沒有更新，還可以利用bbed的copy命令來從一個最近的備份中copy過來一個資料區塊恢複，具體不示範。

 

2.有可能存在資料丟失的恢複（在沒有備份沒有歸檔的情況下）

---使用者表資料損毀

<1>正常情況下資料條目數

SQL> select count(*) from test;  COUNT(*)----------     50604

 

<2>製作一個壞塊

select rowid,dbms_rowid.rowid_relative_fno(rowid) rel_fno,dbms_rowid.rowid_block_number(rowid) blockno,dbms_rowid.rowid_row_number(rowid) rownofrom livan.test;        BBED> set dba 6,76        DBA             0x0180004c (25165900 6,76)BBED> d /v dba 6,76 offset 0 File: /u02/app/oradata/PSDB/livan_tbs01.dbf (6) Block: 76      Offsets:    0 to  127  Dba:0x0180004c------------------------------------------------------- 06a20000 4c008001 d3220800 00000104 l .?.L...?...... b8510000 01000000 ddce0000 b4220800 l 窺......菸..?.. 00000000 03003201 41008001 ffff0000 l ......2.A....... 00000000 00000000 00000000 00800000 l ................ b4220800 00000000 00000000 00000000 l ?.............. 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00019200 l ................ <16 bytes per line>BBED> modify /x 12345 dba 6,76 offset 0 File: /u02/app/oradata/PSDB/livan_tbs01.dbf (6) Block: 76               Offsets:    0 to  127           Dba:0x0180004c------------------------------------------------------------------------ 01234500 4c008001 d3220800 00000104 b8510000 01000000 ddce0000 b4220800  00000000 03003201 41008001 ffff0000 00000000 00000000 00000000 00800000  b4220800 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00019200  <32 bytes per line>BBED> sum playBBED-00202: invalid parameter (play)BBED> sum apply Check value for File 6, Block 76:current = 0xd0fa, required = 0xd0fa     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 # 6, block # 76)ORA-01110: data file 6: ‘/u02/app/oradata/PSDB/livan_tbs01.dbf‘

發現我們第6個檔案第76號資料區塊損壞,報ORA-0178錯誤，我們知掉只要資料庫報ORA-01578錯誤,
說明該資料區塊已經被標識為:"software corrupt"

 

<3>確認壞塊的類型

SQL> select segment_name,partition_name,segment_type,owner,tablespace_name  2  from sys.dba_extents  3  where file_id=&AFN  4  and &bad_block_id between block_id and block_id + blocks-1;Enter value for afn: 6old   3: where file_id=&AFNnew   3: where file_id=6Enter value for bad_block_id: 76old   4: and &bad_block_id between block_id and block_id + blocks-1new   4: and 76 between block_id and block_id + blocks-1SEGMENT_NAME    PARTITION_NAME       SEGMENT_TYPE       OWNER      TABLESPACE_NAME--------------- -------------------- ------------------ ---------- ------------------------------TEST                                 TABLE              LIVAN      LIVAN_TBS

經查我們的資料損毀壞位於我們的使用者表上,無備份資料會丟失。

 

<4>標記壞塊為"software corrupt"

在第2步的時候全表掃描時已經報ORA-01578錯誤,說明該資料區塊已經被標識為:"software corrupt"， 正常情況下可以跳過這步。 我們使用dbms_repair包示範標記壞塊為"software corrupt"

使用dbms_repair包可參考：http://blog.itpub.net/8494287/viewspace-1357457/

 

--利用dbms_repair包必須先建立repair table兩個表：

SQL> begin  2  dbms_repair.admin_tables(  3  table_name=>‘REPAIR_TABLE‘,  4  table_type=>dbms_repair.repair_table,  5  action=>dbms_repair.create_action,  6  tablespace=>‘LIVAN_TBS‘);     7  end;  8  /PL/SQL procedure successfully completed.SQL>  col object_name for a20SQL> select owner,object_name,object_type  2  from dba_objects  3  where object_name like ‘%REPAIR_TABLE%‘;OWNER                          OBJECT_NAME          OBJECT_TYPE------------------------------ -------------------- -------------------SYS                            REPAIR_TABLE         TABLESYS                            DBA_REPAIR_TABLE     VIEW

 

--再建立orphan key table

SQL> begin  2  dbms_repair.admin_tables(  3  table_type=>dbms_repair.orphan_table,  4  action=>dbms_repair.create_action,  5  tablespace=>‘LIVAN_TBS‘);     6  end;  7  /PL/SQL procedure successfully completed.SQL> select owner,object_name,object_type  2  from dba_objects  3  where object_name like ‘%ORPHAN_KEY_TABLE%‘;OWNER                          OBJECT_NAME          OBJECT_TYPE------------------------------ -------------------- -------------------SYS                            ORPHAN_KEY_TABLE     TABLESYS                            DBA_ORPHAN_KEY_TABLE VIEW

 

--檢查對象，檢查結果會放到我們之前建立的repair_table中

SQL> set serveroutput onSQL> declare  2  rpr_count int;  3  begin  4  rpr_count:=0;  5  dbms_repair.check_object(  6  schema_name=>‘LIVAN‘,  7  object_name=>‘TEST‘,  8  repair_table_name=>‘REPAIR_TABLE‘,  9  corrupt_count=>rpr_count);   10  dbms_output.put_line(‘repair count:‘||to_char(rpr_count)); 11  end; 12  /repair count:1PL/SQL procedure successfully completed.

檢查出有1個壞塊

 

--檢查校正的壞塊結果

SQL> select object_name,block_id,corrupt_type,marked_corrupt,  2  corrupt_description,repair_description  3  from repair_table;OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION---------- ---------- ------------ ---------- --------------- ------------------------------TEST               76         6148 TRUE                       mark block software corrupt

我們知道當marked_corrupt為TRUE時，標識這個資料區塊已經被標識過software corrupt

 

---標識壞塊為software corrupt（重新示範一下）

SQL> declare  2  fix_count int;  3  begin  4  fix_count:=0;  5  dbms_repair.fix_corrupt_blocks(  6  schema_name=>‘LIVAN‘,  7  object_name=>‘TEST‘,  8  object_type=>dbms_repair.table_object,  9  repair_table_name=>‘REPAIR_TABLE‘, 10  fix_count=>fix_count); 11  dbms_output.put_line(‘fix count:‘||to_char(fix_count)); 12  end; 13  /fix count:0PL/SQL procedure successfully completed.

 

--再次檢查，因為已經被標誌為software corrupt，所以在此標誌也沒什麼變化

SQL> select object_name,block_id,corrupt_type,marked_corrupt,  2  corrupt_description,repair_description  3  from repair_table;OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION---------- ---------- ------------ ---------- --------------- ------------------------------TEST               76         6148 TRUE                       mark block software corrupt

未被標誌為oftware corrupt ，marked_corrupt列會顯示FALSE

 

<5>檢查其他關聯對象

檢查有多少個索引項目指向了壞塊的記錄

SQL> select index_name from dba_indexes  2  where table_name in (select distinct object_name from repair_table);INDEX_NAME------------------------------INDEX_TEST

查詢發現有一個索引指向這個壞塊

 

--檢查都有多少索引資料

SQL> set serveroutput onSQL> declare  2  key_count int;  3  begin  4  key_count:=0;  5  dbms_repair.dump_orphan_keys(  6  schema_name=>‘LIVAN‘,  7  object_name=>‘INDEX_TEST‘,  8  object_type=>dbms_repair.index_object,  9  repair_table_name=>‘REPAIR_TABLE‘, 10  orphan_table_name=>‘ORPHAN_KEY_TABLE‘, 11  key_count=>key_count); 12  dbms_output.put_line(‘orphan key count:‘||to_char(key_count)); 13  end; 14  /orphan key count:146PL/SQL procedure successfully completed.SQL> select index_name,count(*) from orphan_key_table  2  group by index_name;INDEX_NAME                       COUNT(*)------------------------------ ----------INDEX_TEST                            146

可以看到有146條資料指向壞塊

 

<6>使用dbms_repair.skip_corrupt_blocks或10231事件方式跳過壞塊

SQL> select count(*) from livan.test;select count(*) from livan.test                           *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 76)ORA-01110: data file 6: ‘/u02/app/oradata/PSDB/livan_tbs01.dbf‘SQL> begin  2  dbms_repair.skip_corrupt_blocks(  3  schema_name=>‘LIVAN‘,  4  object_name=>‘TEST‘,  5  object_type=>dbms_repair.table_object,  6  flags=>dbms_repair.skip_flag);  7  end;  8  /PL/SQL procedure successfully completed.SQL> select count(*) from livan.test;  COUNT(*)----------     50458

可以看到當執行完dbms_repair.skip_corrupt_blocks資料可以正常訪問了，只是統計出來的資料比原先
少了146條(50604-50458),也就是我們壞塊上的資料沒有統計，被跳過了。

 

---使用10231事件跳過

SQL> select count(*) from livan.test;  COUNT(*)----------     50458SQL> begin  2  dbms_repair.skip_corrupt_blocks(  3  schema_name=>‘LIVAN‘,  4  object_name=>‘TEST‘,  5  object_type=>dbms_repair.table_object,  6  flags=>dbms_repair.noskip_flag);  7  end;  8  /PL/SQL procedure successfully completed.SQL> select count(*) from livan.test;select count(*) from livan.test                           *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 76)ORA-01110: data file 6: ‘/u02/app/oradata/PSDB/livan_tbs01.dbf‘SQL> alter session set events ‘10231 trace name context forever,level 10‘; Session altered.SQL> select count(*) from livan.test;  COUNT(*)----------     50458          SQL> alter session set events ‘10231 trace name context off‘;Session altered.  

 

<7>使用CTAS方式重建表及索引

SQL> create table test_bak as select * from test;Table created.SQL> create index idx_test_bak on test_bak(object_id);Index created.--重建索引語句SQL> alter index index_test rebuild online;Index altered.

 

<8>使用重建對象的freelists方式修複原表

使用這種方式防止壞塊以後被加入到freelists中 注意這個方法只適用於段空間手動管理的資料表空間(SEGMENT SPACE MANAGEMENT MANUAL), 否則會報ORA-10614: Operation not allowed on this segment 錯誤

SQL> begin  2  dbms_repair.rebuild_freelists(  3  schema_name=>‘LIVAN‘,  4  object_name=>‘TEST‘,  5  object_type=>dbms_repair.table_object);  6  end;  7  /begin*ERROR at line 1:ORA-10614: Operation not allowed on this segmentORA-06512: at "SYS.DBMS_REPAIR", line 401ORA-06512: at line 2

 

<9>壞塊中的資料

  如果壞塊中的資料不可丟失，只能嘗試其他方法從恢複壞塊內容，這其中也有一些第三方付費工具可使用， 也可進行以下嘗試：

*嘗試從索引內容中恢複出索引列的內容

*嘗試使用logminer，從日誌中挖掘

*聯絡Oracle Support，會有些工具解釋資料區塊中的內容。

Oracle資料庫壞塊的恢複