DATA 전문가로 가는 길

[ Oracle - B&R ] Data File 장애 복구 #1 Drop Table 삭제건 시간 기반으로 복구 하기 본문

Data Architecture/Backup & Recovery

[ Oracle - B&R ] Data File 장애 복구 #1 Drop Table 삭제건 시간 기반으로 복구 하기

EstenPark 2011. 3. 15. 16:11

1. 내용

[1-1 장애 내용] DBA는 2월 1일에 전체 백업(Cold Backup)을 정상적으로 수행 하였습니다. 이후 추가 업무로 인해 테이블 스페이스를 생성(TEST)한 후 Scott 유저가 est_tb 테이블을 생성하고 중요한 데이터를 입력 한 후 정상적으로 DROP 시킵니다. DBA는 이런 사실을 모르고 TEST 테이블 스페이스의 데이터 파일인 test01.dbf 파일을 삭제 해버렸습니다.

1. 전체 백업
2. 테이블 스페이스 생성(scott계정의 est_tb 테이블의 TEST 테이블 스페이스)
3. 중요한 데이터 입력
4. commit (commit 시점 시간 확인)
5. drop table 명령어 수행
6. 로그 스위치 발생
7. 정상 종료
8. 복구 하기

* 사용 할 SQL
control.sqldbscn.sqldf.sqlredo.sql


2. 장애 만들기

[2-1 전체 백업] 작업을 시작하기 전에 전체 백업을 받습니다. OS 명령어를 이용해서 백업 하면 됩니다.
[2011-03-17 14:44:15]-[oracle@estenpark:/estenparkdb/backup]
$ file *
control01.ctl:  data
control02.ctl:  data
control03.ctl:  data
example01.dbf:  data
redo01.log:     data
redo02.log:     data
redo03.log:     data
scott_temp01.dbf:       data
sysaux01.dbf:   data
system01.dbf:   data
temp01.dbf:     data
undotbs01.dbf:  data
users01.dbf:    data

[2-2 테이블 스페이스 생성 및 제거] TEST 테이블 스페이스를 생성 후 OS 명령어를 이용하여 삭제를 합니다.

[설명] 복구에 있어서 가장 중요한 부분입니다. 아카이브 로그 모드인지 확인 합니다.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /export/home/oracle/backup/arch2
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19


[설명] 현재 테이블 스페이스와 데이터 파일의 정보를 확인 합니다. 
  1  SELECT  a.ts#, b.name,a.name "file_name"
  2  FROM v$datafile a, v$tablespace b
  3* WHERE a.ts#=b.ts#
SQL> /
       TS# NAME     file_name
---------- -------- --------------------------------------------------
         0 SYSTEM   /estenparkdb/app/oracle/oradata/estenparkdb/system01.dbf
         1 UNDOTBS1 /estenparkdb/app/oracle/oradata/estenparkdb/undotbs01.dbf
         2 SYSAUX   /estenparkdb/app/oracle/oradata/estenparkdb/sysaux01.dbf
         4 USERS    /estenparkdb/app/oracle/oradata/estenparkdb/users01.dbf
         6 EXAMPLE  /estenparkdb/app/oracle/oradata/estenparkdb/example01.dbf

[설명] TEMP 테이블 스페이스 생성
SQL> create tablespace test
  2  datafile '/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf' size 10m;

[설명] TEMP 테이블 스페이스 추가된 내역을 확인 합니다. 
  1  SELECT a.ts#, b.name,a.name "file_name"
  2  FROM v$datafile a, v$tablespace b
  3* WHERE a.ts#=b.ts#
SQL> /
       TS# NAME     file_name
---------- -------- --------------------------------------------------
         0 SYSTEM   /estenparkdb/app/oracle/oradata/estenparkdb/system01.dbf
         1 UNDOTBS1 /estenparkdb/app/oracle/oradata/estenparkdb/undotbs01.dbf
         2 SYSAUX   /estenparkdb/app/oracle/oradata/estenparkdb/sysaux01.dbf
         4 USERS    /estenparkdb/app/oracle/oradata/estenparkdb/users01.dbf
         6 EXAMPLE  /estenparkdb/app/oracle/oradata/estenparkdb/example01.dbf
         9 TEST     /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf

[설명] TEMP 테이블 스페이스의 데이터 파일을 제거 합니다.(OS 명령어를 이용)
SQL> !rm -rf  /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf

SQL> !ls -al  /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf
/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf: No such file or directory

