DBMS_REPAIR패키지 사용하기

작성자: 강명규
OS: Linux 2.4.18
Oralce: Oracle EE 8.1.7

DBMS_REPAIR패키지에는 테이블과 인덱스내의 망가진 블럭(corrupt block --;)을 찾아서 수정해주는
data corruption repair 프로시저를 가지고 있다. 약간 low level적인 작업이므로 DBA만을 위한
패키지이므로 일반사용자라면 이 글을 볼 필요없겠다. 따라서 sys계정으로 작업하게 된다.
그리고 이 패키지는 8i이상에서만 사용할 수 있으므로 하위버전이라면 역시 이 글을 볼 필요없겠다.


db_block_checksum과 db_block_checking

db_block_checksum
체크섬(checksum)이라는 것은 블럭내에 저장된 총 바이트(bytes)를 계산한 값이다.
db_block_checksum패러미터는 DBWn, direct loader가 체크섬을 계산할 것인지 결정한다.
true로 설정하면, 디스크에 write시 모든 데이터블럭의 cache헤더에 체크섬을 저장한다.
checksum은 블럭을 read할때 검증되어진다.
false로 설정하면, system테이블스페이브에 대해서만 DBWn가 체크섬을 계산한다.

db_block_checking
true로 설정시, 모든 데이터블럭에 대한 블럭체킹을 실시한다.
false로 설정시, system테이블스페이스를 제외한 모든 데이터블럭의 블럭체킹을 하지 않는다.
오라클은 블럭내의 데이터를 읽을때(walking through), consistent한지 파악함으로써 블럭을 체크하게된다.

위 둘의 차이점은 체크섬을 저장하느냐 여부로 따라 판단하는 것이 편할 것이다.

db_block_checking을 패러미터파일에 true로 설정한다.
[oracle@ns bdump]$ cat $ORACLE_HOME/dbs/initdb.ora|grep db_block_checking
db_block_checking=true

[oracle@ns bdump]$ svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area                         13947040 bytes
Fixed Size                                          73888 bytes
Variable Size                                     5312512 bytes
Database Buffers                                  8388608 bytes
Redo Buffers                                       172032 bytes
Database mounted.
Database opened.
SVRMGR> @?/rdbms/admin/dbmsrpr.sql -- dbms_repair 패키지 spec생성
Statement processed.
SVRMGR> @?/rdbms/admin/prvtrpr.plb -- dbms_repair 패키지 body생성
Statement processed.
Statement processed.
SVRMGR> quit
Server Manager complete.


손상된 data block을 만들기 위한 사전작업

dbms_repair패키지를 사용하기 위한 테스트로 corrupt block을 만들어야 한다.

SQL> conn system/xxxxxx
Connected.
SQL> create tablespace ts_corrupt_test
  2  datafile '/u01/app/oracle/oradata/db/ts_corrupt_test01.dbf' size 500k;

Tablespace created.

SQL> conn kang/xxxxxx
Connected.
SQL> create table test tablespace ts_corrupt_test
  2  as
  3  select table_name, owner, table_type from all_catalog;

Table created.

primary key생성을 위한 중복행 제거
SQL> delete from test a where a.rowid < (select max(rowid) from test b where b.table_name= a.table_name);

302 rows deleted.

SQL> alter table test
  2  add constraint test_pk primary key(table_name)
  3  using index tablespace ts_corrupt_test;

Table altered.

SQL> commit;

Commit complete.

SQL> select count(1) from test;

  COUNT(1)
----------
      1232

test라는 테이블이 존재하는 데이터파일에 block corruption이 발생하게 만든다.
db shutdown후 데이터파일을 hexa editor로 자~알 건들여서 startup하면
오라클이 에러를 뱉는다. 이땐 그냥 recover database하면 대개 open할 수 있다.
그럼 corruption block이발생한다. 다시 말하지만, 자~알 건드려야 corruption block이 발생한다.
나름대로 노하우를 가지고 있어야 할 것이다.

SVRMGR> startup
ORACLE instance started.
Total System Global Area                         13947040 bytes
Fixed Size                                          73888 bytes
Variable Size                                     5312512 bytes
Database Buffers                                  8388608 bytes
Redo Buffers                                       172032 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/db/ts_corrupt_test01.dbf'
SVRMGR> recover database;
Media recovery complete.
SVRMGR> alter database open;
Statement processed.
SVRMGR> quit


이제 test테이블을 조회해보면 table scan시 다음과 같은 에러가 남을 볼 수 있다.

