메뉴 건너뛰기

Dev tips

DataBase [ORACLE] Flashback (과거시점에서 질의실행)

taknim 2009.07.06 23:34 조회 수 : 10154 추천:2357

http://sens2001.hubweb.net/bbs/view.php?id=oracle&no=486http://dbasupport.com/oracle/ora9i/Flashback_Oracle9i.shtmlFLASHBACK은 9i부터 지원되는 기능으로, 과거시점에서 질의를 실행할 수 있게 해준다.
FLASHBACK모드(과거시점)는 시간, SCN 2가지중 하나로 지정할 수 있다.

시간으로 지정: dbms_flashback.enable_at_time(query_time IN TIMESTAMP);
SCN으로 지정 : dbms_flashback.enable_at_system_change_number(query_scn IN NUMBER);

나머지 DBMS_FLASHBACK패키지에서 제공되는 프로시저는 다음과 같다.

FLASHBACK모드 해제: dbms_flashback.disable;
현재 SCN파악: dbms_flashback.get_system_change_number(RETURN NUMBER);

SCN은 System Change Number의 약자로 트랙잭션마다 주어지는 번호이다.
SCN을 이용하면 정확한 과거시점을 지정할 수 있지만, 해당 시점의 정확한 SCN을 알아내기가 쉽지 않다.
시간으로 지정시, 데이터베이스는 지정한 시간전후 5분내 발생한 SCN을 임의로 선택하여 FLASHBACK을 enable한다.
따라서, 시간으로 지정할때는 현재보다 5분이상 차이가 나는 과거시점으로 지정해야 한다.

flashback모드를 사용하여 과거의 시점에 있더라도 sysdate는 현재의 시간을 반영하는 것에 주의!

FLASHBACK은 DBMS_FLASHBACK 패키지를 통해 사용할 수 있다.
FLASHBACK은 해당 세션에서만 유효하고, DML, DDL연산은 할 수 없다.
따라서 접속이 종료되면 자동으로 현재 시점으로 돌아오게 된다.

FLASHBACK이 유용하게 사용될 수 있는 부분은 다음과 같다.

. 실수로 삭제한 데이터의 복구
. 과거시점의 데이터에 대한 분석


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

Automatic UNDO Management(AUM)모드하에서, 언제까지 과거데이터를 유지할 것인지 undo_retention패러미터를
설정함으로써 지정할 수 있다. 초단위로.


SQL> show parameter undo

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



[테스트]
kang이 소유한 test테이블에는 4개의 row가 존재한다.
이를 삭제하고 난뒤, flashback을 이용하여 과거시점에서 조회하여 어떻게 보이는지 본다.
또한, 삭제된 데이터를 PL/SQL을 이용하여 어떻게 복구하는지도 보이겠다.

SQL> conn kang
암호 입력: **********
연결되었습니다.
SQL> select * from test;

ID         NAME
---------- ----------
maddog     강명규
dbakorea   강명규
superman   슈퍼맨
batman     배트맨

삭제하기전의 날짜를 잘 봐두기 바란다.
FLASHBACK을 이용하여 이 시점에서 데이터를 조회해 볼 것이다.
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2003-02-24 03:11:14

SQL> delete from test;

4 행이 삭제되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from test;

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


system유저는 kang이 소유했던 test테이블에 대한 조회를 할 것이다.
kang에게 DBMS_FLASHBACK패키지에 대한 EXECUTE권한이 준다면 이 작업은 kang이 할 수도 있다.

SQL> conn system
암호 입력: ***********
연결되었습니다.
SQL> exec dbms_flashback.enable_at_time(to_timestamp('2003-02-24 03:11:14', 'yyyy-mm-dd hh24:mi:ss'))

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

SQL> select * from kang.test;

ID         NAME
---------- ----------
maddog     강명규
dbakorea   강명규
superman   슈퍼맨
batman     배트맨

SQL> exec dbms_flashback.disable;

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

