Oracle 10 g q Materialized View Replication Change

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

Oracle 10 g 데이터 관리 데이터 동기화 Materialized View q MV를 사용하는 경우 사용자가 실행한 질의에 포함된 대상 테이블에 대해서 유효한 MV가 존재할 경우에 서버에서 질의를 유효한 MV로 대체하는 Query Rewrite 기능을 수행하게 된다. SQL> SELECT 2 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 6 SQL> CREATE MATERIALIZED VIEW MV_TEST_SUM 2 ENABLE QUERY REWRITE … 3 FROM SUM_TABLE 4 GROUP BY TO_CHAR(SUM_DAY, ’YYYY’), …. Query Rewrite 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

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

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

Oracle 10 g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 q 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 / 18

Oracle 10 g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 q 2. Subscription Handle을 얻고, Subscribe를 한다. SQL>DECLARE 2 VARIABLE HANDLE NUMBER 3 VARIABLE VIEWNAME VARCHAR 2(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( 9 10 SOURCE_SCHEMA => ' TBLOWN ', 11 SOURCE_TABLE => ' ICHEON ', 12 COLUMN_LIST => ‘REGION, 13 KEY_1 , KEY_2 , 14 KEY_3 , 15 KEY_4 ’); 16 17 / 19 SUBSCRIPTION_HANDLE => : handle, END;

Oracle 10 g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 q 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 / q 4. 변경 테이블을 SELECT한다. SQL> SELECT * FROM CDC_ ICHEON; 20

Oracle 10 g 데이터 관리 데이터 동기화 변경 데이터 캡쳐(CDC) – HS사 구현 예제 q 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/ q 6. Subscription을 삭제한다. SQL> BEGIN 2 DBMS_LOGMNR_CDC_SUBSCRIBE. DROP_SUBSCRIPTION(SUBSCRIPTION_HANDLE => : handle); 3 END; 4/ 21

Oracle 10 g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 q Streams 관리자 계정 생성 및 권한 부여 SQL> CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE LOGMNR_TS 2 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 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 / 34 DBMS_RULE_ADM. GRANT_SYSTEM_PRIVILEGE( );

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

Oracle 10 g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 q 3. 객체에 대한 권한 부여 및 Supplemental 로깅 적용 소스 데이터베이스(이천) SQL> CONNECT STRMUSER/STRMUSER@ICHEON. WORLD 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 STRMUSER/STRMUSER@BUNDANG. WORLD SQL> GRANT ALL ON icheon TO STRMADMIN; q 4. 소스/목적 데이터베이스에 각각 큐 생성 SQL> CONNECT STRMADMIN/STRADMIN@ICHEON. WORLD SQL> BEGIN 2 3 QUEUE_TABLE => ‘icheon_queue_table', 4 QUEUE_NAME => ‘icheon_queue'); 5 END; 6/ 37 DBMS_STREAMS_ADM. SET_UP_QUEUE(

Oracle 10 g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 q 5. 전파(PROPAGATION) 생성 : 소스 데이터베이스(이천)에 전파를 생성한다. SQL> CONNECT STRMADMIN/STRMADMIN@ICHOEN. WORLD 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 => 'strmadmin. icheon_queue@BUNDANG. WORLD', 7 INCLUDE_DML => true, 8 INCLUDE_DDL => true, 9 SOURCE_DATABASE => ‘ICHEON. WORLD'); 10 END; 11 / 38

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

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

Oracle 10 g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 q 8. 적용 생성 l 목적 데이터베이스에서 전파된 이벤트를 적용하는 적용 프로세스를 시작하는 작업을 등록한다. SQL> CONNECT strmadmin/strmadmin@bundang 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 지정 SQL> BEGIN 2 DBMS_APPLY_ADM. SET_PARAMETER( 3 APPLY_NAME => ‘icheon_apply', 4 PARAMETER => 'disable_on_error', VALUE => 'n'); 5 END; 6/ 41

Oracle 10 g 데이터 관리 데이터 동기화 Oracle Streams – HS사 구현 예제 q 9. SCN Instantiation(인스턴스화) l SCN Instantiation은 Target에서 등록된 Object에 대한 Sync의 기준 시점을 지정하는 것으로 Source 의 SCN 을 이용하여 지정하게 된다. 즉, Source에서 Target으로 전달된 LCR의 SCN이 지정된 SCN보다 나중의 것인 경우만 Apply Process에 의해 적용된다. 여기서 주의할 것은 SCN은 소스의 SCN을 기준으로 정해야 한다. SQL> CONNECT strmadmin/strmadmin@ICHOEN. WORLD -- 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 / 42

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

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

46