Oracle10g 데이터 관리 데이터 동기화 오라클 데이터 공유방안 Materialized View

Presentation on theme: "Oracle10g 데이터 관리 데이터 동기화 오라클 데이터 공유방안 Materialized View"— Presentation transcript:

1 Oracle10g 데이터 관리 데이터 동기화 오라클 데이터 공유방안 Materialized View
데이터 복제(Replication) 변경 데이터 켭처(Change Data Capture,CDC) Streams External Table 전송 테이블스페이스(Transportable )

2 리두 로그 기반의 비동기식 변경 데이터 공유 기술
Oracle10g 데이터 관리 데이터 동기화 오라클의 데이터 공유 방안 오라클의 데이터 공유 솔루션 Materialized View 질의 성능 향상과 데이터 복제 기반 기술 데이터 복제(Replication) 원격지 및 분산 환경에서의 데이터 복제 기술 변경 데이터 캡쳐(CDC) 내부 트리거 기반의 동기식 변경 데이터 캡쳐 Streams 리두 로그 기반의 비동기식 변경 데이터 공유 기술 외부 테이블 운영 체제의 데이터를 이용할 수 있는 기술 전송 테이블스페이스 대용량 데이터 공유 및 전송 기술

3 Oracle10g 데이터 관리 데이터 동기화 오라클의 데이터 공유 방안 기업내 운영 환경에서 한 데이터베이스 시스템에서 발생하거나 변경된 데이터를 다른 시스템에 적용하려는 분산 및 복제 환경은 보편화 되어 있으며, 서로 다른 시스템간의 데이터 공유는 중요하고 필요한 이슈 중의 하나이다. 데이터 공유를 위한 가장 일반 적인 형태로서는 운영계 시스템의 데이터 복제(Replication) 기술과 정보계 시스템을 위한 데이터 웨어하우스의 ETL(Extract, Transformation, Load)의 프로세스가 대표적이라 할 수 있으며, 이러한 데이터 공유의 솔루션은 오래 전부터 다양한 기술을 통해 제공되어 왔다. Oracle9i 이후 부터 이러한 데이터 공유 기반 데이터 아키텍쳐를 구현 및 구축 하기 위한 추가적인 새로운 기술들이 제공 되었으며, 향후에도 향상된 성능과 새로운 추가 기능을 계속 제공하리라 예상된다. 현 시점에서 이러한 데이터 공유 요구 사항을 수용할 수 있도록 오라클은 운영 환경에서 다양한 운영 환경 및 요구 사항에 따라 크게 다음과 같은 유형의 데이터 공유 기술을 제공하고 있다. Materialized View 질의 성능 향상 및 데이터 복제를 위해 변화된 데이터의 정보를 추출 저장하기 위한 기반 기술이다. 데이터 웨어하우스에서는 일반적인 질의를 통해 대용량 데이터의 처리를 위해서는 많은 자원이 소비되며, 그 결과를 얻기까지 많은 시간을 요구 하게 된고, 따라서 사용자가 요구하는 데이터를 얻기 위해서는 전체 비용이 많이 늘어나게 된다. 이것을 해결하려는 시도에서 나온 것이 Materialized View 로서 요약(Summary) 관리 기능을 구현한 것이라 할 수 있다. 운영 시스템을 분산 및 복제 환경으로 구현하기 위해서는 8i 이전까지 데이터 복제를 스냅샷(Snap Shot) 기술을 제공하여 왔는데, 오라클에서는 이러한 데이터 복제 기술 또한 Materialized View를 통해서 제공되도록 변경되었다.

4 데이터 동기화 오라클의 데이터 공유 방안 데이터 복제(Replication)
Oracle10g 데이터 관리 데이터 동기화 오라클의 데이터 공유 방안 데이터 복제(Replication) 분산 환경에 있어서 데이터베이스에 발생한 변경된 정보를 다른 데이터베이스에 반영하여 무장애 시스템을 구현하기 위한 솔루션으로 예전에 스냅샷으로 사용되었던 기능으로 Oracle8i 이후 부터는 Materialized View를 이용하도록 변경되었다. 변경 데이터 캡쳐(Change Data Capture) 데이터 웨어하우스 및 정보계 시스템에서 해야 할 중요한 작업중의 하나가 운영계 시스템의 변경 데이터들을 추출하는 작업으로 이전에는 별도의 프로그램을 작성하여 변경된 데이터를 얻을 수 있었다. Oracle9i 부터는 분산 환경에서 데이터베이스에 실행된 DML과 DDL에 대해서 프로그래밍 기법에 기반한 내부 트리거(Internal Trigger)를 이용해 변경된 데이터들을 동기화(Synchronous) 모드로 추출해 내는 방식을 제공하고 있다. Streams 오라클에서 변경된 데이터는 리두로그(Redo Log)에 저장되며, 이러한 리두 로그에 저장된 변경 데이터들을 큐(Queue) 기능을 이용해서 비동기적(Asynchronous) 방식으로 추출하는 방식이다. 외부 테이블(External Table) 데이터베이스 외부의 파일 시스템에 존재하는 파일을 마치 오라클의 테이블과 유사하게 이용할 수 있도록 하는 기능으로써 읽기 전용의 데이터 공유 기술로서 사용된다. 전송 테이블스페이스(Transportable Tablespace) 오라클의 스키마 객체를 저장하는 논리적인 구조인 테이블스페이스를 서로 동일한 플랫폼에서 전송하여 바로 사용할 수 있도록 하는 기술이다.

5 데이터 동기화 사용자 질의 주별/월별 GROUP BY Materialized View 주별_집계정보 집계 질의 월별_집계정보
Oracle10g 데이터 관리 데이터 동기화 Materialized View 오라클은 복제환경에서 데이터를 Non-Master 사이트에 복제하는 경우나 데이터 웨어하우스에서 고비용의 질의 성능을 향상하기 위해서 데이터를 캐쉬 및 요약(Summary)하기 위한 Materialized View를 제공한다. 먼저 질의 성능 향상 관점인 캐시 역할에 대한 부분을 살펴 보면 다음과 같다. 데이터베이스의 성능을 향상시키기 위한 방법의 하나로 오라클에서는 요약 정보(Summary)를 생성하고, 관리 하는 환경을 Materialized View를 통해 제공을 하고 있는데, 질의 실행시 성능을 향상시키기 위하여 복잡한 조인이나 집계 연산을 미리 수행한 후 그 결과를 데이터베이스의 테이블에 저장하는 기술이다. 예를 들면, 지역별 및 제품별 매출의 합을 미리 계산하여 테이블로 저장한 뒤 주기적으로 요약 정보를 갱신하도록 하는 기능을 제공한다. Materialized View 스키마 객체를 사용하여 질의 성능 향상 뿐 만 아니라, 데이터 복제 등의 기능을 제공한다. 사용자 질의 주별/월별 GROUP BY 주별_집계정보 집계 질의 월별_집계정보 일별_집계정보에 대한 GROUP BY가 아닌, 사전 주별,월별 입계 요약 정보를 사용

