오라클 9i R2 block corruption 복구 테스트

OS환경 : Oracle Linux 4.8 (64bit)

DB 환경 : Oracle Database 9.2.0.4

방법 : 오라클 9i R2 block corruption 복구 테스트

오라클 9i에서 아카이브모드지만 아카이브 로그가 모두 삭제된 상태이고

백업본이 없는 상태에서 복구시도 하는 시나리오임

*특정 데이터파일은 복구가 되지 않기때문에 cold backup 후 진행함

cold backup 진행

db 종료

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

콜드백업

$ mkdir -p /oracle/app/oracle/backup
$ cd /oracle/app/oracle/oradata/ORCL/
$ cp -av ./* ../../backup/
`./control01.ctl' -> `../../backup/control01.ctl'
`./control02.ctl' -> `../../backup/control02.ctl'
`./control03.ctl' -> `../../backup/control03.ctl'
`./cwmlite01.dbf' -> `../../backup/cwmlite01.dbf'
`./drsys01.dbf' -> `../../backup/drsys01.dbf'
`./example01.dbf' -> `../../backup/example01.dbf'
`./indx01.dbf' -> `../../backup/indx01.dbf'
`./odm01.dbf' -> `../../backup/odm01.dbf'
`./redo01.log' -> `../../backup/redo01.log'
`./redo02.log' -> `../../backup/redo02.log'
`./redo03.log' -> `../../backup/redo03.log'
`./system01.dbf' -> `../../backup/system01.dbf'
`./temp01.dbf' -> `../../backup/temp01.dbf'
`./test01.dbf' -> `../../backup/test01.dbf'
`./tools01.dbf' -> `../../backup/tools01.dbf'
`./undotbs01.dbf' -> `../../backup/undotbs01.dbf'
`./users01.dbf' -> `../../backup/users01.dbf'
`./xdb01.dbf' -> `../../backup/xdb01.dbf'

db 기동

SQL> startup
ORACLE instance started.
 
Total System Global Area  320308744 bytes
Fixed Size             742920 bytes
Variable Size          285212672 bytes
Database Buffers       33554432 bytes
Redo Buffers             798720 bytes
Database mounted.
Database opened.

데이터파일 확인

SQL> 
set lines 200 pages 1000
col file_name for a60
select file_id, file_name from dba_data_files;
 
   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
     1 /oracle/app/oracle/oradata/ORCL/system01.dbf
     2 /oracle/app/oracle/oradata/ORCL/undotbs01.dbf
     3 /oracle/app/oracle/oradata/ORCL/cwmlite01.dbf
     4 /oracle/app/oracle/oradata/ORCL/drsys01.dbf
     5 /oracle/app/oracle/oradata/ORCL/example01.dbf
     6 /oracle/app/oracle/oradata/ORCL/indx01.dbf
     7 /oracle/app/oracle/oradata/ORCL/odm01.dbf
     8 /oracle/app/oracle/oradata/ORCL/tools01.dbf
     9 /oracle/app/oracle/oradata/ORCL/users01.dbf
    10 /oracle/app/oracle/oradata/ORCL/xdb01.dbf
    11 /oracle/app/oracle/oradata/ORCL/test01.dbf
 
11 rows selected.

db 강제 종료

SQL> shutdown abort
ORACLE instance shut down.

울트라 에딧으로 블록 커럽션 발생

system.dbf 파일을 제외한 데이터파일을 block curruption 시키기 위해 hex code를 수정함

SCP툴을 이용해 windows 로 모든 데이터파일 복사 후 

울트라 에딧등 에디터 프로그램을 이용해 헥사코드를 수정함

내용 중간에 ddddddddddddddddddd, asjddh등의 무작위 문자를 반복적으로 넣어줌

편집 및 저장 후 다시 SCP툴을 이용해 해당 dbf파일을 기존 위치로 복사

아카이브 로그 모두 삭제

$ cd $ORACLE_HOME/dbs
$ ls -ltr ar*
-rw-r-----  1 oracle dba      4608 Oct 14  2019 arch1_137.dbf
-rw-r-----  1 oracle dba  22896128 Oct 14  2019 arch1_136.dbf
-rw-r-----  1 oracle dba 104857088 Nov 13  2019 arch1_138.dbf
-rw-r-----  1 oracle dba 104857088 Dec 13  2019 arch1_139.dbf
-rw-r-----  1 oracle dba 104857088 Jun 11 19:11 arch1_140.dbf
-rw-r-----  1 oracle dba 104857088 Jul  2 23:53 arch1_141.dbf
-rw-r-----  1 oracle dba 104857088 Aug 26 06:04 arch1_142.dbf
 
$ rm -rf arch1_*
$ ls -ltr ar*
ls: ar*: No such file or directory

db 기동

SQL> startup
ORACLE instance started.
 
Total System Global Area  320308744 bytes
Fixed Size             742920 bytes
Variable Size          285212672 bytes
Database Buffers       33554432 bytes
Redo Buffers             798720 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'

4번 데이터파일 문제로 기동 실패

alert log 확인

Completed: ALTER DATABASE   MOUNT
Wed Aug 26 06:26:21 2020
ALTER DATABASE OPEN
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/app/oracle/oradata/ORCL/example01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/app/oracle/oradata/ORCL/indx01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oracle/app/oracle/oradata/ORCL/odm01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oracle/app/oracle/oradata/ORCL/tools01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Wed Aug 26 06:26:21 2020
Errors in file /oracle/app/oracle/admin/ORCL/bdump/orcl_dbw0_30376.trc:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/oracle/app/oracle/oradata/ORCL/users01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
ORA-1157 signalled during: ALTER DATABASE OPEN...

4번 데이터파일 외에도 많은 데이터파일에 ORA-01154, ORA-01110 메세지 발생

복구 시도

SQL> recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'

실패

4번 데이터파일 offline

SQL> alter database datafile 4 offline;
 
Database altered.

offline 후 복구 재시도

SQL> recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL/drsys01.dbf'

실패

db open 시도

SQL> alter database open;    
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/app/oracle/oradata/ORCL/example01.dbf'

이번엔 5번 데이터파일 문제로 기동 실패

기동시 다음 데이터파일 그다음 데이터파일도 ORA 메세지가 발생함

ORA 메세지 발생하는 데이터파일 모두 offline

SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/oracle/app/oracle/oradata/ORCL/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/app/oracle/oradata/ORCL/example01.dbf'
SQL> alter database datafile 5 offline;
.
.
 
SQL> alter database datafile 11 offline;
 
Database altered.

db 기동

SQL> alter database open;
 
Database altered.

dbv 로 블록 손상 확인(1번으로는 제대로 결과가 안나올 수도있기 때문에 dbv 2번 실행)

$ cd /oracle/app/oracle/oradata/ORCL/
$ dbv file=xdb01.dbf  blocksize=8192
 
DBVERIFY: Release 9.2.0.4.0 - Production on Wed Aug 26 07:26:18 2020
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
DBVERIFY - Verification starting : FILE = xdb01.dbf
Page 6419 is marked corrupt
***
Corrupt block relative dba: 0x02801913 (file 10, block 6419)
Bad check value found during dbv: 
Data in bad block -
 type: 6 format: 2 rdba: 0x02801913
 last change scn: 0x0000.0260fa20 seq: 0x1 flg: 0x04
 consistency value in tail: 0xfa200601
 check value in block header: 0xa8a1, computed block checksum: 0xb0
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
 
 
 
DBVERIFY - Verification complete
 
Total Pages Examined         : 12800
Total Pages Processed (Data) : 6829
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 363
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2261
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3346
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0

Marked Corrupt 가 1로 나옴

6419 블록에 corrput가 생김

참조 

정상 파일 dbv 

$ dbv file=system01.dbf blocksize=8192
.
.
DBVERIFY - Verification complete
 
Total Pages Examined         : 48640
Total Pages Processed (Data) : 32988
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 4256
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1917
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 9479
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0

Marked Corrupt가 0 임

손상된 블록의 테이블 확인

fild_id 는 dba_data_files에 있는 file_id

block_id는 위 dbv로 나온 block 번호(6419)

SQL>
col owner for a10 
col segment_name for a10
col segment_type for a10
select owner,tablespace_name, segment_name, segment_type
from dba_extents
where file_id = 10 and 6419 between block_id and block_id + blocks-1;
 
OWNER       TABLESPACE_NAME      SEGMENT_NA SEGMENT_TY
---------- ---------------    ---------- ----------
JSH9       XDB                  TEST         TABLE

JSH9유저의 XDB 테이블스페이스의 TEST 테이블에 블록이 손상됨

해당 datafile online (안될수 있음)

SQL> alter database datafile 10 online;
 
Database altered.

해당 테이블 조회(online 된경우만 가능)

SQL> conn jsh9/jsh9
Connected.
 
SQL> select count(*) from test;
 
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 6419)
ORA-01110: data file 10: '/oracle/app/oracle/oradata/ORCL/xdb01.dbf'

손상된 블록으로 인해 에러발생

select * from test 시 어느정도는 읽어짐 하지만 손상된 블록부분을 읽는 순간 위 에러가 발생함

rman 복구시도

$ rman target /
RMAN> blockrecover datafile 10 block 6419;
 
Starting blockrecover at 26-AUG-20
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
 
 
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00010
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oracle/app/oracle/product/9.2.0/dbs/02ueb62v_1_1 tag=TAG20191014T235511 params=NULL
channel ORA_DISK_1: block restore complete
 
starting media recovery
 
archive log thread 1 sequence 143 is already on disk as file /oracle/app/oracle/product/9.2.0/dbs/arch1_143.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 08/26/2020 07:42:49
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 142 scn 41736685 found to restore
RMAN-06025: no backup of log thread 1 seq 141 scn 41388986 found to restore
RMAN-06025: no backup of log thread 1 seq 140 scn 41082484 found to restore
RMAN-06025: no backup of log thread 1 seq 139 scn 40795043 found to restore
RMAN-06025: no backup of log thread 1 seq 138 scn 40510054 found to restore

아카이브 로그 파일이 없어서 복구 불가

위와 같은 경우 아카이브 로그 파일이 있다면 

rman 으로 복구하면 되지만 아카이브 로그 파일이 없다면

datafile online 후 dbms_repair 패키지로 손상된 블록을 skip 처리해야함

DBMS_REPAIR 패키지를 이용하여 손상 블록 skip 진행

REPAIR_TABLE 을 담을 테이블 스페이스 지정 및 REPAIR_TABLE 생성

SQL> conn / as sydba
BEGIN
  DBMS_REPAIR.ADMIN_TABLES (
  TABLE_NAME => 'REPAIR_TABLE',
  TABLE_TYPE => dbms_repair.repair_table,
  ACTION => dbms_repair.create_action,
  TABLESPACE => '&tablespace_name');
END;
/

데이터파일 online 시도

아래 작업을 하려면 해당 데이터파일이 online 이어야함(안될수 있음, 안된다면 skip도 불가함)

SQL> alter database datafile 10 online;
 
Database altered.

jsh9 유저의 test 테이블 CHECK_OBJECT 로 손상 블록 식별

SQL> 
set serveroutput on
DECLARE num_corrupt INT;
BEGIN
  num_corrupt := 0;
  DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME => '&object_name',
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  corrupt_count => num_corrupt);
  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

repiar_table에 식별된 블록 및 손상 타입이 저장됨

SQL>
col block_id for 99999
col corrupt_type for 99999
 col corrupt_description for a20
select block_id, corrupt_type, corrupt_description
from repair_table;
 
BLOCK_ID CORRUPT_TYPE CORRUPT_DESCRIPTION
-------- ------------ --------------------
    6419     6148

    

식별 된 블록을 손상된 것으로 표시(FIX_CORRUPT_BLOCKS)

SQL>
DECLARE num_fix INT;
BEGIN
  num_fix := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME=> '&object_name',
  OBJECT_TYPE => dbms_repair.table_object,
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  FIX_COUNT=> num_fix);
  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

식별된 블록 skip 하도록 허용

SQL>
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME => '&object_name',
  OBJECT_TYPE => dbms_repair.table_object,
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/

테이블 조회

SQL> select count(*) from jsh9.test;
 
  COUNT(*)
----------
    492186

더이상 에러가 발생하지 않음

skip 된 테이블 확인

skip_corrupt 가 ENABLED로 되어있으면 skip 처리를 한 테이블

SQL>
col owner for a10
col table_name for a10
col skip_corrupt for a15
select owner, table_name, skip_corrupt 
from dba_tables
where owner = 'JSH9';
 
OWNER     TABLE_NAME   SKIP_COR
-------   ---------    --------
JSH9        TEST       ENABLED

online으로 되지 않는 데이터파일 복구 시도

datafile 11

dbv로 손상된 블록 확인

$ dbv file=test01.dbf blocksize=8192
Page 1280 is influx - most likely media corrupt
***
Corrupt block relative dba: 0x02c00500 (file 11, block 1280)
Fractured block found during dbv: 
Data in bad block -
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 consistency value in tail: 0x00000000
 check value in block header: 0x0, block checksum disabled
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
 
 
 
DBVERIFY - Verification complete
 
Total Pages Examined         : 1280
Total Pages Processed (Data) : 301
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 9
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 970
Total Pages Influx           : 512

1280 블록 외에도 많은 블록들이 나옴

데이터파일 online 시도

아래 작업을 하려면 해당 데이터파일이 online 이어야함(안될수 있음, 안된다면 skip도 불가)

SQL> alter database datafile 11 online;
 
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/oracle/app/oracle/oradata/ORCL/test01.dbf'

offline에서 online으로 바뀌지 않음

REPAIR_TABLE2 를 담을 테이블 스페이스 지정 및 REPAIR_TABLE2 생성

SQL> conn / as sydba
BEGIN
  DBMS_REPAIR.ADMIN_TABLES (
  TABLE_NAME => 'REPAIR_TABLE2',
  TABLE_TYPE => dbms_repair.repair_table,
  ACTION => dbms_repair.create_action,
  TABLESPACE => '&tablespace_name');
END;
/

test 테이블 CHECK_OBJECT 실행

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
  num_corrupt := 0;
  DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME => '&object_name',
  REPAIR_TABLE_NAME => 'REPAIR_TABLE2',
  corrupt_count => num_corrupt);
  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
 
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: '/oracle/app/oracle/oradata/ORCL/test01.dbf'
ORA-06512: at "SYS.DBMS_REPAIR", line 284
ORA-06512: at line 4

데이터파일이 offline 상태여서 CHECK_OBJECT 불가

위 경우는 rman 도 사용불가, dbms_repair로 skip도 불가한 상황

이때는 일반적인 방법으로는 복구가 불가한 상황임, 

백업본이 있다면 백업본으로 복구를 하거나, 개발DB가 있다면 개발DB에서 데이터를 가져오거나 

그렇게도 못한다면 손상된 데이터를 포기하는 수 밖에 없음

오라클 ACS 계약이 되어있다면 오라클 ACS 엔지니어를 호출해서 복구가 가능한 지 확인받아야함

오라클 ACS 복구 작업시 데이터파일을 모두 읽어서 재구성하는 방식으로 복구를 진행함

참조 : 

https://positivemh.tistory.com/617

Doc. 840978.1, 819533.1, 556733.1  

https://choko11.tistory.com/entry/Block-corruption-%EA%B3%BC-DBVEIRFY-DBMSREPAIR

https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/repair.htm

https://docs.oracle.com/database/121/ADMIN/repair.htm#ADMIN11835