BLOCK CORRUPTION (ORA-1578) 처리 (ORACLE 8I NEW FEATURE)
========================================================

PURPOSE
-------
Block Corruption의 처리 방안에 대해 알아본다.


Problem Description
-------------------

block corruption 시 10210, 10211,10231 의 event 를 사용해서
해당 block 을 skip 할 수도 있지만 V8.1 이상에서는

dbms_repair.fix_corrupt_blocks ,
dbms_repair.skip_corrupt_block 를 이용하여

corrupt가 발생한 block을 detect하고 skip, 또는 repair해주는 방안이
제시되고 있다.


Workaround
----------


Solution Description
--------------------

- 먼저 detecting 을 위해 db_block_checking =true 를 init.ora 에 set
- dbms_repair 의 package 를 사용하는데 이 package 는 dbmsrpr.sql,
prvtrpr.plb를 수행한다 .

- sys 로 접속하여 package 를 실행한다.

다음의 예제를 살펴보자

T1 테이블에 corrupt 된 block 이 있다고 가정한다.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ---------
COL1 NOT NULL NUMBER(38)
COL2 CHAR(512)


SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file

이때 ANALYZE로 부터 발생된 trace file 에 corrupt 된 block 에 3 row 의
(nrows = 3) data 가 있음을 알수 있다고 가정하자.


DBMS_REPAIR.ADMIN_TABLES (repair and orphan key)
================================================

ADMIN_TABLES 은 table 을 위한 repair table과,인덱스를 위한 orphan key
tables을 제공한다.


SQL> @adminCreate
SQL> connect sys/change_on_install
Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2 begin
3 -- Create repair table
4 dbms_repair.admin_tables (
5 -- table_name => 'REPAIR_TABLE',
6 table_type => dbms_repair.repair_table,
7 action => dbms_repair.create_action,
8 tablespace => 'USERS'); -- default TS of SYS if not specified
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_name like '%REPAIR_TABLE';

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE


SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2 begin
3 -- Create orphan key table
4 dbms_repair.admin_tables (
5 table_type => dbms_repair.orphan_table,
6 action => dbms_repair.create_action,
7 tablespace => 'USERS'); -- default TS of SYS if not specified
8 end;
9 /

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 DBA_ORPHAN_KEY_TABLE VIEW
SYS ORPHAN_KEY_TABLE TABLE




DBMS_REPAIR.CHECK_OBJECT
=========================

CHECK_OBJECT procedure 는 기술된 object를 check 하고, repair 를 위한 정보를 수집하기 위함이다.

SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2 rpr_count int;
3 begin
4 rpr_count := 0;
5 dbms_repair.check_object (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
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: 1

PL/SQL procedure successfully completed.

SQL> desc repair_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE

SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2 corrupt_description, repair_description
3 from repair_table;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1 3 1 FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
mark block software corrupt




Data Extraction
===============

repair table에 의하면 file 6 ,block 3 에 corrupt 이 났음을 알수 있다
그러나 아직 이 block 은 corrupt 로 mark 되어 있지 않으므로 필요 data 를
추출하여야 한다.


1. ALTER SYSTEM DUMP (nrows = 3) 에 의해 block안에 있는 row수를 결정한다.
2. corrupt object를 select 하여 가능한 정보를 추출한다.


SQL> -- The following query can be used to salvage data from a corrupt block.
SQL> -- Creating a temporary table facilitates data insertion.

SQL> create table temp_t1 as
2 select * from system.t1
3 where dbms_rowid.rowid_block_number(rowid) = 3
4 and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;

Table created.

SQL> select col1 from temp_t1;

COL1
----------
2
3




DBMS_REPAIR.FIX_CORRUPT_BLOCKS (ORA-1578)
============================================

FIX_CORRUPT_BLOCKS procedure는 repair table 의 정보를 이용하여 corrupt
blocks 을 fix 한다
그러나 아직 full table scan 시 여전히 error 가 발생한다
SQL> declare
2 fix_count int;
3 begin
4 fix_count := 0;
5 dbms_repair.fix_corrupt_blocks (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
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: 1

PL/SQL procedure successfully completed.

SQL> select object_name, block_id, marked_corrupt
2 from repair_table;

OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1 3 TRUE

SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'




DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================

DUMP_ORPHAN_KEYS는 corrupt data 에 해당하는 index 를 나타내 준다


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

INDEX_NAME
------------------------------
T1_PK

SQL> @dumpOrphanKeys
SQL> set serveroutput on
SQL>
SQL> declare
2 key_count int;
3 begin
4 key_count := 0;
5 dbms_repair.dump_orphan_keys (
6 schema_name => 'SYSTEM',
7 object_name => 'T1_PK',
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: 3
PL/SQL procedure successfully completed.

SQL> desc orphan_key_table
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE

SQL> select index_name, count(*) from orphan_key_table
2 group by index_name;

INDEX_NAME COUNT(*)
------------------------------ ----------
T1_PK 3

Note: orphan key table의 index 는 다시 rebuild 되어야 한다.



DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
===============================

SKIP_CORRUPT_BLOCKS 은 table 과 index 의 corrupt block 을 skip 하는 것을 enable/disable 을 실시한다.


Suggestion: SKIP_CORRUPT_BLOCKS 가 enabled되면 orphan key table의 모든
index 는 모두 rebuild 되어야 한다. ( all index associated with object
if DUMP_ORPHAN_KEYS was omitted).

SQL> @skipCorruptBlocks
SQL> declare
2 begin
3 dbms_repair.skip_corrupt_blocks (
4 schema_name => 'SYSTEM',
5 object_name => 'T1',
6 object_type => dbms_repair.table_object,
7 flags => dbms_repair.skip_flag);
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> select table_name, skip_corrupt from dba_tables
2 where table_name = 'T1';

TABLE_NAME SKIP_COR
------------------------------ --------
T1 ENABLED

SQL> -- rows in corrupt block skipped, no errors on full table scan
SQL> select * from system.t1;

COL1 COL2
--------------------------------------------------------------------------------
4 dddd
5 eeee

--> Notice the pk index has not yet been corrected.

SQL> insert into system.t1 values (1,'aaaa');
insert into system.t1 values (1,'aaaa')
*
SQL> select * from system.t1 where col1 = 1;

no rows selected




DBMS_REPAIR.REBUILD_FREELISTS
===============================

REBUILD_FREELISTS rebuilds freelists for the specified object.

SQL> declare
2 begin
3 dbms_repair.rebuild_freelists (
4 schema_name => 'SYSTEM',
5 object_name => 'T1',
6 object_type => dbms_repair.table_object);
7 end;
8 /

PL/SQL procedure successfully completed.




Rebuild Index
=============

Note: Every index identified in the orphan key table should be rebuilt to
ensure consistent results.

SQL> alter index system.t1_pk rebuild online;

Index altered.

SQL> insert into system.t1 values (1, 'aaaa');

1 row created.

SQL> select * from system.t1;

COL1 COL2
--------------------------------------------------------------------------------
4 dddd
5 eeee
1 aaaa