Microsoft Word - 기술노트[19회] Flashback.doc

Transcription

1 Goodus 기술노트 [19 회 ] Flashback Author 권웅원, 나지혜 Creation Date Last Updated Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 권웅원, 나지혜 문서최초작성 2 3

2 Contents 1. Flashback Flashback 이란? Flashback(9i) Flashback Overview 예제 1 (AS OF SCN) 예제 2 (AS OF TIMESTAMP) 예제 3 (Package, SCN / timestamp) Flashback(10g) Flashback Database Flashback Drop Flashback Versions Query Flashback Query Flashback transaction query Flashback Table Flashback Use Case

3 1. Flashback 1.1. Flashback 이란? 사용자실수에의한손상된데이터를 Database 의크기와상관없이복구를할수있는기능이다. 이 Flashback 기능은일반적인복구에서우려되는데이터베이스의크기를걱정하지않아도된다. 보통의사용자실수는커다란시스템장애가수반되며, 이를복구하기위해서는많은자원과시간이필요하다. 하지만 9i 에서지원되는 flashback query 와 10g 에서지원하는다양한 flashback 을통하여손쉽게사용자실수를손쉽게복구한다. Oracle 9i 부터는 AUM 환경하에서 Flashback 기능을이용하여잘못된 DML operation 으로인한복구를쉽게할수있다. 물론이전까지했던방법인 Point in Time Recovery 또한유효하다. 9i : Flashback query 10g : Flashback Database Flashback Drop Flashback Version Query Flashback Transaction Query Flashback Table Oracle Flashback Feature 는 10g Standard Edition 에서는지원하지않는다. Note : 여기서한가지짚고넘어갈점은 Flashback table, Flashback Database, Flashback Drop, Flashback Version Query, Flashback Transaction Query 는아래의표와같이각기다른영역을사용한다는점이다. Flashback Technologies Flashback Operation Flashback Database Flashback Drop Flashback Version Query Flashback Transaction Query Flashback Table Implementation Flashback logs + Redo logs Recycle bin Undo Undo Undo 1.2. Flashback(9i) Flashback Overview - Oracle 9i New features - Flashback 은사용자가 Database 의과거시점의 Consistent view 를볼수있게해준다. - 사용자들은 System time or SCN 를기초로 Read-only view 를생성할수있다. - 그시점의 Transaction committed 부분만볼수있다. - Self-service repair 를가능하게해준다. - DDL 은지원하지않는다. - Flashback 은 AUM (Automate Undo Management) 사용시만가능하다. - Undo 정보는 System level 의 Undo retention 기간동안만유지한다. - Flashback 은 Session level 에서 Enabled 할수있다. - Flashback 기능을 disable 하기전에 open 된 PL/SQL cursor 를이용하면 disable 시킨후에는 DML 를통해서 self-service repair 를할수있다. Undo Retention 지정 SQL> connect /as sysdba SQL> alter system set undo_retention = <seconds> ; 이 parameter 은 dynamic 하게변경이가능하며 initsid.ora 에지정할수있다. undo_retention 은각 Site 별로업무성격및 Undo Size 에따라서적절하게산정해서명시해준다. 또한 undo_management=auto 인지확인한다. 권한부여 - 3 -

4 SQL> grant execute on dbms_flashback to scott; 예제 1 (AS OF SCN) SCOTT session 에서 SYSTEMSTAMP 를이용하여현재시간을조회하시오. SQL> conn soctt/tiger Connected. SQL> select systimestamp from dual; SYSTIMESTAMP AUG AM -07:00 SQL> conn /as sysdba Connected. SQL> grant execute on dbms_flashback to scott; Grant succeeded. SQL> conn scott/tiger Connected. SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() SCOTT 소유의 Table 에서부서번호가 20 인부서원, 부서정보를모두삭제, Commit 하시오. SQL> delete from emp where deptno=20; 5 rows deleted. Commit complete. 삭제된 Data 가잘못삭제된것을알게되었다. 삭제된 Data 를다시되살리고자한다. 5 분후에 SQL>!date Sun Aug 7 07:40:59 PDT 2005 SQL> select * from emp where deptno=20; no rows selected SQL> select * from emp as of scn where deptno=20; SMITH CLERK DEC JONES MANAGER APR SCOTT ANALYST DEC ADAMS CLERK JAN FORD ANALYST DEC SQL> insert into emp 2 select * from emp as of scn where deptno=20; 5 rows created