[2-2 테이블 스페이스 생성 및 제거] est_tb 테이블을 생성 후 중요한 데이터를 입력 합니다. 이 후 TABLE DROP 한 후에 정상 종료를 합니다.
[설명] TEMP 테이블 스페이스 공간을 사용 할 수 있게 테이블을 만들어 줍니다.
SQL> create table scott.est_tb(no number(2)) tablespace test;
Table created.

[설명] SCOTT 계정의 est_tb 테이블에 데이터를 입력 합니다.
SQL> insert into scott.est_tb values(1);
1 row created.

SQL> insert into scott.est_tb values(2);
1 row created.

[설명] commit을 수행한 후 REDO BUFFER에 있는 데이터를 LGWR 프로세스를 통해서 REDO LOG FILE에 내려쓰게 합니다.
SQL> commit;
Commit complete.

[설명] 중요한 부분입니다. 원래는 이렇게 시간을 볼 수 없고 로그 마이너나 플래시 백을 이용해서 DROP된 시점을 잡는 것이 바랍직 합니다. 만약 테이블 스페이스가 DROP된 것이라면 alert[SID].log 파일을 확인 한 후 처리 해도 됩니다.(alert[SID].log 파일에는 DROP TABLE의 정보는 남지 않습니다.)
SQL> @time
SP2-0310: unable to open file "time.sql"
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-03-17:15:07:33

[설명] 테이블을 삭제 합니다.
SQL> drop table scott.est_tb ;
Table dropped.

[설명]  현재는 아카이브 로그 모드이므로 데이터를 아카이브 파일로 내려쓰게 하기 위해서 입니다. 참고로 여기에서 리두 로그의 시퀀스 번호를 보는 것도 바랍직합니다.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.

[설명] 정상 종료를 했기 때문에 Normal Checkpoint가 발생 됩니다.  Normal Checkpoint와 Incremental Checkpoint가 있는데 이 중에서 정상 종료가 될 경우에는 Normal Checkpoint가 발생 되게 되며 작동 원리는 리두 로그 버퍼에 있는 내용을 LGWR 백 그라운드 프로세스가 리두 로그 파일로 내려 쓰고, 데이터 버퍼 캐시의 모든 변경 된 블록을 DBWR 백 그라운드 프로세스가 데이터 파일로 내려 쓰고, CKPT 백 그라운드 프로세스는 컨트롤 파일의 체크포인트 포지션을 리두 로그 파일의 가장 마지막 부분에 갱신하고 SCN(System Change Number), LSN(Log Sequence Number)을 컨트롤 파일 및 데이터 파일 헤더에 기록 합니다.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.



3. 장애 복구 하기 

[3-1 복구 절차] 백업된 데이터 파일을 복원 하고 현재 사용하고 있는 리두 로그 파일과 컨트롤 파일을 사용 하여 복구를 시작 합니다.

[설명] 현재 사용하고 있던 데이터 파일을 모두 제거 합니다. (TEMP 파일은 제거 하지 않아도 됩니다.)
[2011-03-17 16:14:58]-[oracle@estenpark:/estenparkdb/app/oracle/oradata/estenparkdb]
$ rm -rf *.dbf
rm: remove example01.dbf (yes/no)? y
rm: remove scott_temp01.dbf (yes/no)? y
rm: remove sysaux01.dbf (yes/no)? y
rm: remove system01.dbf (yes/no)? y
rm: remove undotbs01.dbf (yes/no)? y
rm: remove users01.dbf (yes/no)? y

[설명] 전체 백업 되어있었던 데이터 파일을 현재 사용하고 있는 경로로 복사 합니다.
[2011-03-17 16:28:09]-[oracle@estenpark:/estenparkdb/backup]
$ cp *.dbf /estenparkdb/app/oracle/oradata/estenparkdb

[설명] 마운트 단계에서 작업을 시작 합니다.
SQL> startup mount;

[설명] 상태를 확인 해본 결과 RECOVER로 변경 된 것을 볼 수 있습니다.
SQL> @df
     FILE#        TS# NAME     file_name                                          STATUS  CHECKPOINT_CHANGE#
---------- ---------- -------- -------------------------------------------------- ------- ------------------
         1          0 SYSTEM   /estenparkdb/app/oracle/oradata/estenparkdb/system01.dbf     SYSTEM              665432
         2          1 UNDOTBS1 /estenparkdb/app/oracle/oradata/estenparkdb/undotbs01.dbf    ONLINE              665432
         3          2 SYSAUX   /estenparkdb/app/oracle/oradata/estenparkdb/sysaux01.dbf     ONLINE              665432
         4          4 USERS    /estenparkdb/app/oracle/oradata/estenparkdb/users01.dbf      ONLINE              665432
         5          6 EXAMPLE  /estenparkdb/app/oracle/oradata/estenparkdb/example01.dbf    ONLINE              665432
         6          9 TEST     /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf       RECOVER             664491

