Flashback Technology

Oracle Database 10g 부터 지원하는 Flashback 기술은 사용자의 실수를 해결하기 위해 등장한 새로운 기능이다. 실수로 테이블을 삭제 했거나 잘못 실행 된 DML 문장을 COMMIT 시켰을 경우 보다 손 쉽게 이전 상태로 되돌리 수 있는 기능이다.

사용자의 실수 또는 응용 프로그램의 오류로 인해 논리적 손상이 발생한 경우 빠르고 쉽게 Recovery를 지원한다.

• Flashback Database: Flashback Log를 이용하여 불완전 복구를 손쉽게 수행
• Flashback Table: Undo Data를 이용하여 Table을 과거 시점으로 복구
• Flashback DROP: Recycle bin을 이용하여 DROP 된 Table 복구
• Flashback Query: 과거의 특정 시점의 Data를 쿼리 (Undo Data 활용)
• Flashback Version Query: 여러 트랜잭션의 각 버전별 상태 확인 (Undo Data 활용)
• Flashback Transaction Query: 특정 트랜잭션의 Undo SQL 생성 (Undo Data 활용)
• Flashback Transaction: 특정 트랜잭션을 복구 (Undo, Redo Data 활용)
• Flashback Data Archive: 트랜잭션의 데이터를 보다 장시간 동안 유지 (Undo Data 활용)

Flashback Query

이전 시점을 확인 하기 위해서 현재의 시간 및 SCN을 확인 한다.

SCN(System Change Number): Database는 운영되는 도중 수 많은 명령문들이 실행되고 있으며, 그때마다 일률적으로 증가되는 Number를 부여하게 되므로 명령문들의 순서 및 시점을 확인 할 수 있다.

[orcl:~]$ . oraenv

ORACLE_SID = [orcl] ? orcl

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[orcl:~]$ sqlplus / as sysdba

SQL> alter user scott identified by tiger account unlock ;

SQL> conn scott/tiger

SQL> set linesize 150

SQL> CONN system/oracle

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI:SS') AS SCN_DATE FROM dual;

SCN_DATE

------------------

20-APR-10 00:44:38

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

     686299

현재 정보를 확인 한다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

       FROM emp

      WHERE empno = 7788;

EMPNO ENAME SAL  DEPTNO

----- ----- ---- ------

7788  SCOTT 3000 20

SQL> UPDATE emp

        SET sal = sal * 1.2

      WHERE empno = 7788;

SQL> COMMIT;

상기 UPDATE DML 문장을 통해서 데이터 수정 및 COMMIT한 상태로 ROLLBACK이 불가능한 상태이다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

       FROM emp

      WHERE empno = 7788;

EMPNO ENAME SAL  DEPTNO

----- ----- ---- ------

7788  SCOTT 3600 20

모든 세션은 COMMIT 된 3600의 결과를 확인 할 수 있다. 하지만 이러한 DML이 사용자의 실수로 인해 잘못 수정된 값이라면, ROLLBACK도 불가능하므로 이전 값으로 되돌리기가 까다로울 수 있다.

모든 DML은 값의 수정을 진행하면서 Undo Data가 생성된다. Oracle Database 10g 이전까지는 Undo Data를 따로 접근 가능한 방법이 존재하지 않았으나 이후로는 가능하다.

FLASHBACK Query(Oracle Database 9i 부터 지원)는 아존 시점의 Data를 확인 할 수 있는 Query이며 COMMIT이 진행 되었더라도 Undo Data를 확인 할 수 있게 해준다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

       FROM emp AS OF TIMESTAMP TO_DATE('20-APR-10 00:44:38', 'DD-MON-RR HH24:MI:SS')

      WHERE empno = 7788;

EMPNO ENAME SAL  DEPTNO

----- ----- ---- ------

7788  SCOTT 3000 20

SQL> SELECT empno

          , ename

          , sal

          , deptno

       FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE))

      WHERE empno = 7788;

SQL> SELECT empno

          , ename

          , sal

          , deptno

       FROM emp AS OF SCN 626299

      WHERE empno = 7788;

위의 문장 모두 동일한 결과를 가져 올 수 있다. 이렇듯 Flashback Query를 사용하면 특정 시점을 지정하여 필요한 이전 상태의 정보를 확인 할 수 있다. 단, Undo Data가 존재하는 경우 가능하다.

Flashback Version Query

Data가 반복되는 DML에 의해서 계속 변경되고 있다면, 변경 되는 상태 전체를 확인 가능하다.

SQL> UPDATE emp

        SET sal = sal * 1.2

      WHERE empno = 7788;

SQL> COMMIT;

SQL> UPDATE emp

        SET sal = sal * 1.2

      WHERE empno = 7788;

SQL> COMMIT;

SQL> UPDATE emp

        SET sal = sal * 1.2

      WHERE empno = 7788;

SQL> COMMIT;

동일한 상원번호에 대하여 반복적인 UPDATE가 진행 되었다. COMMIT이 계속 진행 되었으므로 검색을 해 보면 최종 UPDATE 된 정보를 확인 할 수 있다. 하지만 변경을 진행하는 각 과정(version)에 대한 정보를 확인하고 싶다면?

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI:SS') AS SCN_DATE FROM dual;

