SOURCE - HP-UX stds01 B.11.23 U ia64 2131756364 unlimited-user license GLOBAL_NAME -------------------------------------------------------------------------------- CRM11T.TURKCELL BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi TARGET - SunOS fatih 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-15000 GLOBAL_NAME -------------------------------------------------------------------------------- REENG.TURKCELL BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi -- PRE-SETUP STEPS - -- database parameters are set, users are created with requested grants and dblinks are created -- force logging and supplemental logging is enabled -- References -- OracleŽ Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 16 Change Data Capture -- http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/cdc.htm#DWHSG016 -- http://www.rittmanmead.com/2006/04/14/asynchronous-hotlog-distributed-change-data-capture-and-owb-paris/ conn / as sysdba -- at source SELECT log_mode, force_logging, a.supplemental_log_data_min, a.supplemental_log_data_pk, a.supplemental_log_data_ui, a.supplemental_log_data_fk, a.supplemental_log_data_all FROM v$database a; ARCHIVELOG YES YES NO NO NO NO SELECT NAME, VALUE FROM v$system_parameter b WHERE b.NAME IN ('aq_tm_processes', 'compatible', 'java_pool_size', 'global_names', 'job_queue_processes', 'open_links', 'shared_pool_size', 'streams_pool_size', 'undo_retention', 'log_archive_dest_1', 'log_archive_format', 'open_links_per_instance', 'parallel_max_servers', 'processes') ORDER BY NAME; aq_tm_processes 3 compatible 10.2.0.1.0 global_names FALSE java_pool_size 167772160 job_queue_processes 10 log_archive_dest_1 LOCATION=/crm06/CRM11T/arch log_archive_format arch_crm11t%t_%S_%r.arc open_links 4 open_links_per_instance 4 parallel_max_servers 64 processes 600 shared_pool_size 1157627904 streams_pool_size 117440512 undo_retention 18000 !!! GLOBAL_NAMES=TRUE setup is not exceptable at source, because you need to make sure the name of the database link matches the name of the target database exactly. !!! -- as a workaround database logon trigger is used CREATE OR REPLACE TRIGGER cdc_trig AFTER LOGON ON DATABASE DECLARE sqlstr VARCHAR2(200) := 'ALTER SESSION SET GLOBAL_NAMES=TRUE'; BEGIN IF (USER IN ('HR', 'CDCADMIN', 'SYS', 'SYSTEM')) THEN execute immediate sqlstr; END IF; END cdc_trig; / -- at target SELECT log_mode, force_logging, a.supplemental_log_data_min, a.supplemental_log_data_pk, a.supplemental_log_data_ui, a.supplemental_log_data_fk, a.supplemental_log_data_all FROM v$database a; NOARCHIVELOG NO YES NO NO NO NO SELECT NAME, VALUE FROM v$system_parameter b WHERE b.NAME IN ('aq_tm_processes', 'compatible', 'global_names', 'job_queue_processes', 'open_links', 'shared_pool_size', 'streams_pool_size', 'undo_retention', 'log_archive_dest_1', 'log_archive_format', 'open_links_per_instance', 'parallel_max_servers', 'processes') ORDER BY NAME; aq_tm_processes 3 compatible 10.2.0.3.0 global_names TRUE java_pool_size 62914560 job_queue_processes 10 log_archive_dest_1 log_archive_format %t_%s_%r.dbf open_links 4 open_links_per_instance 4 parallel_max_servers 160 processes 500 shared_pool_size 209715200 streams_pool_size 62914560 undo_retention 900 CREATE USER cdcadmin IDENTIFIED BY cdcadmin DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 0 ON system QUOTA 10M ON sysaux QUOTA 20M ON users; GRANT create session TO cdcadmin; GRANT create table TO cdcadmin; GRANT create sequence TO cdcadmin; GRANT create procedure TO cdcadmin; GRANT dba TO cdcadmin; GRANT execute_catalog_role TO cdcadmin; GRANT select_catalog_role TO cdcadmin; GRANT execute ON dbms_cdc_publish TO cdcadmin; GRANT execute ON dbms_cdc_subscribe TO cdcadmin; execute dbms_streams_auth.grant_admin_privilege('CDCADMIN'); grant select on cdc_change_sets$ to cdcadmin ; grant select on cdc_subscribers$ to cdcadmin ; grant select on cdc_change_tables$ to cdcadmin ; grant select on streams$_process_params to cdcadmin ; grant execute on dbms_aqadm to cdcadmin; grant execute on dbms_capture_adm to cdcadmin; grant execute on dbms_apply_adm to cdcadmin; grant execute on dbms_propagation_adm to cdcadmin; grant execute on dbms_streams_adm to cdcadmin; grant create session, create table, create database link, select_catalog_role, execute_catalog_role, unlimited tablespace to cdcadmin; begin dbms_rule_adm.grant_system_privilege ( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'cdcadmin', grant_option => FALSE); dbms_rule_adm.grant_system_privilege ( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'cdcadmin', grant_option => FALSE); END; / -- at source dblink create database link REENG.TURKCELL connect to cdcadmin identified by cdcadmin using 'REENG' / select * from global_name@REENG.TURKCELL ; -- at target dblink create database link CRM11T.TURKCELL connect to cdcadmin identified by cdcadmin using 'source_cdc.turkcell' / select * from global_name@CRM11T.TURKCELL ; -- demo purpose table at source create table toracle_cdc(c1 number, c2 date, c3 varchar2(64)); ALTER TABLE toracle_cdc ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; create unique index ui_cdc_demo on toracle_cdc(c1) ; insert into toracle_cdc values (1, sysdate, '1' ) ; commit ; -- SETUP STEPS -- A - create_hotlog_change_source step at target begin dbms_cdc_publish.create_hotlog_change_source( change_source_name => 'demo_src', description => 'toracle_cdc source', source_database => 'CRM11T.TURKCELL') ; -- database link name end; / commit ; select cap.capture_name, q.name, qt.queue_table, cap.status from dba_capture@CRM11T.TURKCELL cap , dba_queues@CRM11T.TURKCELL q , dba_queue_tables@CRM11T.TURKCELL qt where cap.queue_owner = 'CDCADMIN' and q.owner = 'CDCADMIN' and qt.owner = 'CDCADMIN' and q.name = cap.queue_name and qt.queue_table = q.queue_table ; CAPTURE_NAME NAME QUEUE_TABLE STATUS ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------- CDC$C_DEMO_SRC CDC$Q_DEMO_SRC CDC$T_DEMO_SRC DISABLED /* from source's alert.log Logminer Bld: Done Fri Mar 21 11:29:28 2008 knlciAlterCapture: start scn changed. scn: 0x0873.dc08a46e */ -- B - create_change_set step at target begin dbms_cdc_publish.create_change_set( change_set_name => 'demo_set', description => 'toracle_cdc change set', change_source_name => 'demo_src') ; end ; / commit; select set_name, set_description, change_source_name , apply_name, queue_name, queue_table_name from change_sets where publisher = 'CDCADMIN' ; SET_NAME SET_DESCRIPTION CHANGE_SOURCE_NAME APPLY_NAME QUEUE_NAME QUEUE_TABLE_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ DEMO_SET toracle_cdc change set DEMO_SRC CDC$A_DEMO_SET CDC$Q_DEMO_SET CDC$T_DEMO_SET select app.apply_name, q.name, app.status, qt.queue_table from dba_apply app , dba_queues q , dba_queue_tables qt where app.apply_user = 'CDCADMIN' and q.owner = 'CDCADMIN' and qt.owner = 'CDCADMIN' and q.name = app.queue_name and qt.queue_table = q.queue_table ; APPLY_NAME NAME STATUS QUEUE_TABLE ------------------------------ ------------------------------ -------- ------------------------------ CDC$A_DEMO_SET CDC$Q_DEMO_SET DISABLED CDC$T_DEMO_SET -- at source select p.propagation_source_name, p.propagation_name , p.staging_database, p.destination_queue, ps.change_set_name from change_propagations p , change_propagation_sets ps where p.destination_queue_publisher = 'CDCADMIN' and ps.change_set_publisher = 'CDCADMIN' and ps.propagation_source_name = p.propagation_source_name and ps.propagation_name = p.propagation_name and ps.staging_database = p.staging_database ; PROPAGATION_SOURCE_NAME PROPAGATION_NAME STAGING_DATABASE DESTINATION_QUEUE CHANGE_SET_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ DEMO_SRC CDC$P_DEMO_SET REENG.TURKCELL CDC$Q_DEMO_SET DEMO_SET select propagation_name, source_queue_owner, source_queue_name , destination_queue_owner, destination_queue_name , destination_dblink from dba_propagation@CRM11T.TURKCELL where destination_queue_owner = 'CDCADMIN' ; PROPAGATION_NAME SOURCE_QUEUE_OWNER SOURCE_QUEUE_NAME DESTINATION_QUEUE_OWNER DESTINATION_QUEUE_NAME DESTINATION_DBLINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- CDC$P_DEMO_SET CDCADMIN CDC$Q_DEMO_SRC CDCADMIN CDC$Q_DEMO_SET REENG.TURKCELL -- C - create_change_table step at target begin dbms_cdc_publish.create_change_table( owner => 'CDCADMIN', change_table_name => 'demo_ct', change_set_name => 'DEMO_SET', source_schema => 'CDCADMIN', source_table => 'TORACLE_CDC', column_type_list => 'C1 NUMBER,C2 DATE,C3 VARCHAR2(64)', capture_values => 'both', rs_id => 'y', row_id => 'n', user_id => 'n', timestamp => 'y', object_id => 'n', source_colmap => 'n', target_colmap => 'y', options_string => null) ; end ; / commit; select change_table_name, change_set_name , source_schema_name, source_table_name from change_tables where change_table_schema = 'CDCADMIN' order by change_table_name ; CHANGE_TABLE_NAME CHANGE_SET_NAME SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ DEMO_CT DEMO_SET CDCADMIN TORACLE_CDC select streams_name, streams_type, table_owner, table_name , rule_type, source_database from dba_streams_table_rules where rule_owner = 'CDCADMIN' order by table_name, rule_type, streams_type ; STREAMS_NAME STREAMS_TYPE TABLE_OWNER TABLE_NAME RULE_TYPE SOURCE_DATABASE ------------------------------ ------------ ------------------------------ ------------------------------ --------- -------------------------------------------------------------------------------- CDC$A_DEMO_SET APPLY CDCADMIN TORACLE_CDC DDL CRM11T.TURKCELL CDC$A_DEMO_SET APPLY CDCADMIN TORACLE_CDC DML CRM11T.TURKCELL select streams_name, streams_type, table_owner , table_name, rule_type, source_database from dba_streams_table_rules@CRM11T.TURKCELL where rule_owner = 'CDCADMIN' order by table_name, rule_type, streams_type ; STREAMS_NAME STREAMS_TYPE TABLE_OWNER TABLE_NAME RULE_TYPE SOURCE_DATABASE ------------------------------ ------------ ------------------------------ ------------------------------ --------- -------------------------------------------------------------------------------- CDC$C_DEMO_SRC CAPTURE CDCADMIN TORACLE_CDC DDL CRM11T.TURKCELL CDC$P_DEMO_SET PROPAGATION CDCADMIN TORACLE_CDC DDL CRM11T.TURKCELL CDC$C_DEMO_SRC CAPTURE CDCADMIN TORACLE_CDC DML CRM11T.TURKCELL CDC$P_DEMO_SET PROPAGATION CDCADMIN TORACLE_CDC DML CRM11T.TURKCELL -- D - alter_change_set step at target begin dbms_cdc_publish.alter_change_set( change_set_name => 'DEMO_SET', enable_capture => 'Y') ; end ; / commit; select apply_name, status from dba_apply where apply_user = 'CDCADMIN' ; APPLY_NAME STATUS ------------------------------ -------- CDC$A_DEMO_SET ENABLED -- E - alter_hotlog_change_source step at target begin dbms_cdc_publish.alter_hotlog_change_source( change_source_name => 'demo_src', enable_source => 'Y') ; end; / commit; select capture_name, status from dba_capture@CRM11T.TURKCELL where queue_owner = 'CDCADMIN' ; CAPTURE_NAME STATUS ------------------------------ -------- CDC$C_DEMO_SRC ENABLED -- F - create_subscription step at target begin dbms_cdc_subscribe.create_subscription( change_set_name => 'DEMO_SET', description => 'toracle_cdc change subscription', subscription_name => 'demo_subs1'); end; / begin dbms_cdc_subscribe.subscribe( subscription_name => 'demo_subs1', source_schema => 'CDCADMIN', source_table => 'toracle_cdc', column_list => 'C1,C2,C3', subscriber_view => 'demo_chg_vw') ; end ; / begin dbms_cdc_subscribe.activate_subscription( subscription_name => 'demo_subs1') ; end ; / commit; SELECT s.subscription_name, s.set_name, s.description, st.source_schema_name, st.source_table_name, st.view_name, sc.column_name FROM user_subscriptions s, user_subscribed_tables st, user_subscribed_columns sc WHERE st.handle = s.handle AND sc.handle = s.handle AND st.source_schema_name = sc.source_schema_name AND st.source_table_name = sc.source_table_name ORDER BY st.source_schema_name, st.source_table_name, sc.column_name; SUBSCRIPTION_NAME SET_NAME DESCRIPTION SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME VIEW_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ DEMO_SUBS1 DEMO_SET toracle_cdc change subscription CDCADMIN TORACLE_CDC DEMO_CHG_VW C1 DEMO_SUBS1 DEMO_SET toracle_cdc change subscription CDCADMIN TORACLE_CDC DEMO_CHG_VW C2 DEMO_SUBS1 DEMO_SET toracle_cdc change subscription CDCADMIN TORACLE_CDC DEMO_CHG_VW C3 -- POST SETUP - change test - -- at source alter system switch logfile ; insert into toracle_cdc values (2, sysdate, '2' ) ; update toracle_cdc t set t.c3 = 'TONGUC' where t.c1 = 1 ; insert into toracle_cdc values (3, sysdate, '3' ) ; delete from toracle_cdc where c1 = 3 ; commit ; -- at target SELECT * FROM demo_ct ORDER BY timestamp$; OPERATION$ CSCN$ COMMIT_TIMESTAMP$ XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ TIMESTAMP$ TARGET_COLMAP$ C1 C2 C3 ---------- ---------- ----------------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------------------------------------------- ---------- ----------- ---------------------------------------------------------------- begin dbms_cdc_subscribe.extend_window( subscription_name => 'demo_subs1'); end; / commit; SELECT * FROM demo_ct ORDER BY timestamp$; OPERATION$ CSCN$ COMMIT_TIMESTAMP$ XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ TIMESTAMP$ TARGET_COLMAP$ C1 C2 C3 ---------- ---------- ----------------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------------------------------------------- ---------- ----------- ---------------------------------------------------------------- I 9293705836 21.03.2008 11:48: 13 51 14218 1 21.03.2008 FE1F0000000000000000000000000000000000000000000000000000000000000000000000000000 2 21.03.2008 2 UO 9293705836 21.03.2008 11:48: 13 51 14218 2 21.03.2008 FE030000000000000000000000000000000000000000000000000000000000000000000000000000 1 21.03.2008 1 UN 9293705836 21.03.2008 11:48: 13 51 14218 2 21.03.2008 FE130000000000000000000000000000000000000000000000000000000000000000000000000000 1 21.03.2008 TONGUC I 9293705852 21.03.2008 14:37: 14 97 9171 3 21.03.2008 FE1F0000000000000000000000000000000000000000000000000000000000000000000000000000 3 21.03.2008 3 D 9293705852 21.03.2008 14:37: 14 97 9171 4 21.03.2008 FE030000000000000000000000000000000000000000000000000000000000000000000000000000 3 21.03.2008 3 -- If needed to cleanup the demo run below at target begin dbms_cdc_subscribe.drop_subscription( subscription_name => 'demo_subs1') ; end ; / commit; begin dbms_cdc_publish.alter_hotlog_change_source( change_source_name => 'demo_src', enable_source => 'N') ; end; / commit; begin dbms_cdc_publish.drop_change_table( owner => 'CDCADMIN', change_table_name => 'demo_ct', force_flag => 'Y' ); end ; / commit; begin dbms_cdc_publish.drop_change_set( change_set_name => 'DEMO_SET') ; end ; / commit; begin dbms_cdc_publish.drop_change_source( change_source_name => 'demo_src') ; end; / commit;