Oracle FlashBack 플래시백 ( 데이터 복원)

Oracle Flashback 기능이란?

DB관리중에 실수로 데이터를 삭제하거나 데이터값을 잘못 변경하는 실수가 가끔 발생 합니다.
이러한 실수는 commit 이전이라면 rollback명령어로 수행한 작업을 원복 시킬수 있지만 기억할수 없는 다수의 컬럼의 수정 및 삭제를 한뒤 commit을 했다면??
이러한 경우에 특정한 시간 또는 특정 시점으로 되돌릴수 있는 기능이 Oracle FlashBack 입니다.

 

• dump파일 없이 논리적 장애를 (DML, DDL)을 빠르게 복구

 

• 물리적인장애 (파일의손상, 디스크손상) 에 대해서는 복구 불가

 

• row level, table level, database level 3개 분류로 나눌수 있음.

 

• row level, table level : oracle 에서 기본 권한 사용 가능

 

• database level :  system 권한 필요, system down 후 진행
 

 

 

FLASHBACK을 사용하기 위한 요구 조건

FLASHBACK 을 사용하기 위한 요구조건에 대해 아래에서 자세히 알아보시기 바랍니다.

 1) SHOW PARAMETER UNDO; ( system 계정 ) 
 
NAME            TYPE    VALUE
--------------- ------- --------
undo_management string  AUTO
undo_retention  integer 900
undo_tablespace string  UNDOTBS1
 


2) SELECT * FROM V$VERSION; -- oracle enterprise 버전만 가능 express 버전 X

 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 
express에서 사용할 경우 : flashback 기능 사용시 --  ORA-00439: feature not enabled: Flashback Table 에러 발생
PARAMETER UNDO 항목 대한 설명

① UNDO_MANAGEMENT

– 테이타베이스의 Undo 모드를 자동 모드로 사용 할지 수동 모드를 사용할지 여부를 결정 합니다.
– AUTO 또는 MANUAL 값 중 하나로 설정할 수 있으며 초기화 파라미터 파일에서 설정해야 합니다.
– AUTO로 설정하게 되면 데이터베이스는 자동 Undo 관리 모드로 설정되며 undo 테이블스테이스가 필요합니다.
– UNDO_MANAGEMENT = AUTO 로 설정 되어있어야 함
② UNDO_RETENTION
– 일관성 읽기를 위해 제공되는 Undo 데이타의 보유 기간을 결정합니다.
– 초기화 파일에서 설정하거나, ALTER SYSTEM 명령을 사용하여 동적으로 수정할 수 있습니다.
– 이 parameter는 초 단위로 지정됩니다. 기본값은 900초이며, 이는 Undo 데이타를 15분 동안 보유합니다.
※ 설정된 시간 만큼 undo data를 유지 하겠다는 뜻이며, 결국 flash back query는 undo_retention 범위 안에서 유효하다

③ UNDO_TABLESPACE
– 사용할 특정 UNDO 테이블스페이스를 지정 합니다.
– 최소한 하나의 UNDO 테이블 스페이스 생성이 필요 합니다.
– 초기화 파일에서 설정하거나 데이터베이스의 운영중에도 ALTER SYSYTEM 명령을 사용하여 동적으로 변경할 수 있습니다.
–시스템이 관리하는 undo sgment가 만들어질 table space

참고 사이트 : UNDO_RETENTION 설정
 

 

 

FLASHBACK 사용방법

사용방법은 10g, 11g 기준으로 정리 아래에서 자세히 알아보시겠습니다.

1. ROW LEVEL FLASHBACK 

[문제 발생 시나리오]

-- 계정 ynkim 생성 및 로그인
 
create table t_a (
  a  varchar2(10),
  b  varchar2(10)
);
 
insert into t_a(a,b) values('111', '111');
insert into t_a(a,b) values('222', '222');
........
........
 
commit;
 
delete from t_a;
commit; -- 앗차 지우면안되는데!!
 
 

[복원방법]

 

    1) AS OF TIMESTAMP 를 이용하여, 시점으로 확인하기

 

 

 

 