SCN_DATE

------------------

20-APR-10 00:47:01

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

     686356

시점을 확인 하기 위해서 현재의 시간 정보 및 SCN 확인.

SQL> SELECT empno

          , sal

          , versions_starttime

          , versions_endtime

          , versions_operation

       FROM emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE

      WHERE empno = 7788;

EMPNO SAL    VERSIONS_STARTTIME    VERSIONS_ENDTIME      VERSIONS_OPERATION

----- ------ --------------------- --------------------- ------------------

7788  6220.8 20-APR-10 12.46.27 AM                       U

7788    5184 20-APR-10 12.46.15 AM 20-APR-10 12.46.27 AM U

7788    4320 20-APR-10 12.46.15 AM 20-APR-10 12.46.15 AM U

7788    3600 20-APR-10 12.44.59 AM 20-APR-10 12.46.15 AM U

7788    3000                       20-APR-10 12.44.59 AM

Flashback Version Query를 사용하면 된다. Version Query는 시간(TIMESTAMP) 또는 SCN을 기반으로 범위를 지정하며, 해당 범위 내의 변경 내역을 확인 할 수 있다. 이때 각 버전의 시작시간(versions_starttime)및 종료시간(versions_endtime)과 어떤 작업이 진행 되었는지(version_operation)를 함계 확인 할 수 있다. MINVALUE와 MAXVALUE는 접근 가능한 최소, 최대의 시간을 가져 올 수 있다(접근 가능한 Undo Data의 범위 까지).

SQL> SELECT empno

          , sal

          , versions_starttime

          , versions_endtime

          , versions_operation

       FROM emp VERSIONS BETWEEN TIMESTAMP TO_DATE('20-APR-10 00:44:38', 'DD-MON-RR HH24:MI:SS')

                                       AND TO_DATE('20-APR-10 00:47:01', 'DD-MON-RR HH24:MI:SS')

      WHERE empno = 7788;

동일한 결과를 가져올 수 있는 문장이다. MINVALUE, MAXVALUE를 사용하지 않고 원하는 시간을 지정할 수 있다.

SCN을 기반으로 동일한 결과를 확인 할 수 있다. 각 버전의 시작, 종료 시간 외에도 versions_startscn, versions_enscn을 함께 검색할 수 있다.

SQL> SELECT empno

          , sal

          , versions_startscn

          , versions_endscn

          , versions_operation

       FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

      WHERE empno = 7788;

EMPNO SAL    VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION

----- ------ ----------------- --------------- ------------------

7788  6220.8 686337                            U

7788    5184 686333            686337          U

7788    4320 686331            686333          U

7788    3600 686305            686331          U

7788    3000                   686305

SQL> SELECT empno

          , sal

          , versions_starttime

          , versions_endtime

          , versions_operation

       FROM emp VERSIONS BETWEEN SCN 686299 AND 686356

      WHERE empno = 7788;

이렇게 COMMIT이 진행된 DML의 이전 Data를 확인 할 수 있다는 것은 필요 시 이전 상태로 되돌리는 것도 보다 쉽게 가능하다(뒤에서 FLASHBACK TABLE 명령 확인)..

SQL> UPDATE emp

        SET sal = (SELECT sal

                     FROM emp AS OF TIMESTAMP (SYSDATE - INTERVAL  '10' MINUTE)

                    WHERE empno = 7788)

      WHERE empno = 7788;

SQL> SELECT empno

          , sal

       FROM empno = 7788;

EMPNO SAL

----- ----

7788  3000

SQL> COMMIT;

Flashback Transaction Query

Flashback Query와 Version Query를 이용하면 특정 시점 및 변경 되는 각 버전의 상태를 확인 할 수 있다. 또한 이를 이용해서 이전 시점으로 손 쉽게 되돌아 갈 수도 있다.

Transaction Query는 변화되고 있는 각 Data를 이전으로 되돌릴 수 있는 명령문(DML) 문장을 만들어 주는 Query 이다(FLASHBACK_TRANSACTION_QUERY의 분류 Query 해야 하기 때문에 FLASHBACK_TRANSACTION_QUERY의 SELECT 권한 필요).

SQL> SELECT * FROM dept;

DEPTNO DNAME      LOC

------ ---------- -------

10     ACCOUNTING NEWYORK

20     RESEARCH   DALLAS

30     SALES      CHICAGO

40     OPERATIONS BOSTON

현재 상태를 확인한다. 그 뒤 여러 DML 문장을 실행 시키고 각각 COMMIT도 진행한다..

SQL> DELETE dept WHERE deptno = 40;

SQL> UPDATE dept

        SET loc = 'SEOUL'

      WHERE deptno = 10;

SQL> COMMIT;

SQL> INSERT INTO dept

     VALUES (50, 'MARKETTING', 'BUSAN');

SQL> UPDATE dept

        SET loc = 'JEJU'

      WHERE deptno = 10;

SQL> COMMIT;

전체 4개의 DML 문장이 실행 되었으며 두 개의 Transaction으로 나뉘어 실행 되었다.