[설명] 리두 로그의 상태를 확인 합니다.
SQL> @redo
GROUP# SEQUENCE#         MB MEMBER                                             STATUS
------ --------- ---------- -------------------------------------------------- --------
     1        17         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo01.log       INACTIVE
     2        18         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo02.log       INACTIVE
     3        19         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo03.log       CURRENT

[설명] 컨트롤 파일 덤프 파일 확인 합니다.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.

SQL> @session
       SID        PID SPID         USERNAME   MACHINE    TERMINAL   PROGRAM
---------- ---------- ------------ ---------- ---------- ---------- ------------------------------
       170          2 908                     estenpark   UNKNOWN    oracle@estenpark (PMON)
       169          3 910                     estenpark   UNKNOWN    oracle@estenpark (PSP0)
       168          4 912                     estenpark   UNKNOWN    oracle@estenpark (MMAN)
       167          5 914                     estenpark   UNKNOWN    oracle@estenpark (DBW0)
       166          6 916                     estenpark   UNKNOWN    oracle@estenpark (LGWR)
       165          7 918                     estenpark   UNKNOWN    oracle@estenpark (CKPT)
       164          8 920                     estenpark   UNKNOWN    oracle@estenpark (SMON)
       163          9 922                     estenpark   UNKNOWN    oracle@estenpark (RECO)
       162         10 924                     estenpark   UNKNOWN    oracle@estenpark (CJQ0)
       161         11 926                     estenpark   UNKNOWN    oracle@estenpark (MMON)
       160         12 928                     estenpark   UNKNOWN    oracle@estenpark (MMNL)     
  SID        PID SPID         USERNAME   MACHINE    TERMINAL   PROGRAM
---------- ---------- ------------ ---------- ---------- ---------- ------------------------------
       159         15 934          SYS        estenpark   pts/7      sqlplus@estenpark (TNS V1-V3)

12 rows selected.

[설명] 세션의 SPID의 번호를 확인하여 udump에서 찾아보고 vi편집기로 확인 합니다.
[2011-03-17 16:37:28]-[oracle@estenpark:/estenparkdb/app/oracle/admin/estenparkdb/udump]
$ vi estenparkdb_ora_934.trc

[설명] 현재 test.dbf 파일이 없기 때문에 관련 파일을 offline 한 후 생성합니다. 모두 정상적으로 진행 된다면 관련 파일을 online 합니다.  전/후의 차이점이 무엇인지 파악 하시기 바랍니다.
SQL> alter database datafile '/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf' offline drop;
 Database altered.

[설명] 삭제 된 파일을 신규로 만들어 줍니다. 이렇게 되면 데이터 파일은 빈 파일이고 컨트롤 파일에 의해서 인식 할 수 있게 하는 것입니다. 여기 까지는 664491  checkpoint_change의 변화가 없는 것을 알 수 있습니다.
SQL> alter database create datafile '/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf' as '/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf';
 Database altered.

[설명] RECOVER을 하기 위해서 데이터 파일을 온라인 시키겠습니다. 역시 checkpoint_change는 변화가 없습니다.
SQL> alter database datafile '/estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf' online;
 Database altered.

[설명] online 해도 아무런 변화가 없다고 생각 할 수 있지만 실제로 BYTES/1024/1024를 확인 해보면 기존에 생성 했던 10M가 나오는 것을 확인 할 수 있습니다.(중요)
SQL> @df
     FILE#        TS# NAME     file_name                                                  MB STATUS  CHECKPOINT_CHANGE#
---------- ---------- -------- -------------------------------------------------- ---------- ------- ------------------
         1          0 SYSTEM   /estenparkdb/app/oracle/oradata/estenparkdb/system01.dbf            480 SYSTEM              665432
         2          1 UNDOTBS1 /estenparkdb/app/oracle/oradata/estenparkdb/undotbs01.dbf            35 ONLINE              665432
         3          2 SYSAUX   /estenparkdb/app/oracle/oradata/estenparkdb/sysaux01.dbf            250 ONLINE              665432
         4          4 USERS    /estenparkdb/app/oracle/oradata/estenparkdb/users01.dbf           11.25 ONLINE              665432
         5          6 EXAMPLE  /estenparkdb/app/oracle/oradata/estenparkdb/example01.dbf           100 ONLINE              665432
         6          9 TEST     /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf               10 RECOVER             664491

