Flashback 기능은 사용자의 논리적인 오류를 아주 빠르게 복구할 수 있는 방법
Flashback 명령어 3가지 (LEVEL) 방법
1. Row Level Flashback
2. Table Level Flashback
3. Database Level Flashback
오라클버젼 |
Flashback 종류 |
원리 |
9i |
Flashback Query |
Undo data 사용 |
10g |
Flashback Version Query Flashback Transanction Query Flashback Table Flashback Database |
Undo data 사용 Undo data 사용 Undo data 사용 / recyclebin 사용 Undo data 사용 / Redo log 사용 |
11g | 10g 와 동일 | 10g와 동일 / Flashback Data Archive 추가됨 |
1. Row Level Flashback
특정 테이블의 특정 Row 만 Flashback 해주는 기능 ( Flashback 기능은 commit 된 데이터만 복구 가능 )
실습 1. Row Level Flashback 을 사용하여 특정 행 복구
실습전에 DB의 Supplemental Logging 을 Enable 로 변경 후 작업
SYS> select supplemenatl_log_data_min from v$database ;
SUPPLEME
---------
NO
-> SYS> alter database add supplemental log data ;
SYS> select supplemenatl_log_data_min from v$database ;
SUPPLEME
---------
YES
SYS> create table scott.fmem
2 ( name varchar2(10),
3 addr varchar2(10),
4 tel varchar2(10)) ;
SYS> insert into scott.fmem values ( '박동주','부천','111') ;
SYS> insert into scott.fmem values ( '서진수','강남','222') ;
SYS> insert into scott.fmem values ( '김효섭','구리','333') ;
SYS> commit ;
SYS> select * from scott.fmem ;
NAME ADDR TEL
-------------------
박동주 부천 111
서진수 강남 222
김효섭 구리 333
SYS> update scott.fmem
2 set name='안지혜'
3 where tel=111 ;
SYS> commit ;
SYS> update scott.fmem
2 set name='김세실'
3 where tel=222 ;
SYS> commit ;
SYS> select * from scott.fmem ;
NAME ADDR TEL
-------------------
안지혜 부천 111
김세실 강남 222
김효섭 구리 333
안지혜를 다시 박동주로 돌리자
- 해당 데이터의 과거 변경 이력을 전부 찾아 주는 쿼리 : Flashback Version Query
9i 는 Flashback Query 가 지원되었는데 특정 시점의 변경 내역만 알수 있어서 좋지 않음.
변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리를 Flashback Transaction Query 라고 한다.
변경 이력을 찾는 Flashback Version Query 수행
SYS> select versions_startscn st_scn,versions_endscn endscn,
2 versions_xid txid,versions_operation opt,name
3 from scott.fmem versions between scn minvalue and maxvalue
4 where tel=111 ;
ST_SCN ENDSCN TXID O NAME
---------------------------------------------------
2046479 070004006904000 U 안지혜
2046412 2046479 010007004004000 I 박동주
scn_to_timestamp( ) 라는 함수를 사용하여 변경사항이 발생한 시간을 추적할 수 있음
SYS> select scn_to_timestamp(2046479) from dual ;
SCN_TO_TIMESTAMP(2046479)
------------------------------
12-NOV-14 12.12.12.0000000000AM
Flashback Transaction Query 사용 과정이 복잡해 일반적으로 DBA가 직접 update 작업을 수행함
SYS> update scott.fmem set name='박동주' where tel=111 ;
SYS> commit ;
2. Table Level Flashback
특정 테이블 안에 있는 데이터를 전부 한 번에 특정 시점으로 복구 할 수 있는 방법
Table의 칼럼이 삭제된 후 undo segment 내역을 못찾는 경우 flashback으로 복구할 수 없다.
실습 2. SCN을 조회하여 DML 에러 복구하기 - Undo data 사용
SYS> create table scott.fruits (
2 no number,
3 name varchar2(10),
4 price number) ;
SYS> insert into scott.fruits values (1,'apple',1000) ;
SYS> insert into scott.fruits values (2,'grape',1500) ;
SYS> insert into scott.fruits values (3,'peach',800) ;
SYS> commit ;
SYS> select * from scott.fruits ;
NO NAME PRICE
-----------------
1 apple 1000
2 grape 1500
3 peach 800
SYS> select current_scn from v$database ;
CURRENT_SCN
--------------
2058671
SYS> update scott.fruits
2 set prifce = 2000
3 where no=2 ;
SYS> commit ;
SYS> select * from scott.fruits ;
NO NAME PRICE
-----------------
1 apple 1000
2 grape 2000
3 peach 800
SYS> select current_scn from v$database ;
CURRENT_SCN
--------------
2058724 <-- grape 가격이 2000으로 변경된 scn
grape 가격이 1500원인 때로 돌아가고 싶다는 요청이 들어오면 그 시점의 scn으로 flashback 하면 됨
SYS> flashback table scott.fruits to scn '2058724' ;
ERROR at line 1 :
ORA-08189 : cannot flashback the table because row movement is not enabled
위와같이 row movement 속성이 not enable 되어서 에러 발생, 이 기능을 활성화 시킨 후 다시 실행
SYS> alter table scott.fruits enable row movement ;
SYS> flashback table scott.fruits to scn '2058724' ;
SYS> select * from scott.fruits ;
NO NAME PRICE
-----------------
1 apple 1000
2 grape 1500 <-- 원래값으로 돌아옴
3 peach 800
실시간으로 장애를 복구 하려면
SYS> flashback table scott.fruits
2 to timestamp(systimestamp - interval '5' minute) ; <--5분전 상황으로 flashback 하겠다는 의미
실습 3. Drop table 복구하기 - Recyclebin 이용
recyclebin - 윈도우의 휴지통과 같은 기능, drop table하면 recyclebin 으로 보관하다가 복구 가능
step1. 새로운 사용자 생성 및 테이블 생성
SYS> create user test identified by test
2 default tablespace users
3 temporary tablespace temp ;
SYS> grant connect, resource to test ;
SYS> conn test/test
TEST> create table test1(no number) ;
TEST> insert into test1 values(1) ;
TEST> commit ;
TEST> select * from tab ;
TNAME TABTYPE CLUSTERID
----------------------------
TEST1 TABLE
stpe2. 테이블을 삭제 후 휴지통을 확인
TEST> drop table test1 ;
TEST> select * from tab ;
TNAME TABTYPE CLUSTERID
----------------------------------------------------
BIN$5AVBt_oQMSfgQAB/AQBnzQ==$0 TABLE
TEST> show recyclebin ;
ORIGINALNAME RECYCLEBINNAME OBJECTTYPE DROPTIME
---------------------------------------------------------------------------------
TEST1 BIN$5AVBt_oQMSfgQAB/AQBnzQ==$0 TABLE 2014-11-12:15:15:15
TEST> select * from "BIN$5AVBt_oQMSfgQAB/AQBnzQ==$0" ;
NO
---
1
원래 TEST 였던 TABLE 이 Drop 되면서 BIN$ 로 이름이 변경되었고, show parameter 로 조회하니 삭제 정보를 알 수 있음
복구 방법 ->
TEST> flashback table test1 to before drop <-- drop 전으로 flashback
TEST> select * from tab ;
TNAME TABTYPE CLUSTERID
----------------------------
TEST1 TABLE <-- 쉽게 복구 완료
TEST> show recyclebin <-- recyclebin 없어서 결과가 안나옴
실습4. Recyclebin 관리하기
Recyclebin 의 위치는 원래 테이블이 있던 Tablespace에 그대로 있고, drop 되면 이름을 Bin$... 로 변경하고 딕셔너리에서만 삭제
만약 다른테이블이 먼저 지워졌던 테이블이 있는 공간을 사용하려는 상황이 되면 그때 데이터를 지우고 신규테이블에게 공간을 할당
Recyclebin 에 넣지않고 완전 삭제하려면 purge 옵션 사용
Recyclebin 을 사용하지 않으려면 alter session set recyclebin=off 로 해당 세션에서 사용안하게 해주거나
parameter file 에 recyclebin=off 를 등록해주고 디비 재시작. 다시 on 으로 바꾸려면 parameter file 에서 지워주면 됨
recyclebin 상태 확인
select a.ksppinm, b.ksppstvl, b.ksppstdf
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%recyclebin%'
order by a.ksppinm ;
KSPPINM KSPPSTVL KSPPSTDF
----------------------------
recyclebin on TRUE
실습5. 테이블 관련 다른 object 확인하기
flashback 으로 테이블을 삭제하게 되면 해당 테이블과 관련된 모든 object ( index, constraint 등) 들도 함께 삭제됨
flashback 으로 테이블을 복구하면 다른 object 들도 테이블과 함께 복구 됨.
flashback 으로 복구하게 되면 view 의 상태는 INVALID 로 사용불가 상태가 되고 index는 BIN$ .. 로 이름이 변경되어 조회되지않음
@stats로 확인, index 의 이름을 alter index "BIN$...." rename to 원래인덱스명 ; 으로 바꿔주고 사용해야함
view 는 사용불가 상태라서 삭제 후 다시 생성해야 함.
@cons 제약조건 확인 제약조건 역시 BIN$... 로 제대로 이르 변경이 안됬지만 작동은 하므로 이름만 변경해주면 됨
alter table 테이블명 rename constraint "BIN$...." to "제약조건명" ;
table 이 flashback 으로 복구 되더라도 다른 object 는 관리자가 추가로 작업해줘야 함
만약 recyclebin 에 같은이름의 테이블이 여러개 삭제되어 있었을 경우 가장 최근에 지워진 것부터 복구 되며, flashback table 은 table 과 같이 지워진 index는 복구 되지만 index만 따로 지워진 경우는 복구 안됨
3. Database Level Flashback
database 전체를 과거의 특정시점으로 돌리는 불완전복구와 비슷한 개념을 갖고있다. 그러나 불완전 복구보다 빠르고 간단함
- 불완전 복구 : 장애시 백업된 데이터파일을 복원해 리두로그와 아카이브 로그를 적용시켜 복구
- flashback database : 장애가 발생한 데이터파일에 flashback log 와 리두로그, 아카이브로그를 바로 적용시켜 복구
flashback database 명령어를 사용하기위해 준비할 것
- db 아카이브 모드 , flashback database mode
환경설정 : inittestdb.ora 파일 설정
db_recovery_file_dest : /app/oracle/fast_recovery_area <--11g 일경우
db_recovery_file_dest_size : 4196401152 <--11g 일경우 4G 기본값
db_flashback_retention_target=30 <-- 별도로 추가해줘야함
MOUNT 모드로 STARTUP 해서 alter database archivelog , alter database flashback on 설정
※fast_recovery_area 에 저장되는 파일들
1. flashback log file
2. archive redo log file
3. RMAN backup file 등
기본 크기가 부족할 수 있기 때문에 위에서 언급한 3종류의 파일을 다른 경로로 설정해서 관리해주는게 hang에 안걸리기 좋음
Flashback database 명령어로 복구해야만 하는 대표적인 장애 : drop user 와 truncate table 장애
실습6. Drop User 장애 복구 ( Flashback database 사용 )
step1. 테스트용 계정과 테스트용 테이블을 생성
SYS> create user tuser
2 identified by abc123
3 default tablespace users
4 temporary tablespace temp ;
SYS> grant connect, resource to tuser ;
SYS> conn tuser/abc123
TUSER> create table test1 (no number) ;
TUSER> create table test2 (no number) ;
TUSER> insert into test1 values(1) ;
TUSER> insert into test1 values(2) ;
TUSER> insert into test2 values(3) ;
TUSER> insert into test2 values(4) ;
TUSER> commit ;
step2. 테스트용 계정 tuser 를 삭제한 후 데이터를 확인
TUSER> conn sys/oracle as sysdba ;
SYS> drop user tuser cascade ;
step3. Flashback 명령어로 복구
SYS> flashback database to timestamp (systimestamp - interval '5' minute) ;
ERROR at line 1:
ORA-38757 : Database must be mounted and not open to FLASHBACK <-- MOUNT 단계에서 복구하라고 에러남
SYS> shut immediate ;
SYS> startup mount
SYS> flashback database to timestamp (systimestamp - interval '5' minute) ;
SYS> alter database open
ERROR at line 1:
ORA-01589 : must use RESETLOGS or NORESETLOGS option for database open <-- 과거로 돌아갔기때매 resetlogs로 open
SYS> alter database open resetlogs
이러고 데이터 복구된지 확인, 만약 안되었으면 계속 shutdown 한후 mount에서 flashback 명령을 수행하면 됨.
Flashback Data Archive
Row Level Flashback 과 Table Level Flashback중 DML 장애를 처리하는것은 undo data를 활용하며 만약 다른 누군가가 undo segment 를 재활용하면 해당 flashback 기능을 사용할 수 없다.
11g 부터는 Online Redo log 를 Archive 하듯이 undo segment 에 있는 commit 된 내용을 특정 테이블 스페이스에 archive 하여 영구적으로 저장하는 기능을 제공, 그것을 Flashback Data Archive(FBDA)라고 한다.
Flashback Data Archive 원리
FBDA 백그라운드 프로세스가 비 정기적으로 undo segment 의 내용을 파일에 archive 해서 flashback data archive 기능을 구현
Flashback Data Archive 의 특징
1. FBDA 가 Undo segment 의 내용을 모두 기록하기 전에 해당 undo segment 는 재활용 되지 않음
2. 대량의 DML 이 발생할 경우 병목현상이 생길 우려가 있어 최대 10개 까지의 FBDA백그라운드 프로세스가 동시 작업가능
3. FBDA 프로세스는 평소 Sleep상태로 있다가 특정 시간이 되면 자동으로 활성화 되어 undo segment 내용을 저장
( undo 량이 많이 발생할 경우 자주 내려쓰며, 기본값은 5분으로 설정되어 있음)
4. 해당 데이터는 자동으로 파티셔닝 되어 저장되며 관리자라도 그 내요을 변경할 수 없음
5. Retention time 을 설정하여 데이터를 관리하며, retention time 이 지난 데이터는 자동삭제
6. insert 되는 데이터는 이기능을 사용하지 않음
Flashback Database Archive 활성화 하기
<작업순서>
1. Flashback history table 을 저장할 테이블 스페이스를 생성
2. Flashback data archive 를 관리할 관리자 계정을 생성
3. 관리자 계정에 권한을 할당
4. 관리자 계정으로 로그인한 후 flashback history table 을 생성
1.
SQL> create tablespace ts_fda01
2 datafile '/app/oracle/oradata/testdb/ts_fda01.dbf' size 5m ;
2.
SQL> create user fbadmin identified by fbpwd
2 default tablespace ts_fda01 ;
3.
SQL> grant resource, connect to fbadmin ;
SQL> grant flashback archive adminster to fbadmin ;
flashback archive adminster 권한
- flashback data archive 를 관리하기 위한 특별한 시스템 권한
- flashback history table 을 생성하고 관리할 수 있음
- 테이블 스페이스를 추가하거나 삭제할 수 있음
- flashback history table retention time 을 관리할 수 있음
4.
SQL> create flashback archive fda01 tablespace ts_fda01
2 retention 30 day ; -- history table에 저장되는 undo data의 보존기간을 의미, 30일 지나면 자동으로 삭제함
SQL> conn fbadmin/fbpwd ;
flashback history table 조회
select owner_name,flashback_archive_name,retention_in_days,status from dba_flashback_archive ;
OWNER_NAME FLASHBACK_ RETENTION_IN_DAYS STATUS
--------------------------------------------------------
FBADMIN FDA01 30
Flashback Database Archive 실습
실습을 하기전 용량이 작은 undo tablespace 를 생성해서 기존에 사용하던 undo tablespace 를 변경하고 시작
SQL> show paramert undo ;
NAME TYPE VALUE
-----------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace undo_fda
2 datafile '/app/oracle/oradata/testdb/undo_fda01.dbf' size 256k ;
SQL> alter system set undo_tablespace=undo_fda ;
SQL> alter system set undo_retention=3 ;
SQL> show parameter undo ;
NAME TYPE VALUE
-----------------------------------------
undo_management string AUTO
undo_retention integer 3
undo_tablespace string UNDO_FDA
SQL> conn scott/tiger
SCOTT> create table test01(no number, name varchar2(10) ;
SCOTT> insert into test01 values(1,'AAA') ;
SCOTT> insert into test01 values(2,'BBB') ;
SCOTT> insert into test01 values(3,'CCC) ;
SCOTT> commit ;
SQL> select * from test01 ;
NO NAME
-----------
1 AAA
2 BBB
3 CCC
SCOTT> update test01 set name='DDD'
SCOTT> select * from test01 ;
NO NAME
-----------
1 DDD
2 DDD
3 DDD
다른 세션에서 로그인해 현재 잘못된 update 를 수행한 트랜잭션이 사용중인 undo segment 를 확인
SCOTT> conn sys/oracle as sysdba ;
SYS> select segment_name,owner,tablespace_name,status from dba_rollback_segs ;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
----------------------------------------------------------------------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU10_2606106477$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9_3975004876$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8_286634474$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7_574775315$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6_3013749843$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5_3147209834$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4_562970181$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3_1483640053$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2_3037561007$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU1_2081786551$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU11_1522516751$ PUBLIC UNDO_FDA ONLINE
SYS> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollname r
3 where s.taddr=t.addr and t.xidusn=r.usn ;
SID SERIAL# USERNAME ROLLBACK SEG
------------------------------------------------------
1 17 SCOTT _SYSSMU11_1522516751$ <-- SCOTT 사용자가 사용하는 Undo segment
SCOTT> commit ;
이제 다른 사용자 (userb) 를 생성한 후 로그인해서 대량의 DML 을 발생시켜 위 undo segment 를 재활용하도록 만듬
SYS> create user userb identified by userb
2 default tablespace example ;
SYS> grant connect, resource to userb ;
SYS> conn userb/userb ;
USERB> create table test02 ( no number , name varchar2(20)) ;
USERB> begin
2 for i in 1..1000 loop
3 insert into test02 values(i,'overwrite!') ;
4 end loop ;
5 end ;
6 /
USERB> select count(*) from test02 ;
COUNT(*)
--------
1000
다른 터미널에서 sys로 로그인해서 undo segment 의사용내역을 조회
