오라클/백업/복구

Flashback

김포춘 2014. 11. 12. 18:21
반응형

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 의사용내역을 조회
























반응형

'오라클 > 백업/복구' 카테고리의 다른 글

RMAN+Clone DB 사용해서 복구  (0) 2014.11.17
RMAN 복구하기  (0) 2014.11.14
RMAN  (0) 2014.11.13
Clone DB  (0) 2014.11.10
sql loader  (0) 2014.11.10
데이터 이동하기  (0) 2014.11.06
Redo log flle 장애 처리방법  (0) 2014.11.05