6 데이터 동기화 Query Rewrite Materialized View
Oracle10g 데이터 관리 데이터 동기화 Materialized View MV를 사용하는 경우 사용자가 실행한 질의에 포함된 대상 테이블에 대해서 유효한 MV가 존재할 경우에 서버에서 질의를 유효한 MV로 대체하는 Query Rewrite 기능을 수행하게 된다. SQL> SELECT TO_CHAR(SUM_DAY,’YYYY’), 3 SUM_01, 4 SUM_02, 5 SUM_03, 6 SUM_04, 7 SUM_05, 8 SUM_06, 9 SUM(SUM_QTY_1), 10 SUM(SUM_QTY_2), 11 SUM(SUM_QTY_3) 12 FROM SUM_TABLE 13 GROUP BY 14 TO_CHAR(SUM_DAY,’YYYY’), 15 SUM_01, 16 SUM_02, 17 SUM_03, 18 SUM_04, 19 SUM_05, 20 SUM_06 SQL> CREATE MATERIALIZED VIEW MV_TEST_SUM 2 ENABLE QUERY REWRITE … 3 FROM SUM_TABLE 4 GROUP BY TO_CHAR(SUM_DAY,’YYYY’), …. SQL> SELECT 2 TO_CHAR(FYSUM_DAY,’YYYY’), 3 SUM_01, 4 SUM_02, 5 SUM_03, 6 SUM_04, 7 SUM_05, 8 SUM_06, 9 SUM(SUM_QTY_1), 10 SUM( SUM_QTY_2), 11 SUM( SUM_QTY_3) 12 FROM MV_TEST_SUM Query Rewrite

7 데이터 동기화 데이터 복제(Replication) 복제 환경에서의 MV
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) 복제 환경에서의 MV 복제 환경에서의 MV는 이전 버전에서는 스냅샷이라고 불리었던 것으로 데이터를 Non-Master 사이트에 복제하는데 사용된다. MV는 어떤 시점에 있어서 목표 Master의 복제 본이라 할 수 있으며, Master는 Master 사이트의 Master 테이블이나 MV 사이트의 Master MV일 수 있다. MV는 갱신(Refresh) 작업을 통해서 하나 이상의 Master에 대해 대해서 각각의 배치 작업을 통해서 갱신된다. MV를 사용하는 이유 복제나 분산환경에서 중요한 이슈 중에 하나로 네트워크의 부하를 감소시키는 것인데, 전체 기업 내에서 단일 데이터베이스 서버를 접근하는 것 보다는 다중의 데이터베이스 서버에 사용자들을 분산시킴으로 인해 네트워크 자원을 효율적으로 사용할 수 있다. 배포 템플릿(Deployment Template)을 이용하게 되면 MV 환경을 로컬에 사전 생성이 가능하고, 이를 이용하여 대량의 배표 환경을 쉽고, 자동화할 수 있게 된다. MV는 컬럼의 부분 집합 또는 로우의 부분 집합을 기반으로 생성할 수 있기 때문에 각 사이트별로 서로 다른 데이터의 집합을 가지게 함으로써 데이터 보안 및 데이터 부분관리를 가능하게 한다. MV는 전용 네트워크 연결을 필요로 하지 않기 때문에 질의 수행을 로컬에서 수행함으로 인해서 네트워크 장애에 대해서 영향을 받지 않고 데이터를 조회할 수 있다. MV 데이터의 갱신은 스케쥴링을 통해서 관리를 하거나 수작업을 통해서 갱신을 할 수 있기 때문에 DBA의 작업 관리 제어에 대한 선택을 폭을 넓게 해 준다.

8 데이터 동기화 데이터 복제(Replication)
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) 데이터 복제는 분산 데이터베이스 시스템에서 여러 데이터베이스 간에 테이블과 같은 데이터베이스 객체를 복사하고 관리하는 작업을 말하는 것으로 하나의 사이트에서 적용된 변경 데이터를 로컬에 저장한 후에 원격지 데이터베이스에 다시 적용하는 과정을 거치게 되는데, 이러한 고급 복제(Advanced Replication)는 별도의 서버가 필요 없으며 오라클 데이터베이스 서버를 통해서 서비스 된다. 복제 객체(Replication Objects) 분산 데이터베이스 환경에서 여러 서버에 존재하는 데이터베이스 객체로서 만약 한 서버의 복제 객체에 변경이 일어날 경우 다른 서버에도 이러한 변경된 작업이 적용된다. 복제 그룹(Replication Group) 복제 환경에서 복제 대상 객체들은 복제 그룹을 통해서 관리하게 되며, 논리적으로 연관된 복제 객체들의 집합을 말한다. 복제 사이트(Replication Site) 복제 그룹은 여러 사이트에 존재할 수 있으며, 크게 Master 사이트와 Materialized View 사이트로 나눌 수 있다. 하나의 사이트는 하나의 복제 그룹에 대한 Master 사이트가 되거나 다른 복제 그룹을 위한 Materialized View 사이트가 될 수 있다. 하나의 사이트는 동일한 복제 그룹에 대해서 Master 사이트와 Materialized View 사이트가 동시에 될 수 는 없다. Master 사이트는 복제 그룹내의 모든 대상 객체의 완전한 복사본을 가지고 있으며, Materialized View 사이트는 복제 그룹의 대상 객체의 모든 또는 부분 집합을 가지게 된다. MV 사이트는 특정 시점의 테이블 데이터의 복사 본 또는 MV를 가지게 된다.

9 데이터 동기화 데이터 복제(Replication) MV 복제
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) MV 복제 MV는 특정 시간에 목표 Master의 완전한 또는 부분적인 복사본을 포함하며, 목표 Master는 Master 사이트의 Master 테이블 또는 MV Site의 Master MV일 수 있다. Master MV란 다른 MV에 대해서 Master 로서의 기능을 하는 MV를 말한다. MV를 통해서 얻는 장점은 다음과 같다. 로컬 접근이 가능하므로, 응답 시간을 향상시키고, 가용성을 증대시킨다. 로컬 MV 에 대해서 질의를 수행할 수 있으므로, Master 사이트에 부하를 적게 준다. 목표 Master 데이터 집합의 선택된 부분 집합만을 복제하는 것이 가능하기 때문에 데이터 보안을 향상시킨다. 읽기 전용 MV 기본적인 설정으로서 MV는 Master 사이트 또는 Master MV 사이트로부터 얻어진 테이블 데이터에 대해서 읽기 전용 접근을 제공한다. 네트워크의 가용성과 상관없이 즉, Master 사이트에 네트워크 접근을 하는 것을 피해서 읽기 전용 MV로부터 데이터를 질의하게 된다. 하지만, DML을 위해서는 Master 사이트의 데이터 접근이 가능해야 한다. 갱신가능 MV 로컬 MV에 레코드를 INSERT,UPDATE,DELETE 함으로써 목표 Master 테이블 또는 Master MV에 이러한 DML이 적용된다. 쓰기 가능 MV MV를 생성할 때 ‘FOR UPDATE’를 기술하며, MV 그룹에 속하지 않는 것으로 사용자가 MV에 DML을 수행하더라도 이러한 DML이 Master 에 반영되지 않고, MV가 Refresh되면 변경된 데이터가 사라지게 된다.

