RMAN修復無效的資料塊(ORA-01578)

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files, or results of SQL queries.

To recover data blocks:

    Obtain the block numbers of the corrupted blocks if you do not already have this information.

    The easiest way to locate trace files and the alert log is to connect SQL*Plus to the target database and execute the following query:

    SQL> SELECT NAME, VALUE
      2  FROM V$DIAG_INFO;

    Start RMAN and connect to the target database.

    Run the RECOVER command to repair the blocks.

    The following RMAN command recovers all corrupted blocks:

    RMAN> RECOVER CORRUPTION LIST;

    You can also recover individual blocks, as shown in the following example:

    RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;
模擬測試(生產環境一定要謹慎操作)
SQL> create tablespace netdata datafile '/data/netdata/netdata01.dbf' size 10M autoextend on next 20M maxsize 1G;

表空間已建立。

SQL> create user netdata identified by netdata default tablespace netdata temporary tablespace temp
  2  ;

使用者已建立。

SQL> grant connect,resource to netdata;

授權成功。

SQL> conn netdata/netdata;
已連線。
SQL> create table test (id int,name varchar2(20));

表已建立。

insert into test
select b.id,a.name from (select 'test' name from dual)a,
(select rownum id from dual connect by level <100000000)b

SQL> commit;

提交完成。


關閉資料庫
並用UE修改資料庫netdata01.dbf檔案,隨便修改幾個字元
資料庫open
SQL> startup;
ORACLE 例程已經啟動。

Total System Global Area  855982080 bytes
Fixed Size            2258040 bytes
Variable Size          692063112 bytes
Database Buffers      155189248 bytes
Redo Buffers            6471680 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL>  select count(*) from netdata.test;
 select count(*) from netdata.test
                              *
第 1 行出現錯誤:
ORA-01578: ORACLE 資料塊損壞 (檔案號 7, 塊號 9020) ORA-01110:
資料檔案 7: '/data/netdata/netdata01.dbf'


SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#      BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
     7     9020           1          0 CHECKSUM
    

RMAN>  blockrecover datafile 7 block 9020;

啟動 recover 於 21-6月 -15
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=400 裝置型別=DISK

通道 ORA_DISK_1: 正在還原塊
通道 ORA_DISK_1: 正在指定要從備份集還原的塊
正在還原資料檔案 00007 的塊
通道 ORA_DISK_1: 正在讀取備份片段 +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t025253_0.276.882931979
通道 ORA_DISK_1: 段控制程式碼 = +FRA/netdata/backupset/2015_06_21/nnndf0_tag20150621t025253_0.276.882931979 標記 = TAG20150621T025253
通道 ORA_DISK_1: 已從備份片段 1 還原塊
通道 ORA_DISK_1: 塊還原完成, 用時: 00:00:07

正在開始介質的恢復
介質恢復完成, 用時: 00:00:08

完成 recover 於 21-6月 -15

RMAN>