SQL> SELECT * FROM dept;

DEPTNO DNAME      LOC

------ ---------- -------

50     MARKETTING BUSAN

10     ACCOUNTING JEJU

20     RESEARCH   DALLAS

30     SALES      CHICAGO

SQL> SELECT deptno

          , dname

          , loc

          , versions_xid       AS XID

          , versions_startscn  AS START_SCN

          , versions_endscn    AS END_SCN

          , versions_operation AS OP

       FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

DEPTNO DNAME      LOC     XID              START_SCN END_SCN  OP

------ ---------- ------- ---------------- --------- -------- --

50     MARKETTING BUSAN   06001A0078010000 692534             I

40     OPERATIONS BOSTON  0200190065010000 692529             D

40     OPERATIONS BOSTON                             692529

10     ACCOUNTING JEJU    06001A0078010000 692534             U

10     ACCOUNTING SEOUL   0200190065010000 692529    692534   U

10     ACCOUNTING NEWYORK                            692529

20     RESEARCH   DALLAS

30     SALES      CHICAGO

VERSION Query를 이용하여 DEPT 테이블의 값들이 어떻게 변경 되었는지를 확인한다. versions_xid는 Transaction ID이며 어떤 명령문이 어떤 순서로 실행 되었는지를 확인 할 수 있다.

이제 FLASHBACK_TRANSACTION_QUERY를 확인하여 UNDO SQL을 확인 한다. 위에서 확인 한 transaction id를 이용하여 특정 transaction에서 진행된 명령문의 UNDO SQL 만 확인 할 수 있다.

SQL> SELECT start_scn

          , commit_scn

          , operation_sql

       FROM FLASHBACK_TRANSACTION_QUERY

      WHERE xid = HEXTORAW('&XID');

Enter value for xid: 06001A0078010000

START_SCN COMMIT_SCN OPERATION UNDO_SQL

--------- ---------- --------- ----------------

692530    692534     UPDATE    UPDATE DEPT SET LOC = 'SEOUL' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

692530    692534     DELETE    DELETE FROM DEPT WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

Version Query에서 확인 했던 XID 값을 이용한다.

SQL> SELECT undo_sql

       FROM FLASHBACK_TRANSACTION_QUERY

      WHERE table_owner = 'SCOTT'

        AND table_name = 'DEPT'

        AND start_timestamp >= SYSTIMESTAMP - INTERVAL '10' MINUTE;

UNDO-SQL

--------------------------------------------------------------------------

UPDATE DEPT SET LOC = 'NEWYORK' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');

UPDATE DEPT SET LOC = 'SEOUL' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

DELETE FROM DEPT WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

START_TIMESTAMP, TABLE_OWNER, TABLE_NAME 등을 이용하여 원하는 시간 및 테이블에 접근했던 문장들의 UNDO SQL을 검색할 수 있다.

SQL> SELECT undo_sql

       FROM FLASHBACK_TRANSACTION_QUERY

      WHERE xid IN (SELECT versions_xid FROM dept VERSIONS BETWEEN SCN MINVAL AND MAXVALUE);

UNDO-SQL

--------------------------------------------------------------------------

UPDATE DEPT SET LOC = 'SEOUL' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

DELETE FROM DEPT WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

UPDATE DEPT SET LOC = 'NEWYORK' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');

또는 Version Query를 통해서 얻을 수 있는 XID(Transaction ID)를 Subquery로 이용해도 동일한 결과를 구할 수 있다.

검색 된 Undo SQL들 중 필요한 문장만 복사해서 실행하면 이전 상태로 되돌릴 수 있다.

SQL> DELETE FROM DEPT WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

SQL> UPDATE DEPT SET LOC = 'NEWYORK' WHERE ROWID = 'AAAMfKAAEAAAAAQAAA';

SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');

SQL> COMMIT;

SQL> SELECT * FROM dept;

DEPTNO DNAME      LOC

------ ---------- -------

40     OPERATIONS BOSTON

10     ACCOUNTING NEWYORK

20     RESEARCH   DALLAS

30     SALES      CHICAGO

Flashback Table ( Recyclebin )

Flashback Table은 지정된 테이블을 과거의 시점으로 되돌리거나 삭제된 테이블을 삭제되기 이전 상태로 되돌릴 수 있는 기능이다. 우선은 DROP 된 테이블을 되살릴 수 있는 기능부터 확인 해 보자.

실습을 위해서 새로운 테이블스페이스 및 테이블들을 생성한다.

SQL> CONN system/oracle

SQL> CREATE TABLESPACE tbs

     DATAFILE 'test01.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 20M

     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Locally Management Tablespace를 생성하며, Uniform Size는 1MB로 지정한다. 최초 크기는 10MB이며, 20MB까지 자동확장 가능하게 설정한다.

SQL> CREATE TABLE emp

     TABLESPACE tbs

     STORAGE (MINEXTENTS 2)

     AS SELECT * FROM emp;

SQL> CREATE TABLE dept

     TABLESPACE tbs

     STORAGE (MINEXTENTS 2)

     AS SELECT * FROM dept;