[설명] 위에서 하는 작업은 아래 작업 시간기반 복구 작업을 하기 위한 설정이라고 하면 지금 부터는 복구를 시작 하겠습니다.
SQL> recover database until time '2011-03-17:15:07:33';
ORA-00279: change 653615 generated at 03/15/2011 17:21:03 needed for thread 1
ORA-00289: suggestion : /export/home/oracle/backup/arch2/1_12_740434176.arc
ORA-00280: change 653615 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto               <===== auto 입력,  의미는 아카이브 로그 파일을 모두 적용
ORA-00279: change 656556 generated at 03/15/2011 23:39:28 needed for thread 1
ORA-00289: suggestion : /export/home/oracle/backup/arch2/1_13_740434176.arc
ORA-00280: change 656556 for thread 1 is in sequence #13
ORA-00278: log file '/export/home/oracle/backup/arch2/1_12_740434176.arc' no longer needed for this recovery

Log applied.
Media recovery complete.
[설명] 데이터베이스를 OPEN 합니다. 여기에서 중요한 부분은 RESETLOGS로 올려야 한다는 것입니다.
만약 alert database open 으로 수행하였다면 정상적으로 OPEN이 되지 않습니다.
이유는 데이터 파일과 컨트롤 파일, 리두 로그 파일의 Checkpoint SCN 정보가 서로 동일하지 않습니다.
그래서 RESETLOGS 옵션을 이용하게 되면 아래와 같은 작업을 합니다.
 - LSN(Log Sequence Number) 정보가 0으로 초기화(단 CURRENT는 1 )
  - 이전에 사용하던 리두로그 및 아카이브 로그는 사용 불가능 합니다.
 - 데이터 파일의 Checkpoint SCN 정보는 open 시점의 scn으로 업데이트 됩니다.
 - 딕셔너리 데이터 파일의 정보와 컨트롤 파일의 정보를 비교하여 컨트롤 파일에 없을 경우 MISSINGXXXXXX와 같이 파일명이 만들어 집니다.

SQL> alter database open resetlogs;

[설명] resletlogs 옵션을 사용하면서 어떠한 작업을 하는지 alert[SID].log에서 확인 해보겠습니다.


[설명] resletlogs 옵션으로 DB를 OPEN 한 정보를 모두 확인 해보겠습니다.
SQL> @df
   FILE#        TS# NAME     file_name                                                  MB STATUS  CHECKPOINT_CHANGE#
---------- ---------- -------- -------------------------------------------------- ---------- ------- ------------------
         1          0 SYSTEM   /estenparkdb/app/oracle/oradata/estenparkdb/system01.dbf            480 SYSTEM              664849
         2          1 UNDOTBS1 /estenparkdb/app/oracle/oradata/estenparkdb/undotbs01.dbf            35 ONLINE              664849
         3          2 SYSAUX   /estenparkdb/app/oracle/oradata/estenparkdb/sysaux01.dbf            250 ONLINE              664849
         4          4 USERS    /estenparkdb/app/oracle/oradata/estenparkdb/users01.dbf           11.25 ONLINE              664849
         5          6 EXAMPLE  /estenparkdb/app/oracle/oradata/estenparkdb/example01.dbf           100 ONLINE              664849
         6          9 TEST     /estenparkdb/app/oracle/oradata/estenparkdb/test01.dbf               10 ONLINE              664849

6 rows selected.

SQL> @redo
GROUP# SEQUENCE#         MB MEMBER                                             STATUS
------ --------- ---------- -------------------------------------------------- --------
     1         0         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo01.log       UNUSED
     2         0         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo02.log       UNUSED
     3         1         50 /estenparkdb/app/oracle/oradata/estenparkdb/redo03.log       CURRENT

[설명] 정상적으로 복구 되었지만 DROP TABLE 되기 이전으로 모두 돌아와야 하므로 est_tb 테이블의 값을 확인 해보겠습니다. 아래와 같이 정상적으로 데이터가 남아 있다면 성공 하신 겁니다.
SQL> select * from scott.est_tb;
        NO
----------
         1
         2


4. 마치며 