SQL> select * from kang.test;

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



[삭제된 데이터의 복구]

SQL> DECLARE
  2  CURSOR test_cursor is
  3   SELECT * FROM kang.test;
  4   v_test kang.test%ROWTYPE;
  5  BEGIN
  6   dbms_flashback.enable_at_time(to_timestamp('2003-02-24 03:11:14', 'yyyy-mm-dd hh24:mi:ss'));
  7  
  8   open test_cursor;
  9   dbms_flashback.disable; -- flashback을 disable했지만(현재 시점으로 돌아왔음을 의미),
10                           -- 커서(test_cursor)는 여전히 과거시점의 데이터를 가지고 있음을 주의
11   loop
12    fetch test_cursor into v_test;
13    exit when test_cursor%NOTFOUND;
14    insert into kang.test values (v_test.id, v_test.name);
15   end loop;
16   close test_cursor;
17   commit;
18  END;
19  /

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

SQL> select * from kang.test;

ID         NAME
---------- ----------
maddog     강명규
dbakorea   강명규
superman   슈퍼맨
batman     배트맨



Reference:
Oracle Online Manual
Using Flashback in Oracle 9i ( http://dbasupport.com/oracle/ora9i/Flashback_Oracle9i.shtml )

This article comes from dbakorea.pe.kr (Leave this line as is)  

--------------------------------------------------------
Flashback 예제

--드롭
dropb table webdev.TEMP22;

-- 플래시백정보(휴지통)
select * from tab;

--복구
FLASHBACK TABLE webdev.TEMP22 TO BEFORE DROP;

-- undo_Retention     10800   3600*3  3시간
--                                     900           25분  

번호 제목 글쓴이 날짜 조회 수
82 [MySQL] FIND_IN_SET 배열 형식의 값을 비교할 때 사용 taknim 2017.10.18 56
81 mysql split 함수 (split_str) taknim 2017.03.14 213
80 [MSSQL] DataBase 안에 있는 procedure에 텍스트 내용을 검색 프로시저 생성 taknim 2015.11.19 1424
79 sqlite3 테이블 정보 관련 쿼리 taknim 2014.03.22 3650
78 [MSSQL] mdf 파일로 DB 복원하기 taknim 2013.08.28 4664
77 [MySQL] 전체 테이블 mysqlcheck repair & optimize taknim 2013.06.11 5259
76 [oracle] isqlplus 에서 특수문자(&) 입력하는 방법 taknim 2012.08.10 16064
75 [오라클] 프로시저 확인하기 taknim 2011.10.04 8078
74 오라클9i 에서 MD5 암호화 taknim 2010.06.08 13052
73 [MSSQL] MS-SQL 동적쿼리 이용하기 taknim 2009.12.28 16981
72 [oracle] PLSQL에서 LONG RAW DATA DML 하기 taknim 2009.09.25 12342
71 [oracle] 실행계획 보기, 토드 설정법 explain plan taknim 2009.09.24 12170
70 [MySQL] 사용자 계정 관리하기 (추가/변경/삭제) taknim 2009.09.18 10048
69 [mysql] mysqlbinlog 실행시 캐릭터셋(utf8) 오류 taknim 2009.08.25 15765
68 [mysql] 동적 쿼리 생성시 변수를 대입하거나 값을 받아오기 taknim 2009.08.17 17198
67 [MSSQL] sp_who2 를 대신할 sql 코드 file taknim 2009.08.13 10941
66 [MSSQL] 변수를 TABLE로 선언 후 데이터 입력 taknim 2009.07.30 11704
» [ORACLE] Flashback (과거시점에서 질의실행) taknim 2009.07.06 10154
64 [Oracle] DBNEWID Utility - DBNAME,DBID변경하기 taknim 2009.07.06 15576
63 [Oracle] INSERT … ON DUPLICATE KEY UPDATE taknim 2009.06.25 13864