오라클 플래쉬백 사용하기(Flashback Query)

Oracle Flashback 기능 이란?

  DB 관리중에 실수로 데이타를 삭제하거나 데이타의 값을 잘못 변경 하는 실수가 가끔 발생을 합니다.

  이러한 오류를 바로 인식할 경우는 Rollback이라는 명령으로 바로 전에 수행한 작업을 원상복귀시킬 수 있지만, COMMIT을 한 이후 시점이나, 한참 시간이 지난 후에 알았다면 간단하게 복구하기가 난감 합니다.

  이러한 경우에 특정한 시간 또는 시점으로 되돌릴 수 있는 기능이 Oracle Flashback 기능 입니다.

  간단하게 말해서 Flashback 기능은 특정한 과거시점의 질의를 실행할 수 있게 해 줍니다. 데이타베이스에 구조적인 변화를 가하지 않고 과거 일정 시점의 데이타 상태를 확인할 수 있는 기능 입니다.

  일종의 오라클에서 지원하는 타임머신이라고 할 수 있죠.

Flashback을 사용하기 위한 요구조건

  자동 언두 관리 시스템을 사용해야 합니다. (UNDO_MANAGEMENT 파라미터를 AUTO로 설정)
    - UNDO_MANAGEMENT = AUTO

  이전의 어느 시점까지의 언두(UNDO)정보를 보유하여 Flashback Query를 수행할것인지 UNDO_RETENTION 파라미터를 설정해야 합니다.
    - ALTER SYSTEM SET UNDO_RETENTION=1800

  일반사용자가 Flashback 기능을 이용하기 위해서 DBMS_FLASHBACK 패키지에 대한 EXECUTE권한이 있어야 합니다.

Flashback 사용하기

  Flashback의 사용 방법은 과거시점의 특정 시간으로 사용하는 방법과 SCN(System Change Number)을 사용하는 방법이 있습니다.

  과거시점의 시간 사용: DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);

  SCN 사용 : DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);

  과거시점의 시간을 지정하여 Flashback 기능을 사용 할 경우 오라클은 내부적으로 이를 SCN으로 전환하여 처리 합니다. 시간 정보를 SCN으로 Mapping하는 시간이 필요한데 통상 5분 주기로 이루어 집니다. 따라서, 시간으로 지정할때는 현재보다 5분이상 차이가 나는 과거시점을 지정해야 합니다.

  또한 Flashback 기능은 무한대로 이전의 데이터를 조회할 수 있는 기능이 아니고, 관리자가 UNDO_RETENTION 파라미터를 통해서 정해준 시간(초) 동안의 데이터를 조회할 수 있습니다. 디폴트 UNDO_RETENTION 시간은 10800(3시간) 입니다.

  그리고 Flashback data를 참고하는 경우엔 DML, DDL등의 작업을 직접 수행 할 수 없습니다.

Flashback 사용을 위한 환경설정

 
C:\> SQLPLUS /NOLOG
 
 
-- SYSDBA 권한으로 접속
SQL> CONN / AS SYSDBA
 
 
-- UNDO MANAGEMENT MODE 확인
SQL> SHOW PARAMETER UNDO;
NAME                        TYPE        VALUE
--------------------------- ----------- ---------
undo_management             string       AUTO
undo_retention              integer      10800
undo_suppress_errors        boolean      FALSE
undo_tablespace             string       UNDOTBS1
 
   
-- undo_management가 MANUAL로 되어있을경우 아래와 같이 변경하고 
-- UNDO 테이블스페이스를 생성하고 지정합니다..
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO
     SCOPE=SPFILE;
 
 
-- UNDO 테이블 스페이스 생성
SQL> CREATE UNDO TABLESPACE UNDOTBS2
     DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf' 
     SIZE 1000M;
 
 
-- UNDO 테이블 스페이스 지정
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2
 
 
-- UNDO_RETENTION 시간을 변경하시면 
-- 실제 적용을 위해 5분정도 기다려야 합니다.
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800
 
 
-- scott유저에게 DBMS_FLASHBACK EXEUCTE 권한 부여 
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;  
    

Flashback 사용예제

 
-- 테스트를 위해서 scott 유저에 접속을 합니다. 
SQL> CONN scott/tiger
 
 
-- emp 테이블 14건의 데이터 확인
SQL> SELECT * FROM emp;
14 개의 행이 선택되었습니다...
  
 
-- 데이터 삭제하기전의 날짜를 확인 합니다. 
-- Flashback을 이용하여 이 시점에서 데이터를 조회, 복구 할 것입니다. 
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 
     FROM DUAL;
 
TO_CHAR(SYSDATE,'YY
-------------------
2006-01-21 17:16:51
 
 
-- 데이터를 삭제 합니다. 
SQL> DELETE FROM emp;
14 행이 삭제되었습니다.
 
 
-- commit 수행
SQL> COMMIT;
 
 
-- 데이터 확인
SQL> SELECT * FROM emp;
선택된 레코드가 없습니다.
 
 
-- Flashback 과거시점의 시간사용 모드 Enable로 데이터 확인
SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_TIME
     (TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))'
 
 
-- 데이터를 확인 할 수 있습니다.
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
 
 
-- Flashback Disable로 변경
SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL 처리가 정상적으로 완료되었습니다.
 
 
-- Flashback Disable로 변경하면 데이터를 확인 할 수 없습니다.
SQL>SELECT * FROM emp;
선택된 레코드가 없습니다.
 
 
-- Flashback Disable 상태에서도 이전데이터를 보기위해서는 
-- 데이터 복구 작업을  진행해야 합니다. 
-- 아래는 삭제된 데이터 복구 예제 입니다.   
    

삭제된 데이터 복구

  Oracle9i Database Release 2이상 버전에서는 SELECT...AS OF 명령을 사용하여 쉽게 Flashback 데이터를 복구 할 수 있습니다.

  Oracle9i Database Release1 버전에서는 DBMS_FLASHBACK프로시저를 이용해서 데이터를 복구해야 합니다.

 
-- 삭제된 데이터 복구(오라클 버전 Release 9.2.0.1.0 실행)
SQL>INSERT INTO emp
    (SELECT * 
     FROM emp AS OF TIMESTAMP 
          TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))
 
 
-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
 
  
-- 위에 복구된 데이타를 Rollback으로 지우고 DBMS_FLASHBACK 패키지를 이용해서 복구해 봅니다. 
-- 삭제된 데이터 복구
SQL> DECLARE 
    
     CURSOR emp_cursor is
       SELECT * FROM emp;
     
      v_emp emp%ROWTYPE;
      
    BEGIN
    
     DBMS_FLASHBACK.ENABLE_AT_TIME
     (TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'));
     
     OPEN emp_cursor;
     
     -- Flashback을 Disable했지만 커서(test_cursor)는 
     -- 여전히 과거시점의 데이터를 가지고 있습니다.
     DBMS_FLASHBACK.DISABLE;
                             
     LOOP
        FETCH emp_cursor INTO v_emp;
            EXIT WHEN emp_cursor%NOTFOUND;
            INSERT INTO emp 
            VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, 
                    v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
     END LOOP;
     CLOSE emp_cursor;
     COMMIT;
    END;
    /
 
PL/SQL 처리가 정상적으로 완료되었습니다.

-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.