[Oracle] Flashback 사용법 정리(Versions, Transaction, Table, Drop)

Oracle Flashback

DML 작업을 하다가 의도치 않게 혹은 실수로 Commit으로 Transaction을 끝냈는데 이전 시점으로 돌아가야하는 상황이 올 수 있습니다. 이 문제를 해결하기 위해 Oracle의 Flashback 기술을 이용해서 특정 시점으로 돌아가거나 보여주는 복구 작업을 할 수 있습니다. 다만, Flashback 기술은 Undo Data를 사용하기 때문에 undo_retention 파라미터값에 의해 보존되는 시간이 정해져 있습니다. Undo Data를 보존하는데 공간과 자원이 필요합니다. 그래서 undo_retention의 기본값은 900초로 되어있는데 좀 더 오랜 기간 돈안 Undo Data를 보존하기 위해 Flashback Data Archive 기능을 사용할 수 있습니다. 해당 내용은 아래 링크에서 확인 가능합니다.

[Oracle] Flashback Data Archive 데이터 복원 작업

 

이번 글에서는 Oracle 10g에서 New Feature로 공개된 몇가지 Flashback 기능들을 알아보도록 하겠습니다. Oracle 9i release 2에서 처음 선보인 Flashback Query를 이용해서 특정 시점의 테이블 데이터를 확인할 수 있는 방법도 있습니다. 해당 내용은 아래 링크에서 확인 가능합니다.

[Oracle] Flashback Query AS OF 절 사용하기

 

Flashback 실습환경

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl
host : 601d2fce71dc

Flashback 관련 실습은 위와 같은 환경에서 진행했습니다.

 

 

Flashback Versions Query 실습

Flashback Versions Query는 지정된 기간 동안 특정 ROW의 상태(Version)를 추적할 수 있습니다.

 

실습용 테이블 생성

[oracle@601d2fce71dc orcl]$ sqlplus scott/tiger
SCOTT@orcl> create table emp_test as select * from emp;

Table created.

SCOTT@orcl> select * from emp_test;

실습용 테이블로 emp_test를 생성했습니다.

 

 

테이블 수정

SCOTT@orcl> update emp_test set sal=sal*1.2 where deptno=10;

3 rows updated.

SCOTT@orcl> commit;    

Commit complete.

SCOTT@orcl> update emp_test set sal=sal+5000 where deptno=20;

5 rows updated.

SCOTT@orcl> commit;

Commit complete.

변경된 내역들을 확인하기 위해 몇가지 변경작업을 진행했습니다.

 

 

Flashback Versions 조회

SCOTT@orcl> col versions_endtime for a30
SCOTT@orcl> col versions_starttime for a30 
SCOTT@orcl> set linesize 200
SCOTT@orcl> 
select
    versions_xid
    ,versions_operation
    ,versions_startscn
    ,versions_endscn
    ,versions_starttime
    ,versions_endtime
    ,empno
    ,sal
    ,deptno
from emp_test
versions between timestamp sysdate - 1/24/60*10 and sysdate
where deptno in(10,20)
order by empno;

10분전부터 현재까지 10, 20번 부서에 속해있는 사원의 사원번호, 급여, 부서번호에 대한 버전을 확인하는 쿼리를 작성했습니다. Flashback Versions Query를 사용할때 사용가능한 Pseudo Column을 이용해서 각 행들의 버전의 세부정보를 확인할 수 있습니다.

versions_xid : 행의 변화를주었을 때 발생한 Transaction의 ID값
versions_operation : Transaction의 액션
    I --> insert
    U--> update
    D--> delete
versions_startscn : 해당 값으로 처음 변경되었을 때의 SCN
versions_endscn : 해당 값을 마지막으로 갖고 있었을 때의 SCN
versions_starttime : 해당 값으로 처음 변경되었을 때의 Time
versions_endtime : 해당 값을 마지막으로 갖고 있었을 때의 Time

 

중간 중간 versions_start 혹은 versions_end와 관련된 내용이 없는 경우가 있습니다. versions_start의 값이 없는 경우 해당 데이터는 최초의 데이터라는 의미입니다. versions_end에 대한 정보가 없는 Row는 현재 상태를 의미합니다. Flashback Versions의 Pseudo Column들을 같이 사용하여 행들의 변경내역을 추적할 수 있습니다.

 

 

 

Flashback Transaction Query 실습

Flashback Transaction Query는 각 Transaction의 세부 정보를 확인할 수 있습니다. 주로 변경이 있었던 Transaction의 UNDO SQL 정보를 확인하는 용도로 사용 가능합니다. 어느 시점으로 돌아가는 작업보다는 특정 Transaction의 변경 내용을 되돌리는데 유리하게 사용할 수 있습니다. 실습은 Flashback Versions Query에서 생성한 emp_test 테이블로 진행하겠습니다.

 

 

권한 및 사전 설정

SYS@orcl> grant select on sys.flashback_transaction_query to scott;
SYS@orcl> grant select any transaction to scott;

Flashback Transaction에 대한 정보를 Scott 유저가 조회할 수 있도록 권한을 부여해줬습니다.

 

SYS@orcl> alter database add supplemental log data;

Database altered.

SYS@orcl> select supplemental_log_data_min from v$database;

supplemental_log_data_min 정보가 NO로 되어있는 경우 UNDO_SQL에 대한 정보가 flashback_transaction_query 뷰를 조회했을 때 보이지 않습니다. 설정을 변경합니다.

 

 

