Getting the most out of MetaLink 이은지 한국오라클 ( 주 ) 제품지원실
목차 1. Flashback Overview 2. Flashback drop 3. Flashback Versions Query 4. Flashback Transaction Query 5. Flashback Table 6. Flashback database 7. Summary 세미나의목차는다음과같습니다. 1. Flashback Overview 2. Flashback drop 3. Flashback Versions Query 4. Flashback Transaction Query 5. Flashback Table 6. Flashback database FLASHBACK의기능은 Oracle database version 10G 를기반으로한것입니다. 각각에대한 overview 와사용예, 사용시발생할수있는 error 에대해서설명하겠습니다.
Flashback Overview Flashback Time Navigation Recovery Level Database Level Table Level Row Level Oracle Database 9i 까지는물리적, 논리적 corruption 의경우 Data file 과관련된 archive log files 들을 restore 해서복구를해야했습니다. 이방법은 Recovery 에많은시간이소요되고 Recovery 가끝날때까지 Database 운영이불가능하거나 Transaction Fail 이생기어업무에많은영향을줄수도있었습니다. Oracle Database 10G 에서는 Flashback 이라는기능이추가되어 Table drop 이나특정 data 를잘못 update 나 delete 하는등의사용자실수에의한 corruption 의경우쉽고빠르게복구할수있도록합니다. 이작업은 Database 가 online 중에어떤 user 라도쉽게할수있습니다. Oracle 9i 에서제공되었던 Flashback query 는과거어떤시점의 data 를확인했다면 10g 에서는 object 의어떤시간간격동안과거 data 를 query 하여부분적인 auditing 기능에사용할수도있습니다. Oracle Database 10g 에서는 Flashback 을사용하여 database, table, row level 의모든 level 에서빠르고쉬운복구가가능합니다.
Flashback Drop Overview Flashback Drop Oracle Database 이전버전에서 Drop table command는 table이사용한 space 와관련한 object를 Database내에서영구적으로제거했습니다. 그래서 User 의실수로 drop 한 table 을복구하기위해서는 datafile과관련된 archive file을이용해서 table을 drop 하기전시간으로 recovery 해야했습니다. 이방법은많은시간을필요로하고복구하는동안업무의손실을가져왔습니다. Oracle Database 10g 에서소개되는 Flashback drop 은 time based recovery 방법을사용하지않고 Drop table 의 undo 효과를가져올수있습니다. 즉, table을 drop하면 table과관련 obejct 는 recycle bin에위치하게되고 recycle bin 으로부터 drop한 table을간단한명령어로 restore 할수있습니다. 이는 system의모든 user 에게영향을주는 time based recovery 작업을줄일수있도록하였습니다.
Recyclebin Overview select * from user/dba_recyclebin; SQL>show recyclebin Drop Table. Purge ; Recyclebin Overview Drop table command 를실행하면 table과관련 object 는 Recyclebin에저장됩니다. Recyclebin은 drop한 object의이름과정보를유지하는 dictionary table 이며, user_recyclebin 이나 recyclebin를통해 drop한 objects 의정보를 query할수있습니다. recyclebin에서 drop된 table과그와관련된 objects들 (indexes,constraints, 다른관련된 objects 등 ) 은 BIN$$ 시작하는다른이름으로바뀌어있습니다. 이는동일이름으로 table 을생성과 drop 을반복한경우 Recyclebin 에서충돌을피하기위함입니다. Drop한 table에대해서도 recyclebin의 object 이름을사용하여 data 를 select 를할수있습니다. 그러나 DML 작업은불가능합니다. DROP TABLE PURGE command 는object 를바로drop하고object 에의해할당받은space 를 deallocate 합니다. 이 command는 object 가 recyclebin에위치하지않고이전버전의 drop table 과같은기능을하도록합니다. 참고 recycling 기능을없애는방법은 "_recyclebin" 을 FALSE 로 setting 하면됩니다. Default tablespace 가 non-system tablespace 이고 locally managed tablespace 여야만 recyclebin 사용이가능합니다.
Recyclebin 의 space 반환 Space is needed by other objects in the tablespace. Oracle Database 10g에서 drop한 table은 recyclebin으로이동하고 drop 한 table과관련 object가사용한 space는비록 dba_free_space 에서는 free space 로보이더라도바로반환이되지않습니다. space 반환은다음의규칙을따릅니다. 1) Purge command를사용하여수동으로 space 반환을요청한경우즉시반환이됩니다. 2) 수동으로반환요청을하지않는경우는다음순서로 space 를 check 한후자동으로반환이됩니다. 1 recyclebin object 가차지하지않은 tablespace 내의 free space 를사용 2 tablespace 내에 space 를다사용하고도부족하면 recyclebin 내의 object 가차지하고반환하지않은 space 를사용합니다. 이경우 recyclebin object 는 FIFO (first in,first out) 방식으로 purge 됩니다. 3 이 space 도다사용하고도여전히부족하면 auto extensible 의경우자동 allocate 됩니다. Space 반환은내부적으로관리되므로어떤 paramter 를설정할필요는없습니다. 만일 recyclebin 내의 object 가 space 부족으로인해 purge 되었다면이 table 의경우에는 fashback drop이불가능합니다. recyclebin내에존재하는 object에한해서 flashback drop이가능합니다.
(1) 다음은 undo2_t table 을 drop 한후 space 의변화를확인한것입니다. SQL> select sum(bytes),max(bytes) from dba_free_space where tablespace_name='users'; SUM(BYTES) MAX(BYTES) -------------------- --------------------- 15990784 14286848 SQL> drop table undo2_t; Table dropped. SQL> select OBJECT_NAME,ORIGINAL_NAME,SPACE,TS_NAME from user_recyclebin; OBJECT_NAME ORIGINAL_NAME SPACE TS_NAME ----------------------------- ------------- ---------- -------------------- BIN$7WlkZokfE/HgNAADugsnQQ==$0 UNDO2_T 256 USERS SQL> select sum(bytes),max(bytes) from dba_free_space where tablespace_name='users'; SUM(BYTES) MAX(BYTES) -------------------- -------------------- 18087936 14286848
Recyclebin Query( 예 ) SQL>select * from "BIN$7MWRLq91SVvgNAADugsnQQ==$0"; SQL>select * from "BIN$7MWRLq91SVvgNAADugsnQQ==$0" as of timestamp.. where.. recyclebin에존재하는 object에대해서도 query 가가능합니다. 또한 undo 정보가남아있는한 flashback query도가능함을알수있습니다. Recyclebin의 query 는오직해당 object 의 owner만가능합니다. 다음예는 drop 한 table 에대해서도 empno가 7369 의 emane 을 flash 로 update 하기전의값이무엇이었는지 flashback (versions) query 를통해서볼수있음을보여줍니다.
SQL> select * from emp where empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ------- -------- ----- ------------ --- ------- ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 SQL> select to_char(sysdate,'yyyymmddhh24miss') from dual; TO_CHAR(SYSDAT -------------- 20050103073118 SQL> update emp set ename='flash' where empno=7369; 1 row updated. SQL> commit; Commit complete. SQL> select * from emp where empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ------- -------- ----- ------------ --- ------- ------- 7369 FLASH CLERK 7902 17-DEC-80 800 20 SQL> drop table emp; Table dropped. SQL> show recycle; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- --------------------------- ------------ ------------------- EMP BIN$7Mh0hJgYbongNAADugsnQQ==$0 TABLE 2005-01-03:07:32:07 SQL> select ename from "BIN$7Mh0hJgYbongNAADugsnQQ==$0" as of timestamp 2 to_timestamp('20050103073110','yyyymmddhh24miss') where empno=7369; ENAME ---------- SMITH
Commands Purge table tablename; Purge index indexname; Purge tablespace tablespacename (USER username); Drop tablespace.. Including contents command Drop tablespace.. Drop user.. (Cascade) FLASHBACK TABLE tablename TO BEFORE DROP ( RENAME TO new_tablename) ; PURGE recyclebin Purge dba_recyclebin Dba_tables 의 dropped column Purge table/index command 는 recyclebin내의 table,index을제거하고 object 가사용한 space 를반환합니다. 만일 recyclebin내에여러개의동일 table이존재하는경우 original name으로 purge 를하게되면 FIFO 의규칙으로 purge 됩니다. Purge tablespace command 는이 tablespace 내에존재한모든 object 를 recycle bin 내에서제거하고 object 가사용한 space 를반환합니다. Purge 시 Username 까지명시하는경우해당 schema 에해당 tablespace 가포함하는 object 만 purge 됩니다. 만일 partition 이나 lob segment 같이한 table 에여러개의다른 tablespace 로지정되어있을경우 Default tablespace 가 purge 되면전부 purge 됩니다. Drop tablespace.. Including contents; command 를사용하게되면 tablespace 내의모든 object 들은 recyclebin 에저장되지않고바로 purge 됩니다. Drop tablespace tablespacename; command 는해당 tablespace 내에 object 가없어야만성공할수있습니다. 그러나 recyclebin 내의 object 가속한 tablespace 이름을명시하는경우 recyclebin 내의 object 가 purge 됩니다. Drop user.. Cascade command 도 user 와 user 내의모든 objects 가 recyclebin 에저장되지않고바로 purge 됩니다. 즉. tablespace 나 user 를 drop 하는경우에는 recycling 되지않음을알수있습니다.
Commands(continue) Purge table tablename; Purge index indexname; Purge tablespace tablespacename (USER username); Drop tablespace.. Including contents command Drop tablespace.. Drop user.. (Cascade) FLASHBACK TABLE tablename TO BEFORE DROP ( RENAME TO new_tablename) ; PURGE recyclebin Purge dba_recyclebin Dba_tables 의 dropped column FLASHBACK TABLE tablename TO BEFORE DROP RENAME TO new_tablename; FLASHBACK TABLE tablename TO BEFORE DROP command 를이용하여 drop한 table을 restore 할수있습니다. 그러나 drop한 table 과동일한이름의 table이 DB에존재한다면 rename option을사용하셔야합니다. 또한 recyclebin에동일이름의 table이여러개있다면 original name 으로 restore 하는경우에는 LIFO(last in, first out) 방식이적용됩니다. 만일과거의 object 를 restore 하고자한다면 recyclebin의 object name 을이용해서 restore 하시면됩니다. 여기서 Indexes,triggers,constraints 는 recyclebin 내의이름으로복원이되므로 alter command 를이용해서적절한이름으로바꾸셔야합니다. Reference constraint,mview log,bitmap join index 는 flashback 복원되지않습니다. PURGE recyclebin command는현재 user가소유하고있는 recyclebin 내의모든 object를 purge하고 space를반환합니다. Purge dba_recyclebin command 는 recyclebin 내의모든 object 를 purge 하고 space 를반환합니다. Reclyclebin 에존재하는 table 이라도 DBA_TABLES,DBA_OBJECTS, DBA_SEGMENTS 등의몇몇 dictionary table 에서 query 가가능합니다. 그러므로 drop 된 object 인지아닌지는 DBA_TABLES 의 dropped 라는 column 을통해서확인을하실수있습니다. 만일 drop 한 table 이라면 dropped column 이 yes 로보입니다. Tablespace drop 시에해당 tablespace 내의 table 과 recycle bin 에있는 table 모두 drop 됩니다.
SQL>select table_name from user_tables where tablespace_name='atest' ; TABLE_NAME ------------------------ A B SQL> drop table a; Table dropped. SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ------------- ----------------------------- ---------- ------------------- A BIN$3aYfOcHXEpzgMAB/AQBJdw==$0 TABLE 2004-06-25:10:16:32 SQL> drop tablespace atest; drop tablespace atest * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option SQL> drop tablespace atest INCLUDING CONTENTS; Tablespace dropped. SQL> show recyclebin SQL> SQL> select * from b; select * from b * ERROR at line 1: ORA-00942: table or view does not exist
Flahsback Versions Query Overview Flashback Version Query Overview Flashback Query 는 9i에서도소개되었습니다. 그때는과거의어떤시점의정보를시간과 scn 을이용해서 query 가가능했었습니다. Oracle Database 10g 에서는그기능이확장되어 versions between을이용해서어떤시간간격사이나어떤시점사이에존재했던 row들의값을 query 할수있습니다. 이를 Flashback versions query라고합니다. Flashback versions query에의해return되는row 들은transaction에의해변화한row들의history를보여줍니다. 이기능은 row의 history 를 query 함으로서 data들이어떻게바뀌었는지 auditing 기능을가능하게하며 committed 된 data만추출합니다. Flashback versions query를통해얻은 transaction Id를이용해서더추가적인정보를 Flashback Transaction Query를통해얻을수있습니다. Versions between은 query plan을변화시키지않으며 view query 시에는사용할수없지만 view 를생성하기위한 definition으로사용은가능합니다. DDL 문에의해Table의구조가바뀌었을때DDL 문을수행하기전과거의data는versions query에서볼수없습니다. 다음의예는 dept table에 deptno 10에대해서 dname을 F_V_Q_1,F_V_Q_2를 update 한후 alter table을이용해서 table의구조를변경했습니다. 그후해당 Flashback versions query 를해본결과ora-1466 error 가발생함을볼수있습니다.
SQL> select to_char(sysdate,'yyyymmdd hh24miss') from dual; TO_CHAR(SYSDATE ---------------------------- 20050106 005230 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; GET_SYSTEM_CHANGE_NUMBER -------------------------------------------------------- 788303 SQL> update dept set dname='f_v_q_1' where deptno=10; 1 row updated. SQL> update dept set dname='f_v_q_2' where deptno=10; 1 row updated. SQL>select dname, Versions_startscn, Versions_starttime, Versions_endscn, Versions_endtime, Versions_xid, Versions_operation from dept VERSIONS BETWEEN TIMESTAMP to_timestamp('20050106 005230','yyyymmdd hh24miss') and to_timestamp('20050106 005350','yyyymmdd hh24miss') where deptno=10; Dname Vers_startscn Versions_starttime Vers_endscn Versions_endtime Vers_xid Vers_oper -------- --------------------- ---------------------------------- ------------ F_V_Q_2 788327 06-JAN-05 12.53.31 AM 0004002500000286 U F_V_Q_1 788318 06-JAN-05 12.53.19 AM 788327 06-JAN-05 12.53.31 AM 0004002300000286 U F_V_Q_2 788318 06-JAN-05 12.53.19 AM SQL> select to_char(sysdate,'yyyymmdd hh24miss') from dual; 20050106 005542 SQL> alter table dept modify (dname varchar2(30)); Table altered. SQL> select dname,versions_startscn,versions_starttime, Versions_endscn, Versions_endtime, Versions_xid, Versions_operation from dept VERSIONS BETWEEN TIMESTAMP to_timestamp('20050106 005230','yyyymmdd hh24miss') to_timestamp('20050106 005350','yyyymmdd hh24miss') where deptno=10; from dept * ERROR at line 3: ORA-01466: unable to read data - table definition has changed
Flashback Versions Query Configure Flashback versions query는 undo tablespace 로부터 data를추출함으로 UNDO_RETENTION 초기화 parameter를이용해서 DB 내에유지될수있는 undo 정보를조절하여야합니다. 만일 active transaction이 undo space 를요구하고 undo tablespace에 free space 가없다면 system은 Undo tablespace에대해해당 undo space를재사용합니다. 즉 undo_retention이충분히잡혀있어도 undo space가부족하면 Flashback Versions Query가불가능합니다. 또한 flashback versions query 를사용하려는 user 는 object 에대해 FLASHBACK 이나 select 권한을가져야하며 Flashback any table 권한을가지고있다면모든 table 에대한 query 가가능합니다. 다음의경우는 Flashback versions query 를사용할수없습니다. External tables, Temporary tables, Fixed tables, Views DDL 문장으로인해 table의구조가변경되기전의 versions query Segment shrink
Versions Between SELECT. VERSIONS BETWEEN SCN SELECT. VERSIONS BETWEEN TIMESTAMP New SQL Conversion Functions SCN_TO_TIMESTAMP TIMESTAMP_TO_SCN Using VERSIONS in Subqueries Versions between 은 scn 과시간의간격으로조건을줄수있습니다. 다음예는 scn 의모든범위에서 a가 1 인 data 가어떻게변하였는지를보여줍니다. SQL> SELECT versions_xid AS XID, versions_startscn AS START_SCN, versions_endscn AS END_SCN, versions_operation AS OPERATION, b from f_query VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE where a=1; XID START_SCN END_SCN O B ---------------- ----------- -------------- - ------------ 0019000200000015 972527 D a 001D001100000013 972519 972527 I a 위의예를보시면 scn 972519에 insert transaction 이발생했습니다. 그후 scn 972527 일때 delete 되었음을알수있습니다. 다음예는 2002 12/16 이후현재까지의시간에서 deptno 가 40 인 data 가어떻게변하였는지를볼수있습니다 SELECT * FROM dept VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP ('2002-12-16', 'YYYY-MM-DD') AND TO_TIMESTAMP (SYSDATE) WHERE deptno=40;
Oracle Database 10g에서는시간과 scn을 mapping 시켜주는새로운 SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN conversion function이생겼습니다. SCN_TO_TIMESTAMP 는 number type의 scn을 timestamp 값으로변환하며, TIMESTAMP_TO_SCN 는 timestamp를 number type의 scn으로바꾸어보여줍니다. SQL> SELECT SCN_TO_TIMESTAMP(current_scn) FROM v$database; SCN_TO_TIMESTAMP(CURRENT_SCN) --------------------------------------------------------------------------- 06-JAN-05 02.26.04.000000000 AM VERSIONS BETWEEN 는다음예에서처럼 View 를 query 할경우에는사용이안되지만 DDL과 DML의 subquery에사용은가능합니다. SQL> CREATE view v_dept AS SELECT * FROM dept 2 ; View created. SQL> SELECT * FROM v_dept VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE deptno=10; 2 3 SELECT * FROM v_dept * ERROR at line 1: ORA-30051: VERSIONS clause not allowed here SQL> create view v_flash as SELECT * FROM dept VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE deptno=10; View created.
Versions_startscn Versions_starttime Versions_endscn Versions_endtime Versions_xid Versions_operation Pseudo Columns Oracle Database 10g 에서는 Flashback Versions Query를위해서다음과같은 pseudo columns 이추가되었습니다. 이값들은 select 문장이나 where 절모두에서사용이가능합니다. VERSIONS_STARTSCN 나 VERSIONS_STARTTIME는 row 에 modify가생길때시점이되는 scn과 timestamp입니다. VERSIONS_ENDSCN or VERSIONS_ENDTIME 이값은마지막 scn과 timestamp입니다. 현재의값인경우 ( 마지막 update 한값인경우 ) 이값은 null 입니다. 또한 delete 된 data 인경우에도 null입니다. VERSIONS_XID는현상태에서생성된 row의 transaction ID 입니다. VERSIONS_OPERATION는 Transaction에의해수행된 Operation ((I)nsert, (U)pdate or (D)elete)
Flashback Transaction Query Overview Flashback Transaction Query Overview Flashback Transaction Query 는 transaction level에서 DB에이루어진변경사항을보기위한진단둘입니다. 이는 transaction 의분석과 auditing 이가능합니다. Flashback Transaction Query 를통해서 undo sql 문장을확인할수있고 transaction level 의작업을 rollback 하기위해사용될수있습니다. 이는 undo data 를 index access 하므로 LogMiner 를사용하는것보다 transaction 의 undo 를위해서는더빠른방법을제공합니다.
Flashback Transaction Query Configure Flashback Transaction Query 는 undo tablespace 내에저장된 undo data 를사용합니다. UNDO_RETENTION 초기화 parameter 를이용해서 DB 내에유지할수있는 undo 정보를조절할수있으며 active transaction 이 undo space 를요구하고 undo tablespace 에 free space 가없다면 system 은 tablespace 에대해해당 undo space 를재사용합니다. 즉 undo_retention 은충분히잡혀있어도 undo space 가부족하면 Flashback Transaction Query 가불가능합니다. Flashback Transaction Query, Flashback Versions Query 와 Flashback Table 은 undo data 사용과 Space 활용에있어서전부동일합니다.
Supplemental Logging 과권한 Enabling Supplemental Logging 권한 SELECT ANY TRANSACTION system 권한 FLASHBACK ANY TABLE system 권한 Flashback Transaction Query 기능을사용하기위해서는다음과같이 supplemental logging 이 enable 되어있어야합니다. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; supplemental logging이할성화되어있을때 column 명이 undo segment 와 redo log file에자세히기록이됩니다. SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; 로확인가능합니다. Flashback Transaction Query를사용하는모든 user 에게 select any transaction system 권한이부여되어야합니다. SELECT ANY TRANSACTION system 권한은 FLASHBACK_TRANSACTION_QUERY view를 Query하여 trasnasction 정보를얻고자할때필요합니다. FLASHBACK ANY TABLE system (Flashback object 권한 ) 권한은 backup 의 restore 없이과거의어떤시점으로 table 을복구하려고할때필요한권한입니다.
Flashback Transaction Query( 예 ) SELECT operation,undo_sql,table_name FROM flashback_version_query ; SELECT operation,undo_sql,table_name FROM flashback_version_query Where xid= 0009000000000174 ; SELECT operation,undo_sql,table_name FROM flashback_version_query WHERE start_time and commit_time; 어떤특정시간동안에생긴변화나특정 transaction 에의해생긴변화를보기위해 Flashback Transaction Query를이용할수있습니다. 특정 transaction 에대해충분한 undo data가없다면 Flashback Transaction Query 의 operation column이 unknown 으로보입니다. 또한 drop된 object 나 user인경우에는 table name과 table owner 가 number로보입니다.
Flashback Versions Query & Flashback Transaction Query SELECT sal,versions_starttime, versions_endtime,versions_xid, versions_operation FROM emp VERSIONS BETWEEN scn MINVALUE AND MAXVALUE where empno=7900; SELECT xid, operation,logon_user, undo_sql FROM flashback_transaction_query WHERE xid='001b001200000018' 다음예제는 EMP 의 empno 값이 7900 인 data 의 salary 가어떻게변했는지를 flashback versions query 를통해확인해보고특정 transaction (001B001200000018) 에 undo 정보를 flashback_transaction_query 에서보았습니다. 이 transaciton 은 delete 와 insert 가한번에이루어졌으며 sal=100 인 data 가현재까지유효함을알수있습니다.
SELECT sal,versions_starttime, versions_endtime,versions_xid, versions_operation FROM emp VERSIONS BETWEEN scn MINVALUE AND MAXVALUE where empno=7900; SAL VERS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V_OPER ------ --------------- ---------------- ------------ -------- 100 16-JAN-05 12.17.07 AM 001B001200000018 I 400 16-JAN-05 12.17.07 AM 001B001200000018 D 400 16-JAN-05 12.15.59 AM 16-JAN-05 12.17.07 AM 001A000A0000001B U 950 16-JAN-05 12.12.05 AM 16-JAN-05 12.15.59 AM 001D002E00000017 I SELECT xid, operation,logon_user, undo_sql FROM flashback_transaction_query WHERE xid='001b001200000018' XID OPERATION LOGON_USER --------------------------- ------------------ ----------------------- UNDO_SQL ----------------------------------------------------------------------------- 001B001200000018 INSERT SCOTT delete from "SCOTT"."EMP" where ROWID = 'AAAMgsAAEAAAAAmAAA'; 001B001200000018 DELETE SCOTT insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D EPTNO") values ('7900','JAMES','CLERK','7698',TO_DATE('1981/12/03 00:00:00', 'yy yy/mm/dd hh24:mi:ss'),'400',null,'30'); 001B001200000018 BEGIN SCOTT
Flashback Table Overview Flashback Table Overview Flashback Table은 table 에우연히잘못된 data를입력하거나 data를삭제했을경우잘못된작업을하기전시점으로쉽고빠르게돌려주기위해제공되는방법입니다. Flashback Table은 backup의 restore 없이특별한시점까지 table들을복구합니다. 이는 table의 data에대해서복구되며이 table 과관련한모든 objects( index, constraints,triggers 등 ) 는현재시점으로유지가되며통계정보는 Flashback 되지않습니다. 앞서의 Flashback Versions Query와 Flashback Transaction Query를함께사용하여복구시간이나 scn 을결정할수있으며작업동안에 table에는 exclusive 의 lock을잡게됩니다. Flashback table 을한후에도원래의 data 는가지고있어다시현시점으로돌아올수있습니다. Flashback Table 은다음의경우에는사용할수없는제한이있습니다. Cluster, Mview, AQ tables, static data dictionary tables, system tables, remote tables 등입니다.
Flashback Table 의 Configure Flashback Table 은 undo data를사용하여과거시점으로빠르게복구를할수있습니다. 이방법은 DBA의관여없이 user 가직접쉽고빠르게 recover 를할수있다는장점이있습니다.. Flashback Table 은 undo tablespace 로부터정보를추출하며 UNDO_RETENTION 초기화 parameter 를이용해서 DB 내에유지하는 undo 정보를조절할수있습니다. Undo_retention 값이너무작게잡게되면 flashback data 를보관하고있는시간이짧아 flashback 을할수없게됩니다.
Flashback Table ( 예 ) 권한 Alter table tablename enable row movement ; SQL> flashback table flash_test to scn 924015; SQL> flashback table flash_test to timestamp(systimestamp - interval '3' minute); SQL> flashback table emp,dept to scn 1017122; flashback table 을위해서는 flashback object 권한이나 flashback any table system 권한이부여되어야합니다. 또한해당 table 에대한 select,insert,delete,update,alter table object 권한도 user 에게필요합니다. flashback table은 rowid 를보존하지않기때문에 Alter table command를사용하여 row movement 를허용해야합니다. flashback table 은특정 SCN이나시간으로 table을 flashback 할수있습니다. 이는하나의 transaction으로실행되며 table 이성공적으로 flashback 하거나모두실패하거나둘중하나입니다. 그렇기때문에 flashback table을 rollback할수없습니다. 만일 rollback을원한다면 flashback table을하기전시점으로 Flashback table을다시하시면됩니다. 이런경우를대비해서반드시 flashback table을실행하기전에현재의 scn을기록해두어야합니다. 이는 v$database 에서확인이가능합니다. SQL> SELECT current_scn FROM v$database; 만일 flashback table시에여러 table 명을줄수있는데이는 dependency 를가지고있는 table이있다면함께 data의 consistency 를맞추어야할필요가있을때사용할수있습니다. 또한 flashback table command 는 alert log 에기록이남습니다. 다음은 flashback table 전에해당 table 에대한 row movement 가필요함을보여줍니다.
SQL> SELECT a, b,versions_startscn, versions_endscn,versions_xid,versions_operation FROM flash_test VERSIONS BETWEEN scn MINVALUE AND MAXVALUE ; A B VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID Vers_oper ------ ------ ------------------ --------------- ------------ --------- 5 tt 924015 0018002F00000017 I 4 tt 924015 0018002F00000017 I 4 second 922778 0018000600000015 D 4 second 922773 922778 0017002E0000000B U 4 first 922769 922773 0018000800000015 I 1 second 918180 0018000400000013 U 1 first 918095 918180 0018000000000013 I SQL> flashback table flash_test to scn 923993; flashback table flash_test to scn 923993 * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled ALTER TABLE flash_test ENABLE ROW MOVEMENT SQL> flashback table flash_test to scn 923993; Flashback complete. SQL> select * from flash_test; A B ---------- ---------- 1 second SQL> flashback table flash_test to scn 924015; Flashback complete. SQL> select * from flash_test; A B ---------- ---------- 1 second 4tt 5tt
Flashback Table Errors ORA-02091: transaction rolled back ORA-02291:integrity constraint(hr.emp_job_fk) violated - parent key not found ORA-02091: transaction rolled back ORA-02292:integrity constraint(hr.emp_job_fk) violated - child record found Flashback table emp to scn 397196 enable triggers; ORA-1466 unable to read data table definition has changed ORA-8183 Flashback cannot be enabled in the middle of a transaction 만일 flashback table을실행할때 referential integrity constraints 가거부되면이 command 는실패합니다. (ora-2091,ora-2291,ora-2292 error ) Table 은 flashback table을실행하기전상태로그대로있습니다. flashback table 작업동안에는 user trigger 는 disable 상태로있습니다. flashback table작업이끝난후에 trigger status는 flashback 전의상태로돌아갑니다. flashback table 에 Enable Triggers option은현재 Table에정의된모든 Trigger를 Enable합니다. 만일선택적으로 Trigger를 Disable하려면 Enable Triggers 하기전에 Alter table trigger Command를이용해서 Disable을미리해두시면됩니다. Flashback하려는 table의구조를바꾸는 DDL operation(upgrading, moving, truncate, add, modify, drop, merging, split, coalescing 등 ) 이실행이되었다면해당 DDL operation 전으로 flashback은불가능합니다. (ora-1466 error 를만나게됩니다.) Storage를변경을하는 DDL operation은 Flashback table command 에영향을받지않습니다. Flashback table 은 transaction 중간단계로 flashback 은불가능하므로 (ora-8183 or ora-54) 반드시현재의 transaction 이 commit 이나 rollback 되어야합니다.
Flashback Database Overview Logical database corruptions User나 application에서 operation 실수 Flashback Database Overview DB 의크기가커짐에따라전체 datafile을 restore하기위해서소요되는시간은전통적인복구방법을어렵게합니다. Flashback database는전체 Oracle Database를과거의어떤시점의상태로돌려놓습니다. Flashback database는 user의 operation 실수에의한잘못된결과를 undo 하기위해서만사용됩니다. 만일물리적 corruption 이나 media loss 에의한 recovery 는기존의방법으로하셔야합니다. Flashback database는 backup과 redo log file들을사용하는전통적인방법보다더빠른복구방법이며 DB 의 flashback 시간은 Db의 size 에비례하지않고 Back out 하기위해필요한 scn 수에비례합니다.
Flashback Database Configure Flashback Logs RVWR(Recovery Writer) ground process Flashback Database는 Flashback Database log 라고불리는새로운 type의 log file들을사용하여 recover 합니다. Oracle database server가주기적으로 flashback database log에 data block의 before image 를기록하며이는 Flashback Database 시에 datafile들을 back out 하기위해사용됩니다. Flashback Database log 는 Flash recovery area 내에서자동적으로생성되고관리됩니다. 그러므로 flashback log files의생성과관리, 정확한크기의측정이따로필요하지않습니다. 다만 Flash recovery area를위한 disk space를얼마나할당할것인지만고려하면되고이 file은 archive 되지않습니다. Flashback database 가 enable 되면새로운 RVWR background process 가 start 됩니다. 이는 Flashback buffer 의 Flashback Database data 를 Flashback database log 에순차적으로 write 하기위한 background process 입니다.
Flashback Database Setup init.ora or spfile file DB_RECOVERY_FILE_DEST (dynamically modifiable) DB_RECOVERY_FILE_DEST_SIZE (dynamically modifiable) DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) SQL> shutdown normal (immediate) SQL> Startup mount; SQL> Alter database flashback on; SQL> Alter database open; SQL> select flashback_on from v$database; FLASHBACK_ON ------------ YES Flashback Database 사용을위해서다음의세 parameter 를 init file 이나 spfile 에설정을합니다. DB_RECOVERY_FILE_DEST (dynamic) RVWR background process 가 flashback logs를 write 할물리적인위치입니다. DB_RECOVERY_FILE_DEST_SIZE (dynamic) DB_RECOVERY_FILE_DEST내에저장될 flashback logs의최대크기입니다. DB_FLASHBACK_RETENTION_TARGET (dynamic) Database 를 Flashback 할수있는상한선입니다.( 단위 : 분 ) Database Shutdown은반드시 immediate 나 normal 로내립니다. 또한 Database 는반드시 archive log mode로운영하셔야합니다. Database를 mount 상태에서 flashback mode 를 enable 후에 open 하시고 flashback_on과 rvwr process 가기동되어있는지확인이되시면 Flashback database configure 는정상적으로 setting 이된것입니다.
Flashback Database Monitoring SQL> SELECT * FROM v$flashback_database_log; SQL> SELECT * FROM v$flashback_database_stat; SQL> ALTER DATABASE OPEN READ ONLY; SQL> ALTER DATABASE OPEN RESETLOGS; Flashback Database 의 monitoring 을위해서다음의두 view 를제공합니다. V$FLASHBACK_DATABASE_LOG 에서 flashback 가능한 scn과시간, flashback 을위해필요한 disk space 등의정보를볼수있습니다. 만일 flash recovery area의 disk space가부족하다면과거의 flashback log가 delete 됩니다. V$FLASHBACK_DATABASE_STAT를통해서 flashback database log에 flashback data를기록하는동안의 overhead 를관찰할수있습니다. 이 view의각 row는한시간간격의기록을나타내고 24시간보존됩니다. 즉, 24시간동안 1시간간격으로얼마나많은 flashback data가생성되는지를알수있습니다. 이정보를기초로 retention time 이나 flash recovery area size를조정하는데도움을얻을수있습니다. FLASHBACK_DATA 와 REDO_DATA column은시간간격동안쓰여진 flashback data 와 redo data 입니다. DB_DATA column 은 read,write 한 data block size 입니다. Flashback Database 는 Oracle database server 가 back out하기위해서 flashback log 를사용하지만 DB내에서 consistency를맞추기위해서 redo log entry도함께적용될수도있습니다. Flashback Database operation이끝난후원하는시점으로 Flashback 되었는지확인을위해서 Database 를 read only mode로 open 할수있습니다. 만일원하는시점이아니라면 DB를다시 flashback 할수있습니다. 실재운영을위해서는 Flashback Database 후에는 Resetlogs 로 open 하여야합니다.
SQL> SELECT * FROM v$flashback_database_log; OLD_FLASH_SCN OLD_FLASH_TIME RET_TARGET FLASH_SIZE ESTIMATED_FLASHBACK_SIZE --------------------------- --------------------------- ---------------------- ---- ---------------- ------------------------------------------------ 938625 09-JAN-05 1440 8192000 116121600 OLDEST_FLASHBACK_SCN flashback database를할수있는근사치의가장작은 scn (system change number) 값을나타냅니다. OLDEST_FLASHBACK_TIME flashback database를할수있는근사치의가장작은시간값을나타냅니다. RETENTION_TARGET flashback database를위한 target retention time ( 분 ) 입니다. FLASHBACK_SIZE flashback data 의현재 size (in bytes) 입니다. ESTIMATED_FLASHBACK_SIZE Flashback data가기록이되면현재 flashback retention target을만족시키기위해서 Flash recovery area의 disk space 가얼마나필요한지를예측하는 column입니다. SQL> SELECT * FROM v$flashback_database_stat; BEGIN_TIM END_TIME FLASH_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE ----------------- ----------------- ---------------------- ---------------- -------- ----------- ----------------------------------------------- 09-JAN-05 09-JAN-05 1540096 1638400 346624 0
Flashback Database ( 예 ) SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24); SQL> FLASHBACK DATABASE TO SCN 53943; FLASHBACK DATABASE는시간과 scn을사용하여원하는과거로돌아갈수있습니다. 만일 flashback database operation을 undo 하기위해서는 database forward 로 recover 해야합니다.
Flashback Database Failure(1) The control file has been restored or re-created. A tablespace has been dropped. A datafile has been shrunk. Recovery occurs through a RESETLOGS operation. Datafile drop Datafile resize 다음의경우에는 flashback database operation 을사용할수없고전통적인복구방법을이용하셔야합니다. The control file has been restored or re-created. A tablespace has been dropped. A datafile has been shrunk. Recovery occurs through a RESETLOGS operation. Flashback database는 drop된 datafile을복구하기위해서는사용할수없습니다. Drop된 datafile은 control file 에 add 되고 offline으로 mark 되며 flashback 할수없습니다. 이경우는 Flashback Database 후에해당 datafile을 restore 후복구하시면됩니다. Datafile resize 전시간으로 flashback은할수없습니다. 이는 file이 shrink 된경우만해당이되고 expand되는경우는상관이없습니다. 또한 autoextension으로된경우에도 flashback database 를사용할수있습니다. file이 shrink 된경우해당 datafile 을 restore 하고 recover해야합니다. ( 첨부 3)
Flashback Database Failure(2) Flashback Database 중에 Error ORA-38729 : Not enough flashback database log data to do flashback. ORA-38753 : Cannot flashback data file 4; no flashback log data. 만일 Flashback database 작업중에 Flashback database command 가실패한다면 Database 는여전히 mount 상태이고 Flashback 를계속하기위해 Flashback database command 를다시실행할수도있고전통적인방법으로 recover 할수도있습니다. ORA-38729 error 는 Flashback Database data가충분하지않아서생기는 error 입니다. 적절한 Flashback 시간이나 scn 를선택하든지 time based recover 를이용하셔야합니다. ORA-38753 error 는해당 file 에대해서 Flashback Database log data 가충분하지않아서생기는 error 입니다 Flashback Database log data 가부족한 datafile 은 offline 으로두고나머지 datafile 에대해서만 flashback database 를적용한후에 Offline datafile 은전통적인 recover 방법을이용해서복구해야합니다
Flashback Database Commands SQL> ALTER TABLESPACE example FLASHBACK ON/OFF; SQL> select tablespace_name,flashback_on from v$tablespace; SQL> ALTER DATABASE FLASHBACK OFF; Flashback database 사용을위한 data 기록을원하지않는 tablespace 가있다면다음 command 를이용해서 Flashback Database 에서해당 tablespace 를제외할수있습니다. SQL> ALTER TABLESPACE example FLASHBACK OFF; Tablespace 를생성하면 default 로 flashback기록을남기도록되어있으며만일 flashback 기록을남기지않은 tablespace는 Database 를 Flashback하기전에 offline 하여야합니다. 이경우전통적인방법으로 datafile을 recover 해야합니다. (1) control file을재생성한다면모든 tablespace 는 flashback on 상태에있게됩니다. SQL> select name, flashback_on from v$tablespace; 명령어를사용하여 tablespace 의 flashback status 를확인할수있습니다. SQL> ALTER DATABASE FLASHBACK OFF; 명령어를사용하여 flashback database 를 disable 할수있으며이경우존재하는모든 flashback log 들은삭제됩니다.
(1) SQL> select current_scn from v$database; CURRENT_SCN ---------------------------- 948921 SQL> alter tablespace users FLASHBACK OFF; Tablespace altered. SQL> select current_scn from v$database; CURRENT_SCN ------------------------------ 949336 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size 1301976 bytes Variable Size 261890600 bytes Database Buffers 25165824 bytes Redo Buffers 1048576 bytes Database mounted. Database opened. SQL> flashback database to scn 949336; flashback database to scn 949336 * ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: '/apac/net/64bit/app/oracle/oradata/apn10ko/users01.dbf' SQL> alter database datafile '/apac/net/64bit/app/oracle/oradata/apn10ko/users01.dbf' offline; Database altered. SQL> flashback database to scn 949336; Flashback complete.
Summary Flashback Operation Implementation Flashback Database Flashback logs+redo logs Flashback Drop Recycle bin Flashback Versions Query Undo Flashbacj Transaction Query Undo Flashback Table Undo 이상에서각 Operation 에맞는최적의조건들을유지하도록하여 Oracle Database 10g 에서제공되는 Flashback 기능을잘활용함으로써불필요한 recovery 시간을줄일수있는데도움이됩니다. 다음에첨부된 file 은참조해보시기바랍니다.
( 첨부 1) Retention Guarantee SQL> CREATE UNDO TABLESPACE undotbs1 DATAFILE '/apac/net/64bit/app/oracle/oradata/apn10ko/undotbs01.dbf' SIZE 3M RETENTION GUARANTEE; VS SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/apac/net/64bit/app/oracle/oradata/apn10ko/undotbs02.dbf' SIZE 3M; SQL> CREATE UNDO TABLESPACE undotbs1 DATAFILE '/apac/net/64bit/app/oracle/oradata/apn10ko/undotbs01.dbf' SIZE 3M RETENTION GUARANTEE; Tablespace created. SQL> alter system set undo_tablespace='undotbs1'; System altered. SQL> show parameter undo NAME TYPE VALUE --------------------- ----------- ---------------------------- undo_management string AUTO undo_retention integer 1800 undo_tablespace string undotbs1 SQL> select * from dba_data_files where tablespace_name='undotbs1'; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- ---- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ------ /apac/net/64bit/app/oracle/oradata/apn10ko/undotbs01.dbf 2 UNDOTBS1 3145728 384 AVAILABLE 2 NO 0 0 0 3080192 376
SQL>insert into undo1_t select * from undo1_t * ERROR at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' Error 후 Flashback version query SELECT * from undo1_t VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE ; => 결과 return ************ SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/apac/net/64bit/app/oracle/oradata/apn10ko/undotbs02.dbf' SIZE 3M; Tablespace created. SQL> alter system set undo_tablespace='undotbs2'; System altered. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- undo_management string AUTO undo_retention integer 1800 undo_tablespace string undotbs2 SQL> / insert into undo2_t select * from undo2_t * ERROR at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2' Error 후 Flashback version query SQL> SELECT * from undo2_t VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE ; 2 ERROR: ORA-01555: snapshot too old: rollback segment number 35 with name "_SYSSMU35$" too small
( 첨부 2) Datafile Drop 후 Flashback Database Open 시 Error 를만나게됨 ORA-01245: offline file 9 will be lost if RESETLOGS is done ORA-01111: name for data file 9 is unknown - rename to correct file ORA-01110: data file 9: '/apac/net/64bit/app/oracle/product/10.1.0/dbs/unnamed00009 SQL> create tablespace test datafile '/apac/net/64bit/app/oracle/oradata/apn10ko/test1.dbf' size 1m; Tablespace created. SQL> select current_scn from v$database; CURRENT_SCN ----------- 944667 SQL> drop tablespace test; Tablespace dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 289406976 bytes Fixed Size 1301976 bytes Variable Size 261890600 bytes Database Buffers 25165824 bytes Redo Buffers 1048576 bytes Database mounted. SQL> flashback database to scn 944667; Flashback complete. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01245: offline file 9 will be lost if RESETLOGS is done ORA-01111: name for data file 9 is unknown - rename to correct file ORA-01110: data file 9: '/apac/net/64bit/app/oracle/product/10.1.0/dbs/unnamed00009' 와같은 error 를만납니다. 이후의 recovery 방법은 online datafile 을 restore 하고 tablespace drop 하기이전시점으로 recover 하시면되겠습니다.
( 첨부 3) Datafile Resize 후 Flashback Database 1) shrink SQL> flashback database to scn 946738; ERROR at line 1: ORA-38766: cannot flashback data file 9; file resized smaller ORA-01110: data file 9: '/apac/net/64bit/app/oracle/oradata/apn10ko/test2.dbf' VS 2) expand SQL> flashback database to scn 945438; Flashback complete. 1. datafile 를작은 size 로 shrink 할경우 SQL> select current_scn from v$database; CURRENT_SCN ----------- 946738 SQL> alter database datafile '/apac/net/64bit/app/oracle/oradata/apn10ko/test2.dbf resize 1m; Database altered. SQL> select current_scn from v$database; CURRENT_SCN ----------- 946766 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size 1301976 bytes Variable Size 261890600 bytes Database Buffers 25165824 bytes Redo Buffers 1048576 bytes Database mounted.
SQL> flashback database to scn 946738; flashback database to scn 946738 * ERROR at line 1: ORA-38766: cannot flashback data file 9; file resized smaller ORA-01110: data file 9: '/apac/net/64bit/app/oracle/oradata/apn10ko/test2.dbf' 2. datafile 를큰 size 로 expand 할경우 SQL> select current_scn from v$database; CURRENT_SCN ----------- 945438 SQL> alter database datafile '/apac/net/64bit/app/oracle/oradata/apn10ko/users01.dbf' resize 25m; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size 1301976 bytes Variable Size 261890600 bytes Database Buffers 25165824 bytes Redo Buffers 1048576 bytes Database mounted. SQL> flashback database to scn 945438; Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select * from dba_data_files ; FILE_NAME FILE_ID TABLESPACE_NAME ---------------------------------------------- ---------------------- BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY ------- ------- -------- --------------- ---------- ---------- --------- USER_BYTES USER_BLOCKS ----------- ------------ /apac/net/64bit/app/oracle/oradata/apn10ko/users01. 율 4 USERS 23592960 2880 AVAILABLE YES 3.4360E+10 4194302 160 23527424 2872