백업&복구 23 - Flashback : Table Level

Table Level Flashback

○ 두가지 경우에 사용 
  - DML → undo data 이용
  - Drop → Recyclebin (휴치통기능) 이용
 

휴지통 공간이 따로 있지 않음.
drop하면 해당 자리를 지우는것이 아니라 BIN$... 로 바꿔놓는다.
데이터파일 용량이 꽉 차면 BIN$..도 덮어쓰기 때문에, 너무 믿으면 안된다.
autoexetend on 옵션을 줘도, 기존의 내용을 다 쓰고나면 늘어난다
 → 덮어쓰고 나서 용량없으면 늘어나는 것이기 때문에 소용없음
 
 

---

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습3 시작
실습 3: SCN으로 DML 에러 복구하기 - undo data 사용
 : 장애 발생전의 특정 SCN을 확인 한 후 해당 SCN을 사용하여 복구
 : row movement 라는 속성 사용 (주의사항은 실습4 참조)
 
 
SCOTT> conn / as sysdba;
 
SYS> grant dba to scott; → scott 이 scn을 확인하기 위해 권한필요함
                            ※ SYS 소유의 테이블은 Flashback 안먹힌다.
SYS> conn scott/tiger
 
SCOTT> select current_scn from v$database;
SCOTT> select * from ibgo;
 
SCOTT> update ibgo set i_name='왕새우' where i_name='새우';
SCOTT> commit;
SCOTT> select * from ibgo;
SCOTT> select current_scn from v$database;
새우를 왕새우로 변경 한 후 SCN
 
SCOTT> update ibgo set i_name='대왕문어' where i_name='문어';
SCOTT> commit;
SCOTT> select current_scn from v$database;
문어를 대왕문어로 변경 한 후 SCN
 
SCOTT> delete from ibgo;
SCOTT> commit;
SCOTT> select current_scn from v$database;
모든 데이터 삭제후 SCN
 
SCOTT> select * from ibgo;
현재 데이터 한 건도 없음
 
사용자가 100번 새우가 있던 시절로 테이블을 복구해 달라고 요청
→ Flashback기능이 안된다면 시간기반 불완전 복구를 이용해서 복구해야 하지만 Flashback 기능으로 간단히 복구가능
 
SCOTT> flashback table ibgo to scn '새우가 있던 시점의 SCN';
에러
ORA-08189: cannot flashback the table because row movement is not enabled
 
이 기능을 사용하려면 테이블에 row movement 라는 속성이 enable 되어있어야 한다.
 
SCOTT> alter table ibgo enable row movement;
SCOTT> flashback table ibgo to scn '새우가 있던 시점의 SCN';
SCOTT> select * from ibgo;
복구완료
 
같은 방법으로 문어가 대왕문어인 시점으로 복구해 달라고 다시 요청했다면 해당시점의 SCN으로 flashback 하면 됨
 
SCOTT> flashback table ibgo to scn '대왕문어가 있던 시점의 SCN';
SCOTT> select * from ibgo;
복구완료
 
 
실제적으로 개발자가 개발하면서 SCN 적어놓지는 않는다.
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습3 끝
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습4 시작
실습 4: Row movement 사용시 주의사항
 : 특정 테이블의 row movement 속성을 enable 할 경우
   그 테이블과 연관된 다른 기능들이 사용할 수 없게 되는 (즉 invalid 상태가 되는) 경우발생
   ex. 인덱스, 제약조건 등..
 

1. 현재 상태 확인
SQL> set line 200
SQL> col object_name for a10
SQL> col object_type for a10
SQL> col status for a8
 
SQL> select object_name, object_type, status
  2  from dba_objects
  3  where owner='SCOTT'
  4  and object_name like 'IBGO%';
  
  
2. Ibgo table 에 index 와 view 를 생성
 
SQL> create index ibgo_iname_idx on ibgo (i_name);
 
SQL> create view ibgo_v
  2  as select i_name, qty
  3     from ibgo;
 
SQL> col objet_name for a15
SQL> select object_name, object_type, status
  2  from dba_objects
  3  where owner='SCOTT'
  4  and object_name like 'IBGO%';
  
 모든 object 의 상태가 valid 인 것이 확인. 이 상태에서 row movement 속성을 변경한 후 다시 조회해보기
 
SQL> alter table ibgo disable row movement;
 