5 Commit complete. SQL> select * from emp 2 where deptno=20; SMITH CLERK DEC JONES MANAGER APR SCOTT ANALYST DEC ADAMS CLERK JAN FORD ANALYST DEC 예제 2 (AS OF TIMESTAMP) HR_TEST01 session 에서 SYSTEMSTAMP 를이용하여핸재시간을조회하시오. SQL> show user USER is "HR_TEST1" SQL> select systimestamp from dual; SYSTIMESTAMP AUG AM -05:00 SQL> delete emp_test1 where department_id=20; 2 rows deleted. Commit complete. SQL> select employee_id from emp_test1 2 where department_id=20; no rows selected 삭제된 Data 가잘못삭제된것을알게되었다. 삭제된 Data 를다시되살리고자한다. 5 분후에 SQL> select employee_id 2 from emp_test1 as of timestamp(to_timestamp('02-aug-05:03:11:00', 'DD-MON-YY:HH24:MI:SS')) 3* where department_id=20; EMPLOYEE_ID SQL> select employee_id 2 from emp_test1 3 where department_id=20; no rows selected SQL> insert into emp_test1 2 select * from emp_test1 as of timestamp(to_timestamp('02-aug-05:03:11:00', 'DD-MON-YY:HH24:MI:SS')) 3 where department_id=20; 2 rows created. Commit complete. SQL> select employee_id - 5 -

6 2 from emp_test1 3* where department_id=20; EMPLOYEE_ID 예제 3 (Package, SCN / timestamp) HR_TEST01 session 에서 SYSTEMSTAMP 를이용하여핸재시간을조회하시오. SQL> show user USER is "HR_TEST1" SQL> select systimestamp from dual; SYSTIMESTAMP AUG AM -05:00 SQL> conn /as sysdba Connected. SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() SQL> conn hr_test1/oracle Connected. SQL> delete from emp_test1 2 where department_id=20; 2 rows deleted. SQL> delete from dept_test1 2 where department_id=20; 1 row deleted. Commit complete. 삭제된 Data 가잘못삭제된것을알게되었다. 삭제된 Data 를다시되살리고자한다. 5 분후에 SQL> conn /as sysdba SQL> grant execute on dbms_flashback to hr_test1; Grant succeeded. SQL> conn hr_test01/oracle SQL> begin dbms_flashback.enable_at_system_change_number(107270); end; / PL/SQL procedure successfully completed. SQL> select employee_id from emp_test1 where department_id=20; - 6 -

7 EMPLOYEE_ID SQL> select * from dept_test1 where department_id=20; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID Marketing SQL> begin dbms_flashback.disable; end; / PL/SQL procedure successfully completed. SQL> declare cursor emp_curs is select * from emp_test1 where department_id = 20; cursor dept_curs is select * from dept_test1 where department_id = 20; emp_rec emp_curs%rowtype; dept_rec dept_curs%rowtype; begin dbms_flashback.enable_at_system_change_number(107270); -- dbms_flashback.enable_at_time(to_timestamp( 02-AUG-05:03.31:21, DD-MON-YY:HH24:MI:SS )); open emp_curs; open dept_curs; dbms_flashback.disable; loop fetch dept_curs into dept_rec; exit when dept_curs%notfound; insert into dept_test1 values(dept_rec.department_id, dept_rec.department_name, dept_rec.manager_id, dept_rec.location_id); end loop; loop fetch emp_curs into emp_rec; exit when emp_curs%notfound; insert into emp_test1 values(emp_rec.employee_id, emp_rec.first_name,emp_rec.last_name, emp_rec. , emp_rec.phone_number,emp_rec.hire_date, emp_rec.job_id, emp_rec.salary, emp_rec.commission_pct, emp_rec.manager_id, emp_rec.department_id); end loop; end; / PL/SQL procedure successfully completed. 또는, 위내용을 Procedure 를생성해서복구할수도있다.(flash.sql) create or replace procedure exam_flash as cursor tmp_curs is - 7 -