SELECT * FROM  YNKIM.T_A AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '10' MINUTE); 

-- 10분전 테이블 데이터 조회
 
SELECT * FROM TEST_TABLE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

-- 1일전 테이블 데이터 조회
 
select * from   YNKIM.T_A as of timestamp to_timestamp('20170117163000','yyyyMMdd hh24:mi:ss');

-- 1월 17일 16시30분 데이터 조회 


* t_a의 테이블에 현재는 데이터가 비어있으나, 해당 시점으로 조회 가능.



-- 10분전 삭제된 데이터를 다시 삽입

INSERT INTO T_A
(SELECT * FROM
T_A
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)
)


결과

A B
--------
111 111
222 222

 

 

• oracle 9i Database Release 2 이상부터는 SELECT... AS OF TIMESTEMP 명령어가 가능 !!

 

 

2) version query를 사용한 구간 검색

 

commit 내용 검색
select versions_startscn st_scn, versions_endscn endscn, versions_xid txid, versions_operation opt
, YNKIM.T_A.*
from YNKIM.T_A
versions between scn minvalue and maxvalue; -- opt 에서 D 값 이전 st_scn번호를 확인한다.


조회결과

ST_SCN ENDSCN XID Opt A B
---------- ---------- ----------------
3815692055 370017008F1E0000 D 222 222
3815692055 370017008F1E0000 D 111 111
3815682739 41001500BC160000 I 222 222
3815682739 41001500BC160000 I 111 111


- 결과의 의미는 t_a테이블에 A,B 컬럼값에 111,222 가 INSERT 되었다가 DELTE 되었음. 

 

 

2. Table LEVEL FLASHBACK

 

 

 

• 전제조건 : ROW MOVEMENT 가 활성화되어 있어야한다. (자세한 내용은 아래 SCRIPT )
 

 

 

 

• DML에러가 발생했을때 특정 테이블 전체를 문제 발생전 상태로 FLASHBACK 하는 방법
  1) SCN 으로 DML 복구
  * SCN이란?
  Commit 이 발생하게 되면 LGWR의 해당 트랜젝션은 고유한 번호를 부여받아서 함께 관리 됩니다.
  이렇게 Commit 할 때 생성되는 번호를 오라클은 System Commit Number(SCN)이라고 부릅니다.
   
  
  1. 위에서 확인한 version query 결과
  
  조회결과
  
   ST_SCN     ENDSCN        XID Opt A B 
  ---------- ---------- ---------------- 
  3815692055 370017008F1E0000 D 222 222 
  3815692055 370017008F1E0000 D 111 111 
  3815682739 41001500BC160000 I 222 222 
  3815682739 41001500BC160000 I 111 111 
  
  
  2. commit SCN 넘버 기준으로 되돌리기
  
  flashback table T_A to scn '3815682739';
  
  -- delete 이전 st_scn 확인하여 flashback 실행
  
  
  ALTER TABLE YNKIM.T_A ENABLE ROW MOVEMENT; -- 에러 발생시조치 : ORA-08189: 행 이동이 사용으로 설정되지 않았으므로 테이블을 플래시백할 할수없음.
  
  
  
  Flashback을(를) 성공했습니다.
  
  A B
  --------
  111 111
  222 222
  
  
   
  
  2) 시점으로 테이블 복구
   
  
  FLASHBACK TABLE T_A TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);
  -- 10분전으로 되돌리기
  
  
  FLASHBACK TABLE YNKIM.T_A TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
  -- 1일전 테이블 데이터 복원
  
  FLASHBACK TABLE YNKIM.T_A TO timestamp to_timestamp('20170417163000','yyyyMMdd hh24:mi:ss');
  -- 4월 17일 16시30분 데이터 복원
  
  
  
  관련 에러 :
  ORA-01466: unable to read data - table definition has changed
  - 시간을 너무 뒤로 돌려서 해당 테이블 또는 commit 정보가 존재하지 않는 시간대를 선택했을시 발생.

 

 

 

3. Database LEVEL FLASHBACK (sysdba 권한)

