오라클 테이블 및 데이터 복구 (Oracle Table & Data Restoration)

1. 테이블이 삭제된 경우

FLASHBACK : 특정한 시간 또는 시점으로 되돌릴 수 있는 기능

1. FLASHBACK을 사용하여 복구

 1) 휴지통에서 지운테이블 확인
 > SELECT * FROM RECYCLEBIN;

 ※ 휴지통이 비어있는 경우 복구 못한다.(Ex. PURGE를 한 경우)

 2) 휴지통에 있는 테이블 복원
 > FLASHBACK TABLE 테이블명 TO BEFORE DROP;


2. FLASHBACK을 사용하기 위한 요구조건 - UNDO MANAGEMENT MODE 조회

 1) SYSDBA 권한으로 접속
 > sqlplus /nolog
 > conn /as sysdba
 or
 > sqlplus "/as sysdba"


 2) UNDO MANAGEMENT MODE 확인
 > SHOW PARAMETER UNDO;

    NAME			TYPE		VALUE
    ---------------------------------------------------------------
    undo_management		string		AUTO
    undo_retention		integer		10800
    undo_suppress_errors		boolean		FALSE
    undo_tablespace		string		UNDOTBS1

 3) undo_management의 VALUE가 AUTO로 되어있는 경우 FLASHBACK을 사용할 수 있다.


3. FLASHBACK을 사용하기 위한 요구조건 - undo_management의 VALUE가 MANUAL로 되어있는 경우

 1) undo_management의 VALUE값을 AUTO로 변경
 > ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE = SPFILE;

 2) UNDO TABLESPACE 생성
 > CREATE UNDO TABLESPACE UNDOTBS2
    DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf' 	--오라클 경로는 알아서 지정
    SIZE 1000M;

 3) UNDO TABLESPACE 지정
 > ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2

 4) UNDO_RETENTION 시간을 변경하려면
 > ALTER SYSTEM SET UNDO_RETENTION=1800

 ※ 실제 적용을 위해서는 5분정도 기다려야 함


4. FLASHBACK을 사용하기 위한 요구조건 - USER에게 DBMS_FLASHBACK EXEUCTE 권한 부여 

 > GRANT EXECUTE ON DBMS_FLASHBACK TO 유저명;

 

2. 테이블의 데이터만 삭제된 경우

1. 테이블 데이터 실수로 지우고 커밋 한 경우 복구 방법

 1) 10분전 데이터 검색
 > SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE);

 2) 정확한 날짜시간을 입력하여 검색
 Ex) 2015년 3월 24일 오후 5시 5분 55초 
 > SELECT * FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('20150324170555', 'YYYYMMDDHH24MISS');

 3) 복구할 테이블에 데이터 INSERT
 > INSERT INTO 테이블명(복구할 칼럼들)
   (SELECT 칼럼들 FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '복구 시점' MINUTE) WHERE 복구하려는테이터조건절);

 > INSERT INTO 테이블명(복구할 칼럼들)
   (SELECT 칼럼들 FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('20150324170555', 'YYYYMMDDHH24MISS') WHERE 복구하려는테이터조건절);

 4) 이미 PK데이터를 다시 만든 경우는 UPDATE를 함
 > UPDATE 테이블명 
    SET (칼럼명) = (SELECT 칼럼명 FROM TEST  AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '9' MINUTE) WHERE 불러오는조건절) 
    WHERE 복구하려는 조건절;


 ※ 복구는 최대 3시간 전까지 복구 가능 한 듯.
 ※ 자주 사용하는 테이블이면 임시 테이블을 만들어 복구 시키는 방법을 사용한다.
 ※ 테이블이 DROP되어있으면 우선 테이블을 살려야 한다.

 

3. DATABASE LEVEL FLASHBACK

환경설정
$ vi /home/oracle/product/10g/inittestdb.ora
db_recovery_file_dest=/home/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
db_flashback_retention_target=30
:wq!


모드변경
 > shutdown immediate;
 > startup mount;
 > alter database archivelog;
 > alter database flashback on;
 > alter database open;
 

수행
 > shutdown imediate;
 > startup mount;
 > flashback database to timestamp (systeimstamp - interval '10' minute);
 > alter database open resetlogs;