Flashback 으로 삭제된 데이터 복구
Flashback?
실수로 삭제된 데이터를 복구하기 위해서 사용하는 방법.
데이터베이스의 UNDO 기능을 사용해서 복구를 하는 방법입니다.
삭제한 데이터가 COMMIT한 후이거나, 시간이 좀 지난 후 알았을 경우에 사용가능합니다.
DDL은 지원하지 않으며 DML의 경우에 지원하여 사용 가능합니다.
Flashback 사용을 위한 환경설정
Oracle
undo_management가 AUTO로 설정되어 있어야합니다.
UNDO_RETENTION 파라미터 값이 설정되어 있어야 합니다.(초 단위)
일반 사용자가 Flashback기능을 이용하기 위해서 DBMS_FLASHBACK 패키지에 대한 EXECUTE 권한이 있어야 합니다.
확인하는 쿼리
SQL> select name,value from v$parameter where name like '%undo%'
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
select * from [테이블명] as of timestamp (시간);으로 사용가능합니다.
|
시간에 입력가능한 형식 1. systimestamp - (systimestamp-interval '2' minute) 2. systimestamp - 1/1440 3. TO_TIMESTAMP('2020-11-30 17:20:00', 'YYYY-MM-DD HH24:MI:SS')) |
시간을 제대로 입렵하기위해서는 그전에 ,
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
으로 세션 날짜 포맷을 변경한 후 사용합니다.
--SCOTT 유저의 emp 테이블을 테스트용으로 복사
SQL>create table scott.emp_flash_test
as select * from scott.emp;
--확인
SQL> select * from scott.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
--삭제 후 commit;
SQL> delete from scott.emp_flash_test; commit;
--데이터 없는거 확인
SQL> select * from scott.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
0 rows selected.
--FLASHBACK 옵션 사용해서 복구
SQL> insert into scott.emp_flash_test
select * from scott.emp_flash_test
as of timestamp (systimestamp-interval '5' minute);
commit;
--복구 완료
SQL> select * from scott.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
+)추가 티베로6에서도 테스트
Tibero6
확인하는 쿼리
SQL> show param undo
NAME TYPE VALUE
---------------------------- -------- ----------------------------------------
UNDO_RETENTION INT32 900
UNDO_TABLESPACE STRING UNDO0
Tibero에서는 scott유저가 아니라 test유저에서 테스트를 진행했습니다.
오라클과 동일한 명령어로 사용 가능합니다.
--SCOTT 유저의 emp 테이블을 테스트용으로 복사
SQL>create table test.emp_flash_test
as select * from test.emp;
--확인
SQL> select * from test.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
--삭제 후 commit;
SQL> delete from test.emp_flash_test; commit;
--데이터 없는거 확인
SQL> select * from test.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
0 rows selected.
--FLASHBACK 옵션 사용해서 복구
SQL> insert into test.emp_flash_test
select * from test.emp_flash_test
as of timestamp (systimestamp-interval '2' minute);
commit;
--복구 완료
SQL> select * from test.emp_flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
오라클과 티베로에서 동일한 명령어로 사용가능합니다.
UNDO에 쌓이는 양이 많다면, FLASHBACK시 아래와 같은 에러를 발생시킬 수 있습니다.
아래의 에러는 UNDO에 FLASHBACK해야할 데이터가 덮어씌어져버려서 없기 때문에 발생한 에러이므로 해당 에러 발생시에는 FLASHBACK이 아니라 다른 방안으로 복구하는 방법을 생각해야합니다.
오라클
ORA-01555: snapshot too old
티베로
TBR-21003: Snapshot is too old