Block Recovery 를 위한 Corruption Block 찾아내기

1. datafile 에 block corruption이 일어 났을 경우

dbv file=’/nas1/backup/oradata1/system01.dbf’ blocksize=8192
dbv file=’/nas1/backup/oradata1/undotbs01.dbf’ blocksize=8192

dbv file=’/restore/oradata/ora8i/system01.dbf’ blocksize=8192
dbv file=’/restore/home3/home3/cti_ts1.dbf’ blocksize=8192
dbv file=’/home1/app/oracle/product/8.1.7/oradata/ora8i/rbs01.dbf’ blocksize=8192

dbv file=’/dbf/hotbackup/backup2/control.19092007065048′ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile01.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile02.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile03.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile04.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile05.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/datafile06.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/indx01.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/indx02.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/indx03.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/indx04.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/sysaux01.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/system01.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/undotbs01.dbf’ blocksize=8192
dbv file=’/dbf/hotbackup/backup2/users01.dbf’ blocksize=8192

DBVERIFY – Verification starting : FILE = /disk1/INDEX/pamridx03.dbf

Block Checking: DBA = 90216039, Block Type = KTB-managed data block
**** actual rows locked by itl 30 = 0 != # in trans. header = 1
**** actual free space credit for itl 30 = 0 != # in trans. hdr = 24
**** actual rows marked deleted = 3 != kdxlende = 28

—- end index block validation

Page 2135655 failed with check code 6401

DBVERIFY – Verification complete
Total Pages Examined : 3840000
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 3176746
Total Pages Failing (Index): 1
Total Pages Processed (Other): 12
Total Pages Empty : 663242
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

2. Block Checking: DBA = 90216039를 이용하여 file#, block#를 찾아낸다.

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS
x NUMBER;
digits# NUMBER;
results NUMBER := 0;
file# NUMBER := 0;
block# NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;
BEGIN
IF upper(imode) = ‘H’ THEN
digits# := length( iblock );
FOR x IN 1..digits# LOOP
cur_digit := upper(substr( iblock, x, 1 ));
IF cur_digit IN (‘A’,’B’,’C’,’D’,’E’,’F’) THEN
cur_digit# := ascii( cur_digit ) – ascii(‘A’) +10;
ELSE
cur_digit# := to_number(cur_digit);
END IF;
results := (results *16) + cur_digit#;
END LOOP;
ELSE
IF upper(imode) = ‘D’ THEN
results := to_number(iblock);
ELSE
dbms_output.put_line(‘H = Hex Input … D = Decimal Input’);
RETURN;
END IF;
END IF;

file# := dbms_utility.data_block_address_file(results);
block# := dbms_utility.data_block_address_block(results);

dbms_output.put_line(‘.’);
dbms_output.put_line( ‘The file is ‘ || file# );
dbms_output.put_line( ‘The block is ‘ || block# );
END;
/

SQL> set serveroutput on
SQL> exec cdba(‘ 130144565′,’d’);

The file is 21
The block is 2135655
The file is 31
The block is 121141

3. 위 file#, block#를 이용하여 어떤 인덱스 혹은 테이블에 문제인지를 찾아낸다.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 31
and 121141 between block_id AND block_id + blocks – 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
—————————— —————— ——————————
SEGMENT_NAME
——————————————————————————–
CTI_IDX_TS INDEX CTI
CT_SERVICE_LOG_NEW_PK

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 22
and &b between block_id AND block_id + blocks – 1;