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