SQL> select object_name, object_type, status
  2  from dba_objects
  3  where owner='SCOTT'
  4  and object_name like 'IBGO%';
  
3. 정리
 : 특정 테이블의 row movement 속성을 변경할 경우, 연관되어 있는 view가 사용할 수 없는 상태로 변경되기 때문에 해당 뷰를 다시 생성해야 한다.
   Flashback table을 사용하기 위해 row movement 를 enable로 변경했다면, 반드시 해당 테이블과 연관되어 있는 view를 확인해야 한다.
   11g 부터는 이 기능이 개선되어 모두 valid 상태.
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습4 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습5 시작
실습 5: 시간으로 DML 에러 복구하기 - undo data 사용
 : 일반적으로 해당 테이블의 SCN을 모를 경우가 많다.

방법 두가지
① Flashback Version Query 를 이용해서 SCN을 찾아내서 작업
② 시간으로 추측해서 복구하기
 
 
1. 잘못된 delete
SCOTT> select * from ibgo;
SCOTT> update ibgo set i_name='대왕오징어' where i_code=102;
SCOTT> commit;
SCOTT> select * from ibgo;
 
SCOTT> update ibgo set i_name='대왕쭈꾸미' where i_code=105;
SCOTT> commit;
SCOTT> select * from ibgo;
 
SCOTT> delete from ibgo where i_code=105;
SCOTT> commit;
SCOTT> select * from ibgo;
 
 
2. 대왕쭈꾸미가 지워지기 전으로 복구
 
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
 

※ 참고 
systimestamp = 현재 쿼리 날리는 시간
interval 뒤의 시간은 분, 초 모두 가능 (초의 경우 second)
ex. 30초 이전> flashback table ibgo to timestamp (systimestamp - interval '30' second);

 
 
 
SCOTT> select * from ibgo;
너무 이전으로 돌아갔음
 
다시시도
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '4' minute);
SCOTT> select * from ibgo;
삭제 후 시점으로 돌아갔음
 
 
다시시도
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
SCOTT> select * from ibgo;
너무 이전
 
 
다시시도
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
SCOTT> select * from ibgo;
복구완료
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습5 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습6 시작
실습 6: Flshback 으로 복구가 안되는 경우
 : Flashback은 undo data를 기반으로 복구가 되는 원리이다.
   undo segment 에 해당 내역이 있지 않게 되면 복구를 할 수 없게 된다.
 
 
 
6-1: 복구 시간을 너무 빠르게 설정해서 undo segment 내역을 못 찾는 경우
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '70' minute);
에러
ORA-00604: error occurred at recursive SQL Level 1
ORA-12801: error signaled in parallel query server P000
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
 
너무 오래 전 시간으로 돌리니까 undo 에러가 발생한다.
 
 
 
6-2: Table 구조가 변경 된 후 undo segment 내역을 못 찾는 경우
SCOTT> alter table ibgo add (price number);
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '10' minute);
SCOTT> select * from ibgo;
 
 
SCOTT> update ibgo set i_name='흰수염고래'; ← 잘못된 update 수행
SCOTT> commit;
SCOTT> select * from ibgo;
 
SCOTT> alter table ibgo drop column price;
테이블에서 price 칼럼을 삭제하여, 테이블 구조 변경
 
 
테이블 구조 변경 후 flashback 시도해보기
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '2' minute);
에러
ORA-01466: unable to read data - table definition has changed
 : 테이블 구조 변경으로 Flashback 할 수 없다는 메시지가 나옴.
 
잠시 기다렸다가 다시 시도
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '2' minute);
flashback은 수행되었지만 데이터가 돌아오지 않음
 
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '6' minute);
에러
ORA-01466: unable to read data - table definition has changed
 
 
SCOTT> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
flashback은 수행되었지만 데이터가 돌아오지 않음
 
▶ 정리 
시간에 따라 Flashback이 수행 될수는 있지만, 데이터는 돌아오지 않는다.
 → 다른 사용자가 undo segment를 덮어 쓴다든지 테이블 구조가 변경이 되는 상황이 되면 복구 할수 없다.
     =  undo data가 없으면 복구가 되지 않는다. 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습6 끝
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습7 시작
실습 7: Drop table 복구하기 - 휴지통 기술 이용
 : drop table 하게 되면 휴지통으로 들어가서 보관되다가 flashback으로 간단히 복구 된다.
 : 실제 휴지통의 공간이 따로 할당 되어 있는것은 아니다.
   ex) 따로 공간이 있어서 Drop할때 그곳으로 데이터가 옮겨간다면 10건짜리 테이블과 1000건짜리 테이블 drop 시간은 다를 것이다.
 
 
 
