백업 복구2 - p68
Flashback Query의 3가지 분류
1. Row level Flashback
2. Table Level Flashback
3. Database Level Flashback
1. Row Level Flashback (기본값 : 사용함)
※ Table의 특정 부분만 찾아서 변경하는 방법
※ 이 기능은 Undo Data의 정보를 이용한다. 만약 Undo Segment를 다른 Transaction에서 재사용하면 이 기능으로 Flashback 할 수 없다.
※ table의 구조가 변경되어도 Flashback 할 수 없다.
Flashback Version Query (해당 데이터의 과거 변경 이력을 전부 찾아주는 쿼리)
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;
ST_SCN ENDSCN TXID O 이름
---------- ---------- ---------------- - ----------
1654659 06001500F9030000 U 홍길동
1654625 1654659 09001D0005040000 I 김유신
SCN을 시간으로 (Version Query를 시간으로 변경하여 보여주는 기능)
SCOTT> select scn_to_timestamp(1654625) from dual;
SCN_TO_TIMESTAMP(1654625)
--------------------------------
06-JUL-13 03.28.07.000000000 PM
Flashback Transaction Query (원래 데이터로 돌려주게 하는 기능, 업데이트 쿼리를 가르쳐 주는 기능)
이 기능은 본인이 UPDATE로 원래 데이터로 돌릴 수 있다면 필요성이 낮은 기능
1 select undo_sql from flashback_transaction_query
2 where table_name='회원'
3 and commit_scn between 611043 and 611071
4 order by start_timestamp desc
select undo_sql from flashback_transaction_query
*
ERROR at line 1:
ORA-01031: insufficient privileges -------------------- 권한 에러가 발생한다
SCOTT> conn / as sysdba
Connected.
SYS> grant select any transaction to scott;
Grant succeeded.
SYS> conn scott/tiger
Connected. 권한을 부여한다
1 select undo_sql from flashback_transaction_query
2 where table_name='회원'
3 and commit_scn between 1654625 and 1654659 -> 위에서 조회한 SCN 번호
4 order by start_timestamp desc
이렇게 치면 업데이트를 시키게 해 주는 쿼리가 나오는데 복사해서 실행하면 된다.
나는 왜 안나오지... 안나와서 그냥 넘어간다.
2. Table Level Flashback 백업과 복구2 p82 (기본값 : 사용함)
DML 에러가 발생했을 때 특정 테이블 전체를 되돌리는 Flashback 방법
※ Supplimental log 기능이 enable 되어 있어야 사용하능하다
※ 이 기능은 Undo Data의 정보를 이용한다. 만약 Undo Segment를 다른 Transaction에서 재사용하면 이 기능으로 Flashback 할 수 없다.
※ table의 구조가 변경되어도 Flashback 할 수 없다.
※ 테이블 전체의 내용이 변경 된다.
① 특정 테이블에 DML 에러가 발생했을 때 사용하는 방법 - Undo data 사용
② 특정 테이블이 drop table 되었을 때 사용하는 방법 - Recyclebin 사용
① 특정 테이블에 DML 에러가 발생했을 때 사용하는 방법
▼ 복구시점의 SCN을 알고 있을 경우 복구 방법
SCOTT> select * from ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 새우 100
101 문어 50
102 오징어 20
105 쭈꾸미 50
SCOTT> conn / as sysdba scott이 SCN을 확인하기 위해 권한을 부여함
Connected.
SYS> grant dba to scott;
Grant succeeded.
SCOTT> select current_scn from v$database; 현재 SCN 조회하기
CURRENT_SCN
-----------
1674124
SCOTT> update ibgo set i_name='왕새우' where i_name='새우';
1 row updated.
SCOTT> commit;
Commit complete.
SCOTT> select * from ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 왕새우 100
101 문어 50
102 오징어 20
105 쭈꾸미 50
SCOTT> select current_scn from v$database;
CURRENT_SCN
-----------
1674255 새우를 왕새우로 변경한 후 SCN
SCOTT> update ibgo set i_name='대왕문어' where i_name='문어';
1 row updated.
SCOTT> commit;
Commit complete.
SCOTT> select current_scn from v$database;
CURRENT_SCN
-----------
1674279 문어를 대왕문어로 변경한 후 SCN
SCOTT> delete from ibgo;
4 rows deleted.
SCOTT> commit;
Commit complete.
SCOTT> select current_scn from v$database;
CURRENT_SCN
-----------
1674306 모든 데이터를 삭제한 후 SCN
SCOTT> flashback table ibgo to scn '1674124'; 새우가 있던 table로 flashback
flashback table ibgo to scn '1674124'
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
(이 기능을 사용하려면 테이블에 row movement라는 속성이 enable 되어 있어야 한다)
SCOTT> alter table ibgo enable row movement;
Table altered.
SCOTT> flashback table ibgo to scn '1674124';
Flashback complete.
SCOTT> select * from ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 새우 100
101 문어 50
102 오징어 20
105 쭈꾸미 50
복구시점의 SCN만 알면 쉽게 복구가 가능하지만, SCN을 알고 있는 경우가 많이 없기 때문에 별로 쓰이지는 않는 내용이었습니다.
row movement
어떤 테이블에서 row movement를 enable 하면 그 테이블과 연관된 View 기능을 사용할 수 없게 되는 경우가 발생한다
Flashback table을 사용하기 위해 row movement를 enable로 변경하였다면 반드시 해당 테이블과 연관된 view를 확인해야 한다 (10g 까지만, 11g는 예외)
명령어 : alter table ibgo enable row movement
SCOTT> set line 200;
SCOTT> col object_name for a10
SCOTT> col object_type for a10
SCOTT> col status for a8
SCOTT> select object_name,object_type,status from dba_objects where owner='SCOTT' and object_name like 'IBGO%';
OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- --------
IBGO TABLE VALID 현재 상태조회
SCOTT> create index ibgo_iname_idx on ibgo (i_name);
Index created.
SCOTT> create view ibgo_v as select i_name,qty from ibgo;
View created.
(ibgo table에 index와 view생성)
SCOTT> col object_name for a15
SCOTT> select object_name,object_type,status from dba_objects where owner='SCOTT' and object_name like 'IBGO%';
OBJECT_NAME OBJECT_TYP STATUS
--------------- ---------- --------
IBGO TABLE VALID
IBGO_INAME_IDX INDEX VALID
IBGO_V VIEW VALID 현재 상태 조회
SCOTT> alter table ibgo disable row movement;
Table altered.
SCOTT> select object_name,object_type,status from dba_objects where owner='SCOTT' and object_name like 'IBGO%';
OBJECT_NAME OBJECT_TYP STATUS
--------------- ---------- --------
IBGO TABLE VALID
IBGO_INAME_IDX INDEX VALID
IBGO_V VIEW INVALID
(10g 까지만 이렇게 되니 11g는 걱정않고 사용해도 됩니다)
▼ SCN은 모르지만 시간을 알고 있을 때 복구하는 방법 (백업과 복구2 p.88)
장애가 난 시간을 추측하여 Flashback 하는 방법
Flashback Version Query를 사용하여 변경사항을 조회해서 찾을 수 있기도 하지만
시간으로 간단히 해결해보자
SCOTT> flashback table ibgo to timestamp(systimestamp - interval '5' minute);
Flashback complete.
(지금으로부터 5분전으로 되돌리기)
SCOTT> flashback table ibgo to timestamp(systimestamp - interval '30' second);
Flashback complete.
(지금으로부터 30초전으로 되돌리기)
원하는 데이터를 찾을 때까지 반복할 수 있으나 이 방법도 Undo data를 이용하기 때문에 오래 전 데이터는 Flashback 할 수 없을 가능성도 많다
② 특정 테이블이 drop table 되었을 때 사용하는 방법 - 휴지통(Recyclebin) 사용
10g 부터 휴지통 기능이 생겼다
※ 테이블이 삭제되어 휴지통으로 옮겨지게되면 해당 테이블스페이스에 그대로 용량을 차지하고 남아있게 되지만, 해당 테이블스페이스의 용량이 꽉 차고 다른 데이터가 insert 되면 휴지통에 해당하는 부분에 덮어 쓰게 되어 그 이후로 사라지게 된다. 이 점은 Autoextend 기능이 on 으로 되어 있어도 같다.
※ sys사용자의 table은 삭제해도 휴지통으로 들어가지 않고 purge 된다. But sys사용자가 다른 사용자의 테이블을 삭제하면 해당 사용자의 user_recyclebin에 지워진 테이블이 저장된다.
▼ drop table 복구하기
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ASSM TABLE
BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE
BONUS TABLE
CLOBTEST TABLE
DEPT TABLE
DEPT2 TABLE
DEPT20 TABLE
EMP TABLE
GOGAK TABLE
IBGO TABLE
IBGO_V VIEW
JOB_TEST01 TABLE
RTEST01 TABLE
RTEST02 TABLE
RTEST2 TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
T1 TABLE
TEST02 TABLE
TEST3 TABLE
TEST4 TABLE
TT100 TABLE
UTEST01 TABLE
회원 TABLE
24 rows selected. 현재 사용자의 테이블 상황 확인
SCOTT> drop table ibgo;
Table dropped.
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ASSM TABLE
BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE
BIN$4NYp0Gt+2kngQAB/AQA5BA==$0 TABLE ibgo 테이블이 휴지통으로 옮겨짐
BONUS TABLE
CLOBTEST TABLE
DEPT TABLE
DEPT2 TABLE
DEPT20 TABLE
EMP TABLE
GOGAK TABLE
IBGO_V VIEW
JOB_TEST01 TABLE
RTEST01 TABLE
RTEST02 TABLE
RTEST2 TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
T1 TABLE
TEST02 TABLE
TEST3 TABLE
TEST4 TABLE
TT100 TABLE
UTEST01 TABLE
회원 TABLE
24 rows selected.
SCOTT> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
IBGO BIN$4NYp0Gt+2kngQAB/AQA5BA==$0 TABLE 2013-07-06:19:20:41
TEST3 BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE 2013-07-06:09:05:07
휴지통 확인
SCOTT> flashback table ibgo to before drop - 테이블 복구
Flashback complete.
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ASSM TABLE
BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE
BONUS TABLE
CLOBTEST TABLE
DEPT TABLE
DEPT2 TABLE
DEPT20 TABLE
EMP TABLE
GOGAK TABLE
IBGO TABLE
IBGO_V VIEW
JOB_TEST01 TABLE
RTEST01 TABLE
RTEST02 TABLE
RTEST2 TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
T1 TABLE
TEST02 TABLE
TEST3 TABLE
TEST4 TABLE
TT100 TABLE
UTEST01 TABLE
회원 TABLE
24 rows selected.
SCOTT> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE 2013-07-06:09:05:07
ibgo 테이블이 복구되었고 휴지통에서 사라졌다
휴지통 비우는 방법
SCOTT> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST3 BIN$4MyQZhMJCajgQAB/AQAnRA==$0 TABLE 2013-07-06:09:05:07
SCOTT> purge table test3; test3 테이블만 휴지통에서 삭제
Table purged.
SCOTT> show recyclebin;
아무것도 없다.
SCOTT> purge recyclebin; 휴지통 전체를 비우는 방법
Recyclebin Purged.
SCOTT> drop table 회원 purge; 휴지통에 넣지 않고 완전히 삭제하는 방법
Table dropped.
