http://storing.tistory.com/56

 

'DataBase > SQL' 카테고리의 다른 글

flashback  (0) 2016.12.09

http://www.jigi.net/4247 참고

 

 

 

1. Flashback
  가. 개요 : 사용자의 논리적인 장애(DDL, DML)를 빠르게 복구해내는 방법, undo segment 사용
  나. 종류
    - Row Level Flashback : 특정 row만 과거시점으로 되돌리는 기능, commit된 데이터만 flashback 할 수 있음
    - Table Level Flashback : 특정 table만 과거시점으로 되될리는 기능
    - Database Level Falshback : 데이터베이스 전체를 특정 시점으로 되돌리는 기능, 불완전 복구와 유사한 기능

2. Row Level Flashback 예제

1
2
3
4
5
6
7
8
9
10
select versions_startscn startscn, verstions_endscn endscn,
versions_xid, versions_operation operation, 컬럼명
from 테이블명 versions between scn minvalue and maxvalue
where 조건내용;
 
select undo_sql
from flashback_transaction_query
where talbe_name='테이블이름'
and commit_scn between 시작scn and 종료scn
order by start_timestamp desc;



3. Table Level Flashback 예제
  가. scn 번호로 flashback

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
conn / as sysdba
 
grant dba to scott;
 
conn scott/tiger
 
create table test01 (no number);
 
insert into test01 values (1);
 
commit;
 
insert into test01 values (2);
 
commit;
 
insert into test01 values (3);
 
commit;
 
select * from test01;
 
-- 현재 scn 메모
select current_scn from v$database;
 
-- 잘못된 업데이트 문장 수행
update test01 set no=10;
 
commit;
 
select * from test01;
 
-- 앞서 scn 메모한 곳으로 flashback
flashback table test01 to scn 'scn번호';
 
alter table test01 enable row movement;
 
flashback table test01 to scn 'scn번호';
 
select * from test01;



  나. timestamp로 flashback

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
conn scott/tiger
 
create table test02 (no number(3));
 
insert into test02 values (1);
 
commit;
 
insert into test02 values (2);
 
commit;
 
insert into test02 values (3);
 
commit;
 
select * from test02;
 
-- 잘못된 업데이트 문장 수행
update test02 set no=10;
 
commit;
 
select * from test02;
 
-- 5분전으로 flashback
flashback table test02 to timestamp (systimestamp - interval '5' minute);
 
alter table test02 enable row movement;
 
-- 테이블이 생성되기 이전시점이라서 오류발생
flashback table test02 to timestamp (systimestamp - interval '5' minute);
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
 
-- 1분전으로 flashback
flashback table test02 to timestamp (systimestamp - interval '1' minute);
 
-- 원하는 데이터가 아님
select * from test02;
 
        NO
----------
        10
        10
        10
 
-- 200초 이전으로 되돌아감
flashback table test02 to timestamp (systimestamp - interval '200' second);
 
-- 원하는 데이터 발견
select * from test02;
 
        NO
----------
         1
         2
         3



  다. drop 된 테이블 복구

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
conn scott/tiger
 
-- 테이블 생성
create table test01 (no number);
 
desc test01
 
-- 테이블 삭제
drop table test01;
 
select * from test01;
 
-- 테이블 복구
flashback table test01 to before drop;
 
select * from test01;
 
-- 테이블 완전 삭제
drop table test01 purge;
 
-- 테이블 생성
create table test02 (no number);
 
-- 테이블 삭제
drop table test02;
 
-- 휴지통 확인
 show recyclebin;
 
-- 테이블 확인(BIN$ 로 시작하는 테이블 존재 확인가능)
select * from tab;
 
-- 휴지통에서 원하는 테이블만 삭제
purge table test02;
 
-- 휴지통에서 삭제됐는지 확인
show recyclebin
 
-- 휴지통에 있는 모든 테이블 삭제
purge recyclebin;
 
-- 휴지통 확인
show recyclebin
 
-- 현재 세션에서 휴지통 기능 비활성화
alter session set recyclebin=off;

 

참고 : 휴지통(recyclebin)은 사용자마다 할당되며, 다른사용자의 휴지통은 접근 할 수 없다.


  라. foreign key 제약조건(consraint)으로 묶인 테이블의 삭제 및 복구
     foreign key로 묶인 테이블을 drop 후 flashback 하게 되면, 수동으로 다시 제약조건을 생성해 줘야 한다.


4. Database Level Flashback 예제
  가. 필요시점
    - truncate table 장애 복구 시
    - 특정 시점으로 전체 데이터베이스 되돌릴 때
  나. 전통방식의 백업/복구와의 비교
    - 전통방식에 비해 복구 속도가 빠름(datafile을 restore 하는 과정이 없음)
    - 전통방식이 백업데이터, archivelog, redolog 파일을 이용하지만, flashback는 flashback log 사용
    - 전통방식의 복구의 경우 특정시점으로 복구하였으나 원하는 결과가 나타나지 않아 다른시점으로 변경해야 하는 경우 모든 작업을 처음부터 다시해줘야 했으나, flashback 방식은 언제라도 원하는 시점으로 되돌아 갈 수 있음  
  다. 사전 환경설정
    - parameter 파일에 db_flashback_retention_target 설정
    - mount 단계에서 flashback on 설정

$ vi $ORACLE_HOME/dbs/inittest.ora