SQL> conn kang/xxxxxx
Connected.
SQL> analyze table test validate structure;
analyze table test validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file


SQL>
제대로 block corruption이 발생하게끔 만들었음을 알 수 있다.
이제 dbms_repair패키지를 사용하여 이를 어떻게 처리할지 보겠다.


손상된 data block에 대한 정보를 보관할 테이블,뷰 생성
block corruption이 발생한 곳의 레코드는 조회할 수 없다.
블럭손상에 대한 결과를 저장하기 위한 테이블,뷰을 생성한다.
admin_tables라는 프로시저는 4개의 인수를 갖고 각각의 인수는 다음과 같다.
테이블명(table_name)
테이블타입(table_type)  : ORPHAN_TABLE, REPAIR_TABLE
하고자하는 작업(action) : CREATE_ACTION, PURGE_ACTION, DROP_ACTION
테이블이 생성될 테이블스페이스명(tablespace)

SQL> conn sys/xxxxxx
Connected.
SQL> exec dbms_repair.admin_tables('REPAIR_TABLE',DBMS_REPAIR.REPAIR_TABLE,DBMS_REPAIR.CREATE_ACTION,'USERS');

PL/SQL procedure successfully completed.

위와 같이 해주면 다음과 같이 repair_table 테이블과 dba_repair_table 이라는 뷰가 생성된다.

SQL> select * from tab where tname like '%REPAIR%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DBA_REPAIR_TABLE               VIEW
REPAIR_TABLE                   TABLE

이 놈은 corrupt된 블럭을 가진 테이블의 인덱스를 위한 테이블을 생성한다.
SQL> exec dbms_repair.admin_tables('ORPHAN_KEY_TABLE', DBMS_REPAIR.ORPHAN_TABLE,DBMS_REPAIR.CREATE_ACTION,'USERS');

PL/SQL procedure successfully completed.

SQL> select * from tab where tname like '%ORPHAN%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DBA_ORPHAN_KEY_TABLE           VIEW
ORPHAN_KEY_TABLE               TABLE

SQL>

위에서 보듯이 지정된 테이블이 생성되고, DBA_[지정된 테이블명]으로 된 뷰가 생성된다.
이제 손상된 정보를 저장하기 위한 테이블을 생성했으니 CHECK_OBJECT프로시저를 사용하여
REPAIR테이블에 손상정보를 파악하여 입력되도록 하겠다. 이 프로시저에 대한 인수는 다음과 같다.

schema_name        : 객체를 소유한 스키마
object_name        : 체크할 테이블이나 인덱스
partition_name     : 파티셔닝된 경우에만 해당 파티션을 지정하고 아니면 생략할 것.
object_type        : TABLE_OBJECT, INDEX_OBJECT
repair_table_name  : admin_tables프로시저에서 지정했던 테이블명(repair_table)
flags              : 사용되지 않음
relative_fno       : 블럭범위(block range)지정시 relative file number. 몰라도 되겠다.
block_start        : 블럭범위지정했을 경우, 첫번째 블럭
block_end          : 블럭범위지정했을 경우, 마지막 블럭
corrupt_count      : 설명없어도 알 수 있겠지.. 이것은 out변수이므로 입력이 아니라 출력받는 값이다.

SQL> set serveroutput on
SQL> var num number
SQL> exec dbms_repair.check_object('KANG','TEST',repair_table_name=>'REPAIR_TABLE',corrupt_count=>:corrupt_count);

PL/SQL procedure successfully completed.

SQL> print corrupt_count

CORRUPT_COUNT
-------------
            1

SQL> col corrupt_description format a40
SQL> col repair_description format a40
SQL> set linesize 1000
SQL> select RELATIVE_FILE_ID, BLOCK_ID, CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION, MARKED_CORRUPT from repair_table;

RELATIVE_FILE_ID   BLOCK_ID CORRUPT_DESCRIPTION                      REPAIR_DESCRIPTION                       MARKED_COR
---------------- ---------- ---------------------------------------- ---------------------------------------- ----------
               7          5 Block Checking: DBA = 29360133, Block Ty mark block software corrupt              FALSE
                            pe = KTB-managed data block
                            kdbchk: there are free slots not on the
                            free list
                                    nfree=35


SQL>

위에서 보듯이, 파일7, 블럭5에 corruption이 발생했음을 알 수 있다.
위에서 marked_corrupt컬럼이 FALSE값을 가지고 있으므로, TRUE로 변경하여 corruption을 mark하도록 변경한다.