두 개의 테이블을 생성하며 각각 Extent를 2개씩 할당 받는다.

SQL> CREATE TABLE test

     TABLESPACE tbs

     AS SELECT * FROM emp;

SQL> CREATE INDEX test_idx ON test (empno)

     TABLESPACE tbs;

실습에 필요한 TEST 테이블을 생성하며 Index도 함께 생성한다..

SQL> ALTER TABLE test

       ADD PRIMARY KEY (empno);

SQL> CREATE TRIGGER test_trig

      AFTER INERT ON test

      BEGIN

            NULL;

        END;

/

추가적으로 제약조건 및 Trigger도 생성하였다.

SQL> SELECT object_name

          , object_id

          , object_type

          , status

       FROM user_objects

      WHERE created > SYSDATE - INTERVAL '5' MINUTE;

OBJECT_NAME OBJECT_ID OBJECT_TYPE STATUS

----------- --------- ----------- ------

EMP         52686     TABLE       VALID

DEPT        52687     TABLE       VALID

TEST        52688     TABLE       VALID

TEST_IDX    52689     INDEX       VALID

TEST_TRIG   52690     TRIGGER     VALID

현재 생성된 Object 들이다.

SQL> SELECT constraint_name

          , constraint_type

       FROM user_constraints

      WHERE table_name = 'TEST';

CONSTRAINTS_NAME CONSTRAINTS_TYPE

---------------- ----------------

SYS_C005443      P

TEST 테이블에 추가된 제약 조건의 이름을 확인 한다.

SQL> SELECT *

       FROM dba_free_space

      WHERE tablespace_name = 'TBS':

TABLESPACE FILE_ID BLOCK_ID BYTES   BLOCKS RELATIVE_FNO

---------- ------- -------- ------- ------ ------------

TBS        6       777      3145728 384    6

TBS 테이블스페이스에는 여러 Object들이 생성 되었으므로 Free Space도 줄어 들어 있는 것을 확인 한다. 현재 상태는 Block ID 777부터 384개의 3MB의 여유 공간이 존재한다.

SQL> SELECT segment_name

          , extent_id

          , block_id

          , blocks

       FROM dba_extents

      WHERE tablespace_name = 'TBS';

SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES   BLOCKS

------------ --------- -------- ------- ------

EMP          0           9      1048576 128

EMP          1         137      1048576 128

DEPT         0         265      1048576 128

DEPT         1         393      1048576 128

TEST         0         521      1048576 128

TEST_IDX     0         649      1048576 128

TBS 테이블스페이스에 생성된 Object들이 어떻게 공간을 소모하고 있는지 확인한다(Enterprise Manager의 Extent Map).

확인 될 결과 해당 테이블스페이스의 마지막 3MB가 여유공간으로 남아 있으며 앞의 공간들은 모두 사용되고 있는 것을 확인 할 수 있다.

SQL> DROP TABLE test;

SQL> SELECT * FROM test;

ERROR at line 1;

ORA-00942: table or view does not exist

테이블을 삭제 하였다. 때문에 TEST 테이블을 통한 검색 및 어떠한 DML도 실행할 수 없다. 각각의 뷰를 확인하여 어떻게 처리되고 있는지를 확인한다.

SQL> SELECT *

       FROM dba_free_space

      WHERE tablespace_name = 'TBS':

TABLESPACE FILE_ID BLOCK_ID BYTES   BLOCKS RELATIVE_FNO

---------- ------- -------- ------- ------ ------------

TBS        6       777      3145728 384    6

TBS        6       649      1048576 128    6

TBS        6       521      1048576 128    6

TEST와 관련된 정보는 DBA_EXTENTS 뷰를 통해서도 확인 할 수 없다.

SQL> SELECT object_name

          , object_id

          , object_type

          , status

       FROM user_objects

      WHERE created > SYSDATE - INTERVAL '1' HOUR;

OBJECT_NAME                    OBJECT_ID OBJECT_TYPE STATUS

------------------------------ --------- ----------- --------

EMP                            52686     TABLE       VALID

DEPT                           52687     TABLE       VALID

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 52688     TABLE       VALID

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 52689     INDEX       VALID

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 52690     TRIGGER     INVALID

하지만 삭제된 TEST 테이블과 관련된 Object 공간은 삭제된 것처럼 보이지만 이름만 변경된 것을 확인 할 수 있다.

SQL> SELECT constraint_name

          , constraint_type

       FROM user_constraints

      WHERE table_name = 'TEST';

CONSTRAINTS_NAME               CONSTRAINTS_TYPE

------------------------------ ----------------

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 P

제약 조건 역시 이름이 변경 되었다.

SQL> SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME

------------- ------------------------------ ----------- -------------------

TEST          BIN$hjvE20sxQhPgQKjAZGQmEw==$0 TABLE       2012-08-28:08:22:38

RECYCLEBIN을 확인 해 보면 TEST 테이블이 언제 삭제 되었는지, 변경된 이름이 무엇인지 확인 가능하다. Oracle Database 10g 부터는 테이블을 삭제하면 보다 손쉬운 복구를 위해서 이름을 변경한다.