테이블 수정

SYS@orcl> conn scott/tiger
Connected.
SCOTT@orcl> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

먼저 현재 시간을 확인해줍니다. 해당 시간을 기준으로 변경된 내용을 확인할겁니다.

 

 

SCOTT@orcl> update emp_test set sal=sal*2 where deptno=30;

6 rows updated.

SCOTT@orcl> commit;

Commit complete.

30번 부서의 사원들의 급여를 2배로 수정했습니다.

 

 

Flashback Transaction 조회

SCOTT@orcl> col undo_sql for a100
SCOTT@orcl>
select
    xid
    ,undo_sql
from flashback_transaction_query
where table_owner = 'SCOTT'
and table_name = 'EMP_TEST'
and start_timestamp > to_timestamp('2020-07-21 14:34:19','yyyy-mm-dd hh24:mi:ss');

Flashback Transaction Query를 이용해서 수정된 데이터의 UNDO_SQL을 확인할 수 있습니다. 위의 쿼리에서는 하나의 변경작업만 실행해서 시간으로 확인했습니다. Flashback Versions Query를 사용해서 특정 Transaction의 작업을 되돌리는데 유히하게 사용할 수 있습니다. 해당 SQL그대로 실행하게되면 일부 트랜잭션에 한해서 이전 시점으로 돌아갈 수 있습니다.

 

 

 

Flashback Table

Flashback Table은 특정 시점으로 테이블 전체를 복구할수 있는 기능입니다. 해당 기능을 사용하기 전에 Flashback할 Table에 대해 Flashback Object 권한이 있어야합니다. Flashback Transaction Query에서 ANY TABLE에 대해 FLASHBACK 권한을 부여했으니 emp_test 테이블을 갖고 실습을 진행하겠습니다.

 

 

사전 설정

SCOTT@orcl> alter table emp_test enable row movement;

Table altered.

데이터의 물리적인 위치 이동을 활성화해줘야합니다. row movement를 활성화한다는 것은 행의 rowid가 보존되지 않는다는 뜻입니다. Flashback 작업 이전의 rowid를 특정한 application에서 참조하고 있었다면 Flashback 이후의 rowid값과 상응하지 못하여 문제가 발생하는 것을 막기 위함입니다.

 

 

테이블 수정

SCOTT@orcl> conn / as sysdba
Connected.
SYS@orcl> select current_scn from v$database;

되돌아갈 SCN을 미리 확인해줍니다.

 

 

SCOTT@orcl> select * from emp_test;

비교를 위해 변경전 데이터를 확인해줍니다.

 

 

SCOTT@orcl> update emp_test set sal = 1000;

14 rows updated.

SCOTT@orcl> commit;

Commit complete.

SCOTT@orcl> select * from emp_test;

모든 사원의 급여를 1000으로 변경했습니다. 이후 Commit하고 데이터를 확인하겠습니다.

 

 

Flashback Table 작업

SCOTT@orcl> flashback table emp_test to scn 1406603;

Flashback complete.

이전에 미리 확인해놨던 SCN 번호로 flashback 작업을 했습니다.

 

 

SCOTT@orcl> select * from emp_test;

변경하기 이전의 시점의 데이터로 복구된것을 확인할 수 있습니다.

 

 

 

Flashback Drop

Oracle에서 Drop  Table명령을 사용해서 테이블을 삭제하면 recycle bin 휴지통으로 테이블의 세그먼트가 이동됩니다. 윈도우 운영체제에서 휴지통으로 파일을 삭제하고 아직 휴지통에 파일이 남아있다면 복원할 수 있듯이, 오라클에서도 Flashback Drop 기능을 사용해서 복원작업이 가능합니다. 단, purge 명령어를 사용해서 테이블을 Drop했다면 복원이 불가능합니다.

 

 

실습용 테이블 생성

SCOTT@orcl> create table dept_test as select deptno from dept;

Table created.

SCOTT@orcl> drop table dept_test;

Table dropped.

SCOTT@orcl> create table dept_test as select deptno, dname from dept;

Table created.

SCOTT@orcl> drop table dept_test;

Table dropped.

SCOTT@orcl> create table dept_test as select deptno, dname, loc from dept;

Table created.

SCOTT@orcl> drop table dept_test;

Table dropped.

실습을 위해 dept_test라는 테이블을 3번 각각 구조를 다르게 생성하고 Drop하는 작업을 했습니다.

 

 

recycle bin

SCOTT@orcl> show recyclebin

명령어를 통해 휴지통에 있는 테이블들을 확인할 수 있습니다. Drop한 3개의 dept_test 테이블이 있습니다.

 

 

Flashback Drop 작업

SCOTT@orcl> flashback table dept_test to before drop;

Flashback complete.

SCOTT@orcl> desc dept_test;

to before drop 명령어를 사용하면 가장 마지막에 Drop한 테이블의 정보를 확인할 수 있습니다.

 

 

SCOTT@orcl> drop table dept_test;

Table dropped.

SCOTT@orcl> flashback table "BIN$qvYhLOvKQqngUBGsAgAC+g==$0" to before drop;

Flashback complete.

SCOTT@orcl> desc dept_test;

recycle bin의 RECYCLE_NAME을 이용해서 동일한 이름의 테이블이더라도 원하는 DROP version의 테이블을 복원할 수 있습니다.