8 select * from emp where deptno = 20; emp_rec tmp_curs%rowtype; begin end; / dbms_flashback.enable_at_time('03/08/21 10:47:32 '); open tmp_curs; dbms_flashback.disable; loop fetch tmp_curs into emp_rec; exit when tmp_curs%notfound; insert into emp values ( emp_rec.empno, emp_rec.ename, emp_rec.job, emp_rec.mgr,emp_rec.hiredate, emp_rec.sal, emp_rec.comm, emp_rec.deptno ); end loop; Flashback 을이용해과거데이터복구 /* exam_flash procedure 생성 */ SQL> exec exam_flash SQL> select employee_id from emp_test1 where department_id = 20; EMPLOYEE_ID SQL> select * from dept_test1 where department_id=20; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID Marketing Commit complete Flashback(10g) Flashback Database Flashback Database 개요 Oracle Database 10g 이전까지는 transactional point-in-time recovery 를위해서는 backup 용 file 과 redo log file 을이용하여원하는시간까지의복구를하였었다. 그러나이방법은 backup 용 file 이오래된것이며, archive log 가많이쌓여있을때는많은시간이소요된다. Oracle Database 10g 부터는 flashback database 를이용하여좀더빠른 recovery 가가능하게되었다. Flashback database 의사용용도는 logical data corruption 이나 user error 시유용하다. (Physical data corruption 은 H/W 문제이기때문에 Flashback database 로 recovery 는불가능하다.) Flashback Database 의장점은기존의 traditional point-in-time recovery 에비해매우빠른 recovery 가가능하다는것이다. 이러한빠른성능을낼수있는이유는 flashback database 는 database 의크기에비례해서 recovery 시간이늘어나는것이아니라, 변경된 data 의양에비례해서 recovery 시간이걸린다는점이다

9 위의그림, 앞의설명과같이 Flashback Database 는매우빠른시간의 recovery 를가능하게한다. Flashback Database 를수행하기위한 3 가지구성요소 1. Archive Mode Flashback Database 기능을적용하기위해서는 Archive Mode 로설정하여야한다. 2. Flashback Log File Flashback Log File 은오라클 Database 를구성하는 Block( 변경되기이전의이미지 Block) 을저장하는로그파일로서 10g 에서새롭게소개되고있는데이터베이스복구영역 (database recovery area) 에생성되어진다. 기존의 redo log 와의차이점 - redo log 의경우에는 archive 할수있는기능이함께제공되었지만, Flashback Log 는 archive 기능이따로제공될필요가없다.(db_recovery_file_dest, db_recovery_file_dest_size) - Flashback Log 의경우에는물리적인 database 복구에는사용될수없다는점이다. 3. RVWR Background Process Flashback Database 기능이활성화되어지면, rvwr 이라는 background process 가시작된다. 역할 : Flashback Database Data 를 Flashback Log 에기록 Flashback Database 테스트 Database 에 Flashback 기능이 ON 되어있는지확인한다. SQL> select FLASHBACK_ON from v$database; FLASHBACK_ON NO SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes - 9 -

10 Database Buffers Redo Buffers Database mounted bytes bytes SQL> select FLASHBACK_ON from v$database; FLASHBACK_ON YES SQL> alter database open; Database altered. Test Case 생성 SQL> conn scott/tiger Connected. SQL> drop table flash; Table dropped. SQL> conn scott/tiger Connected. SQL> create table flash as select * from user_objects; Table created. SQL> select count(*) from flash; COUNT(*) SQL> select current_scn from v$database; CURRENT_SCN SQL> truncate table flash; Table truncated. SQL> select current_scn from v$database; CURRENT_SCN SQL> select count(*) from flash; COUNT(*) Flashback Database 를준비하기위해 Instance 를종료시킨다. SQL> conn /as sysdba Connected

11 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. Flashback Database 를준비하기위해 Insatnce 를 Mount 시킨다. SQL> startup mount ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. 원하는시점으로되돌아가기위해조금전에기록했던 SCN 으로 Flash Back 한다. SQL> flashback database to scn ; Flashback complete. alter_<sid>.log Incomplete Recovery applied until change Flashback Media Recovery Complete Completed: flashback database to scn Database 를 read only 로 open 하여 Data 를확인후에, Resetlogs 로 Open 하여 truncate 전의데이터를복구한다. SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open read only; Database altered. SQL> select count(*) from scott.flash; COUNT(*) SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted

12 SQL> alter database open resetlogs; Database altered. SQL> select count(*) from scott.flash; COUNT(*) Flashback Drop 10g 에서 DROP TABLE 을하게되면기본적으로실제그것을 DROP 하는것보다 RECYCLE BIN 에이동시키거나이름을바꾸게됩니다. Drop 된 Table 을복구한다. Drop table 이완전 drop 되지않고, window 의휴지통과같은 recyclebin 에보관된다. 이 drop 된 table 은완전삭제를위해서는 purge 작업이필요하며, space 가부족한경우에는자동 reuse 된다. Drop 되어 recyclebin 에있는 bin$xxxxxx table 에대한직접조회도가능함. 관련 view - dba_recyclebin, user_recyclebin 관련 parameter _recyclebin = FALSE : recyclebin 기능을사용하지않는경우 False 로지정 제약사항 : table 이 system tablespace 에있는 object 는복구불가. locally managed tablespace 에위치해있는 table 만복구가능. Table 이복구되면그 table 의 index, trigger 등의연관된 object 도함께복구된다. (bitmap join index 제외 ) Partioned index-organized table 은 recycle bin 에의해보호받지못한다. recycle bin 은참조무결성을보장하지않는다. 예제 1 1) Table 을 drop 하기 ( 장애만들기 ) SQL> select * from tab; TNAME TABTYPE CLUSTERID EMP TABLE DEPT TABLE BONUS TABLE SALGRADE TABLE DUMMY TABLE SQL> drop table emp; Table dropped. SQL> select * from tab; TNAME TABTYPE CLUSTERID BIN$Lr5d8GB+sLbgQz36Y8Owtg==$0 TABLE DEPT TABLE BONUS TABLE SALGRADE TABLE DUMMY TABLE Recycle Bin 보기 SQL> select object_name, original_name, type, DROPTIME, can_undrop from user_recyclebin 2 order by droptime;

13 OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN BIN$LumGwpeDSHHgRAAQgzW92A==$0 EMP TABLE :15:01:08 YES 2) Drop 된 Table 복구하기 1 SQL> flashback table emp to before drop; Flashback complete. SQL> select object_name, original_name, type from user_recyclebin; no rows selected SQL> select table_name from user_tables; TABLE_NAME DUMMY SALGRADE BONUS DEPT EMP 3) Drop 된 table 복구하기 2 ( 동일이름의 table 이이미있는경우, 다른이름으로복구하기 ) SQL> flashback table scott.emp to before drop rename to dropped_emp; Drop된 table 완전삭제하기 SQL> drop table scott.emp purge; SQL> purge recyclebin; or SQL> purge dba_recyclebin; or SQL> purge table scott.emp 아래는몇가지 PURGE 옵션의예입니다. PURGE TABLE tablename; PURGE INDEX indexname; PURGE TABLESPACE ts_name; PURGE TABLESPACE ts_name USER username; PURGE RECYCLEBIN; PURGE DBA_RECYCLEBIN; -- drop 시바로 purge하는경우 Specific table Specific index All tables in a specific tablespace All tables in a specific tablespace for a specific user The current users entire recycle bin The whole recycle bin 예제 2 휴지통 (recyclebin) 에같은이름의 table 이여러개있을때 PURGE and FLASHBACK TO BEFORE DROP 방법 같은이름을가지는 table 이휴지통 (recyclebin) 에하나이상있을경우다루는방법입니다. table 을 PURGE 하는경우가장오래된 table 이휴지통에서 PURGE 되고 table 을 restore(flashback BEFORE DROP) 하는경우가장최근의 table 이저장됩니다. Example ======== 5 개의 table 을생성하고 drop 하자.. $ sqlplus scott/tiger SQL> CREATE TABLE t1(a NUMBER); SQL> DROP TABLE t1; SQL> CREATE TABLE t1(a varchar2(10)); SQL> DROP TABLE t1; SQL> CREATE TABLE t1(a date);