SQL> SELECT object_name

          , original_name

          , type

          , can_undrop

       FROM recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE    CAN_UNDROP

------------------------------ ------------- ------- ----------

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 TEST          TABLE   NO

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 TEST_IDX      INDEX   NO

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 TEST_TRIG     TRIGGER YES

보다 상세한 정보는 RECYCLEBIN 뷰를 통해 확인 가능하다.

Extent Map을 확인 해 보면 삭제된 공간을 Unmapped로 처리하고 있다. 이제 삭제된 테이블 TEST를 복구하는 방법은 아래와 같다.

SQL> FLASHBACK TABLE test TO BEFORE DROP;

SQL> SELECT * FROM test;

EMPNO ENAME JOB      MGR  HIREDATE  SAL  COMM DEPTNO

----- ----- -------- ---- --------- ---- ---- ------

7369  SMITH CLERK    7902 17-DEC-80  800      20

7499  ALLEN SALESMAN 7698 20-FEB-81 1600 300  30

7521  WARD  SALESMAN 7698 22-FEB-81 1250 500  30

7566  JONES MANAGER  7839 02-APR-81 2975      20

SQL> SELECT object_name

          , object_id

          , object_type

          , status

       FROM user_objects

      WHERE created > SYSDATE - INTERVAL '1' HOUR;

OBJECT_NAME                    OBJECT_ID OBJECT_TYPE STATUS

------------------------------ --------- ----------- --------

EMP                            52686     TABLE       VALID

DEPT                           52687     TABLE       VALID

TEST                           52688     TABLE       VALID

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 52689     INDEX       VALID

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 52690     TRIGGER     INVALID

SQL> SELECT constraint_name

          , constraint_type

       FROM user_constraints

      WHERE table_name = 'TEST';

CONSTRAINTS_NAME               CONSTRAINTS_TYPE

------------------------------ ----------------

BIN$hjvE20sxQhPgQKjAZGQmEw==$0 P

하지만 테이블을 제외한 다른 Object 및 제약 조건은 원래의 이름으로 되돌아 가지 않는다.

삭제된 테이블을 복구 할 때 다른 이름으로 복구를 하려면 RENAME 절을 사용한다..

SQL> FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

동일한 이름이 많아 혼동이 될 때는 아래와 같이 현재 이름을 이용해도 된다. 단, 이때 테이블의 이름은 큰 따옴표로 묶어야 한다..

SQL> FLASHBACK TABLE "BIN$hjvE20sxQhPgQKjAZGQmEw==$0" TO BEFORE DROP;

SQL> DROP TABLE test;

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME

------------- ------------------------------ ----------- -------------------

TEST          BIN$hjvE20sxQhPgQKjAZGQmEw==$0 TABLE       2012-08-28:08:22:38

SQL> SELECT * FROM "BIN$hjvE20sxQhPgQKjAZGQmEw==$0";

EMPNO ENAME JOB      MGR  HIREDATE  SAL  COMM DEPTNO

----- ----- -------- ---- --------- ---- ---- ------

7369  SMITH CLERK    7902 17-DEC-80  800      20

7499  ALLEN SALESMAN 7698 20-FEB-81 1600 300  30

7521  WARD  SALESMAN 7698 22-FEB-81 1250 500  30

7566  JONES MANAGER  7839 02-APR-81 2975      20

삭제가 된 상태에서도 SELECT를 통하여 Data의 검증이 가능하다.

TEST 테이블을 다시 삭제 한 상태이다. 이때 공간의 할당은 해제 되지 않고 이름만 변경 한다고 하였다. 그렇다면 TBS 테이블스페이스의 다른 Object들이 공간을 늘려 가거나 새로운 Object가 생성 된다면 어떻게 될까?

SQL> ALTER TABLE emp ALLOCATE EXTENT;

SQL> ALTER TABLE dept ALLOCATE EXTENT;

SQL> CREATE TABLE salgrade

     TABLESPACE tbs

     AS SELECT * FROM salgrade;

SQL> SELECT segment_name

          , extent_id

          , block_id

          , bytes

          , blocks

       FROM dba_extents

      WHERE tablespace_name = 'TBS';

SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES   BLOCKS

------------ --------- -------- ------- ------

EMP          0            9     1048576 128

EMP          1          137     1048576 128

EMP          2          777     1048576 128

DEPT         0          265     1048576 128

DEPT         1          393     1048576 128

DEPT         2          905     1048576 128

SALGRADE     0         1033     1048576 128

SQL> SELECT *

       FROM dba_free_space

      WHERE tablespace_name = 'TBS':

TABLESPACE FILE_ID BLOCK_ID BYTES   BLOCKS RELATIVE_FNO

---------- ------- -------- ------- ------ ------------

TBS        6       649      1048576 128    6

TBS        6       521      1048576 128    6

삭제 된 TEST 테이블이 차지하고 있는 공간은 그대로 남겨두고 원래 빈 공간으로 남아 있던 영역을 사용하는 것을 확인 할 수 있다. 하지만 원래 있던 빈 공간 3MB는 다 사용된 상태이다. 여기서 추가적인 Extent가 필요하면 어떻게 될까? TBS 테이블스페이스는 자동확장 기능을 사용하고 있기 때문에 20MB까지 추가 확장은 가능하다.