fix_corrupt_blocks프로시저는 check_object프로시저에 의해 생성된 repair테이블의 데이터를 참조하여,
지정된 objects가 가진 corrupt block을 표시한다.(fix).
이 프로시저는 다음과 같은 인수를 갖는다.

schema_name       :
object_name       : fix할 블럭을 가진 object
partition_name    :
object_type       : TABLE_OBJECT or INDEX_OBJECT
repair_table_name : REPAIR_TABLE
flags             : 사용되지 않음
fix_count         : fix된 블럭수

SQL> var fix_count number
SQL> exec dbms_repair.fix_corrupt_blocks('KANG','TEST',object_type=>dbms_repair.table_object,repair_table_name=>'REPAIR_TABLE', fix_count=> :fix_count);

PL/SQL procedure successfully completed.

SQL> print fix_count

FIX_COUNT
----------
         1

SQL> select * from kang.test;

TABLE_NAME                     OWNER                          TABLE_TYPE
------------------------------ ------------------------------ -----------
DUAL                           PUBLIC                         SYNONYM
..
DBA_SNAPSHOT_REFRESH_TIMES     PUBLIC                         SYNONYM
ALL_SNAPSHOT_REFRESH_TIMES     PUBLIC                         SYNONYM
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 5)
ORA-01110: data file 7: '/u01/app/oracle/oradata/db/ts_corrupt_test01.dbf'



150 rows selected.

SQL>


table scan시 에러가 발생하고 있다.
fix_corrupt_blocks프로시저가 표시해 놓은 corrupt_blocks을 skip_corrupt_blocks프로시저에 의해 skip처리해야 한다.
이 프로시저는 지정한 객체에 대한 table scan이나 index scan시 corrupt block의 skip할 것인가 안할 것인가를 지정한다.


SQL> exec dbms_repair.skip_corrupt_blocks('KANG','TEST',dbms_repair.table_object,dbms_repair.skip_flag);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from kang.test;

TABLE_NAME                     OWNER                          TABLE_TYPE
------------------------------ ------------------------------ -----------
DUAL                           PUBLIC                         SYNONYM
..
CHAINED_ROWS                   KANG                           TABLE
EXCEPTIONS                     KANG                           TABLE

1141 rows selected.


아래에서 보듯이 marked_corrupt컬럼이 true로 변경되었음을 알 수 있다.
SQL> select RELATIVE_FILE_ID, BLOCK_ID, MARKED_CORRUPT from repair_table;

RELATIVE_FILE_ID   BLOCK_ID MARKED_COR
---------------- ---------- ----------
               7          5 TRUE

SQL>

dump_orphan_keys프로시저는 인덱스와 관련된 프로시저이다.
dump_orphan_keys프로시저로 corrupt data block내의 row를 가리키고 있는 인덱스엔트리를 알수있다.

SQL> var key_count number
SQL> begin
  2  dbms_repair.dump_orphan_keys(
  3  schema_name=>'KANG',
  4  object_name=>'TEST_PK',
  5  object_type=>DBMS_REPAIR.INDEX_OBJECT,
  6  repair_table_name=>'REPAIR_TABLE',
  7  orphan_table_name=>'ORPHAN_KEY_TABLE',
  8  key_count=>:key_count);
  9  end;
10  /


SQL> print key_count

KEY_COUNT
----------
        91

SQL> select SCHEMA_NAME, INDEX_NAME, INDEX_ID, TABLE_NAME, KEYROWID from orphan_key_table;

SCHEMA_NAME                    INDEX_NAME                       INDEX_ID TABLE_NAME                     KEYROWID
------------------------------ ------------------------------ ---------- ------------------------------ ----------------------------------------
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABBAA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABBcA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABC4A
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABDIA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABAEA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABAYA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABAsA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABCcA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABA4A
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABC8A
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABDMA
...
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABGgA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABGYA
KANG                           TEST_PK                              3927 TEST                           AAAA9WAAUAAAMABFwA

91 rows selected.

91개의 orphan key가 발견되었으므로 인덱스를 rebuild해준다.
SQL> alter index kang.test_pk rebuild online;

Index altered.


rebuild_freelists는 지정된 object에 대한 freelists를 재생성한다.
인수는 다음과 같다.

schema_name
object_name
partition_name
object_type

SQL> exec dbms_repair.rebuild_freelists('KANG','TEST',object_type=>dbms_repair.table_object);

PL/SQL 처리가 정상적으로 완료되었습니다.

참고문서:Oracle Technical Bulletin No. 11941

Copyleft(C) 명규의 DBAKOREA All rights free