10 데이터 동기화 데이터 복제(Replication)
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) MV가 Master 테이블 또는 Master MV와 일관성을 보장하기 위해서는 주기적으로 MV를 갱신해야 되며, 다음과 같은 세가지 선택사항을 제공한다. 신속한 갱신(Fast Refresh) MV 로그를 이용해서 최근 갱신 이후에 변경된 로우만을 갱신한다. 완전한 갱신(Complete Refresh) 전체 MV를 갱신한다. 강제적 갱신(Force Refresh) 가능할 경우에 신속한 갱신을 하고, Fast Refresh가 가능하지 않을 경우에 완전한 갱신을 수행한다. 갱신 그룹(Refresh Group) 트랜잭션 관점에서 MV가 서로 다른 MV와 일관성을 가져야 될 경우에 이들을 갱신 그룹으로 묶게 된다. 갱신 그룹을 갱신하게 되면 이 갱신 그룹에 속하는 모든 MV는 특정시간에 트랜잭션 관점의 동일한 일관성을 가지게 된다. MV 로그(Materialized View Log) MV 로그는 Master 사이트 또는 Master MV 사이트에서 Master 테이블이나 Master MV에 적용된 모든 DML 변경을 저장하는 테이블로서, Master 테이블과 Master MV는 단지 하나의 MV 로그를 갖게 된다. 신속한 갱신을 위해서는 이러한 Master 테이블과 Master MV가 MV 로그를 가지고 있어야 한다. 복제 카탈로그 모든 복제 환경에서 각 Master 사이트와 MV 사이트는 복제 카탈로그를 가지고 있어야 하며, 이것은 사이트에 존재하는 복제 객체와 복제 그룹에 대한 관리자 정보를 유지 관리하기 위한 테이블과 뷰로 구성된 데이터 딕셔너리이다.

11 데이터 동기화 Master 테이블 Materialized View MV log Master Table Materialized
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) 복제 환경 구성 예 Master Table Materialized view SELECT… FROM... network Master 테이블 MV log Materialized View MV 로그를 이용한 신속한 갱신 변경된 데이만 전송 MV를 이용한 완전한 갱신

12 데이터 동기화 MV_ICHEON ICHEON 데이터베이스 링크 ICHEON.com 분당 이천
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) – HS사 구현 예제 신속한 갱신(Fast Refresh) 복제 구현 예제  반도체 생성 공정 데이터 복제 이천 ICHEON MV_ICHEON 데이터베이스 링크 ICHEON.com 분당 SQL> CREATE MATERIALIZED VIEW TBLOWN.MV_ICHEON PCTFREE 5 TABLESPACE USERS STORAGE (INITIAL 10K NEXT 10K) REFRESH FAST AS SELECT * 7 FROM 8 SQL> CREATE MATERIALIZED VIEW LOG ON TBLOWN.ICHEON WITH PRIMARY KEY PCTFREE 5 TABLESPACE USERS STORAGE (INITIAL 10K NEXT 10K);

13 데이터 동기화 사용자 정보 변경 전송 (1-10분 주기) 영국 지사 (사용자 조회용) VPN 사용자 정보 한국 과천
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) – S사 구현 예제 READ ONLY 복제 구현  사내 시스템 접속 시스템 데이터 복제 사용자 정보 변경 전송 (1-10분 주기) 영국 지사 (사용자 조회용) VPN 사용자 정보 한국 과천 (사용자 정보 변경 가능 시스템) 사용자 정보 변경 전송 (1-10분 주기) 미국 지사 (사용자 조회용)

14 데이터 동기화 … … 변경 가능 Slave들 과천(마스터) Master 테이블 중앙 네트워크 현황 시스템 (테이블 파티션)
Oracle10g 데이터 관리 데이터 동기화 데이터 복제(Replication) – S사 구현 예제 변경 가능 Slave들 Updatable 복제 구현  네트워크 관리 시스템 1 지역 로컬 네트워크 현황 시스템 양방향 Updatable (1 지역 ROW만) 과천(마스터) Master 테이블 1 지역 정보 2 지역 로컬 네트워크 현황 시스템 양방향 Updatable (2 지역 ROW만) 2 지역 정보 … … 9 지역 정보 9 지역 로컬 네트워크 현황 시스템 양방향 Updatable (9 지역 ROW만) 중앙 네트워크 현황 시스템 (테이블 파티션) 양방향 변경 시 발생하는 conflict를 최소화 한 모델로서 Conflict 해결을 위한 관리상의 많은 문제점을 미리 해결

15 데이터 동기화 동기식 vs. 비동기식 데이터 공유 및 추출
Oracle10g 데이터 관리 데이터 동기화 동기식 vs. 비동기식 데이터 공유 및 추출 데이터를 공유하고 추출하는데 있어서 변경 작업의 시점을 기준으로 동기식 즉, 변경 작업이 발생하는 동시에 원격데이터베이스에 적용하는 방식과 변경 작업이 발생한 후에 일정 시점이 지나서 변경 내용을 원격 데이터베이스 적용하는 비동기식으로 나눌 수 있으며, 기업내의 네트워크 자원, 운영 시스템의 현황 및 사용자의 요구 사항에 따라 구현 방식을 적절하게 사용 할 수 있다. 이러한 두 가지 방식에 대한 특성 및 장단점은 다음과 같다. 동기식(Synchronous) 소스 테이블에 대한 변경이 변경을 일으킨 Atomic 트랜잭션의 한 부분으로 기록되며, 내부 트리거(Internal Trigger)를 통해서 제공된다. 따라서, 변경 데이터 추출을 위한 부가 작업이 트랜잭션의 일 부분이기 때문에 초기 추출의 경우 많은 오버헤드를 가지게 된다. 내부 트리거와 PL/SQL API를 이용하기 때문에 적용하고자 하는 애플리케이션을 변경해야 하는 문제가 발생할 수 있다. 대표적으로 변경 데이터 캡처(CDC)를 이용해서 동기적 데이터 추출을 할 수 있다. 비동기식(Asynchronous) 비동기식 데이터 추출의 경우 트랜잭션이 발생한 후에 변경된 데이터는 리두 로그 파일(Redo Log File) 기록되며, 이 리두 로그에서 변경된 데이터를 추출하여 변경 테이블에 적용한다. 변경 데이터 추출이 트랜잭션과는 별도로 이루어지기 때문에 성능과 관련된 오버헤드를 줄일 수 있게 된다. 비동기식 데이터 추출은 어떠한 애플리케이션의 변경도 필요없기 때문에 모든 기존의 애플리케이션에 적용될 수 있다. Oracle Stream으로 Oracle9i R2에서 부터 제공된다.