SQL> ALTER TABLE emp ALLOCATE EXTENT;

SQL> SELECT segment_name

          , extent_id

          , block_id

          , bytes

          , blocks

       FROM dba_extents

      WHERE tablespace_name = 'TBS';

SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES   BLOCKS

------------ --------- -------- ------- ------

EMP          0            9     1048576 128

EMP          1          137     1048576 128

EMP          2          777     1048576 128

EMP          3          521     1048576 128

DEPT         0          265     1048576 128

DEPT         1          393     1048576 128

DEPT         2          905     1048576 128

SALGRADE     0         1033     1048576 128

SQL> SELECT *

       FROM dba_free_space

      WHERE tablespace_name = 'TBS':

TABLESPACE FILE_ID BLOCK_ID BYTES   BLOCKS RELATIVE_FNO

---------- ------- -------- ------- ------ ------------

TBS        6       649      1048576 128    6

자동 확장은 분명 가능하지만 DROP TABLE이 진행되었던 영역 역시 빈 공간으로 인식하므로 해당 공간을 먼저 사용한 것을 확인 할 수 있다. 이렇게 이름만 변경해서 남겨져 있던 TEST 테이블의 공간이 사용되어 버리면 TEST 테이블은 복구 가능할까?

SQL> SELECT * FROM recyclebin;

no rows selected

RECYCLEBIN에 남겨진 정보가 없다. 당연히 FLASHBACK TABLE 명령도 진행 할 수 없다.

SQL> FLASHBACK TABLE test TO BEFORE DROP;

ERROR at line 1;

ORA-38305: object not in RECYCLE BIN

SQL> DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES;

Flashback Table

Flashback Table 명령은 Drop 했던 테이블을 손 쉽게 복구 할 수 있는 기능 이외에도 Undo Data를 활용하여 보다 쉽게 이전 시점으로 Table을 되돌릴 수 있다.

SQL> CONN system/oracle

SQL> SELECT empno

          , ename

          , sal

          , rowid

          , DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) FNO

          , DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) BNO

          , DBMS_ROWID.ROWID_ROW_NUMBER(rowid) RNO

       FROM emp

      WHERE deptno = 10;

EMPNO ENAME  SAL  ROWID             FNO BNO RNO

----- ------ ---- ----------------- --- --- ---

7782  CLARK  2450 AAAMfMAAEAAAAcAAG   4  28   6

7839  KING   5000 AAAMfMAAEAAAAcAAI   4  28   8

7934  MILLER 1300 AAAMfMAAEAAAAcAAN   4  28  13

현재 EMP 테이블의 10번 부서번호를 가지고 있는 행은 3개 존재하며, 4번 파일의 28번 Block 안에 6, 8, 13번 Row 위치에 저장되어 있는 것을 확인 할 수 있다.

SQL> SELECT SYSTIMESTAMP

          , current_scn

       FROM v$database;

SYSTEMSTAMP                         CURRENT_SCN

----------------------------------- -----------

20-APR-10 06:19:30:660541 AM +09:00 712492

Flashback Table 명령을 실행 시킬 때 사용할 현재 시간 및 SCN 확인 후 10번 부서 번호를 가지고 있는 몇몇 행 정보를 변경한다.

SQL> UPDATE emp SET sal = sal * 1.2 WHERE empno = 7782;

SQL> DELETE emp WHERE empno = 7839;

SQL> COMMIT;

DML 진행 후 COMMIT까지 수행 했으므로 ROLLBACK은 불가능한 상태이다. 하지만 앞에서 배운 Flashback Query 및 Version Query를 이용하면 이전 시점의 Data도 확인 가능하다..

SQL> SELECT empno

          , ename

          , sal

       FROM emp AS OF TIMESTAMP (SYSDATE - INTERVAL '5' MINUTE)

      WHERE deptno = 10;

EMPNO ENAME  SAL

----- ------ ----

7782  CLARK  2450

7839  KING   5000

7934  MILLER 1300

SQL> SELECT empno

          , ename

          , sal

          , versions_startscn  AS startscn

          , versions_endscn    AS endscn

          , versions_operation AS OP

       FROM emp AS OF VERSION BETWEEN SCN MINVALUE AND MAXVALUE

      WHERE deptno = 10;

EMPNO ENAME  SAL  STARTSCN ENDSCN   OP

----- ------ ---- -------- -------- --

7839  KINE   5000 712579            D

7782  CLARK  2940 712579            U

7782  CLARK  2450          712579

7839  KING   5000          712579

7934  MILLER 1300

일반적으로 DML 수행 시 시간 정보나 SCN을 확인하며, 작업을 진행하지는 않는다. 때문에 잘못 수행된 DML을 되돌리기 위해서 시간 정보 및 SCN 정보가 필요할 떄 Version Query는 매우 유용한 방법이 될 수 있다.