14 SQL> DROP TABLE t1; SQL> CREATE TABLE t1(a varchar2(5)); SQL> DROP TABLE t1; SQL> CREATE TABLE t1(a number); SQL> DROP TABLE t1; SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN BIN$14s6rPqoHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqmHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqnHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqpHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqqHQngMEWYESkRng==$0 T :17:40: 만일 table t1 을 purge 한다면 dropscn= 가 purge 될것이다. SQL> PURGE TABLE t1; Table purged. SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN BIN$14s6rPqoHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqnHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqpHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqqHQngMEWYESkRng==$0 T :17:40: 만일 table t1 을 restore 한다면 dropscn= 이 restore 할것이다. SQL> FLASHBACK TABLE t1 TO BEFORE DROP ; Flashback complete. SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN BIN$14s6rPqoHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqnHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqpHQngMEWYESkRng==$0 T :17:40: => 이문제를해결하기위해서.. ================================ 이문제를극복하기위해서우리는 original 이름대신에 drop 된 object name 을사용하면된다. object name 은 unique 하므로원하는것을 purge 와 restore 할수있다. Examples: SQL> FLASHBACK TABLE "BIN$14s6rPqoHQngMEWYESkRng==$0" TO BEFORE DROP RENAME TO t2; Flashback complete. SQL> select tname from tab; TNAME

15 DEPT EMP BIN$14s6rPqnHQngMEWYESkRng==$0 T1 BIN$14s6rPqpHQngMEWYESkRng==$0 T2 6 rows selected. SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN BIN$14s6rPqnHQngMEWYESkRng==$0 T :17:40: BIN$14s6rPqpHQngMEWYESkRng==$0 T :17:40: 비슷한방법으로 purge 할수있다. SQL> PURGE TABLE "BIN$14s6rPqnHQngMEWYESkRng==$0"; Table purged. SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN BIN$14s6rPqpHQngMEWYESkRng==$0 T :17:40: Flashback Versions Query 과거의어떤시점의정보를시간과 SCN(SystemChange Number) 를이용하여 Query 하는기능. 9i 부터지원된 Flashback Query 가있으며, 10g 에서는그기능이확장되어 Versions between 을이용해서일정시점이아닌시간간격의데이터를조회할수있는기능. Flashback versions query 에의해추출된 row 들은 transaction 에의해변화된 row 들의 history 를보여줌. 이기능은 data 가어떻게바뀌었는지 auditing 기능을가능하게하며 commit 된데이터만추출함. Flashback versions query 를통해서알수있는 transaction id 를통하여더추가적인정보를 Flashback Transaction Query 를통해얻을수있다. DDL 이수행되어 table 의구조가바뀌면사용불가. Flashback versions query 는 undo 를이용하여과거데이터를읽어오는것은 undo_retention 값과 undo size 에의해자동으로관리됨. 만약 undo_retention 이아주크다고하더라도, undo size 가작아서 undo 를보관하지않고재사용하게되면 flashback versions query 가수행되지않을수있음. Versions between 은시간과 SCN 으로지정할수있음 이기능을지원하기위해 scn_to_timestamp 와 timestamp_to_scn function 이지원된다. 과거의시점에대한 SCN 알기. select timestamp_to_scn(to_timestamp(' ', 'yyyymmdd hh24miss')) t from dual; => 과거의 SCN 을이용하여 Time 알기 select scn_to_timestamp( ) from dual; => 2006/02/13 17:12: Versions Query 의 Pseudo column (Select 절에사용할수있음 ) Versions_startscn, Versions_starttime Versions_endscn Versions_endtime Versions_xid Versions_operation

16 주의 : undo retention 보다이전의 version 을 query 하면 ora : invalid lower limit snapshot expression 발생함. 예제 Data 변경 (Update) 하기 SQL> conn scott/tiger Connected. SQL> select * from emp; SMITH CLERK DEC ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV TURNER SALESMAN SEP ADAMS CLERK JAN JAMES CLERK DEC FORD ANALYST DEC MILLER CLERK JAN rows selected. SQL> update emp 2 set comm= where empno=7934; 1 row updated. Commit complete. SQL> update emp 2 set comm= where empno=7934; 1 row updated. SQL> rollback; Rollback complete. SQL> update emp 2 set comm= where empno=7934; 1 row updated. Commit complete. SQL> update emp 2 set comm=

