백업&복구 22 - Flashback : Row level

FLASHBACK
- 논리적 장애(delete,update,truncate,drop table, drop user)에 사용
 : 언두에서 가지고 옴
 : 컨트롤파일 참조함
 

 

1. Row Level - Version query
                     - Transaction query 
2. Table Level - DML(update) 장애
                       - Drop : 10g부터 bin$식으로 임시 휴지통(?) 개념의 곳에 옮겨놓는다.→ Flashback사용때 가지고 온다.
3. Database Level - 시간기반 복구와 비슷
                             - 기본적으로 사용안함

1 Row level, 2 Table level  :  기본값 = 사용  함 
3 Database level  :  기본값 = 사용안함  

※ 참고
1. 물리적장애 : 파일깨지거나 디스크장애
                       ex. drop tablespace = 물리적장애
2. 논리적장애 : 파일이 고장 안난것 (오라클 입장에서 에러 아닌것들 - 잘못된 update, drop table 등등)

 
 
 
 
◎ Row Level Flashback 실습

---

 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 시작
실습 1: Row Level Flashback
 
개요

① 테이블 생성
이름   주소 연락처
김유신 경주 111
이순신 전남 222
강감찬 강원 333
 
② update 김유신 → 홍길동
③ update 이순신 → 이율곡
④ 홍길동의 변경전 값 찾아서 원래대로 복구

 
 
 
1. 테이블 생성
SQL> conn scott/tiger
 
SQL> create table 회원
  2  (이름 varchar2(10), 주소 varchar2(10), Tel varchar2(15));
 
SQL> insert into 회원 values ('김유신','경주',111);
SQL> insert into 회원 values ('이순신','전남',222);
SQL> insert into 회원 values ('강감찬','강원',333);
SQL> commit;
 
SQL> select * from 회원;
 
2. 내용 업데이트
SQL> update 회원
  2  set 이름='홍길동' where 이름='김유신';
SQL> commit;
 
SQL> update 회원
  2  set 이름='이율곡' where 이름='이순신';
SQL> commit;
SQL> select * from 회원;
 
 
3. '홍길동' 만 원래값인 '김유신' 으로 돌리기
 
 1) Flashback Version Query (10g부터)
    : 과거 변경 이력 전부 찾아주는 쿼리
    : Cf. 9i = Flashback Query : 특정 시점의 변경 내역만 알수 있음.
 2) Flashback Transaction Query
    : 변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리를 찾아주는 쿼리
 
 
1) Flashback Version Query (10g부터) 
SQL> select versions_startscn st_scn,versions_endscn endscn,
    2   versions_xid txid,versions_operation opt, 이름
    3   from 회원 versions between scn minvalue and maxvalue
   4   where tel=111;
 
SQL> /
 
    ST_SCN     ENDSCN TXID             O 이름
---------- ---------- ---------------- - ----------
    889549            0300070051030000 U 홍길동
    889537     889549 0100070019020000 I 김유신
제일 아래가 오래된것 아래 → 위 순서로 변경내역 확인하기
 
 
여러건의 변경사항이 있다면, 어느데이터로 복구해야 할지 알기 어렵다.
해당 변경사항이 발생한 시간으로 짐작해야 한다. 해당시간은 scn_to_timestamp( ) 함수를 사용하여 추적
 
SQL> select scn_to_timestamp(889537) from dual;
 
SCN_TO_TIMESTAMP(889537)
---------------------------------------------------------------------------
17-FEB-12 05.51.01.000000000 PM
 
 
 
2) Flashback Transaction Query : 10g 에서만... 11g에서 값 안나옴.
 : Flashback_transaction_query 라는 뷰를 select 할수 있는 권한이 있는 사람만 사용
 : scott사용자에게 권한 부여하고 실행
   권한관련에러   ORA-01031: insufficient privileges
 
 
SQL> conn / as sysdba;
SQL> grant select any transaction to scott;
SQL> conn scott/tiger
 
 
SQL> select undo_sql from flashback_transaction_query
  2  where table_name='회원'
  3  and commit_scn between 889537 and 889549
  4  order by start_timestamp desc;
 
 
4. 원하는 값으로 변경시키기
SQL> update "SCOTT"."회원" set "이름" = '김유신' where tel=111;
 
cf.
SQL> update "SCOTT"."회원" set "이름" = '김유신' where ROWID = '위의트랜젝션쿼리에서 값이 나왔다면 참조해서 입력';
 
 
SQL> select * from 회원;
 
이름       주소       TEL
---------- ---------- ---------------
김유신     경주       111
이율곡     전남       222
강감찬     강원       333
 

5. 정리
: 해당 Row 의 커밋된 데이터만 변경시킬 뿐 다른 테이블이나 다른 Row의 데이터는 변경시키지 않는다.
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 끝
 
 

---

 


 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 시작
실습 2: Undo segment 와의 관계 확인
  : Row Level Flashback 기능은 Undo data 의 정보를 이용 - Undo segment를 다른 트랜젝션에서 재사용하면 Flashback 불가
 
개요

목표 : undo segment 를 재 사용하게 만들어서 flashback 기능을 사용해서 데이터 복구가 안 되는 것을 확인하는 것
방법 : 기존 undo tablespace 는 너무 커서 확인이 어렵기 때문에, 미리 크기가 작은 실습용 undo tablespace 를 생성하고 스위칭 한 뒤 실습

 
순서