위의 결과는 SCN 712579부터 DELTE, UPDATE가 반영 되었다. VERSIONS_STARTTIME을 함께 검색하였다면 시간 정보도 얻을 수 있다.

잘못 수행 된 명령문이 있음을 확인 하였고 이미 COMMIT이 진행 된 이후 이므로 .ROLLBACK도 불가능한 상태이다. 하지만 Version Query를 이용하여 시점을 확인 하였다면 Flashback Table 명령을 이용하면 손 쉽게 복구 가능하다. 단, Flashback Table 명령을 통해 이전으로 되돌아 가려면 아래의 명령이 언제 수행되어야 한다.

SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;

ROW MOVEMENT 명령을 수행 했다면 Flashback Table 명령을 수행해 보면,

SQL> FLASHBACK TABLE emp TO TIMESTAMP (SYSDATE - INTERVAL '10' MINUTE);

SQL> SELECT empno

          , ename

          , sal

          , rowid

          , DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) FNO

          , DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) BNO

          , DBMS_ROWID.ROWID_ROW_NUMBER(rowid) RNO

       FROM emp

      WHERE deptno = 10;

EMPNO ENAME  SAL  ROWID             FNO BNO RNO

----- ------ ---- ----------------- --- --- ---

7782  CLARK  2450 AAAMfMAAEAAAAcAAG   4  29   6

7839  KING   5000 AAAMfMAAEAAAAcAAI   4  29   8

7934  MILLER 1300 AAAMfMAAEAAAAcAAN   4  29  13

UPDATE, DELETE가 진행되기 이전 상태로 되돌아 간 것을 확인 할 수 있다. 하지만 한가지 눈에 띄는 것은 ROWID이다. Flashback을 하기 전 확인 했던 ROWID는 28번 Block에 저장되어 있던 것이 29번 Block으로 옮겨져 있는 것을 확인 할 수 있다.

Flashback Table 명령은 이렇게 원래 저장되어 있는 주소가 아닌 새로운 Block으로 이전 또는 재 배치를 통해서 수행된다. 때문에 ROW MOVEMENT를 활성화 시켜야만 Flashback Table 명령을 수행 할 수 있게 된다.

참고: 테이블에 INSERT 된 각각의 행들은 다른 주소(ROWID)로 이동이 불가능 하다. 저장된 Row의 주소 값인 ROWID는 Index에서 pointer로 사용하기 위해 Index 안에 저장되어 있는 값이므로 각각의 Row들이 빈번하게 다른 주소로 이동하게 된다면 수 많은 Index에 추가적인 변경 작업이 진행 되어야 한다. 때문에 ROW MOVEMENT는 비활성화 되어 있다.

Flashback Table은 위와 같이 이전 시점으로 Table의 Data를 되돌릴 수 있다. 또한 특정 시간 및 SCN을 직접 지정하는 것도 가능하다.

여기서는 DML을 수행하기 전 미리 시간정보와 SCN 정보를 확인 해 두었기 때문에 해당 값으로 Flashback Table을 진행하는 것도 가능하다. 하지만 실제 업무에서는 이러한 시간 정보를 기록하며 작업하지 않으므로 Version Query를 이용하는 것도 좋은 방법이 된다. 단, VERSIONS_STARTSCN 또는 VERSIONS_STARTTIMESTAMP의 컬럼의 값을 그대로 사용하면 안 된다. 해당 컬럼의 값은 말 그대로 그 시점으로부터 DML이 반영 되었다는 것이므로 해당 값 보다 더 빠른 시간 및 더 낮은 SCN 값을 사용해야 한다.

SQL> FLASHBACK TABLE emo TO TIMESTAMP TO_TIMESTAMP ("20-APR-10 06:19:30:660541 AM");

미리 확인 해 두었던 시간 정보를 이용

SQL> FLASHBACK TABLE emo TO SCN 712578;

Version Query를 통해서 얻은 SCN 값 보다 1 작은 값을 이용

SQL> FLASHBACK TABLE emo TO SCN 712579;

DML이 반영 되었던 SCN 값을 이용하여 다시 DML의 결과를 반영

SQL> SELECT empno

          , ename

          , sal

          , rowid

          , DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) FNO

          , DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) BNO

          , DBMS_ROWID.ROWID_ROW_NUMBER(rowid) RNO

       FROM emp

      WHERE deptno = 10;

EMPNO ENAME  SAL  ROWID             FNO BNO RNO

----- ------ ---- ----------------- --- --- ---

7782  CLARK  2450 AAAMfMAAEAAAAcAAG   4  29  19

7934  MILLER 1300 AAAMfMAAEAAAAcAAN   4  29  25

DML을 재 실행 한 것이 아니고 Flashback을 이용하여 다시 DELETE와 UPDATE 결과가 반영된 것을 확인 할 수 있다. 단, ROWID는 변경 된다.

SQL> FLASHBACK TABLE emo TO SCN 712578;

다시 원래대로 변경한다. 그렇다면 Flashback Table은 원하는 이전 시점 어디로든 되돌아 갈 수 있다.

SQL> FLASHBACK TABLE emo TO SCN 600000;

ERROR at line 1;

