[RMAN] Corrupt block 복구

1. Corrupt block 발생시 복구 절차

Oracle 데이터베이스 운영 중 비 정상적인 OS 재부팅 및 패닉에 의해 간혹 block corruption이 발생한다.

아래는 절차는 corrupt block 발생시 RMAN 백업본을 이용한 복구 방법이다.

DB 인스턴스 기동 상태에서 복구 가능하다.

 

--dbv 수행 Script (Physical 오류 점검)
SELECT 'dbv file='||d.file_name||' blocksize='||t.block_size|| ' feedback=1000 logfile='||substr(file_name,instr(file_name,'/',-1,1)+1)||'.'||file_id||'.log'
  FROM dba_data_files d, dba_tablespaces t
 WHERE d.tablespace_name = t.tablespace_name
 ORDER BY d.file_id DESC
;
 
--Corrupt block 확인
SELECT * FROM v$database_block_corruption
;
 
--Corrupt block 오브젝트 확인(file_id + block_id)
SELECT segment_name, segment_type, owner 
  FROM dba_extents 
 WHERE file_id=8 
   AND 3216058 BETWEEN block_id AND block_id + blocks-1
;
 
--Corrupt block 확인
SELECT e.owner
     , e.segment_type
     , e.segment_name
     , e.partition_name
     , c.file#
     , greatest(e.block_id, c.block#) s_blk#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) - greatest(e.block_id, c.block#)+1 blk_corrupt
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks-1 
   AND e.block_id + e.blocks-1 >= c.block#
 UNION ALL
SELECT s.owner
     , s.segment_type
     , s.segment_name
     , s.partition_name
     , c.file#
     , header_block s_blk#
     , header_block e_blk#
     , 1 blk_corrupt
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks-1
 UNION ALL
SELECT null owner
     , null segment_type
     , null segment_name
     , null partition_name
     , c.file#
     , greatest(f.block_id, c.block#) s_blk#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)-greatest(f.block_id, c.block#)+1 blk_corrupt
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks-1
   AND f.block_id + f.blocks-1 >= c.block#
 ORDER BY file#, s_blk#
;
 
--RMAN block recover 수행
$ rman target /  
 
run 
{
    allocate channel ch01 type 'sbt_tape';
    recover datafile 8 block 3216058;
    release channel ch01;
}