db_flashback_retention_target=30

 

1
2
3
4
5
6
7
8
9
startup mount
 
alter database archivelog;
 
alter database flashback on;
 
select flashback_on from v$database;
 
alter database open;



  라. truncate table 된 데이터 복구

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
conn scott/tiger
 
create table test03 (no number);
 
insert into test03 values (1);
 
commit;
 
insert into test03 values (2);
 
commit;
 
insert into test03 values (3);
 
commit;
 
select * from test03;
 
-- 잘못된 truncate 명령어 발생
truncate table test03;
 
select * from test03;
 
-- 복구시작
conn /as sysdba
 
shutdown immediate
 
startup mount
 
flashback database to timestamp (systimestamp - interval '5' minute);
 
alter database open;
 
alter database open resetlogs;
 
select * from scott.test03;
 
-- 원하는 데이터가 없어 다른 시점으로 재시도
shutdown immediate;
 
startup mount
 
flashback database to timestamp (systimestamp - interval '10' minute);
 
alter database open resetlogs;
 
select * from scott.test03;



  마. 업데이트를 통한 복구

1
2
3
4
5
6
7
8
9
10
11
-- 업데이트 전 시간을 지정하여 변경 전 데이터를 확인
SELECT *
  FROM TABLE_NAME
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2015-02-25 16:10:00', 'YYYY-MM-DD HH24:MI:SS')
                              AND TO_TIMESTAMP('2015-02-25 16:11:00', 'YYYY-MM-DD HH24:MI:SS')
 WHERE ID = 'test';
 
-- 변경 전 데이터로 업데이트
UPDATE TABLE_NAME
      SET COL1= '변경전 데이터'
 WHERE ID = 'test';

 

참고 :Flashback Data Archive
11g의 새로운 기능으로 Undo segment의 commit 데이터를 특정 테이블스페이스에 archive한다. 10g이하 버전에서는 다른사용자에 의해 undo segment가 덮어 쓰여지면 flashback 할 수 없는 상황이 발생하였으나, 11g에서는 이 기능을 통해 undo segment가 덮어 쓰여지기전 해당 undo segment를 별도의 파일에 archive 함으로써, 복구를 원하는 시점으로 데이터를 flashback 할 수 있게 되었다.


5. 추가정보

The following script creates a new tablespace, then creates two flashback data archives using the CREATE FLASHBACK ARCHIVE command. The first is limited in size to 10Gig with a retention period of 1 year, while the second has an unlimited quota and a retention period of 2 years.

CREATE TABLESPACE fda_ts   DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf'   SIZE 1M AUTOEXTEND ON NEXT 1M;  CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts   QUOTA 10G RETENTION 1 YEAR;  CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts   RETENTION 2 YEAR;

Management of flashback archives falls into three distinct categories.

  • Tablespace management. 
    -- Set as default FBA ALTER FLASHBACK ARCHIVE fba_name SET DEFAULT;  -- Add up to 10G of the specified tablespace to the specified flashback archive. ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name QUOTA 10G;  -- Add an unlimited quota of the specified tablespace to the specified flashback archive. ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name;  -- Change the tablespace quota to 20G. ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name QUOTA 20G;  -- Change the tablespace quota to unlimited. ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name;  -- Remove the specified tablespace from the archive. ALTER FLASHBACK ARCHIVE fba_name REMOVE TABLESPACE ts_name;
  • Modifying the retention period. 
    ALTER FLASHBACK ARCHIVE fba_name MODIFY RETENTION 2 YEAR;
  • Purging data. 
    -- Remove all historical data. ALTER FLASHBACK ARCHIVE fba_name PURGE ALL;  -- Remove all data before the specified time. ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);  -- Remove all data before the specified SCN. ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE SCN 728969;

Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.

DROP FLASHBACK ARCHIVE fba_name;

To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used. To try this out create a test user and grant the object privilege on the first flashback archive created earlier.

CONN sys/password AS SYSDBA  CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;  GRANT CONNECT, CREATE TABLE TO fda_test_user; GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;

If we connect to the test user we are able to create a table and associate it with the default flashback archive as follows.

CONN fda_test_user/fda_test_user  CREATE TABLE test_tab_1 (   id          NUMBER,   desription  VARCHAR2(50),   CONSTRAINT test_tab_1_pk PRIMARY KEY (id) ) FLASHBACK ARCHIVE;

If we try to create a similar table, but point it at the second archive it fails, as we have no privileges on it.

CONN fda_test_user/fda_test_user  CREATE TABLE test_tab_2 (   id          NUMBER,   desription  VARCHAR2(50),   CONSTRAINT test_tab_2_pk PRIMARY KEY (id) ) FLASHBACK ARCHIVE fda_2year; CREATE TABLE test_tab_2 ( * ERROR at line 1: ORA-55620: No privilege to use Flashback Archive   SQL>

The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.

-- Enable using the default FBDA. ALTER TABLE table_name FLASHBACK ARCHIVE;  -- Enable using specific FBDA. ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;  -- Disable flashback archiving. ALTER TABLE table_name NO FLASHBACK ARCHIVE;
 

 

 

출처 : http://www.jigi.net/4247 참고

'DataBase > SQL' 카테고리의 다른 글

DB LINK  (0) 2017.01.03

+ Recent posts

티스토리툴바