Oracle Database TIP
오라클 플래쉬백 사용하기(Flashback Query) 31 10 48,258

by 김정식 플래쉬백 FLASHBACK DBMS_FLASHBACK UNDO_MANAGEMENT UNDO_RETENTION [2006.01.21]


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

- 강좌 URL : http://www.gurubee.net/lecture/1876

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 임영미 [2007.03.14 19:59:12]
주옥같은 설명과 예제....완전 감동이예요~~~

by 김은식 [2007.06.18 19:39:20]
DML, DDL등의 작업을 직접 수행 할 수 없구나.. 호 저 플래쉬백 기능 사용하면서
왜 텍스트로 받아야 할까 늘 생각했던 부분인데..
그런데 왜 직접 수행 할수 없는걸까요 ? delete 나 update 는 그렇다고 쳐도..
insert는..음 ...........

by 최치환 [2007.09.03 17:42:38]
flashback기능... drop된 테이블에 대해서는 조회가 안되던데요? 혹시 되나요?

by kim [2007.12.03 23:09:37]
drop은 DDL명령문입니다..9i버전이하에서는 DDL명령문(truncate, drop)에 대해서는 안되는것으로 알고있고 10g부터 drop, truncate 명령을 이용한 경우에 flashback이 되는것으로 알고있습니다.

by 하록 [2009.03.16 19:24:43]
"따라서, 시간으로 지정할때는 현재보다 5분이상 차이가 나는 과거시점을 지정해야 합니다." 라는 내용이 첫번째 테두리의 flashback 사용하기란 타이틀 밑에
있습니다. 이말은 제가 만약 10분으로 데이터를 복원하려고 하면 5분을 지정해야 된다는 뜻인지 궁금합니다. 아니라면 간략한 답변좀 부탁 드립니다.

by 하록 [2009.03.16 19:26:39]
10g Ent 에서 테스트한 결과 (거의 사용률 없음) 5분차이와 상관없이
즉시 복원되고 확인이 가능했습니다. 이 부분은 버전이 업데이트 되면서
변경된건지 궁금합니다. 제가 9i 의 플래쉬백 기능을 써본적이 없어서요 ^^;;
~ 좋은강좌 정말 잘 봤습니다. ^^

by 유지영 [2009.12.02 11:25:10]
플래시백 아카이브를 사용할 경우 시스템에 어느 정도 부하를 주는지 알고 싶습니다?

현재 아카이브 모드로 운영중인 DB서버에서 플래시백 아카이브를 설정할 경우 서비스에 영향을 줄 정도로 부하를 주지는 않겠지요?

by 현 [2009.12.09 15:29:46]
네..
서비스에 영향을 줄 정도로 부하를 주지는 않습니다.
단지, 위의 설명에도 나와있듯이 플래시백 쿼리는 UNDO_RETENTION 과 관계가 있습니다.
따라서 잦은 dml 이 있을 시에는 undo 테이블스페이스 용량에 대한 고려는 있어야 합니다.
플래쉬백이라는 쿼리가 대단한 것이 아니라 우리가 dml을 날리게 되면 롤백을 위해 undo 테이블스페이스에 데이터를 갖고 있게 되고, 이 데이터는 가급적 UNDO_RETENTION 동안 보존을 합니다.(절대적인 것은 아니고 가급적 이죠..)
그 UNDO_RETENTION 동안 보존되어 있는 데이터를 플래쉬백을 사용하여 읽어 오는 것입니다.

이 얘기는 플래쉬백 쿼리에 대한 것이구요, 10g에서 나오는 플래쉬백테이블은 약간 다릅니다...

by zandi [2012.10.17 10:58:30]
궁금했던 내용인데 자세한 예제와 설명 고맙습니다.

by 오뚝이 [2017.04.10 17:09:10]

좋은 정보 감사합니다.^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입