17 3 where empno=7934; 1 row updated. Commit complete. SQL> select * from emp; SMITH CLERK :00: ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: MILLER CLERK :00: rows selected 월 25 일 15 시 50 분 ~ 16 시 00 분까지 empno 가 7934 인 data 가변한내역조회 SQL> col start_time format a21 SQL> col end_time format a21 SQL> set lines 200 SQL> SELECT empno, ename, sal, comm, deptno, 2 versions_xid AS XID, 3 versions_operation as operation, 4 versions_startscn AS START_SCN, 5 versions_starttime as start_time, 6 versions_endscn AS END_SCN, 7 versions_endtime as end_time 8 FROM emp VERSIONS BETWEEN TIMESTAMP 9 TO_TIMESTAMP(' :50:00','YYYY-MM-DD HH24:MI:SS') 10 AND TO_TIMESTAMP(' :00:00','YYYY-MM-DD HH24:MI:SS') 11 WHERE empno=7934; EMPNO ENAME SAL COMM DEPTNO XID O START_SCN START_TIME END_SCN END_TIME MILLER U APR PM 7934 MILLER D U APR PM APR PM 7934 MILLER C U APR PM APR PM 7934 MILLER APR PM SQL> SELECT empno, ename, sal, comm, deptno, 2 versions_xid AS XID, 3 versions_operation as operation, 4 versions_startscn AS START_SCN, 5 versions_starttime as start_time, 6 versions_endscn AS END_SCN, 7 versions_endtime as end_time 8 FROM emp VERSIONS BETWEEN TIMESTAMP

18 9 systimestamp - interval '10' minute and systimestamp - interval '1' minute 10 WHERE empno=7934; EMPNO ENAME SAL COMM DEPTNO XID O START_SCN START_TIME END_SCN END_TIME MILLER U APR PM 7934 MILLER D U APR PM APR PM 7934 MILLER C U APR PM APR PM 7934 MILLER APR PM 조회결과로 Null è 1111è 2222 è 3333 으로변경된내역을볼수있다 Flashback Query Oracle9i 에서부터지난시점의데이터를질의하기위한 DBMS_PACKAGE 를제공했으며 10g 에서는훨씬기능을유연하게발전시켰습니다. Flashback Query 는 AS OF 절을사용하여해당시점에서의데이터값에대한질의가가능하며, 이기능은 DBMS_FLASHBACK 패키지의기능과유사합니다. Flashback versions query 는과거의일정시간구간에서조회하는것에비해 Flashback query 는, 과거의일정한시간에서 query 를하는것. Database 는현재의시간이지만, 수행하는 SQL 은혼자과거의정보를보게됨. 예제 -- Data 삭제 ( 장애만들기 ) SQL> select * from emp; SMITH CLERK :00: ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: MILLER CLERK :00: rows selected. SQL> delete emp 2 where empno=7934; 1 row deleted. Commit complete. SQL> select * from emp; SMITH CLERK :00:

19 7499 ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: rows selected. (1 건이삭제되었음 ) -- 1 시간전 Data 를구하기 SQL> select * from emp as of timestamp ( systimestamp - interval '1' hour); SMITH CLERK :00: ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: MILLER CLERK :00: rows selected 분전 Data 를구하기 delete 후바로조회하면아직 delete 되지않은것으로보인다. SQL> select * from emp as of timestamp ( systimestamp - interval '1' minute); SMITH CLERK :00: ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: MILLER CLERK :00: rows selected

20 -- 1 시간전 Data 와현재 Data 의차이를알고싶을때. -- 즉, 1 시간전과같지않은데이터를모두찾는다. SQL> select * from emp as of timestamp ( systimestamp - interval '1' hour) 2 minus 3 select * from emp; MILLER CLERK :00: 시간전의 Table 을 Backup 해놓을수있다. SQL> create table emp_back 2 as select * from emp as of timestamp ( systimestamp - interval '1' hour); Table created. -- 급하게복구를해야할때. 약 1 시간전에많은건수를삭제한경우. SQL> insert into emp 2 select * from emp as of timestamp ( systimestamp - interval '1' hour) 3 minus 4 select * from emp; 1 row created. Commit complete. SQL> select * from emp; SMITH CLERK :00: ALLEN SALESMAN :00: WARD SALESMAN :00: JONES MANAGER :00: MARTIN SALESMAN :00: BLAKE MANAGER :00: CLARK MANAGER :00: SCOTT ANALYST :00: KING PRESIDENT :00: TURNER SALESMAN :00: ADAMS CLERK :00: JAMES CLERK :00: FORD ANALYST :00: MILLER CLERK :00: rows selected Flashback transaction query FlashBack Transaction Query 라고하는것은 Flashback Version Query 의결과로나타난해당 Transaction 에대해특별한정보를얻을수있는것정도로보시면됩니다. VERSIONS_XID 값이트랜잭션의 ID 라고했는데, 이값을 FLASHBACK_TRANSACTION_QUERY 의인자값으로줘서쿼리를실행하면해당트랜잭션에대한정보를볼수있습니다. 예를들면어떤 DML 을이용했으며어떠한 SQL 이실행되었는지하는것이확인가능합니다. Flashback transaction query 는 Transaction level 에서 Data 의변경사항을추적하기위한기능 Transaction 의분석과진단을하는기능임

