Goodus 기술노트 [19 회 ] Flashback Author 권웅원, 나지혜 Creation Date 2007-04-25 Last Updated 2007-04-25 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2007-04-25 권웅원, 나지혜 문서최초작성 2 3
Flashback Contents 1. Flashback... 3 1.1. Flashback이란?...3 1.2. Flashback(9i)...3 1.2.1. Flashback Overview... 3 1.2.2. 예제 1 (AS OF SCN)... 4 1.2.3. 예제 2 (AS OF TIMESTAMP)... 5 1.2.4. 예제 3 (Package, SCN / timestamp)... 6 1.3. Flashback(10g)...8 1.3.1. Flashback Database... 8 1.3.2. Flashback Drop... 12 1.3.3. Flashback Versions Query... 16 1.3.4. Flashback Query... 18 1.3.5. Flashback transaction query... 22 1.3.6. Flashback Table... 25 1.3.7. Flashback Use Case... 27-2 -
1. Flashback 1.1. Flashback 이란? 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) 1.2.1. 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 -
SQL> grant execute on dbms_flashback to scott; 1.2.2. 예제 1 (AS OF SCN) Flashback SCOTT session 에서 SYSTEMSTAMP 를이용하여현재시간을조회하시오. SQL> conn soctt/tiger Connected. SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 07-AUG-05 07.40.22.644800 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() ----------------------------------------- 851258 SCOTT 소유의 Table 에서부서번호가 20 인부서원, 부서정보를모두삭제, Commit 하시오. SQL> delete from emp where deptno=20; 5 rows deleted. SQL> commit; 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 851258 where deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 SQL> insert into emp 2 select * from emp as of scn 851258 3 where deptno=20; 5 rows created. - 4 -
SQL> commit; Flashback Commit complete. SQL> select * from emp 2 where deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 1.2.3. 예제 2 (AS OF TIMESTAMP) HR_TEST01 session 에서 SYSTEMSTAMP 를이용하여현재시간을조회하시오. SQL> show user USER is "HR_TEST1" SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 02-AUG-05 03.09.52.609909 AM -05:00 SQL> delete emp_test1 where department_id=20; 2 rows deleted. SQL> commit; 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:09:52', 'DD-MON-YY:HH24:MI:SS')) 3* where department_id=20; EMPLOYEE_ID ----------- 201 202 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:09:52', 'DD-MON-YY:HH24:MI:SS')) 3 where department_id=20; 2 rows created. SQL> commit; - 5 -
Commit complete. Flashback SQL> select employee_id 2 from emp_test1 3* where department_id=20; EMPLOYEE_ID ----------- 201 202 1.2.4. 예제 3 (Package, SCN / timestamp) HR_TEST01 session 에서 SYSTEMSTAMP 를이용하여현재시간을조회하시오. SQL> show user USER is "HR_TEST1" SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 02-AUG-05 03.31.21.752682 AM -05:00 SQL> conn /as sysdba Connected. SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ----------------------------------------- 107270 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. SQL> commit; 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; / - 6 -
PL/SQL procedure successfully completed. Flashback SQL> select employee_id from emp_test1 where department_id=20; EMPLOYEE_ID ----------- 201 202 SQL> select * from dept_test1 where department_id=20; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 20 Marketing 201 1800 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.email, 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) - 7 -
Flashback create or replace procedure exam_flash as cursor tmp_curs is 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 을이용해과거데이터복구 SQL> @flash /* exam_flash procedure 생성 */ SQL> exec exam_flash SQL> select employee_id from emp_test1 where department_id = 20; EMPLOYEE_ID ----------- 201 202 SQL> select * from dept_test1 where department_id=20; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 20 Marketing 201 1800 SQL> commit; Commit complete. 1.3. Flashback(10g) 1.3.1. 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 는오라클데이터베이스를과거시점으로되돌리고, 논리적인데이터손상또는사용자실수로인해발생한문제를해결할수있게한다. Flashback Database 는데이터베이스를위한 ' 되감기버튼 ' 과도같다. 데이터베이스백업본을이용하여복구작업을수행하지않고도데이터베이스를과거의시점으로되돌릴수있다. 포인트 - 인 - 타임복구작업에는테이프에저장된데이터베이스백업을복구하는시간이불필요하므로, 한층신속한 - 8 -
복구가가능하다. Flashback Flashback Database 기능은 RMAN, SQL*Plus 에서 FLASHBACK DATABASE 커맨드를이용하여실행되며, 그효과면에서일반적인포인트 - 인 - 타임복구방식과매우유사하다. 이기능을이용하면과거특정시점으로데이터베이스의상태를되돌릴수있다. Flashback Database 기능을활성화하려면, 먼저 Flash Recovery Area 를설정해야한다. Flash Recovery Area 는 Oracle Database 10g 에추가된새로운기능으로, 오라클데이터베이스복구관련파일및작업을위한통합적인저장공간으로활용된다. 복구영역에는 Flash Database 로그이외에도아카이브리두로그, RMAN 백업등이저장된다. 오라클은 Flash Recovery Area 내에 Flashback Log 를자동생성 / 관리한다. Flash Recovery Area 에는쿼타 (quota) 가설정되며, 따라서 Flashback Log 에는공간제한이적용된다. Flashback Log 의사이즈는로그저장기간동안의데이터베이스변경과정에서발생한읽기 / 쓰기작업량에따라크게달라진다. 오래된블록버전의복사본은 Flashback Log 에기록된다. 하루동안 10% 의데이터베이스블록이업데이트되었다면, 24 시간동안의 Flashback Log 사이즈는전체데이터베이스용량의 10 분의 1 수준이될것이다. 데이터베이스를과거시점으로복구하는과정에서더많은디스크공간이필요한경우, DBA 는디스크쿼타를다이내믹하게확장할수있다. 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 시간이걸린다는점이다. 위의그림, 앞의설명과같이 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 가시작된다. - 9 -
Flashback 역할 : 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 394264576 bytes Fixed Size 2006632 bytes Variable Size 218104216 bytes Database Buffers 167772160 bytes Redo Buffers 6381568 bytes Database mounted. SQL> alter database flashback on; Database altered. SQL> select FLASHBACK_ON from v$database; FLASHBACK_ON ------------------ YES SQL> alter database open; Database altered. Test Case 생성 SQL> conn scott/tiger Connected. SQL> create table flash as select * from user_objects; Table created. SQL> select count(*) from flash; COUNT(*) ---------- 65 SQL> select current_scn from v$database; CURRENT_SCN ----------- 25833961-10 -
SQL> truncate table flash; Flashback Table truncated. SQL> select current_scn from v$database; CURRENT_SCN ----------- 25833994 SQL> select count(*) from flash; COUNT(*) ---------- 0 Flashback Database 를위해 Instance 를종료시킨다. SQL> conn /as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. Flashback Database 를위해 Instance 를 Mount 시킨다. SQL> startup mount ORACLE instance started. Total System Global Area 188743680 bytes Fixed Size 778036 bytes Variable Size 162537676 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes Database mounted. 원하는시점으로되돌아가기위해조금전에기록했던 SCN 으로 Flash Back 한다. SQL> flashback database to scn 25833961; Flashback complete. alter_<sid>.log Incomplete Recovery applied until change 25833975 Flashback Media Recovery Complete Completed: flashback database to scn 25833961 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; - 11 -
Flashback COUNT(*) ---------- 65 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 394264576 bytes Fixed Size 2006632 bytes Variable Size 218104216 bytes Database Buffers 167772160 bytes Redo Buffers 6381568 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> select count(*) from scott.flash; COUNT(*) ---------- 65 관련 view V$FLASHBACK_DATABASE_LOG; V$FLASHBACK_DATABASE_STAT; 1.3.2. Flashback Drop 사용자와 DBA 모두에게있어실수로오브젝트를드롭 (drop) 처리하는경우는흔하게발생한다. 사용자들이실수를깨달았을때에는이미때가늦다. 과거에는이렇게드롭처리된테이블, 인덱스, 제약조건, 트리거등을쉽게복구할수있는방법이없었다. Flashback Drop 은 Oracle Database 10g 환경의오브젝트드롭작업을위한안전망을제공한다. 사용자가테이블을드롭하면, 오라클은드롭된오브젝트를 Recyble Bin 에보관한다. 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 하기 ( 장애만들기 ) - 12 -
SQL> select * from tab; Flashback 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; OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN ------------------------------ ---------------------- ------------------------- ------------------- --- BIN$LumGwpeDSHHgRAAQgzW92A==$0 EMP TABLE 2007-04-25: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 -- drop 시바로 purge 하는경우 - 13 -
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; Flashback 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); 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 T1 2004-04-08:17:40:21 2039154 BIN$14s6rPqmHQngMEWYESkRng==$0 T1 2004-04-08:17:40:19 2039107 BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133 BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231 BIN$14s6rPqqHQngMEWYESkRng==$0 T1 2004-04-08:17:40:22 2039252 만일 table t1 을 purge 한다면 dropscn=2039107 가 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 T1 2004-04-08:17:40:21 2039154 BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133 BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231 BIN$14s6rPqqHQngMEWYESkRng==$0 T1 2004-04-08:17:40:22 2039252 만일 table t1 을 restore 한다면 dropscn=2039252 이 restore 할것이다. - 14 -
Flashback 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 T1 2004-04-08:17:40:21 2039154 BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133 BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231 => 이문제를해결하기위해서.. ================================ 이문제를극복하기위해서우리는 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 ------------------------------ 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 T1 2004-04-08:17:40:21 2039133 BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231 비슷한방법으로 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 T1 2004-04-08:17:40:21 2039231-15 -
1.3.3. Flashback Versions Query Flashback 과거의어떤시점의정보를시간과 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 확인. SQL> select timestamp_to_scn(to_timestamp('20060213 171201', 'yyyymmdd hh24miss')) SCN from dual; SCN ------------ 8268801520810 과거의 SCN 을이용하여 Time 확인. SQL> select scn_to_timestamp(8268801520810) TIME from dual; TIME ------------------------ 2006/02/13 17:12:01.000000000 Versions Query 의 Pseudo column (Select 절에사용할수있음 ) Versions_startscn, Versions_starttime Versions_endscn Versions_endtime Versions_xid Versions_operation 주의 : undo retention 보다이전의 version 을 query 하면 ora-30052 : invalid lower limit snapshot expression 발생함. 예제 Data 변경 (Update) 하기 SQL> conn scott/tiger Connected. SQL> select * from emp; 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 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. - 16 -
Flashback SQL> update emp 2 set comm=1111 3 where empno=7934; 1 row updated. SQL> commit; Commit complete. SQL> update emp 2 set comm=2222 3 where empno=7934; 1 row updated. SQL> rollback; Rollback complete. SQL> update emp 2 set comm=2222 3 where empno=7934; 1 row updated. SQL> commit; Commit complete. SQL> update emp 2 set comm=3333 3 where empno=7934; 1 row updated. SQL> commit; Commit complete. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10-17 -
14 rows selected. Flashback 2007 2 월 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('2007-04-25 15:50:00','YYYY-MM-DD HH24:MI:SS') 10 AND TO_TIMESTAMP('2007-04-25 16: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 ------ -------- ------ ------- -------- ---------------- - ---------- --------------------- ---------- --------------------- 7934 MILLER 1300 3333 10 0009000000000988 U 25837411 25-APR-07 03.58.31 PM 7934 MILLER 1300 2222 10 0003000D00000994 U 25837379 25-APR-07 03.57.01 PM 25837411 25-APR-07 03.58.31 PM 7934 MILLER 1300 1111 10 0006001C00000985 U 25837354 25-APR-07 03.56.04 PM 25837379 25-APR-07 03.57.01 PM 7934 MILLER 1300 10 25837354 25-APR-07 03.56.04 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 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 ------ -------- ------ ------- -------- ---------------- - ---------- --------------------- ---------- --------------------- 7934 MILLER 1300 3333 10 0009000000000988 U 25837411 25-APR-07 03.58.31 PM 7934 MILLER 1300 2222 10 0003000D00000994 U 25837379 25-APR-07 03.57.01 PM 25837411 25-APR-07 03.58.31 PM 7934 MILLER 1300 1111 10 0006001C00000985 U 25837354 25-APR-07 03.56.04 PM 25837379 25-APR-07 03.57.01 PM 7934 MILLER 1300 10 25837354 25-APR-07 03.56.04 PM 조회결과로 Null 1111 2222 3333 으로변경된내역을볼수있다. 1.3.4. Flashback Query Oracle9i 에서처음소개된 Flashback Query 는과거시점의데이터를조회하는기능을제공한다. 기본적으로데이터베이스는가장최근에커밋된데이터를기준으로작업을수행한다. 하지만과거의특정시점을기준으로데이터베이스를조회하고자한다면, Flashback Query 기능을이용할수있다. Flashback Query 는특정시점또는 SCN(System Change Number) 을기준으로, 해당시점에커밋된데이터를조회할수있게한다. Flashback Query 메커니즘은 Automatic Undo Management 를이용하는경우가장효과적으로동작한다. 오라클데이터베이스는언두 (undo) 를중요한데이터베이스오브젝트로관리한다. 언두데이터는영구적으로저장 / 관리되며데이터베이스시스템에크래쉬, 셧다운이발생하는경우에도유지된다. 또다른데이터베이스오브젝트와함께데이터베이스버퍼캐시를공유하므로성능보장이가능하다. 오라클데이터베이스는트랜잭션이커밋된이후에도언두데이터를관리하고, 필요한경우논리적손상으로부터복구할수있게한다. - 18 -
Flashback 오라클데이터베이스의관리자는보존할언두데이터의양을명시적으로지정할수있다. 시스템은새로운트랜잭션의언두데이터를생성하기위해만료된언두데이터를자동으로삭제한다. 언두데이터의보존기간은롱 - 러닝 (long-running) 쿼리의실행시간또는논리적손상에대한복구요구사항에따라다르게설정된다. 또는언두보존기간을설정하지않고데이터베이스가알아서최적의보존정책을관리하도록할수도있다. 그러면데이터베이스는롱 - 러닝쿼리에대한실행시간과논리적손상의복구를최대한보장할수있는방안을자동으로적용한다. 디폴트상태에서언두데이터의보존은보장되지않는다. 시스템은현재진행중인트랜잭션의언두데이터기록을위해필요한경우, 언제든오래된언두데이터를만료처리할수있다. 10g R1 부터는 UNDO_RETENTION 이 5 일이상으로지정되어있는경우, 5 일또는그이상경과한과거의데이터를쿼리할수있는기능이추가되었다. 오라클은 Undo Tablespace 데이터파일에충분한공간이남아있는한, 언두데이터를유지한다. 데이터베이스에서 Flashback Query 와기타언두데이터관련플래시백기능을활성화하기위한방법이아래와같다 : 1. 데이터베이스가 Undo Tablespace 를사용하고있는지확인한다. Undo Tablespace 를사용하려면 UNDO_MANAGEMENT 초기화매개변수를 AUTO 로설정해놓아야한다. 2. 가장긴실행시간을갖는쿼리를성공적으로복구할수있는시간, 또는사용자에러로부터복구하기에충분한시간으로 UNDO_RETENTION 초기화매개변수를설정한다. 3. 만료되지않은언두데이터가덮어씌워지지않도록, 언두테이블스페이스에 RETENTION GUARANTEE 조건을추가한다. Flashback Query 기능을이용하면과거의데이터시점의데이터를확인할수있을뿐아니라데이터를처리하는방법을선택하는것도가능하다. 분석작업을수행한후에모든변경작업을취소하거나, 변경데이터를캡처하여다른작업에활용할수도있다. Flashback Query 메커니즘은다양한상황에서활용될수있는유연성을제공한다. 몇가지활용예가아래와같다 : 과거시점의데이터를조회. 현재데이터와과거데이터를비교. ( 개별로우를비교하거나 Intersection, Union 등의조건을이용하여복잡한비교작업을수행할수도있다.) 삭제, 변경된데이터의복구. Oracle9i 에서부터지난시점의데이터를질의하기위한 DBMS_PACKAGE 를제공했으며 10g 에서는훨씬기능을유연하게발전시켰다. Flashback Query 는 AS OF 절을사용하여해당시점에서의데이터값에대한질의가가능하며, 이기능은 DBMS_FLASHBACK 패키지의기능과유사하다. Flashback versions query 는과거의일정시간구간에서조회하는것에비해 Flashback query 는, 과거의일정한시간에서 query 를하는것. Database 는현재의시간이지만, 수행하는 SQL 은혼자과거의정보를보게됨. 예제 -- Data 삭제 ( 장애만들기 ) SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10-19 -
Flashback 14 rows selected. SQL> delete emp 2 where empno=7934; 1 row deleted. SQL> commit; Commit complete. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 13 rows selected. (1 건이삭제되었음 ) -- 1 시간전 Data 를구하기 SQL> select * from emp as of timestamp ( systimestamp - interval '1' hour); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10 14 rows selected. -- 1 분전 Data 를구하기 delete 후바로조회하면아직 delete 되지않은것으로보인다. SQL> select * from emp as of timestamp ( systimestamp - interval '1' minute); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- - 20 -
Flashback 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10 14 rows selected. -- 1 시간전 Data 와현재 Data 의차이를알고싶을때. -- 즉, 1 시간전과같지않은데이터를모두찾는다. SQL> select * from emp as of timestamp ( systimestamp - interval '1' hour) 2 minus 3 select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10 1 시간전의 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. SQL> commit; Commit complete. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30-21 -
Flashback 7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10 14 rows selected. 1.3.5. Flashback transaction query 테이블의데이터변경작업이잘못수행되었음을나중에야발견하는경우가있다. 변경내역을조사하기위해, DBA 는플래시백쿼리를실행하여특정시점의로우데이터를조회할수있다. 또는좀더효율적인방법으로, Flashback Versions Query 기능을이용하여일정기간동안의로우변경내역과트랜잭션 ID 를한꺼번에확인할수도있다. 이때 DBA 는 SELECT 구문에 VERSIONS BETWEEN 절을적용하고, SCN 또는타임스탬프를기준으로일정기간의로우변경히스토리를조회한다. 문제가되는트랜잭션을발견했다면, 다시 Flashback Transaction Query 기능을이용하여해당트랜잭션에의해수행된다른변경사항을확인한다. 그리고변경사항을복구하기위한언두 (undo) SQL 을요청한다. 이때트랜잭션히스토리와언두 SQL 을확인하기위해사용되는것이바로 FLASHBACK_TRANSACTION_QUERY 뷰이다. 잘못실행된트랜잭션을완전히취소하기위해, 언두 SQL 구문을수동으로실행하고사용자 / 애플리케이션에러를쉽게복구할수있다. Flashback Transaction Query 는데이터베이스의온라인진단범위를확장하고, 분석및트랜잭션감사환경을개선할수있게한다. VERSIONS_XID 값이트랜잭션의 ID라고했는데, 이값을 FLASHBACK_TRANSACTION_QUERY의인자값으로 줘서쿼리를실행하면해당트랜잭션에대한정보를볼수있다. 예를들면어떤 DML을이용했으며어떠한 SQL이실행되었는지하는것이확인가능하다. Flashback transaction query는 Transaction level에서 Data의변경사항을추적하기위한기능 Transaction의분석과진단을하는기능임. 변경사항뿐만아니라, 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 Database level 에 logging 이 enable 되어있는지확인한다. SQL> select supplemental_log_data_min from v$database; - 22 -
Flashback SUPPLEME -------- NO SQL> alter database add supplemental log data; Database altered. SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES emp 와 dept 를각각수정한후, 이에대한 transaction query 를하는예제. SQL> conn scott/tiger SQL> select * from emp; 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 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> SQL> update emp set sal = 9999, job='2030' where empno=7934; 1 row updated. SQL> update dept set dname = 'ADMIN' where deptno = 40; 1 row updated. SQL> commit; Commit complete. - 23 -
Flashback SQL> select * from emp; 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 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER 2030 7782 23-JAN-82 9999 10 14 rows selected. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 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 = 7934 4 order by VERSIONS_STARTTIME; VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPNO SAL ------------------------------ ------------------------------ ---------------- - ---------- ---------- 25-APR-07 05.13.51 PM 000A00270000076B U 7934 9999 25-APR-07 05.13.51 PM 7934 1300 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 ------------------------------ ------------------------------ ---------------- - ---------- -------------- 25-APR-07 05.13.51 PM 000A00270000076B U 40 ADMIN 25-APR-07 05.13.51 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 - 24 -
Flashback 2 FROM flashback_transaction_query 3 WHERE xid = HEXTORAW('000A00270000076B'); -- hextoraw 를사용하지않으면, undo tablespace 의크기에따라 10 분이상걸림. XID OPERATION LOGON_USER UNDO_SQL ---------------- ------------ ------------ --------------------------------------------------------------------------------------------- 000A00270000076B UPDATE SCOTT update "SCOTT"."DEPT" set "DNAME" = 'OPERATIONS' where ROWID = 'AAAQLHAAHAAACfeAAD'; 000A00270000076B UPDATE SCOTT update "SCOTT"."EMP" set "JOB" = 'CLERK', "SAL" = '1300' where ROWID = 'AAAQLGAAHAAACfWAAN'; 000A00270000076B 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> commit; SQL> select * from emp; 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 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 1.3.6. Flashback Table Oracle9i Database 에는 Flashback 질의옵션개념이도입되어데이타를과거의시점에서부터검색하지만, 테이블삭제같은 DDL 작업을순간적으로되돌릴수는없습니다. 이경우유일한수단은다른데이타베이스에서테이블스페이스적시복구를사용한다음, 엑스포트 / 임포트또는기타메서드를사용해현재데이타베이스에테이블을다시생성하는것입니다. 이프로시저를수행하려면복제를위해다른데이타베이스를사용하는것은물론, DBA 의많은노력과귀중한시간이요구됩니다. 하지만 Oracle Database 10g 의 Flashback 테이블기능으로들어가면몇개의문만실행하여삭제된테이블을간단히검색할수있습니다. Flashback Table은잘못된데이터처리를한경우, 작업전의시점으로빠르게돌려주기위한기능. (SCN or - 25 -
Flashback 시간 ) 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(*) ---------- 14 SQL> select current_scn from v$database; CURRENT_SCN ----------- 25842839 SQL> delete from emp where rownum < 5; -- 잘못된 transaction 을수행함. 4 rows deleted. SQL> commit; Commit complete. SQL> select current_scn from v$database; CURRENT_SCN ----------- 25842867 SQL> alter table emp enable row movement; -- flashback table 을하기위해 enable 시킴. Table altered. SQL> flashback table emp to scn 25842839; - 26 -
Flashback complete. Flashback SQL> select count(*) from emp; Flashback 후, delete 전의데이터가됨. COUNT(*) ---------- 14 1.3.7. 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 를이용하여변경이력추적 - 27 -
Pro-Active Tuning Service 소개 1. 실제사용자 (End-User) 관점의응답시간튜닝 Pro-active tuning service 는사용자관점의모니터링및분석을통하여실제 End-User 가느끼는응답시간 (Response Time) 을튜닝합니다. APM(Application Performance Management) 툴을이용하여 End-User 의 Request 결과를반환받기까지의모든구간 (Client PC, Internet 구간, FireWall, DNS, Web Server, WAS, DBMS) 을분석하여가장 Delay Time 이많이소요된구간을찾아냅니다. ISP Backb one ISP Client Time Internet Time Firewall Time DNS Time Backend Systems Time 2. 최상의성능상태로비즈니스고가용성을유지 Pro-Active Tuning Service 매업무단위프로젝트마다참여하여업무적용 (Open) 前문제요소를분석하여튜닝. 단위업무적용 (Open) 후매 3 개월 ( 데이터량갱신주기 ) 마다튜닝포인트를설정, 성능둔화요소를해결. 전사적으로새롭게추가되는업무단위프로젝트의모든 SQL 쿼리를검토및튜닝. 다양한대용량데이터베이스관리 / 튜닝기법을도입하여최적의 DB 상태를 1 년내내상시유지. 전략적튜닝 Factor 를분석, 투자대비효율이높은 Targeting 기법적용. ( 비중도높은 SQL 을튜닝함 ) 3. Knowledge Transfer Pro-Active Tuning Service 는고객의 Business Process 를이해하고시스템을분석한후튜닝하는것으로완료되지않습니다. 실제로고객사환경에서튜닝한내용을그대로실무자들에게전수하여내부임직원의역량을제고시킵니다. 또한, Oracle RDBMS 신버젼의 New Features 를교육함으로써, 이용자 ( 관리자및개발자 ) 가스스로개발업무의효율및생산성을향상시킬수있도록지원합니다. 이외에도 DBMS 관리자를위한관리노하우 ( 고급 Trouble-Shooting, 대용량 DB 처리, 병렬처리등 ) 를전수함으로써, 최상의시스템을최고의기술로유지할수있도록지원합니다. UAS (User Adapted Seminar) 진행사례및내용 (Contents) 개발자를위한 SQL 튜닝실무사례세미나 G 쇼핑몰업체튜닝후실제고객사의튜닝사례를개발자들에게전수하여개발자들이성능을고려한 SQL 을작성할수있도록내부역량을제고시킴. Oracle 10g New Features 세미나 S Global 전자기업 : Oracle 10g 버전으로업그레이드하기전, 신버전의새로운기능과주의사항을전파함으로써, 업그레이드후발생할수있는문제점의사전제거와개발자들이새로운기능을이용함으로써, 개발생산성을향상시킴. K 국가기관 DBMS 관리노하우세미나내부관리자 (DBA,SE) 들을대상으로 DBMS 관리자들이흔히겪을수있는상황에대한 Administration Know-How 와고급 Trouble-Shooting 사례를소개함으로써, 관리기술력을향상시킴. - 1 -