Oracle CDC (Change Data Capture)更新数据捕获——Asynchronous HotLog Mode（附带简单的kettle任务实现数据同步）
Performing Asynchronous HotLog Publishing
Step 1 Source Database DBA: Set the database initialization parameters.
Microsoft Windows [版本 6.1.7600] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期四 3月 3 14:19:13 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn /as sysdba 已连接。 SQL> show parameter compatible; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.0.0 SQL> show parameter JAVA_POOL_SIZE; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_pool_size big integer 0 SQL> alter system set JAVA_POOL_SIZE=50000000 scope=BOTH; 系统已更改。 SQL> show parameter JOB_QUEUE_PROCESSES; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 1000 SQL> show parameter PARALLEL_MAX_SERVERS; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_max_servers integer 10 SQL> alter system set PARALLEL_MAX_SERVERS=15 scope=BOTH; 系统已更改。 SQL> show parameter PROCESSES; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 500 SQL> alter system set PROCESSES=507 scope=SPFILE; 系统已更改。 SQL> show parameter SESSIONS; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 772 shared_server_sessions integer SQL> alter system set SESSIONS=774 scope=SPFILE; 系统已更改。 SQL> show parameter STREAMS_POOL_SIZE; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 0 SQL> alter system set STREAMS_POOL_SIZE=71M scope=BOTH; 系统已更改。 SQL> show parameter UNDO_RETENTION; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 SQL> alter system set UNDO_RETENTION=3600 scope=BOTH; 系统已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 3423965184 bytes
Fixed Size 2180544 bytes
Variable Size 1828719168 bytes
Database Buffers 1577058304 bytes
Redo Buffers 16007168 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
Step 2 Source Database DBA: Alter the source database.
SQL> ALTER DATABASE FORCE LOGGING; 数据库已更改。 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 数据库已更改。 SQL> ALTER TABLE snowflake.cdc_ah_prod 2 ADD SUPPLEMENTAL LOG GROUP log_group_prod 3 (ID, NAME, PRICE) ALWAYS; 表已更改。 SQL>
Step 3 Source Database DBA: Create and grant privileges to the publisher.
SQL> CREATE TABLESPACE CDC_TBSP 2 datafile 'D:\app\Administrator\oradata\orcl\CDC_TBSP.dbf' SIZE 50M 3 AUTOEXTEND OFF 4 BLOCKSIZE 8192 5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K; 表空间已创建。 SQL> CREATE USER cdc_pub 2 IDENTIFIED BY Password40 3 DEFAULT TABLESPACE cdc_tbsp 4 TEMPORARY TABLESPACE temp 5 QUOTA UNLIMITED ON cdc_tbsp; 用户已创建。 SQL> GRANT CREATE SESSION TO cdc_pub; 授权成功。 SQL> GRANT CREATE TABLE TO cdc_pub; 授权成功。 SQL> GRANT CREATE TABLESPACE TO cdc_pub; 授权成功。 SQL> GRANT UNLIMITED TABLESPACE TO cdc_pub; 授权成功。 SQL> GRANT SELECT_CATALOG_ROLE TO cdc_pub; 授权成功。 SQL> GRANT EXECUTE_CATALOG_ROLE TO cdc_pub; 授权成功。 SQL> GRANT CREATE SEQUENCE TO cdc_pub; 授权成功。 SQL> GRANT DBA TO cdc_pub; 授权成功。 SQL> GRANT EXECUTE on DBMS_CDC_PUBLISH TO cdc_pub; 授权成功。 SQL> SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdc_pub'); PL/SQL 过程已成功完成。
Step 4 Source Database DBA: Prepare the source tables.
SQL> BEGIN 2 DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'snowflake.cdc_ah_prod'); 3 END; 4 / PL/SQL 过程已成功完成。
Step 5 Staging Database Publisher: Create change sets.
SQL> conn cdc_pub/Password40 已连接。 SQL> exec dbms_cdc_publish.create_change_set('cdc_ah_cs', 'CDC Asynchronous HotL og Mode Change Set', 'HOTLOG_SOURCE', 'Y', NULL, NULL); PL/SQL 过程已成功完成。 SQL>
Step 6 Staging Database Publisher: Create the change tables that will contain the changes to the source tables.
SQL> BEGIN 2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( 3 owner => 'cdc_pub', 4 change_table_name => 'cdc_ah_prod_ct', 5 change_set_name => 'cdc_ah_cs', 6 source_schema => 'snowflake', 7 source_table => 'cdc_ah_prod', 8 column_type_list => 'id varchar2(40 char), name varchar2(200 char), price number(10,4)', 9 capture_values => 'both', 10 rs_id => 'y', 11 row_id => 'n', 12 user_id => 'n', 13 timestamp => 'n', 14 object_id => 'n', 15 source_colmap => 'n', 16 target_colmap => 'y', 17 options_string => NULL); 18 END; 19 / PL/SQL 过程已成功完成。 SQL>
Step 7 Staging Database Publisher: Enable the change set.
SQL> BEGIN 2 DBMS_CDC_PUBLISH.ALTER_CHANGE_SET( 3 change_set_name => 'cdc_ah_cs', 4 enable_capture => 'y'); 5 END; 6 / PL/SQL 过程已成功完成。 SQL>
Step 8 Staging Database Publisher: Grant access to subscribers.
SQL> conn /as sysdba 已连接。 SQL> CREATE TABLESPACE cdc_sub_tbsp 2 datafile 'D:\app\Administrator\oradata\orcl\cdc_sub_tbsp.dbf' SIZE 50M 3 AUTOEXTEND OFF 4 BLOCKSIZE 8192 5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K; 表空间已创建。 SQL> SQL> CREATE USER cdc_sub1 2 IDENTIFIED BY Password40 3 DEFAULT TABLESPACE cdc_sub_tbsp; 用户已创建。 SQL> conn /as sysdba 已连接。 SQL> GRANT connect, resource,dba TO cdc_sub1;
授权成功。
SQL> conn cdc_pub/Password40 已连接。 SQL> GRANT SELECT ON cdc_pub.cdc_ah_prod_ct TO cdc_sub1; 授权成功。 SQL>
Subscribing to Change Data
Step 1 Find the source tables for which the subscriber has access privileges.
SQL> conn cdc_sub1/Password40 已连接。 SQL> SELECT * FROM DBA_SOURCE_TABLES; SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME ------------------------------ ------------------------------ SNOWFLAKE CDC_AH_PROD
Step 2 Find the change set names and columns for which the subscriber has access privileges.
SQL> SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID 2 FROM DBA_PUBLISHED_COLUMNS 3 WHERE SOURCE_SCHEMA_NAME ='SNOWFLAKE' AND SOURCE_TABLE_NAME = 'CDC_AH_PROD' ; CHANGE_SET_NAME COLUMN_NAME PUB_ID ------------------------------ ------------------------------ ---------- CDC_AH_CS ID 119386 CDC_AH_CS NAME 119386 CDC_AH_CS PRICE 119386 SQL>
Step 3 Create a subscription.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION( 3 change_set_name => 'cdc_ah_cs', 4 description => 'Change data for cdc_ah_prod', 5 subscription_name => 'cdc_ah_cs_sub1'); 6 END; 7 / PL/SQL 过程已成功完成。 SQL>
Step 4 Subscribe to a source table and the columns in the source table.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.SUBSCRIBE( 3 subscription_name => 'cdc_ah_cs_sub1', 4 source_schema => 'SNOWFLAKE', 5 source_table => 'CDC_AH_PROD', 6 column_list => 'ID, NAME, PRICE', 7 subscriber_view => 'CDC_AH_PROD_VIEW'); 8 END; 9 / PL/SQL 过程已成功完成。 SQL>
Step 5 Activate the subscription.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL>
Step 6 Get the next set of change data.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL>
Step 7 Read and query the contents of the subscriber views.
SQL> SELECT OPERATION$, ID, NAME, PRICE FROM CDC_AH_PROD_VIEW; OP -- ID -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- PRICE ---------- I 1 苹果 4000 OP -- ID -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- PRICE ---------- I 2 梨 4 OP -- ID -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- PRICE ---------- D 1 苹果 4000 SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.PURGE_WINDOW( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL> SQL> SELECT OPERATION$, ID, NAME, PRICE FROM CDC_AH_PROD_VIEW; 未选定行 SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL> SELECT OPERATION$, ID, NAME, PRICE FROM CDC_AH_PROD_VIEW; OP -- ID -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- PRICE ---------- UO 2 梨 4 OP -- ID -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- PRICE ---------- UN 2 梨 5 SQL>
Step 8 Indicate that the current set of change data is no longer needed.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.PURGE_WINDOW( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL>
Step 9 Repeat Steps 6 through 8.
Step 10 End the subscription.
SQL> BEGIN 2 DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION( 3 subscription_name => 'cdc_ah_cs_sub1'); 4 END; 5 /
注：订阅形成的视图CDC_AH_PROD_VIEW中的数据结构分为两部分，一部分是变更数据本身，另一部分是变更表的必要的控制数据。
发布者可以指定源表中的哪些列可以包含在变更表中。一般，发布者会将源表的主键列和订阅者感兴趣的列加入到变更表中。
变更表中还包括了必要的和可选的控制列，可选的控制列由发布者在创建变更表时指定。控制列由 CDC 来管理。
列 数据类型 模式 可选 描述
----------------------------------------------------------------------
OPERATION$ CHAR(2) 所有 否
（I : INSERT 操作，注意这是'I '，带空格
UO: UPDATE 操作前的源表镜像
UU: UPDATE 操作前的源表镜像，（我实际操作未产生该类型记录）
UN: UPDATE 操作后的源表镜像
D : DELETE 操作，注意这是'D '，带空格）
CSCN$ NUMBER 所有 否 事务的提交 SCN 号(SYSTEM CHANGE NUMBER)
RSID$ NUMBER 所有 是 事务内的唯一操作顺序ID,不能跨事务, 必须和CSCN$一起使用
SOURCE_COLMAP$ RAW(128) 同步 是 源表中更新的列的位掩码.
TARGET_COLMAP$ RAW(128) 所有 是 变更表中更新的列的位掩码.
COMMIT_TIMESTAMP$ DATE 所有 否 事务提交时间.
TIMESTAMP$ DATE 所有 是 源数据库中操作发生的时间.
USERNAME$ VARCHAR2(30) 所有 是 源数据库中完成该操作的用户名.
ROW_ID$ ROW_ID 所有 是 源表中行的ROW ID.
XIDUSN$ NUMBER 异步 否 事务 ID undo 片段号.
XIDSLT$ NUMBER 异步 否 事务 ID 槽号.
XIDSEQ$ NUMBER 异步 否 事务 ID 顺序号.
SYS_NC_OID$ RAW(16) 异步 是 对象 ID.
说明
1. OPERATION$ 是 CHAR(2) 类型，在查询 I 操作和 D操作时，要注意查询条件是 OPERATION$='I ' 和 OPERATION$='D '
2. 如果要按照数据变更发生的顺序排序，则排序子语句是 ORDER BY CSCN$, RSID$
理解 TARGET_COLMAP$和 SOURCE_COLMAP$
TARGET_COLMAP$ 和 SOURCE_COLMAP$ 用于表示那一列发生了变化. TARGET_COLMAP$ 表示变更表中的哪一列发生了变化. SOURCE_COLMAP$ (只使用在同步变更表中) 表示在源表中哪一列发生了变化.
因为 TARGET_COLMAP$ 和 SOURCE_COLMAP$ 的数据类型是 RAW(128), 每一个列可以保存128字节的二进制信息.
Kettle Job
job主要由数据同步（转换）和清空视图，抓取下一批变化数据（SQL）组成。
数据同步转换处理逻辑是，连接订阅者数据库，获取视图（对应订阅中的CDC_AH_PROD_VIEW）中operation$不为'UO'、‘UU'的记录，对于'D '的记录，根据ID，对目标表进行删除，其它记录，根据ID，对目标表进行插入/更新。（目标表是任意数据源上建立的与源表结构相似的表）。
各个步骤：
清空视图，抓取下一批变化数据的SQL处理逻辑是，清空当前视图中的数据，抓取下一批变化数据。