7-1: drop table 복구하기
 
1) 현재 사용자의 테이블 상황 확인하기
 
SCOTT> select * from tab;
 
SCOTT> drop table ibgo;
SCOTT> select * from tab;
BIN$....... 로 표시됨 ← ibgo 테이블이 삭제되면서 휴지통으로 옮겨진 것
 
 
2) 휴지통 확인하기 : 사용자 별로 따로 있다.
 
SCOTT> show recyclebin
10g 부터 휴지통 기능이 드장해서 테이블을 지웩 되면 휴지통으로 옮겨지게 된다.
 
 
3) Ibgo 테이블 복구하기
SCOTT> flashback table ibgo to before drop;
 
SCOTT> select * from tab;
복구완료
SCOTT> show recyclebin
 
 
 
 
7-2: 오라클 EM을 이용하여 휴지통 공간이 따로 할당 되어 있는가 확인해보기
 
사용자가 테이블을 지우게 되면 오라클은 테이블 이름을 BIN$....로 시작하는 이름으로 변경하고 점유고 있던 공간을 할당해제한다.
실제로 데이터를 지우는 것은 아니고, 보관하고 있으면서 flashack table to before drop 란 명령어가 수행되면 복구를 시켜준다.
이 상황에서 다른 테이블이 먼저 지워졌던 테이블이 있는 공간을 사용할 상황이 되면, 그때가 되서야 데이터를 지우고 신규테이블에게 공간을 할당해 준다.
 
즉, 휴지통의 전용 공간이 있어서 데이터가 영원히 저장되는 것이 아니라, 특정 시간이 지나 삭제된 테이블이 차지하던 공간이 다른 테이블에 의해 재사용되는 시점이 되면 더이상 복구할수 없게 된다는 의미이다.
 
 
 

※ 휴지통을 비우려면?
purge 명령사용
 
SCOTT> show recyclebin;
SCOTT> purge table test1;  → test1 테이블만 휴지통에서 삭제
 
SCOTT> purge recyclebin;  → 휴지통 전체 비우기
 
SCOTT> drop table 회원 purge;  → 휴지통에 넣지않고 완전히 삭제
 
 
sys 사용자의 table은 휴지통으로 들어가지 않고 purge됨
사용자 소유의 테이블 삭제시 해당 사용자의 user_recyclebin에 지워진 테이블이 저장됨

 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습7 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습8 시작
실습 8: 테이블 drop후 다른 object 확인하기
 : Flashback 으로 테이블을 삭제하게 되면, 해당 테이블 과 관련된 모든 object들도 함께 삭제 된다.
 
개요
과자 테이블에 constraint 와 index, view 를 생성한 후 drop table해서 다른 object들이 어떻게 되는지와 flashback으로 과자 테이블을 복구한 후 다른 object들이 어떻게 되는지 보기
 
 
1. 예제 테이블과 관련 object 생성
 
SCOTT> create table 과자 (no number, name varchar2(20), price number(10));
SCOTT> insert into 과자 values (1,'새우짱',500);
SCOTT> insert into 과자 values (1,'감자짱',500);
SCOTT> insert into 과자 values (1,'맛큰산',500);
SCOTT> commit;
 
SCOTT> select * from 과자;
 
 
SCOTT> create index 과자_name_idx on 과자(name);
SCOTT> create view 과자_v
   2   as select name,price
   3   from 과자;
 
SQL> alter table 과자 modify name constraint 과자_name_nn not null;
 
SQL> col object_name for a15
SQL> col object_type for a8
SQL> col status for a10
SQL> select object_name, object_type, status from dba_objects
  2  where object_name like '과자%';
SQL> save stats.sql
 
 
SQL> col table_name for a6
SQL> col constraint_name for a15
SQL> col column_name for a10
SQL> select table_name, constraint_name, column_name
  2  from user_cons_columns
  3  wehre table_name='과자';
SQL> save cons.sql
 
 
 
2. 과자 테이블을 drop 하고 다른 object 의 상태 확인
 
SCOTT> select * from tab;
SCOTT> drop table 과자;
SCOTT> show recyclebin;
 