ORA-00604: error occured at recursive SQL level 1

ORA-12801: error signaled in parallel query server P000

ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small

오래된 SCN을 이용하여 Flashback을 진행하면 Snapshot too old error가 발생한다. Flashback Table은 Undo Data를 이용하므로 접근하고자 하는 Undo Data가 존재하지 않으면 수행 할 수 없다.

SQL> SHOW PARAMETER UNDO_RETENTION

NAME           TYPE    VALUE

-------------- ------- -----

undo_retention integer 900

현재 설정된 parameter의 값은 900 초이다. 즉, 15분의 시간 동안은 COMMIT 된 Undo Data(ROLLBACK에 사용되지 못하는 이전 Data)를 읽기 일관성 및 Flashback 기술에 사용 가능하다. 하지만 900 초가 지나가면 Undo Data 들이 저장되어 있는 공간들은 재 사용되므로 접근이 불가능해 질 수 있다.

그밖에도 몇몇 제약들이 좀더 있으므로 보다 자세한 사항은 메뉴얼을 참고한다.

Flashback Database

SQL> conn / as sysdba

SQL> alter database flashback on;

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON

------------

YES

SQL> alter system switch logfile;

SQL> drop user scott cascade;

SQL> shutdown abort

SQL> startup mount

ORACLE instance started.

TotalSystem Global Area 481259520 bytes

Fixed Size                1337352 bytes

Variable Size           398460920 bytes

Database Buffers         75497472 bytes

Redo Buffers              5963776 bytes

Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - INTERVAL '10' MINUTE);

ERROR at line 1:

ORA-38729: Not enough flashback database log data to do FLASHBACK;

SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - INTERVAL '5' MINUTE);

* ERROR 발생 시 RMAN에서 수행

[orcl:~]$ rman target /

RMAN> flashback database to timestamp (sysdate - interval '5' minute);

SQL> alter database open resetlogs;

SQL> select * from scott.dept;

DEPTNO DNAME      LOC

------ ---------- --------

10     ACCOUNTING NEW YORK

20     RESEARCH   DALLAS

30     SALES      CHICAGO

40     OPERATIONS BOSTON

Flashback Data Archive

[orcl:~]$ sqlplus / as sysdba

SQL> show parameter undo

NAME            TYPE    VALUE

--------------- ------- --------

undo_management string  AUTO

undo_retention  integer 1800

undo_tablespace string  UNDOTBS1

SQL> create tablespace flash_tbs

     datafile '/u01/app/oracle/oradata/orcl/flash_tbs.dbf' size 10m autoextend on;

SQL> create user archive_admin

     identified by oracle

     default tablespace flash_tbs;

SQL> grant connect, resource to achive_admin;

SQL> grant flashback archive administer to archive_admin;

SQL> conn archive_admin/oracle

SQL> create flashback archive FLA1

     tablespace flash_tbs

     retention 3 year;

SQL> grant flashback archive on FLA1 to scott;

SQL> conn scott/tiger

SQL> alter table scott.emp flashback archive FLA1;

SQL> alter table scott.dept flashback archive FLA1;

SQL> select owner_name, flashback_archive_name, retention_in_days

       from user_flashback_archive;

OWNER_NAME    FLASHBACK_ARCHIVE_NAME RETENTION_DAYS

------------- ---------------------- --------------

ARCHIVE_ADMIN FLA1                   1095

SQL> select * from user_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS

---------- ---------- ---------------------- ------------------ -------

DEPT       SCOTT      FLA1                   SYS_FBA_HIST_18872 ENABLED

EMP        SCOTT      FLA1                   SYS_FBA_HIST_18873 ENABLED

SQL> select * from emp where deptno = 10;

EMPNO ENAME  JOB       MGR  HIREDATE  SAL  COMM DEPTNO

----- ------ --------- ---- --------- ---- ---- ------

7782  CLARK  MANAGER   7839 09-JUN-81 2450      10

7839  KING   PRESIDENT      17-NOV-81 5000      10

7934  MILLER CLERK     7782 23-JAN-82 1300      10

SQL> update emp set sal = sal * 1.2 where deptno = 10;

SQL> commit;

SQL> select * from emp where deptno = 10;

EMPNO ENAME  JOB       MGR  HIREDATE  SAL  COMM DEPTNO

----- ------ --------- ---- --------- ---- ---- ------

7782  CLARK  MANAGER   7839 09-JUN-81 2940      10

7839  KING   PRESIDENT      17-NOV-81 6000      10

7934  MILLER CLERK     7782 23-JAN-82 1560      10

SQL> select *

       from emp as timestamp (sysdate - interval '1' hour)

      where deptno = 10;

EMPNO ENAME  JOB       MGR  HIREDATE  SAL  COMM DEPTNO

----- ------ --------- ---- --------- ---- ---- ------

7782  CLARK  MANAGER   7839 09-JUN-81 2450      10

7839  KING   PRESIDENT      17-NOV-81 5000      10

7934  MILLER CLERK     7782 23-JAN-82 1300      10

* 일정 시간이 흐른 뒤에 실행해도 검색 가능

출처: Oracle DB와 BigData