오라클 플래시백(Oracle Database Flashback)이란?

1. 소개

프로젝트 진행중에 요긴하게 사용했던 내용입니다.

오라클 9i부터 지원되는 기능중에 flashback query 라는게 있습니다. 과거 일정시점의 데이터를 쿼리해 볼 수 있는 기능입니다. 물론 커밋을 완료해도 이전의 데이터를 볼 수 있습니다.

그렇다고 무한대로 이전의 데이터를 조회할 수 있는건 아니고 관리자가 정해준 기간 [init.ora -> undo_retention (초) 시간동안] 의 데이터를 조회할 수 있습니다.

오라클 디폴트 undo_retention 시간은 10800(3시간) 입니다.

자세한 사용법은 dbms_flashback 패키지를 참조하세요

exec dbms_flashback.enable_at_time(sysdate-10/(24*60));
exec dbms_flashback.disable;

2. 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등의 작업을 직접 수행 할 수 없습니다.

3. Flashback 사용예제

C:\>SQLPLUS /NOLOG

3-1. Flashback 사용을 위한 환경설정 시작

-- 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 100M;

-- 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;

3-2. 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'))

PL/SQL 처리가 정상적으로 완료되었습니다.

-- 데이터를 확인 할 수 있습니다.

SQL>SELECT * FROM emp;

14 개의 행이 선택되었습니다.

-- Flashback Disable로 변경

SQL> EXEC DBMS_FLASHBACK.DISABLE;

PL/SQL 처리가 정상적으로 완료되었습니다.

-- Flashback Disable로 변경하면 데이터를 확인 할 수 없습니다.

SQL>SELECT * FROM emp;

선택된 레코드가 없습니다.

Flashback Disable 상태에서도 이전데이터를 보기위해서는 데이터 복구 작업을 진행해야 합니다.

아래는 삭제된 데이터 복구 예제 입니다.

4. 삭제된 데이터 복구

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 개의 행이 선택되었습니다.