[4-1 마치며] 본 문서에서 나오는 장애는 정확히 말하면 장애라고 할 수 없는 부분입니다. drop table est_tb 명령어는 정상적으로 테이블을 삭제 하겠다는 의미이기 때문에 장애로 보기는 힘들지만 정말 중요한 데이터가 있다고 한다면 반드시 복구해야 하는 사항 입니다.
사례1) 테이블 스페이스만 삭제 되었을 경우(백업 파일 없음)
OPEN 상태에서 삭제된 테이블스페이스는 OFFLINE 또는 RECOVER이 될 것입니다.
 - OFFLINE 해결 방법
  * 다행이라고 할수는 없지만 복구 방법이 쉬워 집니다. 테이블스페이스를 신규로 생성하고 recover를 이용하여 tablespace 기준으로 복구를 진행 합니다.
  * SYS@testdb> alter database create datafile '/home/oracle/oradata/testdb/test.dbf' as '/home/oracle/oradata/testdb/test.dbf';
  * recover tablespace test;     (auto 쓰고 엔터)
  
 - RECOVER 해결 방법
  * 기본적으로 offline으로 변경하는 "alter tablespace test offline"는 offline 모드로 변경할 수 없다고 에러가 발생 됩니다.
  recover datafile를 이용하게 되면 가장 컨트롤파일의 가장 최근의 Checkpoint SCN을 사용하기 이전 데이터를 복구 할 수 없을 수 있습니다.
  리두 로그 파일이나 아카이브 파일에서 복구를 해야 한다면 반드시 "alter database datafile 'filename' offline drop"명령어를 수행 하시기 바랍니다. 이후 작업은 OFFLINE와 동일 합니다.
  * SYS@testdb> alter database datafile '/home/oracle/oradata/testdb/test.dbf' offline drop;
    또는 SYS@testdb>  recover datafile '/home/oracle/oradata/testdb/test.dbf'
  * SYS@testdb> alter database create datafile '/home/oracle/oradata/testdb/test.dbf' as '/home/oracle/oradata/testdb/test.dbf';
  * SYS@testdb> recover tablespace test;        (auto 쓰고 엔터)   <-- 이 부분에서 recover datafile 했다면 생략
  * SYS@testdb> alter tablespace test online;

테이블스페이스가 삭제되고 삭제된 테이블스페이스를 est_tb 테이블에서 사용하여 데이터를 삽입 합니다. 정상적으로 커밋을 수행 하였습니다. 현재 상태라면 데이터파일로 내려쓰지 않았고 리두 로그 파일에서 보관하고 있습니다. 여기에서 정상적으로 테이블을 삭제 하고 로그 스위치가 발생되면서 기존에 데이터는 아카이브 로그 파일로 2차 보관작업을 하게 됩니다.
삭제된 est_tb 테이블을 복원하는 작업인데 여러가지 생각을 많이해야 할 수도 있습니다.

그래서 복구를 하기위한 방법으로 컨트롤 파일의 Checkpoint SCN(데이터파일의 정보가 들어있음)과 온라인 데이터파일의 해더의 Checkpoint SCN 정보를 다르게 하기 위해서 백업했던 데이터만 현재 운영중인 디스크로 복원 하는 것입니다.

recover datafile를 사용하지 않고 alter database datafile를 이용해서 오프라인 -> 신규 test 테이블스페이스 생성 -> 온라인 절차를 이용하는 이유는 위에서 설명한 사례1을 생각해보면 recover datafile 할 경우 가장 최근의 checkpoint로 변경 되기 때문에 별도의 복구 작업은 하지 않고 데이터베이스를 오픈하게 됩니다.(drop table est_tb) 명령어는 다시 말하지만 정상적인 처리입니다.
이러한 어이없는 복구를 하지 않기 위해서 데이터파일(test)의 정보를 다르게 구성하는 것입니다.
시간 기반으로 시점을 되돌린 다면 아주 고맙게도 리두 로그 파일과 아카이브 로그 파일을 정상적으로 찾아서 복구를 시도 합니다.

(중요) 복구(recover) ->  컨트롤 파일
                        데이터 파일
                              -> 같을 경우에는 이상한 복구를 수행 합니다.
                                    -> 다를 경우
                                    리두 로그 파일에서 복구를 수행하고 없을 경우에는 아카이브 파일에서 복구를 수행합니다.
recover가 수행될 수 있는 경우의 수
 - 컨트롤 파일 <> 데이터 파일
 - 컨트롤 파일, 리두 로그 파일 <> 데이터 파일
 - 컨트롤 파일 <> 리두 로그 파일



저작자표시

'Data Architecture > Backup & Recovery' 카테고리의 다른 글

[ Oracle - B&R ] Log Miner 사용 방법과 원리 이해  (0) 2011.02.18
'Data Architecture/Backup & Recovery' Related Articles more
Comments