16 데이터 동기화 변경 데이터 Subscribe Publish 사용자 테이블 변경 뷰 변경 데이터집합 변경 데이터 캡쳐(CDC)
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) 전사내 분산 환경에서 데이터 공유의 한 방법으로 원천 소스 테이블에 DML이 발생할 경우 가장 최근에 추출한 변경 데이터 이후의 신규 변경된 데이터를 추출해서 목표 시스템에 적용하는 방법으로 내부 트리거(Internal Trigger)를 이용하여 원천 데이터베이스에 변경을 동기적(Synchrous)하게 목표 데이터베이스에 적용을 하게 된다. 변경된 데이터는 오라클의 테이블에 저장이 되므로 쉽게 활용할 수 있으며, 소스 데이터베이스의 변경된 데이터를 추출할 뿐 만 아니라, 이를 이용하는데 잠재적 사용자에 대해서 제어와 관리를 할 수 있는 기능을 제공한다. 오라클의 CDC는 원천 소스 데이터베이스 및 목표 데이터베이스와 별개의 변경 데이터를 위한 데이터 집합으로 구성되는 3-계층 구조를 가지게 된다. CDC를 이용하는 방법은 제공하는 PL/SQL API를 이용하면 된다. 사용자 테이블 변경 데이터 변경 데이터집합 Publish Subscribe 변경 뷰

17 데이터 동기화 변경 데이터 캡쳐(CDC) Publish & Subscribe 모델
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) Publish & Subscribe 모델 대부분의 변경 데이터 캡쳐 시스템들은 하나의 publisher를 가지고 몇 개의 오라클 소스 테이블들의 변경 데이터를 캡쳐하고 publish 한다. 변경 데이터에 접근하는 subscriber들은 여러 개 일 수 있다. CDC는 Publish와 Subscriber 태스크들을 수행하기 위해 PL/SQL package를 제공한다. CDC가 Subscriber에 관심 있는 transaction들의 범위를 알려주는 데이터베이스 뷰를 생성함으로써 각 subscriber를 대신하여 subscription window를 관리하게 된다. Publisher 어떤 테이블에 대해서 변경 데이터를 캡쳐를 해야 하는지를 결정하는데, 이들 테이블들이 소스 테이블이 된다. 하나 또는 그 이상의 관계형 테이블들 (소스 테이블들)로 부터 데이터를 획득하기 위한 시스템을 설정하기 위하여 제공되어지는 패키지를 사용하여 변경 데이터를 Publishes한다. Subscribers에 조절된 접근 허용 (SQL GRANT/REVOKE)를 사용하여 변경 테이블에 대한 SELECT 권한을 제어한다. Subscriber 제공되어지는 패키지 DBMS_LOGMNR_CDC_SUBSCRIBE를 사용한다. 각 subscriber는 변경 데이터에 대해 각자의 변경 뷰(Change View)를 가진다. 따라서, 여러 client들은 같은 변경 집합(Change set)에 서로 간섭 없이 동시에 subscribe 가능하며, subscriber views를 준비하여 변경 테이블 내에 저장된 데이터를 확인한다. 데이터가 어떠한 subscriber에 의해서도 더 이상 사용되지 않으면 자동적으로 CDC에 의해 제거되어 진다. 이것은 변경 집합(Change set)이 무한정으로 커지는 것을 방지하기 위해 필수적이다.

18 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 1. 생성된 CDC 사용자로 접속하여, 소스 테이블에 대한 변경 테이블을 생성하는 프로시져를 수행한다. SQL> BEGIN 2 DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE( 3 OWNER=>'cdc', 4 CHANGE_TABLE_NAME => ' CDC_ICHEON ', 5 CHANGE_SET_NAME => 'SYNC_SET',SOURCE_SCHEMA => ‘TBLOWN', 6 SOURCE_TABLE => ' ICHEON ', 7 COLUMN_TYPE_LIST => ‘REGION CHAR(6), 8 KEY_1 CHAR(12), 9 KEY_2 NUMBER(13,4), 10 KEY_3 NUMBER(13,4), 11 KEY_4 CHAR(50), ', 12 CAPTURE_VALUES => 'both', RS_ID => 'y', 13 ROW_ID => 'y', USER_ID => 'y',TIMESTAMP => 'y', OBJECT_ID => 'n', 14 SOURCE_COLMAN => 'y', TARGET_COLMAP => 'y', OPTIONS_STRING => NULL); 15 end; 16 /

19 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 2. Subscription Handle을 얻고, Subscribe를 한다. SQL>DECLARE 2 VARIABLE HANDLE NUMBER 3 VARIABLE VIEWNAME VARCHAR2(50) 4 BEGIN 5 DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE( 6 CHANGE_SET => 'SYNC_SET', DESCRIPTION => 'Change data from ICHEON ', 7 SUBSCRIPTION_HANDLE => :handle); 8 DMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE( 9 SUBSCRIPTION_HANDLE => :handle, 10 SOURCE_SCHEMA => ' TBLOWN ', SOURCE_TABLE => ' ICHEON ', COLUMN_LIST => ‘REGION, 13 KEY_1 ,KEY_2 , 14 KEY_3 , 15 KEY_4 ’); 16 END; 17 /

20 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 3. Subscription을 Activate하고, 새로운 데이터에 대한 경계를 설정한 다음 Subscribe View를 준비한다. SQL> BEGIN 2 DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(SUBSCRIPTION_HANDLE => :handle); 3 DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE => :handle); 4 DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBE_VIEW( 5 SUBSCRIPTION_HANDLE => :handle, 6 SOURCE_SCHEMA => ‘TBLOWN', 7 SOURCE_TABLE => ‘ICHEON', 8 VIEW_NAME => :viewname ); 9 END; 10 / 4. 변경 테이블을 SELECT한다. SQL> SELECT * FROM CDC_ ICHEON;