전통적인 복구 방식 : 장애 발생시 백업된 데이터파일을 적용시켜 복구

Flashback database 복구 방식 : 장애 난 데이터파일에 Flashback log 라는 것을 바로 적용시켜 복구
필요에 따라 redo log를 사용하기도 함
→ 백업 파일을 복원하는 데 시간이 들지 않기 때문에 훨씬 더 빨리 복구 할수 있다.
: 이 방식으로 복구하는 대표적 장애 - drop user / truncate table 장애

1) 테이블 확인 장애 발생

ynkim > select * from t_a;

A B
--------
111 111
222 222

ynkim > truncate table t_a; <-- 논리적 장애발생

Table T_A이(가) 잘렸습니다.


ynkim > select * from t_a;

선택된 행 없음 


*truncate DDL 명령어로 flashback table 로는 복구 불가

 

2) 복구하기 - 명령어 수행

 


YNKIM> flashback database to timestamp (systeimstamp - interval '5' minute);
에러
ORA-01031: insufficient privileges
=> 권한에러 : sysdba 권한을 가지고 있어야 한다!!


YNKIM> conn / as sysdba;
SYS> flashback database to timestamp (systeimstamp - interval '5' minute);
에러
ORA-38757: Database must be mounted and not open to FLASHBACK.
=> MOUNT 상태에서 수행해야 한다!!




Flashback database 명령어는 모든 데이터 파일을 불완전 복구처럼 과거 특정시간으로 돌린다.
→ 데이터파일과 리두로그파일, 컨트롤파일의 SCN정보가 다르게 된다.

SYS> shutdown imediate;
SYS> startup mount; -- 마운트 상태에서 실행
SYS> flashback database to timestamp (systeimstamp - interval '10' minute);
SYS> alter database open resetlogs; -- database open
SYS> select * from YNKIM.T_A;

복구완료

A    B 
--------
111 111 
222 222

 

 

 RECYCLEBIN 이란?

• 사용자마다 할당되며, 다른사용자의 휴지통은 접근할수 없다.

 

• 오라클 10g 에서 새로 제공하는 기능으로 Windows 휴지통과 비슷한 개념이다.

 

• Object 를 Drop 해도 복구가 가능하다.

 

• Drop 된 Object 는 동일한 Tablespace 에 존재한다.

 

• Purge 명령으로 완전히 삭제한다.

 

• Tablespace 나 User 를 Drop 하게되면 복구되지 않는다.

 

1) Drop tblae 테이블 삭제/복원 시나리오

drop table t_a;



ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ----------- -------------------
T_A BIN$TQuJMMCAkwfgUAB/AQAx3w==$0 TABLE 2017-04-13:20:30:41
T_TABLE BIN$Rm5UzQmTRRHgUAB/AQBw9w==$0 TABLE 2017-01-19:16:17:51  



flashback table YNKIM.T_TABLE to before drop;
-- 기존 테이블명 t_table 이름으로 복원

flashback table "BIN$RkW5eyI8OrrgUAB/AQAozg==$0" to before drop;
-- 기존 테이블명 t_table 이름으로 복원 / RECYCLEBIN NAME으로 복원 시


flashback table T_TABLE to before drop rename to T_RENAME;
-- 테이블 이름을 변경하여 복원.

flashback table "BIN$RkW5eyI8OrrgUAB/AQAozg==$0" to before drop rename to T_RENAME;

2) Recyclebin 의 삭제

drop table t_a purge; -- RecycleBin에 남지 않고 Drop되기 때문에 복원 불가

purge table "BIN$lWD2/r7gQT6hLjW265stXQ==$0"; -- 특정 Table을 지정하여 recyclebin에서 삭제

purge recyclebin; -- recyclebin에 있는 모든 Object들 삭제

 

 

oracle flash back 참고 자료

예제 위주로 쉽게 설명되어 있어서, 이해하는데 많은 도움이 되었다.

https://www.appletong.com/entry/Oracle-FLASHBACK-에-대해-알아보자데이터복원

Oracle FLASHBACK 에 대해 알아보자(데이터복원)