1. Undo tablespace 를 용량 작은 것으로 만든 후 변경함
2. scott 사용자로 테이블 생성 후 데이터 업데이트 후 커밋 수행
3. B 사용자로 테이블 생성 후 많은 양의 데이터 입력시켜 scott 사용자의 언두 세그먼트를 재사용 시킴
4. scott 사용자의 언두 세그먼트가 B 사용자에게 덮어 써 졌을 때 Flashback 기능 되는지 확인

 
 
 
1. Undo Tablespace 변경
 
SQL> conn / as sysdba;
SQL> create undo tablespace undo-flashback
  2  datafile '/app/oracle/oradata/testdb/undo_flashback.dbf' size 256k;
SQL> alter system set undo_tablespace = undo_flashback;
SQL> alter system set undo_retention=3;
SQL> sho parameter undo;
 
 
2. Ibgo 테이블 생성
 
SCOTT> create table ibgo (i_code number(5), i_name varchar2(10), qty number(5));
SCOTT> insert into ibgo values (100,'새우',100);
SCOTT> commit;
SCOTT> insert into ibgo values (101,'문어',50);
SCOTT> commit;
SCOTT> insert into ibgo values (102,'오징어',20);
SCOTT> commit;
SCOTT> select * from ibgo;
 
 
3. Version query 실행
SCOTT> !vi vq.sql
select versions_startscn st_scn, versions_endscn endscn,
versions_xid txid, versions_peration opt, I_code, I_name, qty
from ibgo versions between scn minvalue and maxvalue
/
 
SCOTT> @vq
 
 
4. Undo segment 사용 내역 확인
SCOTT> conn / as sysdba;
 
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
  4  and t.xidusn=r.usn;
 
no rows selected → 커밋이 되었기 때문에 사용중인 undo segment 내역 안보임
                                커밋 안 된 데이터 있다면 조회 가능.
 
 
SYS> select segment_name, owner, tablespace_name, status from dba_rollback_segs;
 → online 되어있는 _SYSSMUxx$를 commit 직전에 A사용자가 사용했음.
 
 
현 상황에서 A가 작업한 ibgo 테이블의 Flashback Version Query 수행
SYS> conn scott/tiger
 
SCOTT> @vq
조회됨
 
 
 
5. 다른 사용자 생성해서 Undo segment 를 덮어 쓰게 만듬
 : 다른 일반 사용자를 생성 - 대량의 DML을 발생시켜 scott 사용자의 undo segment를 덮어쓰게 만들기
 
SYS> create user userb identified by userb
  2  default tablespace users
  3  temporary tablespace temp;
 
SYS> grant connect, resource to userb;
SYS> con userb/userb;
 
USERB> create table panmae
   2   (p_date date default sysdate,
   3    p_code number(5),
   4    qty number(5));
 
USERB> begin
   2   for i in 1..600 loop
   3     insert into panmae (p_code,qty) values (i,dbms_rando.value(1,100));
   4   end loop;
   5   end;  → 일부러 commit 안함
   6   /
 
USERB> select count(*) from panmae;
600건
 
USERB> insert into panmae elect * from panmae;
600 rows created
 
USERB> /
1200 rows created
 
USERB> /
2400 rows created
 
USERB> /
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_FLASHBACK' ← undo 에러
 : 대량의 DML 작업을 위해 충분한 undo segment 용량이 필요한데, 그렇지 못해서 생긴 에러
   → 위에서 용량이 부족했기 때문에 userb의 트랜잭션이 scott 사용자가 사용했던 undo segment를 덮어 사용했을 것이다.
 
 
USERB> conn / as sysdba;
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;
 
no row selected : undo segment 용량 부족으로 전체 DML 작업이 취소가 되어 내역 조회가 안된다.
 
▶ 현재까지 정리
① scott 사용자가 테이블 생성 후 DML 수행 → undo segment 사용함
② userb 사용자가 대량의 DML 발생시켜 undo tablespace full 에러 발생함
 
 
 
 
6. scott 사용자의 flashback version query 테스트하기
 
SCOTT> @vq
SCN관련 부분의 조회가 안된다.
 : userb 사용자가 대량의 DML을 발생시켜 scott 사용자가 사용했던 _SYSSMUxx$ segment 를 덮어썼기 때문
 
SCOTT> insert into ibgo values (105,'쭈꾸미',50);
SCOTT> @vq
commit 하지 않은 상태에서 version query 를 조회했기때문에 내용이 나오지 않는다.
 
SCOTT> @vq
마지막 insert한 자료의 내용 조회됨
 
 
 
7. 정리
 : Flashback version query는 undo tablespace 의 정보를 사용하기 때문에
   undo segment를 누군가가 덮어 써 버리면 정보를 가지고 올수 없다.
   즉, commit 이 발생한 지 오래지난 데이터나, 트랜잭션이 많이 일어나는 경우엔 이 방법으로 복구가 불가능 할 가능성이 높음을 의미한다.
   (undo segment를 다른 사용자가 재사용할 확률이 높으므로)
 → 11g 부터는 Flashback Data Archive 라는 기능이 구현된다.
    : undo segment 정보를 특정 위치로 archive 해서 시간이 오래 지난후에도 조회하고 변경할수 있게 한 기능
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 끝