21 변경사항뿐만아니라, Undo SQL을생성할수있으며, 이 SQL을이용하여 Transaction level의작업을 rollback할수있음 undo data를 index access 방식으로조회하므로 logminor 주의 : xid column에조건을줄때반드시 hextoraw function을사용해야만 fixed view의 index를사용함. Flashback versions query와마찬가지로 undo data를이용함. Flashback Transaction query를사용하기위해서는 Database level에 logging이 enable되어야한다. Alter database add supplemental log data; 확인방법 : select supplemental_log_data_min from v$database ( YES 가정상 ) 필요권한 : grant select any transaction to XXX; 기본적으로 flashback_transaction_query 라는 view table을이용하여 query한다. flashback_transaction_query columns. XID RAW(8) Transaction identifier START_SCN NUMBER Transaction start system change number (SCN) START_TIMESTAMP DATE Transaction start timestamp COMMIT_SCN NUMBER Transaction commit system change number (null for active transactions) COMMIT_TIMESTAMP DATE Transaction commit timestamp (null for active transactions) LOGON_USER VARCHAR2(30) Logon user for the transaction UNDO_CHANGE# NUMBER Undo system change number (1 or higher) OPERATION VARCHAR2(32) Forward-going DML operation performed by the transaction: D - Delete I - Insert U - Update B - UNKNOWN TABLE_NAME VARCHAR2(256) Name of the table to which the DML applies TABLE_OWNER VARCHAR2(32) Owner of the table to which the DML applies ROW_ID VARCHAR2(19) Rowid of the row that was modified by the DML emp 와 dept 를각각수정한후, 이에대한 transaction query 를하는예제. SQL> select * from emp; SMITH CLERK DEC ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV TURNER SALESMAN SEP ADAMS CLERK JAN JAMES CLERK DEC FORD ANALYST DEC MILLER CLERK JAN rows selected. SQL> select * from dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> update emp set sal = 9999, job='2030' where empno=7934;

22 1 row updated. SQL> update dept set dname = 'ADMIN' where deptno = 40; 1 row updated. Commit complete. SQL> select * from emp; SMITH CLERK DEC ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV TURNER SALESMAN SEP ADAMS CLERK JAN JAMES CLERK DEC FORD ANALYST DEC MILLER JAN rows selected. SQL> select * from dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 ADMIN BOSTON -- flashback versions query 를이용하여 xid 를찾는다. SQL> col versions_starttime format a30 SQL> col versions_endtime format a30 SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, empno, sal 2 from scott.emp versions between timestamp minvalue and maxvalue 3 where empno = order by VERSIONS_STARTTIME; VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPNO SAL APR PM 000A B U APR PM SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, deptno, dname 2 from scott.dept versions between timestamp minvalue and maxvalue 3 where deptno = 40 4 order by VERSIONS_STARTTIME; VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPTNO DNAME

