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

Similar documents
FlashBackt.ppt

Microsoft Word - Goodus_기술노트[19회]_Flashback

Microsoft PowerPoint - Tech-iSeminar_Flashback.ppt

13주-14주proc.PDF

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate

목 차

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O

윈백및업그레이드 Tibero Flashback 가이드

세미나(장애와복구-수강생용).ppt

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc

歯sql_tuning2

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

ePapyrus PDF Document

Jerry Held


Microsoft Word - 기술노트[23회] Logminer.doc

PRO1_09E [읽기 전용]

MS-SQL SERVER 대비 기능

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT

Oracle Database 10g: Self-Managing Database DB TSC

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Microsoft Word - SQL튜닝_실습교재_.doc

Tablespace On-Offline 테이블스페이스 온라인/오프라인

Microsoft PowerPoint - Tech-iSeminar_Logminer.ppt

10.ppt

歯PLSQL10.PDF

´ÙÁß Row °á°ú¸¦ ´ÜÀÏÇàÀ¸·Î Äĸ¶·Î ºÐ¸®ÇØ Ãâ·ÂÇÏ´Â ¹æ¹ý

SQL Tuning Business Development DB

PowerPoint Presentation

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

untitled

歯815설치1.PDF

PowerPoint Presentation

Jerry Held

슬라이드 1

The Self-Managing Database : Automatic Health Monitoring and Alerting

最即時的Sybase ASE Server資料庫診斷工具

SQL Developer Connect to TimesTen 유니원아이앤씨 DB 기술지원팀 2010 년 07 월 28 일 문서정보 프로젝트명 SQL Developer Connect to TimesTen 서브시스템명 버전 1.0 문서명 작성일 작성자

Commit_Wait / Commit_Logging 두파라미터를통해 Log File Sync 대기시간을감소시킬수있다는것은놀라움과의아함을동시에느낄수있다. 단지파라미터의수정을통해당연히대기해야하는시간을감축한다는것은분명성능을개선해야하는입장에서는놀라운일이될것이다. 반면, 그에따

ALTIBASE HDB Patch Notes

untitled

DBMS & SQL Server Installation Database Laboratory

Microsoft PowerPoint - Oracle Data Access Pattern.ppt

@OneToOne(cascade = = "addr_id") private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a

예제소스는 에서다운로드하여사용하거나툴바의 [ 새쿼리 ]( 에아래의소스를입력한다. 입력후에는앞으로실습을위해서저장해둔다. -- 실습에필요한 Madang DB 와 COMPANY DB 를모두생성한다. -- 데이터베이스생성 US

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Simplify your Job Automatic Storage Management DB TSC

제목을 입력하세요.

PowerPoint 프레젠테이션

<4D F736F F D205BB4EBBBF3C1A4BAB8B1E2BCFA5DB1E2BCFAB9AEBCAD2D524D414EBBE7BFEBBFB9C1A65F39695F313067>

목차 BUG DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4

NoSQL

Microsoft Word - dataguard_세미나_v1.8.doc

MySQL-.. 1

Data Guard 기본개념.doc

RDB개요.ppt

디지털포렌식학회 논문양식

90

슬라이드 1

ETL_project_best_practice1.ppt

ORACLE-SQL

배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 (

Microsoft Word - 05_SUBPROGRAM.doc

Contents Data Mart 1. 개요 실습방향 테스트위한사전설정 본격실습시작 ) 데이터파일 dd 명령어로 백업수행및유실시키기 ) 장애복구수행 결론...7 페이지 2 / 7

ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들

ESQL/C

Remote UI Guide

Microsoft Word - 기술노트[23회]_Logminer_1.1

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT

TITLE

62

단계

객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES B WHERE 1=1 AND A.MANAGER_ID = B.EMPLOYEE_ID AND B.SALARY >= ANY A.SALARY;

<4D F736F F D203033C6C4C6BCBCC72DB8AEBFC0B1D7B9E6B9FD2E646F63>

Microsoft Word - PLSQL.doc

MySQL-Ch10

오라클 데이터베이스 10g 핵심 요약 노트

Tina Admin

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역

DocsPin_Korean.pages

Intra_DW_Ch4.PDF

대량의 DML 작업에대한성능개선방안 엑셈컨설팅본부 /DB 컨설팅팀박준연 개요 대량의데이터를변경해야하는작업은그자체만으로도큰부담으로다가온다. 하지만변경작업자체에만국한되는것이아니라변경되기전데이터와변경이후데이터를각각저장관리해야하는메커니즘이라면성능을개선해야하는입장에서는더욱큰부담

Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용]

Microsoft PowerPoint - Tech-iSeminar_Managing_Tablespace.ppt

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET

APOGEE Insight_KR_Base_3P11

PowerPoint Presentation

PowerChute Personal Edition v3.1.0 에이전트 사용 설명서

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

PRO1_02E [읽기 전용]

Microsoft PowerPoint - Linux_10gRAC_3Node

Spring Boot/JDBC JdbcTemplate/CRUD 예제

ALTIBASE HDB Patch Notes

CD-RW_Advanced.PDF

Tina Admin

PowerPoint 프레젠테이션

Partition Table

Transcription:

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

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... 15 1.3.4. Flashback Query... 18 1.3.5. Flashback transaction query... 20 1.3.6. Flashback Table... 23 1.3.7. Flashback Use Case... 25-2 -

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) 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) 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. 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; ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 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 -

Commit complete. SQL> select * from emp 2 where deptno=20; ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 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. 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 ----------- 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:11:00', 'DD-MON-YY:HH24:MI:SS')) 3 where department_id=20; 2 rows created. Commit complete. SQL> select employee_id - 5 -

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. 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 -

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) create or replace procedure exam_flash as cursor tmp_curs is - 7 -

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 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 의사용용도는 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 시간이걸린다는점이다. - 8 -

위의그림, 앞의설명과같이 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 394264576 bytes Fixed Size 2006632 bytes Variable Size 218104216 bytes - 9 -

Database Buffers Redo Buffers Database mounted. 167772160 bytes 6381568 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(*) ---------- 65 SQL> select current_scn from v$database; CURRENT_SCN ----------- 25833961 SQL> truncate table flash; 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. - 10 -

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 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; 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. - 11 -

SQL> alter database open resetlogs; Database altered. SQL> select count(*) from scott.flash; COUNT(*) ---------- 65 1.3.2. 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; - 12 -

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 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); - 13 -

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 할것이다. 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 - 14 -

------------------------------ 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 1.3.3. 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('20060213 171201', 'yyyymmdd hh24miss')) t from dual; =>8268801520810 과거의 SCN 을이용하여 Time 알기 select scn_to_timestamp(8268801520810) from dual; => 2006/02/13 17:12:01.000000000 Versions Query 의 Pseudo column (Select 절에사용할수있음 ) Versions_startscn, Versions_starttime Versions_endscn Versions_endtime Versions_xid Versions_operation - 15 -

주의 : undo retention 보다이전의 version 을 query 하면 ora-30052 : invalid lower limit snapshot expression 발생함. 예제 Data 변경 (Update) 하기 SQL> conn scott/tiger Connected. SQL> select * from emp; ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 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> update emp 2 set comm=1111 3 where empno=7934; 1 row updated. 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. Commit complete. SQL> update emp 2 set comm=3333-16 -

3 where empno=7934; 1 row updated. Commit complete. SQL> select * from emp; ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 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. 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 - 17 -

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 에서부터지난시점의데이터를질의하기위한 DBMS_PACKAGE 를제공했으며 10g 에서는훨씬기능을유연하게발전시켰습니다. Flashback Query 는 AS OF 절을사용하여해당시점에서의데이터값에대한질의가가능하며, 이기능은 DBMS_FLASHBACK 패키지의기능과유사합니다. Flashback versions query 는과거의일정시간구간에서조회하는것에비해 Flashback query 는, 과거의일정한시간에서 query 를하는것. Database 는현재의시간이지만, 수행하는 SQL 은혼자과거의정보를보게됨. 예제 -- Data 삭제 ( 장애만들기 ) SQL> select * from emp; ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 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. SQL> delete emp 2 where empno=7934; 1 row deleted. Commit complete. SQL> select * from emp; ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20-18 -

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); ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 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); ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 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. - 19 -

-- 1 시간전 Data 와현재 Data 의차이를알고싶을때. -- 즉, 1 시간전과같지않은데이터를모두찾는다. SQL> select * from emp as of timestamp ( systimestamp - interval '1' hour) 2 minus 3 select * from emp; ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 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. Commit complete. SQL> select * from emp; ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 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.3.5. 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 의분석과진단을하는기능임. - 20 -

변경사항뿐만아니라, 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; ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 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> update emp set sal = 9999, job='2030' where empno=7934; - 21 -

1 row updated. SQL> update dept set dname = 'ADMIN' where deptno = 40; 1 row updated. Commit complete. SQL> select * from emp; ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 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 - 22 -

------------------------------ ------------------------------ ---------------- - ---------- -------------- 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 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> select * from emp; ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 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 Flashback Table 은잘못된데이터처리를한경우, 작업전의시점으로빠르게돌려주기위한기능. (SCN or - 23 -

시간 ) 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. 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; - 24 -

Flashback complete. 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 를이용하여변경이력추적 - 25 -