Oracle9i의 Flashback Query를 확장한 내용이 Flashback Versions Query와 Flashback Transaction Query이다. Flashback Versions Query는 Select시 versions between명령을 넣으면 해당 정보의 history 정보가 나온다. Flashback Versions Query와 Flashback Transaction Query는 undo tablespace의 정보를 이용하기 때문에, flashback log와는 관련이 없다.
Flashback Versions Query Test
테스트 개요
Flashback Version Query는 두 가지로 나뉜다. 하나는 System Change Number(SCN)를 기준으로 하는 것과 다른 하나는 timestamp를 기준으로 하는 것이다. 여기서는 두 가지를 다 테스트 해보겠다.
테스트 환경
Undo data를 이용하여 해당 정보의 history를 볼 수 있는 Flashback Versions Query를 테스트 해보겠다. Undo 정보의 retention guarantee를 위해서는 undo tablespace를 retention guarantee해야 한다. 이 내용은 위에서 언급하였으므로 여기서는 생략한다.
Flashback Versions Query는 Flashback database와 상관없음을 확인해 보겠다.
|
SQL> conn / as sysdba Connected. SQL> select flashback_on from v$database;
FLASHB —— NO |
테스트 예제
두 가지 versions query에 대해 test해보겠다.
|
SQL> conn scott/tiger Connected. SQL> select to_char(sysdate, ‘YY-MM-DD HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’YY-MM-DDHH24:MI:S ———————————- 03-10-10 16:06:58
SQL> select empno, ename, job, sal, deptno from test_emp;
EMPNO ENAME JOB SAL DEPTNO ———- ——————– —————— ———- ———- 7782 CLARK MANAGER 2450 10 7839 KING PRESIDENT 5000 10 7934 MILLER CLERK 1300 10
SQL> update test_emp set sal = 1400 where ename =’MILLER’;
1 row updated.
SQL> select empno, ename, job, sal, deptno from test_emp 2 3 where ename=’MILLER’;
EMPNO ENAME JOB SAL DEPTNO ———- ——————– —————— ———- ———- 7934 MILLER CLERK 1400 10 7934 MILLER CLERK 1300 10
SQL> select empno, ename, job, sal, deptno from test_emp 2 versions between timestamp 3 to_timestamp(’03-10-10 16:06:58′, ‘YY-MM-DD HH24:MI:SS’) AND 4 to_timestamp(sysdate) 5 where ename =’MILLER’; select empno, ename, job, sal, deptno from test_emp * ERROR at line 1: ORA-30054: invalid upper limit snapshot expression
SQL> select empno, ename, job, sal, deptno from test_emp 2 versions between timestamp
4 to_timestamp(sysdate) where ename =’MIILER’; select empno, ename, job, sal, deptno from test_emp * ERROR at line 1: ORA-30052: invalid lower limit snapshot expression |
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 는 별 문제 없이 수행되었다. 그러나 VERSIONS BETWEEN TIMESTAMP는 error를 떨어뜨렸다.
ORA-30054: invalid upper limit snapshot expression
이 error는 4번째
줄의 to_timestamp(sysdate)가
잘못되었다는 error로
현재의 current_scn이
미처
현재시간까지 update되지
않았음을
의미한다.
ORA-30052: invalid lower limit snapshot expression
또한 minscn보다 이른 시간을 넣으면 위와 같은 error가 생긴다.
위와 같은 내용을 정확히 처리하기 위해서 Oracle Database 10g부터 새로운 pseudocolumn이 생겼다.
-
VERSIONS_STARTTIME
-
VERSIONS_STARTSCN
-
VERSIONS_ENDTIME
-
VERSIONS_ENDSCN
-
VERSIONS_XID
-
VERSIONS_OPERATION
또한 scn과 timestamp의 관계를 알아볼 수 있는 function 두 가지가 추가되었다.
-
SCN_TO_TIMESTAMP
-
TIMESTAMP_TO_SCN
위의 pseudocolumn을 이용하여 다시 정확히 query를 수행해 보겠다.
|
SQL> update test_emp set sal =1600 where ename =’MILLER’;
1 row updated. SQL> COMMIT; Commit complete.
SQL> select versions_starttime, versions_endtime, sal from test_emp 2 versions between scn minvalue and maxvalue 3 where ename=’MILLER’;
VERSIONS_STARTTIME VERSIONS_ENDTIME SAL —————————— —————————— ———- 10-OCT-03 04.43.55 PM 1600 10-OCT-03 04.40.38 PM 10-OCT-03 04.43.55 PM 1500 10-OCT-03 04.40.38 PM 1400 SQL> select sal from test_emp
4 and to_timestamp(‘2003-10-10 16:43:55’, ‘YYYY-MM-DD HH24:MI:SS’) 5 where ename =’MILLER’;
SAL ———- 1500 1400
SQL> select scn_to_timestamp(current_scn) from v$database;
SCN_TO_TIMESTAMP(CURRENT_SCN) ————————————————————————— 10-OCT-03 04.50.51.000000000 PM
SQL> select sal from test_emp
SAL ———- 1600 1500 1400
|
위의 내용과 같이 변경되기 전의 내용인 sal=1400인 정보는 VERSIONS_STARTTIME은 없고 VERSIONS_ENDTIME만이 있다. 또한 current_scn에 대한 정보에 대한 timestamp를 직접 query하여 정보를 넣으면 마지막 변경 사항을 볼 수 있다.
Flashback Transaction Query Test
테스트 개요
비교적 간단한 Flashback Transaction Query test를 해보겠다. 테스트 환경은 Flashback Versions Query와 차이가 없다.
테스트 예제
|
SQL> select sal from test_emp 2 versions between scn minvalue and maxvalue 3 where ename=’MILLER’;
SAL ———- 1600
SQL> update test_emp set sal=1700 2 where ename=’MILLER’;
1 row updated.
SQL> commit;
Commit complete.
SQL> update test_emp set sal=1800 2 where ename =’MILLER’;
1 row updated.
SQL> commit;
Commit complete.
SQL> select versions_xid, sal 2 from test_emp 3 versions between scn minvalue and maxvalue 4 where ename=’MILLER’;
VERSIONS_XID SAL —————- ———- 0006000F0000001B 1800 0005000F00000036 1700 1300
SQL> select operation, undo_sql, table_name from
OPERATION ——————————– UNDO_SQL ————————————————————————— TABLE_NAME ————————————————————————— UPDATE update “SCOTT”.”EMP” set “SAL” = ‘1300’ where ROWID = ‘AAAL+ZAAEAAAAAdAAN’; EMP |
활용가이드 및 결론
Database를 관리하는 DBA는 항상 주의를 요하게 된다. 하나의 실수가 돌이킬 수 없는 엄청난 결과를 초래할 수 있기 때문이다. 문제는 DBA는 사람이기 때문에 그러한 실수를 원천적으로 봉쇄할 방법이 없다는 것이다. 따라서 차후의 선책으로 Flashback Query를 이용할 수 있다. 이미 일어난 예기치 않은 장애에 대해 많은 부분을 Flashback Query가 해결해 줄 수 있다. 많은 양의 log들과 예기치 않은 장애 시 해결할 방법 및 대책을 효과적으로 만들어 놓았다면 Oracle의 새로운, 그리고 더욱 향상된 Flashback Query를 효과적으로 사용할 수 있을 것이다.