DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
먼저, 오라클 사에서 제공하는 완전복구 방법에 대해 소개하기 전에 데이터베이스 관리자로써 장애와 복구에 대한 전반적인 내용을 이해하고 장애가 발생한 경우 효과적으로 복구작업을 수행하기 위해서 반드시 준비하셔야 할 내용이 있습니다. 그것은 바로, 여러분들이 관리하고 계시는 오라클 데이터베이스의 모든 파일에 대한 구조와 시스템에 대한 기본적인 정보입니다. 데이터베이스 관리자가 평소에 얼마나 정확하게 자신이 관리하고 있는 데이터베이스의 물리적 구조를 잘 이해하느냐는 장애 발생 시 얼마나 빠르고 효과적으로 데이터베이스를 복구할 수 있느냐를 결정하는 첫 번째 기준이 됩니다. 다음은 오라클 데이터베이스의 모든 물리적 구조정보를 분석하는 방법입니다. 1) 현재 생성되어 있는 모든 물리적 구조를 분석한 다음 그 정보를 파일로 생성하십시오. 아카이브 모드 환경에서 데이터베이스에 장애가 발생하면 2가지 방법으로 복구작업을 수행할 수 있습니다. 첫 번째 방법은 완전 복구(Complete Recovery) 이며, 두 번째 방법은 불완전 복구(InComplete Recovery) 입니다. 장애가 발생한 시점에 어떤 복구 방법에 의해 데이터베이스를 복구할 것인지를 결정하는 것은 장애의 발생상태 및 복구 전략에 따라 선별적으로 사용할 수 있습니다. 예를 들어, 장애가 발생한 상태를 확인했을 때, 데이터 파일 만 문제가 생긴 경우와 데이터 파일과 리두로그 파일 모두에 문제가 발생한 경우에 복구 방법은 달라질 수 있다는 것 입니다. 또는, 오라클 데이터베이스를 구성하는 파일구조에 문제가 발생한 것이 아니라, 특정 테이블을 사용자가 실수에 의해 DROP 한 경우 복구 방법은 달라질 수 있습니다. 결론적으로, 장애가 발생한 시점에 먼저, 장애 원인을 정확하게 분석할 수 있어야 하며, 그 결과에 따라 적절한 복구 방법을 선택해야 한다는 것 입니다. 이러한 과정을 통해 장애 복구를 원활하게 수행하기 위해서는 오라클 서버의 구조를 완벽하게 이해해야 하며, 또한 각각의 복구 방법과 절차에 대한 명확한 이해와 장단점을 잘 알고 있어야 합니다. 자 ~ 그럼 아카이브 모드에서 수행할 수 있는 완전복구에 대해 보다 자세히 알아 봅시다. 완전복구(Complete Recovery)는 데이터베이스에 장애가 발생한 경우 마지막으로 수행한 오프라인 백업 데이터와 그 이후 생성된 아카이브 파일 그리고 아카이브 되지 않은 현재 시점의 백업 데이터를 저장하고 있는 리두로그 파일을 통해 장애가 발생한 시점까지의 모든 데이터를 완전하게 복구하는 방법입니다. 위 그림에서, 현재시점 2001년 6월 10일 오전 12시에 데이터베이스를 종료한 후 오프라인 백업을 통해 SCN 번호가 95인 모든 파일(데이터 파일, 컨트롤 파일, 리두로그 파일)들을 백업하고 있습니다. 현재, 데이터베이스는 아카이브 모드이며, 오프라인 백업 이후 발생한 모든 트랜잭션 데이터들은 아카이브 파일과 리두로그 파일에 기록되어 있습니다. 첫 번째 아카이브 파일 ARC1.LOG 에는 마지막 오프라인 백업 이후부터 2001년 6월 11일 오전 12시까지의 백업 데이터들이 저장 되었다고 합니다. 그리고, 두 번째 아카이브 파일 ARC2.LOG 에는 2001년 6월 11일 오전 12시부터 2001년 6월 12일 오전 12시까지의 백업 데이터들이 저장되었으며, 2001년 6월 12일 오전 12시부터 6월 12일 오후 13시까지의 데이터들은 ARC3.LOG 파일에 기록되어 있다고 합니다. 현재 시점, 2001년 6월 12일 오후 13시에 USERS01.DBF 데이터 파일이 저장되어 있는 디스크에 장애가 발생하여 더 이상 데이터베이스를 사용할 수 없다고 합니다. 아카이브 모드에서 DB 내의 특정 데이터 파일들이 장애가 발생하여 더 이상 입력, 수정, 삭제 작업을 수행할 수 없다고 합니다. 다행히 최근에 백업된 데이터 파일들과 아카이브 파일들은 정상적으로 사용 가능한 상태라고 합니다. 전체 데이터베이스 완전 복구를 수행해 보도록 하겠습니다. 1) 오라클 서버가 사용 가능한 상태인지 확인하고 아카이브 파일들이 정상적으로 생성되는지 확인하십시오. 2) 이 사례를 위해 USERS01.DBF 파일을 삭제한 후 오라클 서버를 다시 시작해 보십시오. 3) 이전에 받은 전체 오프라인 백업파일로부터 손상된 데이터 파일들을 재설치 한 후 전체 완전복구 방법으로 복구작업을 수행합니다. 4) 오라클 데이터베이스가 정상적으로 복구 되었는지 확인해 보십시오. 이번에는, 완전복구(Complete Recovery) 3가지 방법 중에 테이블스페이스 완전복구에 대해 알아 보도록 하겠습니다. 전체 완전복구는 MOUNT 단계에서 모든 데이터 파일을 대상으로 복구작업을 수행하는 방법이라면, 테이블스페이스 완전복구는 OPEN 단계에서, 장애가 발생한 데이터 파일의 테이블스페이스를 기준으로 복구 작업을 수행하게 됩니다. 위 그림에서, 현재시점 2001년 6월 10일 오전 12시에 데이터베이스를 종료한 후 오프라인 백업을 통해 SCN 번호가 95인 모든 파일(데이터 파일, 컨트롤 파일, 리두로그 파일)들을 백업하고 있습니다. 현재, 데이터베이스는 아카이브 모드이며, 오프라인 백업 이후 발생한 모든 트랜잭션 데이터들은 아카이브 파일과 리두로그 파일에 기록되어 있습니다. ARC1.LOG, ARC2.LOG, ARC3.LOG 파일에 모든 백업 데이터들이 저장되었다고 합니다. 현재시점, 2001년 6월 12일 오후 13시에 USERS01.DBF 데이터 파일이 저장되어 있는 디스크에 장애가 발생하여 더 이상 관련 테이블스페이스를 사용할 수 없다고 합니다. 아카이브 모드에서 DB 내의 특정 데이터 파일들이 장애가 발생하여 더 이상 입력, 수정, 삭제 작업을 수행할 수 없다고 합니다. 다행히 최근에 백업된 데이터 파일들과 아카이브 파일들은 정상적으로 사용 가능한 상태라고 합니다. 그리고, 문제가 발생한 데이터 파일은 특정 테이블스페이스에 포함된 데이터 파일이라고 합니다. 이런 경우, 데이터베이스 전체를 대상으로 복구작업을 수행하게 되면 많은 시간이 소요될 수 있으므로 테이블스페이스 완전 복구방법을 수행해 보도록 하겠습니다. 1) 오라클 서버가 사용 가능한 상태인지 확인하고 아카이브 파일들이 정상적으로 생성되는지 확인하십시오. 2) 이 실습을 위해 USERS01.DBF 파일을 삭제한 후 오라클 서버를 재 시작해 보십시오. 3) 이전에 수행된 전체 오프라인 백업 파일들로부터 손상된 USERS01.DBF 파일을 재설치한 후 테이블스페이스 완전복구 방법을 이용한 복구작업을 수행하십시오, 이번에는, 완전복구의 마지막 방법 인 데이터 파일 완전복구 방법을 소개하도록 하겠습니다. 위 그림에서, 현재시점 2001년 6월 10일 오전 12시에 데이터베이스를 종료한 후 오프라인 백업을 통해 SCN 번호가 95인 모든 파일(데이터 파일, 컨트롤 파일, 리두로그 파일)들을 백업하고 있습니다. 현재, 데이터베이스는 아카이브 모드이며, 오프라인 백업 이후 발생한 모든 트랜잭션 데이터들은 아카이브 파일과 리두로그 파일에 기록되어 있습니다. 아카이브 모드에서 DB 내에 특정 데이터 파일들이 장애가 발생하여 더 이상 입력, 수정, 삭에 백업된 데이터 파일들과 아카이브 파일들은 정상적으로 사용 가능한 상태라고 합니다. 현재, 장애가 발생한 데이터 파일은 하나의 데이터 파일이라고 합니다. 1) 오라클 서버가 사용 가능한 상태인지 확인하고 아카이브 파일들이 정상적으로 생성되는지 확인하십시오. 2) 이 실습예제를 수행하기 위해 USERS01.DBF 파일을 삭제한 후 오라클 서버를 다시 시작해 보십시오. 해당 파일이 더 이상 존재하지 않기 때문에 OPEN 단계에서 에러가 발생할 것 입니다. 3) 이전에 수행된 전체 오프라인 백업 파일로부터 해당 파일을 재설치하고 데이터 파일 완전복구 방법을 이용한 복구작업을 수행하십시오.OFF-Line 백업을 이용한 완전복구
4장 OFF-Line 백업을 이용한 완전복구
4-1 데이터베이스의 구조분석
C:\] sqlplus "/as sysdba"
SQL> SPOOL oracle_files.dat- 먼저, 모든 데이터 파일의 구조를 분석하십시오.SQL> COL TABLESPACE_NAME FORMAT A15
SQL> COL FILE_NAME FORMAT A50
SQL> SELECT file_id, file_name, tablespace_name FROM dba_data_files;FILE_ID FILE_NAME TABLESPACE_NAME
-------- ------------------------------------- ---------------
1 C:\ORACLE\ORADATA\ORA92\SYSTEM01.DBF SYSTEM
2 C:\ORACLE\ORADATA\ORA92\UNDOTBS.DBF UNDOTBS
3 C:\ORACLE\ORADATA\ORA92\TEMP01.DBF TEMP
4 C:\ORACLE\ORADATA\ORA92\USERS01.DBF USERS- 다음은, 모든 컨트롤 파일의 구조를 분석하십시오.SQL> COL NAME FORMAT A50
SQL> SELECT * FROM v$controlfile;
STATUS NAME
------------------------------------------------
C:\ORACLE\ORADATA\ORA92\CONTROL01.CTL
C:\ORACLE\ORADATA\ORA92\CONTROL02.CTL
C:\ORACLE\ORADATA\ORA92\CONTROL03.CTL- 마지막으로, 모든 리두로그 파일의 구조를 분석하십시오.SQL> COL MEMBER FORMAT A50
SQL> SELECT * FROM v$logfile; GROUP# STATUS TYPE MEMBER
---------- --------- ------ ----------------------------------
1 ONLINE C:\ORACLE\ORADATA\ORA92\REDO01.LOG
2 ONLINE C:\ORACLE\ORADATA\ORA92\REDO02.LOG
3 ONLINE C:\ORACLE\ORADATA\ORA92\REDO03.LOGSQL> SPOOL OFF ← 운영체계 상에 ORACLE_FILE.DAT 파일이 생성됩니다.
SQL> EXIT[C:\] EDIT ooracle_files.dat ← 저정된 물리적 구조정보를 참조할 수 있습니다.
4-2 완전복구 방법
4-2-1 전체 완전 복구
사례-4.
[C:\] sqlplus "/as sysdba"
SQL> startupSQL> host dir c:\oracle\oradata\ora92\arch\*.arc
→ 현재, 아카이브 파일의 개수와 마지막 아카이브 파일번호를 메모해 두십시오.SQL> host more moredept.sql
alter system switch logfile;
insert into scott.dept values(1,'Personnel','Pusan');
insert into scott.dept values(2,'Account','Pusan');
insert into scott.dept values(3,'Q.C','Pusan');
alter system switch logfile;
insert into scott.dept values(4,'Personnel','Seoul');
insert into scott.dept values(5,'Account','Seoul');
insert into scott.dept values(6,'Q.C','Seoul');
alter system switch logfile;
insert into scott.dept values(7,'Personnel','Daejeon');
insert into scott.dept values(8,'Account','Daejeon');
insert into scott.dept values(9,'Q.C','Daejeon');
commit;
select count(*) from scott.dept;→ 이 스크립트를 실행하면 아카이브 파일들이 생성됩니다.SQL> @moredept.sql
SQL> shutdown immediate
SQL> exit[C:\] dir c:\oracle\oradata\ora92\arch\*.arc
→ MOREDEPT.SQL을 실행한 후 아카이브 파일이 몇 개 추가생성 되었는지 확인하십시오.
OPEN 단계에서 파일의 삭제로 인해 데이터베이스의 무결성이 보장되지 않아 에러가 발생할 것 입니다.[C:\]dir c:\oracle\oradata\ora92\*.dbf
→ USERS 테이블스페이스를 구성하는 데이터 파일[C:\]del c:\oracle\oradata\ora92\users01.dbf
→ 해당 파일이 존재하는 디스크에 장애가 발생한 것으로 시나리오를 만듭니다.[C:\] dir c:\oracle\oradata\ora92\*.dbf[C:\] sqlplus "/as sysdba"
SQL> startup
ORA-01157 데이터 9파일을 식별 또는 잠금할 수 없습니다 - DBWR 추적파일을 보십시오
ORA-01110 : 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> shutdown
SQL> exit
[C:\]cd c:\backup
[C:\] dir
[C:\]copy users01.dbf c:\oracle\oradata\ora92\users01.dbf[C:\]sqlplus "/as sysdba"
SQL> startup
Database mounted.
ORA-01113: 9 파일이 매체 복구되어야 합니다.
ORA-01110: 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> SELECT * FROM V$RECOVER_FILE;
FILE# ONLINE ERROR CHANGE#
---------------------------------------------
9 ONLINE 406577SQL> recover database
ORA-00279: Change 7474 generated at 04/24/97 22:52:31
ORA-00289: Suggestion : c:\oracle\oradata\ora92\arch\arch_256.arc
ORA-00280: Change 7474 for thread 1 is in sequence #256
Specify log: {=suggested | filename | AUTO | CANCEL}AUTO ← AUTO를 입력한 후 Enter를 누르면 모든 아카이브 파일들을 자동으로 적용해줍니다.Media recovery complete.
→ 정상적으로 복구작업이 완료되면 반드시 이 메시지가 출력됩니다.SQL> alter database open; → 현재 오라클 서버는 마운트 상태이므로 OPEN 합니다.
SQL> select count(*) from scott.DEPT;
SQL> shutdown immediateSQL> exit
4-2-2 테이블스페이스 완전복구
사례-5.
[C:\] sqlplus "/as sysdba"
SQL> startupSQL> host dir c:\oracle\ora92\database\arch\*.arc
→ 현재, 생성되어 있는 아카이브 파일의 개수 및 마지막 파일명을 메모해 두십시오.SQL> host more moredept.sql
alter system switch logfile;
insert into scott.dept values(1,'Personnel','Pusan');
insert into scott.dept values(2,'Account','Pusan');
insert into scott.dept values(3,'Q.C','Pusan');
alter system switch logfile;
insert into scott.dept values(4,'Personnel','Seoul');
insert into scott.dept values(5,'Account','Seoul');
insert into scott.dept values(6,'Q.C','Seoul');
alter system switch logfile;
insert into scott.dept values(7,'Personnel','Daejeon');
insert into scott.dept values(8,'Account','Daejeon');
insert into scott.dept values(9,'Q.C','Daejeon');
commit;
select count(*) from scott.dept;SQL> @moredept.sql
SQL> shutdown immediate
SQL> exit[C:\]dir c:\oracle\ora92\database\arch\*.arc
→ MOREDEPT.SQL을 실행한 후 생성된 아카이브 파일의 개수와 마지막 파일명을 메모해
두십시오.
OPEN 단계에서 해당 파일이 존재하지 않으므로 에러가 발생할 것 입니다.[C:\]dir c:\oracle\oradata\ora92\*.dbf
→ USERS 테이블스페이스를 구성하고 있는 데이터 파일명을 확인하십시오.[C:\]del c:\oracle\oradata\ora92\users01.dbf
→ 이 실습을 위해 해당 파일을 삭제합니다.[C:\] dir c:\oracle\oradata\ora92\*.dbf[C:\] sqlplus "/as sysdba"
SQL> startup
ORA-01157 데이터 9파일을 식별 또는 잠금할 수 없습니다-DBWR 추적파일을 보십시오
ORA-01110 : 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> shutdown
SQL> exit
[C:\]cd c:\backup
[C:\]dir[C:\]copy users01.dbf c:\oracle\oradata\ora92\users01.dbf[C:\]sqlplus "/as sysdba"
SQL> startup
Database mounted.
ORA-01113: 9 파일이 매체 복구되어야 합니다.
ORA-01110: 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> SELECT * FROM V$RECOVER_FILE;
FILE# ONLINE ERROR CHANGE#
------ ----------- ------------- ------------
9 ONLINE 406577SQL> select FILE#, STATUS, NAME from v$datafile;
← USERS01.DBF 파일의 상태는 ONLINESQL> alter database datafile
'c:\oracle\oradata\ora92\users01.dbf' offline;SQL> select FILE#, STATUS, NAME from v$datafile;
← USERS01.DBF 파일의 상태는 OFFLINESQL> alter database open;
SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces;TABLESPACE_NAME STATUS
----------------------- --------------------------
USERS ONLINESQL> alter tablespace users offline immediate;SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces;TABLESPACE_NAME STATUS
------------------------ ------------------------
USERS OFFLINESQL> recover tablespace users
ORA-00279: Change 7220 generated at 02/24/97 23:51:30
ORA-00289: Suggestion : /DBAX/DBA805/dba숫자/arch_219.arc
ORA-00280: Change 7220 for thread 1 is in sequence #219
Specify log: {=suggested | filename | AUTO | CANCEL}AUTO → 관련된 모든 아카이브 파일들을 자동 적용해 줍니다.SQL> select count(*) from scott.dept;
← 에러 발생, 복구는 되었지만 offline 상태
ORA-00376: 현재 파일 9를 읽을 수 없습니다.
ORA-01110: 9 데이터 파일SQL> alter tablespace users online;SQL> select count(*) from scott.dept; → 정상적으로 수행 됨SQL> shutdown immediate
SQL> exit
4-2-3 데이터 파일 완전복구
ARC1.LOG, ARC2.LOG, ARC3.LOG 파일에 모든 백업 데이터들이 저장되었다고 합니다.
현재시점, 2001년 6월 12일 오후 13시에 USERS01.DBF 데이터 파일이 저장되어 있는 디스크에 장애가 발생하여 더 이상 관련 파일을 사용할 수 없다고 합니다.사례-6
이런 경우, 전체 데이터베이스 또는 특정 테이블스페이스 완전 복구방법을 수행하게 되면 불필요한 복구 시간이 소요될 수 있으므로 이번에는 데이터 파일 완전 복구 방법을 수행해 보도록 하겠습니다.[C:\] sqlplus "/as sysdba"
SQL> startupSQL> host dir c:\oracle\ora92\database\arch\*.arc
→ 현재 생성되어 있는 아카이브 파일의 개수와 마지막 파일명을 메모해 두십시오.SQL> host more moredept.sql
alter system switch logfile;
insert into scott.dept values(1,'Personnel','Pusan');
insert into scott.dept values(2,'Account','Pusan');
insert into scott.dept values(3,'Q.C','Pusan');
alter system switch logfile;
insert into scott.dept values(4,'Personnel','Seoul');
insert into scott.dept values(5,'Account','Seoul');
insert into scott.dept values(6,'Q.C','Seoul');
alter system switch logfile;
insert into scott.dept values(7,'Personnel','Daejeon');
insert into scott.dept values(8,'Account','Daejeon');
insert into scott.dept values(9,'Q.C','Daejeon');
commit;
select count(*) from scott.dept;SQL> @moredept.sql
SQL> shutdown immediate
SQL> exit[C:\]dir c:\oracle\ora92\database\arch\*.arc
→ MOREDEPT.SQL을 실행한 후 추가 생성된 아카이브 파일의 개수와 마지막 파일명을 메모해 두십시오.
[C:\]dir c:\oracle\oradata\ora92\*.dbf
→ USERS 테이블스페이스가 구성하는 테이터 파일을 확인하십시오.[C:\]del c:\oracle\oradata\ora92\users01.dbf
→ 실습 시나리오를 위해 USERS01.DBF 파일을 삭제하십시오.[C:\] dir c:\oracle\oradata\ora92\*.dbf[C:\] sqlplus "/as sysdba"
SQL> startup
ORA-01157 데이터 9파일을 식별 또는 잠금할 수 없습니다-DBWR 추적파일을 보십시오
ORA-01110 : 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> shutdown
SQL> exit
[C:\] cd c:\backup
[C:\] copy users01.dbf c:\oracle\oradata\ora92\users01.dbf[C:\]sqlplus "/as sysdba"
SQL> startup
Database mounted.
ORA-01113: 9 파일이 매체 복구되어야 합니다.
ORA-01110: 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBFSQL> SELECT * FROM V$RECOVER_FILE;
FILE# ONLINE ERROR CHANGE#
------ --------- ------------- -----------------
9 ONLINE 406577SQL> select FILE#, STATUS, NAME from v$datafile;
← USERS01.DBF 파일은 삭제되었지만 데이터 딕션어리에는 해당 파일이 아직 사용
가능한 상태로 나타납니다.(ONLINE 컬럼이 ONLINE) 데이터 파일 완전복구 작업을
수행하기 위해서는 OFFLINE 상태이어야 합니다.SQL> alter database datafile 'c:\oracle\oradata\ora92\users01.dbf' offline;SQL> alter database open;
← 컨트롤 파일 내에 USERS01.DBF 파일의 상태가 OFFLINE 되었기 때문에 오라클 서
버를 오픈할 수 있습니다. 이 의미는 USERS01.DBF가 ONLINE 상태에서는 항상 파
일의 존재 유무를 확인하게 되지만, OFFLINE 상태에서는 확인하지 않기 때문에
오라클 서버를 오픈할 수 있게 됩니다.SQL> recover datafile ' c:\oracle\oradata\ora92\users01.dbf' ;
ORA-00279: Change 7220 generated at 02/24/97 23:51:30 needed
ORA-00289: Suggestion : c:\oracle\oradata\ora92\arch\arch_219.arc
ORA-00280: Change 7220 for thread 1 is in sequence #219
Specify log: {=suggested | filename | AUTO | CANCEL}Auto ← 관련된 모든 아카이브 파일들을 자동으로 적용해 줍니다.Media Complete Recovery … ← 정상적으로 복구작업이 완료되었습니다.SQL> select count(*) from scott.dept; -
→ USERS01.DBF 파일은 아직 OFFLINE 상태이기 때문에 검색할 수 없습니다.SQL> alter database datafile 'c:\oracle\oradata\ora92\users01.dbf ' online;
SQL> select count(*) from scott.dept; → 정상적으로 수행 됨