[ORACLE] ORACLE 오라클 스냅샷 기술자료입니다.
스냅샵의 사용<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
스냅샵은 보통 원격지DB의 내용을 자신의 로컬 DB에 저장하려는 목적으로 사용된다.
한마디로 DB의 복사본이라고 생각하면 된다.
이 스냅샵은 생성시 옵션으로 주기적으로 refresh할 수 있다.
보통 원격지의 DB는 master 라고 한다.
스냅샵은 long컬럼을 가질 수 없다.
스냅샵은 2가지 형태가 있다.
simple, complex
simple snapshot
single remote table에 기반한다.
혹은 제한된 서브쿼리의 형태를 사용하여 많은 테이블에 정의된다.
simple 스냅샵이 포함할 수 없는 것.
group by
connect by
distinct or aggregate functions
joins
set operations
complex snapshot
많은 master databases에 있는 많은 master tables에 기반할 수 있다.
Refresh Mode지정
마스터 DB에 변경상황이 발생했을 경우 이를 스냅샵에 갱신해줘야 한다.
이는 3가지 모드가 있다.
fast
complete
refresh
FAST
오라클은 snapshot log에 기록된 마스터 테이블의 변화가 생기면 snapshot을 갱신한다.
다음의 모든 상황이 만족할때만 fast refresh를 수행할 수 있다.
1. simple snapshot
2. 스냅샵의 마스터 테이블이 snapshot log를 가지고 있다.
3. snapshot의 마지막 갱신/생성 이전에 snapshot log가 생성되었을때
COMPLETE
요놈은 어떻게 보면 상당히 비효율적이다.
스냅샵을 생성하기 위한 질의를 다시 실행한다.
fast refresh는 complete refresh보다 빠르다.
왜냐하면, 말 그대로 fast가 complete보다 더 적은 양의 데이터를 전송하기 때문이다.
fast refresh는 last refresh이후, 마스터테이블 데이터의 변경부분만 전송한다.
반면, complete refresh는 스냅샵 쿼리의 완전한 결과를 전송한다.
FORCE
오라클이 어떻게 스냅샵을 갱신할지를 지정한다.
만일 fast refresh가 가능하면 오라클은 fast refresh를 수행한다.
fast refresh가 가능하지 않다면 오라클은 complete refresh를 수행한다.
|
# 스냅샵 로그 |
원격지 DB
[kang@dev kang]$ sqlplus kang/xxxxxx
SQL*Plus: Release 8.1.6.0.0 - Production on Mon Nov 6 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> select * from test;
NAME AGE BIRTH
---------- ---------- --------
SQL> create snapshot log on test;
create snapshot log on test
*
ERROR at line 1:
ORA-12014: table 'TEST' does not contain a primary key constraint
snapshot log가 사용할 테이블은 primary key가 존재해야 한다.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
AGE NUMBER(2)
BIRTH VARCHAR2(8)
SQL> alter table test
2 add constraints test_pk primary key (name);
Table altered.
SQL> create snapshot log on test;
Materialized view log created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MLOG$_TEST TABLE
RUPD$_TEST TABLE
TEST TABLE
초기에는 이 2놈은 비어있다. 변경사항이 없으므로..
SQL> select * from mlog$_test;
no rows selected
SQL> select * from rupd$_test;
no rows selected
로컬 DB
SQL> connect sys/xxxxxx
연결되었습니다.
SQL> grant create snapshot to kang;
권한이 부여되었습니다.
SQL> connect kang/xxxxxx
연결되었습니다.
SQL> create database link dev.kang.com
2 connect to kang identified by xxxxxx
3 using 'dev'
아래의 스냅샵생성문에서는 start with가 지정되어 있지 않기 때문에 지금으로부터 7일후 첫번째 refresh가 발생한다.
이후 7일마다 주기적으로 refresh가 발생한다.
SQL> create snapshot test_sf
2 pctfree 5 pctused 60
3 tablespace users
4 storage (initial 50k next 50k)
5 refresh fast next sysdate+7
6 as
7 select name from kang.test@dev.kang.com;
구체화된 뷰가 작성되었습니다.
SQL> drop snapshot test_sf;
구체화된 뷰가 삭제되었습니다.
테스트시에는 너무 긴 시간이므로 5분으로 refresh시간을 줄여서 다시 생성하자
SQL> create snapshot test_sf
2 pctfree 5 pctused 60
3 tablespace users
4 storage (initial 50k next 50k)
5 refresh fast next sysdate+(1/288)
6 as
7 select name from kang.test@dev.kang.com;
구체화된 뷰가 작성되었습니다.
SQL> select * from test_sf;
NAME
----------
원격지 DB
데이터를 하나 넣어 변경사항을 만들자.
SQL> insert into test values('
1 row created.
SQL> commit;
Commit complete.
SQL> desc mlog$_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
변경사항은 mlog$_test에 저장된다.
SQL> select * from mlog$_test;
NAME SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
FE
SQL> select * from rupd$_test;
no rows selected
5분후, mlog$_test의 row들은 원격지의 snapshot이 refresh되면서 없어진다.
SQL> select * from mlog$_test;
no rows selected
5분후 로컬 DB에서 확인
SQL> select * from test_sf;
NAME
----------
SQL> set time on
NAME
----------
NAME
----------
5분후에 데이터가 로컬의 snapshot에 추가된 것을 알 수 있다.
NAME
----------
원격지 DB
SQL> drop snapshot log on test;
Materialized view log dropped.
SQL> create snapshot log on test with (age, birth);
Materialized view log created.
로컬 DB
원격지 테이블의 모든 컬럼을 지정하게 snapshot을 변경한다.
SQL> create snapshot test_sf
2 pctfree 5 pctused 60
3 tablespace users
4 storage (initial 50k next 50k)
5 refresh fast next sysdate+(1/288)
6 as
7 select * from kang.test@dev.kang.com;
구체화된 뷰가 작성되었습니다.
SQL> select * from test_sf;
NAME AGE BIRTH
---------- ---------- --------
SQL> drop snapshot test_sf;
구체화된 뷰가 삭제되었습니다.
원격지 DB
SQL> drop snapshot log on test;
Materialized view log dropped.
[complex snapshot]
아래의 예에서는 3대의 DB서버가 필요하다.
dev와 dev2 서버에서 가져온 각각의 데이터를 통합하여 로컬DB의 테이블에 저장하는 예이다.
dev서버에서 emp테이블을 생성하고 '
SQL> connect kang/xxxxxx@dev
연결되었습니다.
SQL> create table emp (
2 name varchar2(10),
3 age number);
테이블이 생성되었습니다.
SQL> insert into emp values('
1 개의 행이 만들어졌습니다.
dev2서버에서 emp테이블을 생성하고 '
SQL> connect kang/xxxxxx@dev2
연결되었습니다.
SQL> create table emp(
2 name varchar2(10),
3 age number);
테이블이 생성되었습니다.
SQL> insert into emp values('
1 개의 행이 만들어졌습니다.
로컬DB로 다시 연결하여 dev,dev2를 이용하여 스냅샵을 만들어 주자.
SQL> connect kang/xxxxxx
연결되었습니다.
SQL> create database link dev2.kang.com
2 connect to kang identified by xxxxxx
3 using 'dev2';
데이타베이스 링크가 생성되었습니다.
SQL> create snapshot all_emps
2 pctfree 5 pctused 60
3 tablespace users
4 storage (initial 50k next 50k)
5 using index storage (initial 25k next 25k)
6 refresh start with round(sysdate+1)+11/24
7 next sysdate+7
8 as
9 select * from kang.emp@dev2.kang.com
10 union
11 select * from kang.emp@dev.kang.com;
구체화된 뷰가 작성되었습니다.
SQL> select * from all_emps;
NAME AGE
---------- ----------
롤백세그먼트 지정(다음에 하자-skip)
마스터와 로컬 사이트에 refresh동안 사용되는 롤백세그먼트를 지정할 수 있다.
complex 스냅샵이라면 마스터 롤백세그먼트는 무시된다.
dev.kang.com
SQL> create tablespace MYRBS
2 datafile '/ora8i/oradata/dev/rbs02.dbf' size 50m;
테이블 영역이 생성되었습니다.
SQL> create rollback segment myrbs01
2 tablespace MYRBS
3 storage( initial 10k next 10k maxextents unlimited);
롤백 세그멘트가 생성되었습니다.
로컬DB
SQL> create tablespace MYRBS
2 datafile 'C:ORACLEORADATAMADDOGrbs02.dbf' size 10m;
테이블 영역이 생성되었습니다.
SQL> create rollback segment myrbs01
2 tablespace MYRBS
3 storage (initial 10k next 10k maxextents unlimited);
롤백 세그멘트가 생성되었습니다.
SQL> create snapshot temp_test
2 refresh fast start with sysdate next sysdate+7
3 using master rollback segment myrbs01
4 local rollback segment myrbs01
5 as
6 select * from test@dev.kang.com;
select * from test@dev.kang.com
*
6행에 오류:
ORA-23413: "KANG"."TEST" 테이블이 스냅샷 로그를 가지고 있지 않습니다
SQL> connect kang/xxxxxx@dev
연결되었습니다.
SQL> select * from test;
NAME AGE BIRTH
---------- ---------- --------
SQL> create snapshot log on test;
구체화된 뷰 로그가 작성되었습니다.
음.. 너무 길어진다.
롤백에서는 자꾸 에러가 나서 더 질려버렸다.
다음에 기회가 되면 다시 하겠다.
1 create snapshot temp_test
2 refresh fast start with sysdate next sysdate+7
3 using master rollback segment myrbs01
4 local rollback segment myrbs01
5 as
6* select name from kang.test@dev.kang.com
SQL> /
select name from kang.test@dev.kang.com
*
6행에 오류:
ORA-06512: 줄 1에서
Primary-key와 rowid스냅샵
Primary-key스냅샵
create snapshot human_genome
refresh fast start with sysdate next sysdate + 1/4096
with primary key
as select * from genome_catalog;
rowid스냅샵
cretate snapshot emp_data with rowid
as select * from emp_table73;
퍼옴 :http://cafe.daum.net/itbanksecu/CYF7/13
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받고 있습니다.
이 포스팅은 제휴마케팅이 적용되어 작성자에게 일정액의 커미션이 제공될수 있습니다.