21 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 5. Subscribe View를 삭제하고, Subscription을 삭제한다. SQL> BEGIN 2 DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW ( 3 SUBSCRIPTION_HANDLE => :handle, 4 SOURCE_SCHEMA => ‘TBLOWN', 5 SOURCE_TABLE => ' ICHEON '); 6 END; 7 / 6. Subscription을 삭제한다. 2 DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(SUBSCRIPTION_HANDLE => :handle); 3 END; 4 /

22 Oracle10g 데이터 관리 데이터 동기화 Oracle Streams Oracle9i R2에서 부터 소개된 Stream은 새로운 정보-통합 기능으로서 데이터베이스에 발생한 변경 데이터 포착하고, 이 변경 작업을 이벤트(Events)로 이러한 변경을 요구하는 목적지로 전파하는 비동기적 방식의 데이터 공유 기능을 제공하는데, 데이터와 이벤트를 공유하는 것을 지원하여 하나의 데이터베이스로부터 다른 데이터베이스에 이러한 공유 정보를 전파하게 된다. Stream이 제공하는 기능은 다음과 같다. 백그라운드 캡처 프로세스를 설정하여 데이터베이스에 수행된 테이블,스키마 또는 전체 데이터베이스에 대한 변경된 정보를 이 프로스세가 리두 로그로부터 논리 변경 레코드(Logical Change Record)로 표현한다. 큐에 변경에 관련된 이벤트를 집어 넣는 과정(Enqueue) 작업을 수행하고, 이 큐는 논리 변경 레코드 이벤트를 동일한 데이터베이스에 공유하거나 또는 다른 데이터베이스에 전파하게 된다. 백그라운드 적용 프로세스(Apply Process)는 이벤트를 큐에서 꺼내는 작업(Dequeue)을 한다. 데이터베이스 이벤트를 적용한다. 그 이외에 Directed Network,자동 충돌 감지,변형 및 이기종 정보 공유 기능을 제공한다. 비동기적 CDC(변경 데이터 포착)은 Oracle Stream의 위에서 제공이 되며 다음과 같은 구성요소를 가지게 된다. 사용자는 어떤 정보가 Stream으로 입력되는가를 제어하게 된다. 어떻게 Streams Flow가 노드에서 다른 노드로 경유하는가를 표현한다. Stream에서 각 노드에 이벤트들이 흘러갈 때 어떤 일들을 해야 되는가를 정의한다. 어떻게 각 Stream이 종료되는가를 정의한다.

23 데이터 동기화 Consumption Staging Capture Oracle Streams Capture
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams Capture Oracle Streams는 Oracle 로그마이너(LogMiner)를 활용한 로그 기반 변경 캡처를 지원하는데,아카이브 로그 파일 뿐만 아니라, 온라인 리두 로그를 분석 및 마이닝(Mining)하는 것을 지원한다. Staging 일반 변경 데이터가 포착되면 이에 해당하는 이벤트는 Staging 영역에 저장되며, 이 Staging 영역은 이벤트를 저장 및 관리하는 서비스를 제공하는 큐(Queue)이다.데이터베이스 테이블에 대한 변경은 논리 변경 레코드(Logical Change Record,LCR)로 형태로 되며, 이를 Subscriber가 소비할 때까지 Staing Area에 저장된다. Consumption Staging 영역의 메시지는 적용 엔진에 의해 소비되며, 메시지가 표현하는 변경은 데이터베이스에 적용되거나 또는 다른 애플리케이션에 의해 소비된다. Consumption Staging Capture

24 데이터 동기화 Oracle Streams 캡처 프로세스 개요
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 캡처 프로세스 개요 데이터베이스내의 객체에 변경이 발생하면 사용자의 실수나 장애시에 복구를 보장하기 위해 리두 로그에 변경 정보를 기록하는데, 캡처 프로세스는 이 리두 로그(Redo Log)를 읽어서 데이터베이스 객체에 발생한 DDL과 DML 문을 캡처하게 된다. 캡쳐 프로세스는 이러한 변경을 논리적 변경 레코드(LCR) 형태로 큐에 넣게 된다. LCR에는 크게 DML 작업으로 인한 로우의 변경 정보를 포함하는 로우 LCR과 DDL 변경을 포함하는 DDL LCR로 나눌 수 있다. 특정 세션이나 적용 프로세스에 의해 생성되는 리두 엔트리를 위한 Streams 태그를 명시할 수 있으며, LCR의 부분이 된다. 또한 Streams 태그는 LCR을 추적하기 위해서도 사용될 수 있다. 리두 로그 DML 작업 DDL 작업 캡처 프로세스 Queue LCR User Message …..

25 데이터 동기화 Oracle Streams 이벤트 저장(Stating) 과 전파(Propagation) 개요
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 이벤트 저장(Stating) 과 전파(Propagation) 개요 Streams는 이벤트의 전파와 소비에 대해 저장하기 위해 큐를 사용하며, 하나의 큐에서 다른 큐로 이벤트를 전파하는데 이들 큐는 동일한 데이터베이스 또는 원격지 데이터베이스에 포함된다. 하나의 이벤트는 하나 또는 그 이상의 중개 (Intermediate) 데이터베이스를 거쳐 소스 데이터베이스에서 목표 데이터베이스로 전파된다. 이 중개 데이터베이스는 이벤트를 받아서 처리 하거나 단순히 다음 중개 또는 목표 데이터베이스로 이벤트를 전파하게 된다. 적용 프로세스(Apply Process) 개요 적용 프로스세는 오라클의 백그라운드 프로세스로서 큐에서 이벤트를 꺼내어 각 이벤트를 데이터베이스 객체에 직접 적용하거나 적용 처리기(Apply Handler)로 불리우는 사용자 정의 프로시져에 매개변수로 전달하게 된다. 적용 프로세스 Queue LCR User Message ….. 메시지 변경 적용 메시지 처리기 프로시져 DML 처리기 프로시져 DDL 처리기 프로시져 Row LCR DDL LCR

26 데이터 동기화 Oracle Streams 규칙(Rule) 개요
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 규칙(Rule) 개요 Streams는 규칙(Rule)을 이용해서 어떤 정보와 어디서 그 정보를 공유할 것인가를 제어하게 되는데, 마치 SQL 문의 WHERE 조건절과 유사하게 조건으로서 명시되고, 관련된 규칙들을 모아서 규칙 집합(Rule Sets)을 구성하게 된다. 규칙 구성요소 규칙 조건은 하나 이상의 표현식과 연산자로의 조합으로 구성되며, 반환값은 이벤트에 기반하여 참,거짓 및 널을 반환한다. 규칙 판별 컨텍스트는 규칙 조건에서 참조 될 수 있는 외부 데이터를 정의하고, 규칙이 판별될 때 규칙 엔진(Rule Engine) 클라이언트에 의해 해석되는 규칙과 관련된 부가 정보이다. 규칙의 종류 테이블 규칙은 규칙을 정의할 때 명시한 테이블에 DML이 실행될 경우 캡쳐 프로세스는 이 변경 정보를 포착하게 되고, 이를 LCR 형태로 생성한 다음에 큐에 넣게 된다. 스키마 규칙은 스키마에 존재하는 데이터베이스 객체에 변경이 생기는 경우로서 예를 들어 인덱스를 생성하거나, 테이블에 새로운 데이터가 삽입되는 경우로서 이 두 가지 작업은 스키마 내의 객체에 해당하기 때문이다. 글로벌 규칙은 데이터베이스 내의 모든 객체에 대해서 발생한 모든 DML 및 DDL 변경 정보를 추출하게 된다. 규칙 기반 변형(Transformation) 어떠한 이벤트에 대해서 그 규칙이 참으로 평가되었을 때 적용되는 변형을 정의하는 것으로 예를 들어 임의 이벤트에 대해 테이블의 컬럼을 데이터타입을 변경하는 경우 규칙 기반 변형을 사용할 수 있다. 이러한 변형 작업은 큐에 이벤트를 저장하거나 이벤트를 전파하는 경우 및 이벤트를 꺼내는 작업에서 정의할 수 있다.

27 Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 이기종 정보 공유는 오라클 뿐 만 아니라 다른 데이터베이스 시스템과의 데이터 공유 측면에서 Streams는 이기종간의 데이터 공유 환경을 제공하는데, 크게 다음과 같은 경우로 나눌 수 있다. 오라클 -> 타 데이터베이스간의 공유 만약 오라클이 소스 시스템이고, 타 데이터베이스 시스템이 목표 시스템일 경우에 타 데이터베이스 시스템은 이벤트를 수신하는 큐와 이벤트를 꺼내고 적용하는 적용 프로세스가 존재하지 않게 된다. 이러한 경우에 오라클 데이터베이스는 목표 시스템에서 수행해야 하는 작업 단계를 오라클에서 직접 수행하는 프록시(Proxy)로서의 작동으로 하게 된다. 즉, 오라클 데이터베이스 자체에서 큐의 이벤트를 꺼내고 이기종 서비스를 사용하는 오라클 데이터베이스의 적용 프로세스가 게이트웨어(Gateway)를 통해서 네트워크로 연결된 타 데이터베이스 시스템에 이벤트를 적용한다. Queue ----- 적용 프로세스 이기종 서비스 오라클 데이터베이스 데이터베이스 객체 타 데이터베이스 게 이 트 웨 변경 적용

28 데이터 동기화 Oracle Streams 타 데이터베이스 -> 오라클의 공유
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 타 데이터베이스 -> 오라클의 공유 타 데이터베이스에서 오라클로 변경 정보를 캡처해서 전파하기 위해서는 사용자가 작성한 애플리케이션이 필요하고, 이 애필리케이션에서 트리거나 다른 방법을 통해서 트랜잭션 로그를 읽어서 변경된 정보를 추출한 다음에 이 정보를 바탕으로 트랜잭션을 조합하고, 순서를 맞춘 다음에 각 변경들을 LCR로 변환해야 한다. Queue ----- 적용 프로세스 오라클 데이터베이스 타 데이터베이스 사용자 애플리케이션 데이터베이스 객체 변경 정보 수집 LCR을 포함하는 사용자 메시지를 큐에 넣는다.

29 Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 오라클에서 제공하는 PL/SQL 패키지를 통해서 주된 Streams 환경을 제어 및 관리할 수 있으며, OEM에 포함되어 있는 툴은 관리하기 위한 몇 가지 설정, 관리 및 모니터링하는 기능을 제공한다. DMBS_STREAMS_ADM 패키지 테이블,스키마 및 데이터베이스 레벨에서 캡처,전파와 적용을 위한 규칙을 추가하고 삭제하는 관리 인터페이스를 제공하고.데이터딕셔너리와 같은 Streams 메타 데이터를 관리하기 위한 큐를 생성하기 위한 프로시져를 포함한다. DBMS_CAPTURE_ADM 패키지 캡처 프로세스를 시작,정지 및 설정하기 위한 관리 인터페이스로서 변경된 데이터의 소스는 리두 로그이고, 리퍼지토리는 큐로서 목적 데이터베이스에서 반영하기 위한 소스 데이터베이스에서 데이터베이스 객체를 준비하기 위한 관리 프로시져를 제공한다. DBMS_PROPAGATION_ADM 패키지 소스 큐에서 목적지 큐로의 전파를 구성하기 위한 관리 인터페이스를 제공한다. DBMS_APPLY_ADM 패키지 적용 프로세스를 시작,정지 및 설정하기 위한 프로세스를 제공한다. DBMS_RULE_ADM 패키지 규칙,규칙 집합 및 규칙 평가 컨텍스트를 생성하고 관리하기 위한 관리 인터페이스를 제공한다. DBMS_RULE 패키지 규칙 집합을 평가하기 위한 EVALUATE 프로시저를 제공하는데, 데이터에 기반한 충족 규칙 리스트를 생성하는 인터페이스이다. DBMS_STREAMS 패키지 SYS.AnyData 객체를 LCR 객체로 변환하고, Streams 애트리뷰트의 정보를 반환한다.

30 데이터 동기화 Oracle Streams 기본 설정
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams 기본 설정 Streams를 사용하기 위해서는 Oracle9i R2를 설치하면 관련된 S/W 및 데이터베이스 객체는 기본적으로 제공되며, 환경 설정을 위한 몇 가지 작업을 해 주어야 한다. Init.ora 파일 수정 AQ_TM_PROCESSES=2 캡처된 LCR의 전파시에 사용하는 QUEUE의 Agent 프로세스 수를 나타내며, 크기는 1이사 10까지의 값으로 한다. ARCHIVE_LAG_TARGET=1800 RAC 환경에서 캡쳐 프로세스가 양쪽 노드의 Archive File을 읽어야 하는데, 정상적인 Log Switch 상태가 아닌 특정 시간에 아카이브 로그를 생성하도록 간격시간을 줄 수 있다. 예를 들어 1800 이면 30분에 해당한다. COMPATIBILITY=9.2.0 데이터베이스 버전의 병합성을 지정하는 것으로 Streams는 Oracle9i R2에서 지원하므로, 이상으로 설정한다. GLOBAL_NAMES=TRUE 분산 데이터베이스 환경에서 데이터베이스 연결을 정의하는 데이터베이스 링크를 생성할 때 사용해야 한다. JOB_QUEUE_PROCESSES=2 큐와 큐로 전달하는 작업을 수행하는 Job 프로세스의 수를 정의한다. LOG_PARALLELISM=1 캡쳐 프로세스를 사용하기 위해 설정하는 것으로 반드시 1로 설정한다.

31 데이터 동기화 Oracle Streams LOGMNR_MAX_PERSISTENT_SESSIONS=10 OPEN_LINK=30
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams LOGMNR_MAX_PERSISTENT_SESSIONS=10 로그 마이너에서 사용하는 동시 세션의 수를 지정하는 것으로 이 세션은 영구적으로 유지되며 현재의 정보를 딕셔너리에 저장한다. 만약 capture를 여러 개 운영하게 되는 경우에는, 최소한 capture의 수보다 더 많이 지정한다. 보통의 권장 값은 10이다. OPEN_LINK=30 소스와 목표의 동시 데이터베이스 링크의 연결 수를 지정하는 매개변수이다. 보통의 경우 30 정도 지정하면 문제 없이 사용할 수 있다. PARALLEL_MAX_SERVERS=12 캡처나 적용 프로세스에 작업이 많을 경우에는 각각 parallel하게 작업을 수행 할 수 있으며, 캡처와 적용의 property에 parallel 값을 지정할 수 있다. SGA_MAX_SIZE=1000M Shared_pool_size보다는 크게 지정. multiple capture process를 위해서는 이 값을 더 늘려야 한다. SHARED_POOL_SIZE=200M 캡처 프로세스는 10M의 SGA를 사용한다.( Shared_pool_size의 10%이상을 사용하지 못하므로 100M 이상을 지정하여야 한다 TIME_STATISTICS=TRUE V$STREAMS_APPLY_COORDINATOR,V$STREAMS_APPLY_READER,V$STREAMS_APPLY_SERVER.V$STREAMS_CAPTURE에 대한 통계 정보를 보기 위해 true로 지정한다. TRANSACTION_AUDITING=TREU DDL에 대한 적용을 정확하게 하기 위해서 지정한다.

32 데이터 동기화 Oracle Streams – HS사 구현 예제 원격 데이테베이스간의 Streams 설정 예 Queue
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 원격 데이테베이스간의 Streams 설정 예 Queue ----- LCR 사용자 메시지 …. 캡쳐 프로세스 소스 오라클 데이터베이스 (이천) 데이터베이스객체 리두로그 변경 정보 적용 목표 오라클 데이터베이스 (분당) LCR 또는 메시지 DDL/DML/메시지 처리기 이벤트 전파 DML/DDL

33 데이터 동기화 Oracle Streams – HS사 구현 예제 데이터베이스 레벨의 작업
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 데이터베이스 레벨의 작업 캡처 프로세스는 일반적으로 리두 로그 를 읽어서 변경을 캡처하지만 RAC환경이나 캡처 프로세스가 다운된 후 재시작될 때 아카이브 로그 파일을 접근하는 경우가 있다. 그러므로 소스가 되는 데이터베이스는 가능한 아카이브 로그로 운영되어야 한다. 로그 마이너 테이블 스페이스 생성 및 로그 마이너 테이블스페이스 이관 SQL> CREATE TABLESPACE LOGMNR_TS DATAFILE ‘/oradata/logmnr_01.dbf’ SIZE 1000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> CONNECT / AS SYSDBA SQL> BEGIN 2 DBMS_LOGMNR.SET_TABLESPACE(‘LOGMNR_TS’); END; /

34 데이터 동기화 Oracle Streams – HS사 구현 예제 Streams 관리자 계정 생성 및 권한 부여
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 Streams 관리자 계정 생성 및 권한 부여 SQL> CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE LOGMNR_TS TEMPORARY TABLESPACE TEMP; SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLETO strmadmin ; SQL> GRANT EXECUTE ON DBMS_AQADM, DBMS_CAPTURE_ADM, DBMS_PROPAGATION_ADM , TO strmadmin; SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM, DBMS_APPLY_ADM , DBMS_FLASHBACK TO strmadmin; SQL> GRANT SELECT ANY DICTIONARY TO strmadmin; -- OEM의 Stream tool을 사용하기 위해. SQL> BEGIN 2 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( 3 PRIVILEGE => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 4 GRANTEE => 'strmadmin', 5 GRANT_OPTION => FALSE 6 ); 7 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( 8 PRIVILEGE => DBMS_RULE_ADM.CREATE_RULE_OBJ, 9 GRANTEE => 'strmadmin', 10 GRANT_OPTION => FALSE 11 ); 12 END; 13 /

35 3. 객체에 대한 권한부여 및 Supplemental 로깅
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 다음은 데이터베이스 설정 후 에 icheon 테이블에 대해서 단방향 Steams를 구현하는 절차이다. 1. 데이터베이스 링크 생성 2. 애플리케이션 사용자 및 테이블 생성 3. 객체에 대한 권한부여 및 Supplemental 로깅 4. 소스/목적 큐 생성 5. 전파(Propagation) 생성 6. 캡처 생성 7. 테이블 동기화 및 전파(Propagation) 8. 적용(Apply) 생성 9. SCN 인스턴스화(Instantiation) 10. 적용 및 캡처 시작

36 데이터 동기화 Oracle Streams – HS사 구현 예제 1. 데이터베이스 링크 생성(이천->분당)
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 1. 데이터베이스 링크 생성(이천->분당) SQL> CONNECT SQL> CREATE DATABASE LINK BUNGDANG.WORLD CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING ‘BUNDANG.WORLD‘; 2. 애플리케이션 사용자 및 테이블 생성(소스 및 목적지에 모두 생성) SQL> CREATE USER STRMUSER IDENTIFIED BY STRMUSER 2 DEFAULT TABLEPSACE LOGMNR_TS TEMPORARY TABLESPACE TEMP; SQL> GRANT CONNECT, RESOURCE TO STRMUSER; SQL> CONNECT SQL> CREATE TABLE ICHEON( 2 REGION NOT NULL CHAR(6), 3 KEY_ NOT NULL CHAR(12), 4 KEY_ NOT NULL NUMBER(13,4), 5 KEY_ NOT NULL NUMBER(13,4), 6 KEY_4 NOT NULL CHAR(50), 7 KEY_ NUMBER(13,4), 8 KEY_ NUMBER(13,4), …

37 데이터 동기화 Oracle Streams – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 3. 객체에 대한 권한 부여 및 Supplemental 로깅 적용 소스 데이터베이스(이천) SQL> CONNECT SQL> GRANT ALL ON icheon TO STRMADMIN; SQL> ALTER TABLE STRMUSER.ICHEON ADD SUPPLEMENTAL LOG GROUP SQL> LOG_GROUP_icheon_PK (REGION, KEY_1, 2 KEY_2, KEY_3) ALWAYS; 목적 데이터베이스(분당) SQL> CONNECT 4. 소스/목적 데이터베이스에 각각 큐 생성 SQL> CONNECT SQL> BEGIN 2 DBMS_STREAMS_ADM.SET_UP_QUEUE( 3 QUEUE_TABLE => ‘icheon_queue_table', 4 QUEUE_NAME => ‘icheon_queue'); 5 END; 6 /

38 데이터 동기화 Oracle Streams – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 5. 전파(PROPAGATION) 생성 : 소스 데이터베이스(이천)에 전파를 생성한다. SQL> CONNECT SQL> BEGIN 2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( 3 TABLE_NAME => 'strmuser.icheon, 4 STREAMS_NAME => ‘icheon_stream', 5 SOURCE_QUEUE_NAME => 'strmadmin.icheon_queue', 6 DESTINATION_QUEUE_NAME => 7 INCLUDE_DML => true, 8 INCLUDE_DDL => true, 9 SOURCE_DATABASE => ‘ICHEON.WORLD'); 10 END; 11 /

39 데이터 동기화 Oracle Streams – HS사 구현 예제 6. 소스 데이터베이스에서 캡처를 생성한다.
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 6. 소스 데이터베이스에서 캡처를 생성한다. SQL> CONNECT SQL> BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( 3 TABLE_NAME => 'strmuser.icheon', -- 등록하는 Table이름 4 STREAMS_TYPE => 'capture', 5 STREAMS_NAME => ‘icheon_capture', --새로운 Capture이름. 6 QUEUE_NAME => 'strmadmin.icheon_queue', -- 송신 queue 7 INCLUDE_DML => true, 8 INCLUDE_DDL => true); 9 END; 10 /

40 데이터 동기화 Oracle Streams – HS사 구현 예제 7. 테이블 동기화 및 전파(Propagation)
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 7. 테이블 동기화 및 전파(Propagation) 복제되는 대상에 대한 초기 Sync와 Preparation을 위해 exp/imp를 사용한다. 만약에 Target쪽에 Table이 없거나 Data가 없을 경우에는 exp option중에 rows를 Yes로 한다. 소스 데이터베이스(이천)에서 Export를 수행한다. $exp userid=strmuser/strmuser FILE=icheon.dmp TABLES=icheon OBJECT_CONSISTENT=y ROWS=n 소스 시스템에서 목적 시스템으로 FTP를 통해서 DMP 파일을 전송한다. 목적 데이터베이스(분당)에서 Import를 수행한다. $imp userid=strmuser/strmuser FILE= icheon.dmp COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y 목적 데이터베이스에서는 Supplmental 로깅이 필요없으므로 삭제한다. SQL> CONNECT SQL> ALTER TABLE strmuser.icheon DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_icheon_PK;

41 데이터 동기화 Oracle Streams – HS사 구현 예제 8. 적용 생성
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 8. 적용 생성 목적 데이터베이스에서 전파된 이벤트를 적용하는 적용 프로세스를 시작하는 작업을 등록한다. SQL> CONNECT SQL> BEGIN 2 DBMS_STREAMS_ADM.ADD_TABLE_RULES( 3 TABLE_NAME => 'strmuser.icheon',STREAMS_TYPE => 'apply', 4 STREAMS_NAME => ‘icheon_apply',QUEUE_NAME => 'strmadmin.icheon_queue', 5 INCLUDE_DML => true, INCLUDE_DDL => true, 6 SOURCE_DATABASE => ‘ICHEON.WORLD'); 7 END; 8 / REM Apply중에 Error나도 계속 진행하는 Parameter 지정 2 DBMS_APPLY_ADM.SET_PARAMETER( 3 APPLY_NAME => ‘icheon_apply', 4 PARAMETER => 'disable_on_error', VALUE => 'n'); 5 END; 6 /

42 데이터 동기화 Oracle Streams – HS사 구현 예제 9. SCN Instantiation(인스턴스화)
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 9. SCN Instantiation(인스턴스화) SCN Instantiation은 Target에서 등록된 Object에 대한 Sync의 기준 시점을 지정하는 것으로 Source 의 SCN을 이용하여 지정하게 된다. 즉, Source에서 Target으로 전달된 LCR의 SCN이 지정된 SCN보다 나중의 것인 경우만 Apply Process에 의해 적용된다. 여기서 주의할 것은 SCN은 소스의 SCN을 기준으로 정해야 한다. SQL> CONNECT -- Source로 접속 SQL> DECLARE 2 iscn NUMBER; -- Variable to hold instantiation SCN value 3 BEGIN 4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN ( 6 SOURCE_OBJECT_NAME => 'strmuser.icheon', 7 SOURCE_DATABASE_NAME => ‘ICHEON.WORLD', 8 INSTANTIATION_SCN => iscn); 9 END; 10 /

43 데이터 동기화 Oracle Streams – HS사 구현 예제
Oracle10g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 10. 캡처와 적용 프로세스를 시작한다. 전파를 생성할 때 자동으로 Enable된다. 목적 데이터베이스(분당) SQL> CONNECT SQL> BEGIN 2 DBMS_APPLY_ADM.START_APPLY(APPLY_NAME => ‘icheon_apply'); 3 END; 4 / 소스 데이터베이스(이천) SQL> CONNECT 2 DBMS_CAPTURE_ADM.START_CAPTURE(CAPTURE_NAME => ‘icheon_capture'); 3 END; 4 /

44 Oracle10g 데이터 관리 데이터 동기화 External Table 기업내 환경에 따라 데이터는 데이터베이스 뿐 만 아니라 데이터베이스 외부의 운영 체제의 파일로 존재하는 경우도 많으며, 이러한 운영 체제의 데이터를 데이터베이스내의 테이블과 연계하여 사용하고자 하는 경우가 발생할 수 있는데, 기존에는 이러한 데이터를 SQL을 통해서 원하는 형태나 데이터만을 추출하여 사용하고자 할 경우에 SQL*Loader를 이용해서 파일을 테이블로 로딩한 후에 SQL을 사용하여야 했다. External table은 오라클 9i부터 제공되는 기능으로 데이터가 데이터베이스내에 존재하지 않고 read-only 테이블이라는 점만 제외하고는 일반적인 SQL 테이블과 유사한 것으로 사용될 수 있는 장점을 가진다. 특히, 데이터 웨어하우스에서와 같이 외부 데이터 (flat file)를 원하는 형태의 데이터를 얻기 위해서는 변형(transformation)작업을 하는 과정으로 외부 데이터를 데이터 베이스 내로 데이터를 로드하고 로드된 데이터의 테이블을 이용하도록 하는 번거로운 작업과 추가적인 테이블스페이스 및 관련된 메타 데이터를 관리해야 했었지만, External Table을 이용하여 이러한 문제를 해소시킬 수 있다. SELECT * FROM ICHEON_EXT; OS file External Table

45 데이터 동기화 External Table – HS사 구현 예제 External Table 정의 예제
Oracle10g 데이터 관리 데이터 동기화 External Table – HS사 구현 예제 External Table 정의 예제 SQL> CREATE TABLE ICHEON_EXT ( 2 REGION NOT NULL CHAR(6), 3 KEY_ NOT NULL CHAR(12), 4 KEY_ NOT NULL NUMBER(13,4), 5 KEY_ NOT NULL NUMBER(13,4), 6 KEY_4 NOT NULL CHAR(50), 7 KEY_ NUMBER(13,4), 8 KEY_ NUMBER(13,4) 12 ORGANIZATION external 13 ( TYPE oracle_loader DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS 14 ( 15 RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16KSC5601 16 BADFILE log_file_dir ‘icheon.bad' LOGFILE log_file_dir ‘icheon.log' FIELDS TERMINATED BY "|" LDRTRIM ) location ( ‘icheon.dat' )) REJECT LIMIT UNLIMITED;

46

47 데이터 동기화 전송 테이블스페이스(Transportable Space) 전송 테이블스페이스의 장점 및 제약 사항
Oracle10g 데이터 관리 데이터 동기화 전송 테이블스페이스(Transportable Space) 전송 테이블스페이스의 장점 및 제약 사항 기존의 Export/Import 보다 빠르다. 관련된 통계 정보 및 인덱스의 재생성이 필요없다. OS 레벨의 복사 또는 FTP를 이용한 전송을 하기 때문에 별도의 추가 S/W가 필요없다. 전송하려는 소스 데이터베이스와 목표 데이테이스의 플랫폼이 동일해야 된다. 소스와 목표 데이터베이스 간의 문자집합이 동일해야 한다. 목표 데이터베이스에 이미 테이블스페이스가 존재하지 않아야 한다. Materialized View/Replication은 지원하지 않는다. HS사 구현 예제 대상 테이블스페이스를 읽기 전용(Read-Only)로 변경한다. SQL>ALTER TABLESPACE TTS_TBS READ ONLY; 데이터 딕셔너리에서 해당 테이블스페이스에 대한 메타 데이터를 Export한다. $exp TRANSPORTABLE_TABLEPSACE=y TABLESPACE=USERS FILE=tts_tbs.dmp 테이블스페이스의 데이터파일을 소스 플랫폼에서 목표 플랫폼으로 복사하는데, OS에서 제공하는 복사명령어(cp) 또는 ftp를 이용한다. $cp tts_tbs.dmp /oradata/ts_files 목표 데이터베이스에 메타 데이터를 Import한다. $imp TRANSPORTABLE_TABLEPSACE=y FILES=‘/prptdata/oradata/TTS_TBS.dbf’ TABLESPACE=TTS_TBS DATAFILES=‘/oradata/ts_files/tts_tbs.dmp’