23 APR PM 000A B U 40 ADMIN 25-APR PM 40 OPERATIONS SQL> col OPERATION format a12 SQL> col LOGON_USER format a12 SQL> col UNDO_SQL format a100 SQL> SELECT xid, operation,logon_user, undo_sql 2 FROM flashback_transaction_query 3 WHERE xid = HEXTORAW('000A B'); -- hextoraw 를사용하지않으면, undo tablespace 의크기에따라 10 분이상걸림. XID OPERATION LOGON_USER UNDO_SQL A B UPDATE SCOTT update "SCOTT"."DEPT" set "DNAME" = 'OPERATIONS' where ROWID = 'AAAQLHAAHAAACfeAAD'; 000A B UPDATE SCOTT update "SCOTT"."EMP" set "JOB" = 'CLERK', "SAL" = '1300' where ROWID = 'AAAQLGAAHAAACfWAAN'; 000A B BEGIN SCOTT -- 해당 Transaction 을 rollback 하기위해서는아래와같이 undo_sql 을수행한다. SQL> update "SCOTT"."DEPT" set "DNAME" = 'OPERATIONS' where ROWID = 'AAAQLHAAHAAACfeAAD'; 1 row updated. SQL> update "SCOTT"."EMP" set "JOB" = 'CLERK', "SAL" = '1300' where ROWID = 'AAAQLGAAHAAACfWAAN'; 1 row updated. SQL> select * from emp; SMITH CLERK DEC ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV TURNER SALESMAN SEP ADAMS CLERK JAN JAMES CLERK DEC FORD ANALYST DEC MILLER CLERK JAN rows selected. SQL> select * from dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Flashback Table Flashback Table 은잘못된데이터처리를한경우, 작업전의시점으로빠르게돌려주기위한기능. (SCN or

24 시간 ) Flashback Table 명령을통해개별적인테이블에대해시간에준한복구를위해서는아래에있는조건을만족해야합니다. 테이블의데이터에대해과거시점으로돌아가서값들을확인하는것이가능합니다. Flashback any table 또는해당 Table 에대한 Flashback object privilege 를가지고있어야합니다. 테이블에대한 SELECT, INSERT, DELETE, ALTER 권한이있어야합니다. ROW MOVEMENT 의경우테이블에대해 ALTER TABLE tablename ENABLE ROW MOVEMENT; 가설정되어있어야합니다. Backup 의 restore 없이 Table 을지정한시점까지되돌려줌. Table 의데이터만을과거시점의데이터로돌려주며, Table 과관련한모든 object (index, constrains, trigger) 등은현재시점으로유지됨 Table 이 Flashback 하는동안에는 exclusive lock 을잡게됨. Flashback 한후, 다시현재시점의 Data 로돌아올수있음. 그러나현재의 SCN 을알고있어야함. SELECT current_scn FROM v$database; -- 현재 SCN 알기 다음의 Object 들에는 Flashback table 안됨. Cluster, Mview, AQ tables, static data dictionary, system tables, remote tables Undo Data 를이용함. undo retention 이전의데이터는복구안됨. flashback versions query 로부터원하는 SCN 을찾아서 flashback table 을할수있음. (VERSIONS_STARTSCN, VERSIONS_ENDSCN) 필요권한 : flashback object, flashback any table, 해당 table 에대한 select, insert, update, delete, alter table 권한. flashback table 을하기위해서는 row movement 를 enable 해주어야함. alter table XXXX enable row movement; Table 에 DDL 의변경작업이있었다면, flashback 불가 (moving, truncate, add, modify, drop,merging, split, coalescing) Flashback Table 예제 : SCN 을이용한과거시점으로 Flashback 하기 SQL> select count(*) from emp; COUNT(*) SQL> select current_scn from v$database; CURRENT_SCN SQL> delete from emp where rownum < 5; -- 잘못된 transaction 을수행함. 4 rows deleted. Commit complete. SQL> select current_scn from v$database; CURRENT_SCN SQL> alter table emp enable row movement; -- flashback table 을하기위해 enable 시킴. Table altered. SQL> flashback table emp to scn ;

25 Flashback complete. SQL> select count(*) from emp; è Flashback 후, delete 전의데이터가됨. COUNT(*) Flashback Use Case 장애의경우에따라 Use Case 를사용하여신속히복구한다. 장애 Case Case 상세복구방법 Table 이 Drop 된경우 Table 에데이터를잘못변경하고 commit 한경우 Program 이잘못수행되어여러개의 table 에변경되었을경우. 데이터에대한변경이력추적시 많은데이터변경시적은데이터변경시 Commit이한번일경우 Commit이여러번인경우 Recyclebin 을조회하여 drop 한 table 의복구가능성을확인한다. Flashback Drop 을이용하여복구한다. 변경시점으로 Table 을 flashback 하는방법. Table에대해 Version query를이용하여해당data의변경 tx를찾는방법. 하나의 Table에서, 변경된 Data에대한 Versions query를하여 Transaction을찾은후, Transaction에대한 undo를뽑아복구. Flashback query 를통해여러 Table 을 Select 하여 backup 본구성. Flashback Version Query 를이용하여변경이력추적