- object 상태 조회.
SCOT> @stats
테이블은 지워져서 안보이고 view는 invalid 상태
 
- 제약조건 상태 조회
SCOTT> @cons
테이블이 삭제되어 제약조건 안보임
 
- index 상태 조회
SCOTT> col table_name for a10
col index_name for a15
col column_name for a10
SCOTT> select table_name, index_name, column_name
  2  from user_ind_columns
  3  where table_name='과자'
SCOTT> save index.sql
인덱스 역시 삭제되어 조회되지 않음
 
 
 
3. Flashback으로 삭제된 테이블 복구한 후 다른 object 상태 조회
 
SCOTT> flashback table 과자 to before drop;
SCOTT> select * from tab;
 
- object 상태 조회.
SCOT> @stats
여전히 사용불가 상태. 다시 생성해 줘야 한다.
 
- 제약조건 상태 조회
SCOTT> @cons
제약조건 이름 원위치 되지 않음.
과자테이블의 name 컬럼에 not null 조건이었음
정상작동여부 확인
SCOTT> insert into 과자 values (4,null,600);
에러
ORA-01400: cannot insert NULL into ("SCOTT"."과자"."NAME")
정상작동함.
▶ 즉, Flashback으로 테이블을 복원 할 경우 제약조건은 동작하지만 이름이 원상복구 되지는 않음.
 
제약조건 이름 원래대로 변경시키기
SCOTT> alter table 과자 rename constraint "BIN$....." to "과자_name_nn";
SCOTT> @cons
정상적으로 변경 완료
 
 
- index 상태 조회
SCOTT> @index
제약조건과 마찬가지로 테이블은 정상적으로 복구되지만, 이름은 휴지통에 있던 그대로 이다.
 
인덱스 이름 원래대로 변경시키기
SCOTT> alter index "BIN$...." rename to 과자_name_idx;
SCOTT> @index
 
 
4. 결론
 : Flashback으로 drop 된 테이블을 복구해도 관련되어 있던 다른 object들은 완전히 복구가 되지 않는다.
   (내용은 복구되지만,이름이 휴지통에 있던 그대로...)
   수동으로 작업을 해 주어야 한다.
 
 
   =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습8 끝
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-  실습9 시작
실습 9: 같은 이름의 table일 경우 복구 우선 순위
 
?? 동일한 이름의 테이블이 휴지통에 있을경우
1. 같은이름 테이블 생성 (구분되게 컬럼 명 다르게)
SCOTT> create table test1 (no1 nuber);
SCOTT> insert into test1 values (1);
SCOTT> commit;
SCOTT> drop table test1;
 
SCOTT> create table test1 (no2 nuber);
SCOTT> insert into test1 values (2);
SCOTT> commit;
SCOTT> drop table test1;
 
2. 휴지통 조회 및 복구 시도
SCOTT> show recyclebin;
휴지통에 test1테이블이 2개 있다.
 
SCOTT> flashback table test1 to before drop;
SCOTT> select * from test1;
!! 나중에 지워진 test1 테이블이 복구된다.
 
 
 
?? 복구하려고 할때 같은 이름의 테이블이 이미 존재할 경우
SCOTT> select * from tab;
이미 test1 테이블이 있는 상태에서 휴지통 복원
 
SCOTT> show recyclebin;
SCOTT> flashback table test1 to before drop;
에러
ORA-38312: original name is used by an existing object
에러나고 복구안됨
 
 
!! 복구하려면 이름 바꾸어서 복구
SCOTT> flashback table test1 to before drop rename to test3;
SCOTT> select * from test3;
test1 테이블이 이름 바뀌어서 복구 완료
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습9 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습10 시작
실습 10: Index만 삭제했을 때 flashback으로 index 복구하기 (결론은 복구안됨)
 
SQL> @index
SQL> show recyclebin;
SQL> drop index 과자_name_idx;
SQL> @index
drop 되었음
 
SQL> show recyclebin;
SQL> flashback table 과자 to timestamp(systimestamp - interval '2' minute);
에러
ORA-08189: cannot flashback the table because row movement is not enabled
 
SQL> alter table 과자 enable row movement;
SQL> flashback table 과자 to timestamp(systimestamp - interval '2' minute);
 
SQL> @index
없음
인덱스만 삭제 되었을 경우에는 복구가 되지 않는다.
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습10 끝