Oracle CDC简介及异步在线日志CDC部署示例

摘要

最近由于工作需要,花时间研究了一下Oracle CDC功能和LogMiner工具,希望能找到一种稳定、高效的技术来实现Oracle增量数据抽取功能。以下是个人的部分学习总结和部署实践。

1. Oracle CDC 简介

很多人都认为,只要是涉及到数据库数据复制和增量数据抽取,都是需要购买收费软件的。实际上,我们通过Oracle提供的CDC和LogMiner等免费工具也能实现数据库数据复制和增量数据抽取,各种数据复制软件只是使得获取增量数据更加便捷,或者是可以支持更多的扩展功能(例如:异构数据库之间的同步,ETL过程的数据清洗、装换),但实际Oracle本身是支持CDC机制,只是很少有人关注,操作起来也有些复杂,而且据传言并不稳定,常常见到论坛上爆出一些莫名其妙的问题。
Oracle11gR2提供给我们以下几种CDC机制:

1.1 Synchronous Change Data Capture Configuration(同步复制)

 原理很简单,原表、目标表必须是同一个库,采用触发器的机制(设置同步CDC后,并看不到触发器,但实际运行机理还是触发器的机制)将原表内容复制到另一个目标表。这个机制就不多说了,和自己给表建触发器没什么太大差别。

1.2 Asynchronous HotLog Configuration(异步在线日志CDC)

这个过程已经没有触发器了,而是使用Redo Log,但是使用在线日志,并不是归档日志。并且原表、目标表仍然必须是同一个库。这种模式是相对简单的,同时这种模式是在Oracle 10以上才产生的,9i是没有这个机制的。

1.3 Asynchronous Distributed HotLog Configuration(异步分布式CDC)

 实际这个模式是对异步在线日志CDC的一种优化,也比较容易理解,就是加入了DB-LINK机制,使原表、目标表不在同一个数据库。实际是和异步在线日志CDC没有什么本质区别。

1.4 Asynchronous Autolog Online Change Data Capture Configuration(异步在线日志复制CDC)

异步在线日志复制CDC模式就要高级很多了,使用Standby Redo Log(热备数据库日志),实际就是使用Oracle的热备机制,将日志写入了热备数据库,目标表就可以建立在热备库上,这对主数据库性能影响就进一步降低。

1.5 Asynchronous AutoLog Archive Change Data Capture Configuration(归档日志CDC)

归档日志CDC模式是最完美的模式,但是需要有机制可以获取归档日志(并行文件系统技术),然后在目标端分析归档日志进行变化数据处理,这种模式理论上来讲,几乎可以完全不影响原数据库的性能。
坦白来说,我对Oracle理解并不深,只是为了解决特定的几个问题多看了一点,在现实工作中遇到类似问题需要解决的,或对技术痴狂的同学可以研究一下,我贴上了4种模式具体的设置步骤,虽然是英文的,但是还是非常明确的。(我比较推荐使用第二种,因为设置比较简单,性能上也属于中规中矩,如果没有什么特别要求,可以采用异步在线日志CDC。
以下是我对异步在线日志CDC环境的部署测试。

2. 异步在线日志CDC环境部署

2.1 环境配置准备

(1)确认数据库版本
  1. SQL> select * from v$version;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  5. PL/SQL Release 11.2.0.1.0 - Production
  6. CORE 11.2.0.1.0 Production
  7. TNS for Linux: Version 11.2.0.1.0 - Production
  8. NLSRTL Version 11.2.0.1.0 - Production
(2)配置数据库参数
  1. SQL> alter system set streams_pool_size=50m;
  2. System altered.
  3. SQL> alter system set java_pool_size=50m;
  4. System altered.
  5. SQL> alter system set undo_retention=3600;
  6. System altered.
  7. SQL> show parameter streams_pool
  8. NAME TYPE VALUE
  9. ------------------------------------ ----------- ------------------------------
  10. streams_pool_size big integer 52M
  11. SQL> show parameter java_pool
  12. NAME TYPE VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. java_pool_size big integer 52M
  15. SQL> show parameter undo_re
  16. NAME TYPE VALUE
  17. ------------------------------------ ----------- ------------------------------
  18. undo_retention integer 3600
(3)开启归档及补充日志
  1. SQL> archive log list
  2. Database log mode Archive Mode
  3. Automatic archival Enabled
  4. Archive destination USE_DB_RECOVERY_FILE_DEST
  5. Oldest online log sequence 487
  6. Next log sequence to archive 489
  7. Current log sequence 489
  8. SQL> alter database force logging;
  9. Database altered.
  10. SQL> alter database add supplemental log data;
  11. Database altered.
  12. SQL> select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
  13. LOG_MODE FOR SUPPLEME
  14. ------------ --- --------
  15. ARCHIVELOG YES YES
(4)准备测试表employee_info
  1. SQL> create table employee_info(n number,name varchar(20),address varchar(150),department varchar(120),organization varchar(150)) tablespace datafile1;
  2. Table created.
  3. SQL> insert into employee_info values(1, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
  4. 1 row created.
  5. SQL> insert into employee_info values(2, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
  6. 1 row created.

2.2 创建发布者和订阅者

(1)创建发布者并授权
  1. SQL> create tablespace cdc_datafile datafile '/u01/app/oracle/orcl/cdc_datafile.dbf' size 1G;
  2. Tablespace created.
  3. SQL> create user cdc_publisher identified by cdc_publisher default tablespace cdc_datafile temporary tablespace temp;
  4. User created.
  5. SQL> grant create session TO cdc_publisher;
  6. Grant succeeded.
  7. SQL> grant create table TO cdc_publisher;
  8. Grant succeeded.
  9. SQL> grant create sequence TO cdc_publisher;
  10. Grant succeeded.
  11. SQL> grant create procedure TO cdc_publisher;
  12. Grant succeeded.
  13. SQL> grant create any job TO cdc_publisher;
  14. Grant succeeded.
  15. SQL> grant execute_catalog_role TO cdc_publisher;
  16. Grant succeeded.
  17. SQL> grant select_catalog_role TO cdc_publisher;
  18. Grant succeeded.
  19. SQL> grant execute ON dbms_cdc_publish TO cdc_publisher;
  20. Grant succeeded.
  21. SQL> grant execute ON dbms_lock TO cdc_publisher;
  22. Grant succeeded.
  23. SQL> grant unlimited tablespace TO cdc_publisher;
  24. Grant succeeded.
  25. SQL> execute dbms_streams_auth.grant_admin_privilege('CDC_PUBLISHER');
  26. PL/SQL procedure successfully completed.
  27. SQL> grant all on backupuser.employee_info to cdc_publisher;
  28. Grant succeeded.
(2)创建订阅者并授权
  1. SQL> create user cdc_subscriber identified by cdc_subscriber default tablespace cdc_datafile temporary tablespace temp;
  2. User created.
  3. SQL> grant create session TO cdc_subscriber;
  4. Grant succeeded.

2.3 发布/订阅具体数据

(1)发布:准备源表(Source Table)
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. BEGIN
  4. DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'backupuser.employee_info');
  5. END;
  6. Connected.
  7. SQL> 2 3 4 /
  8. PL/SQL procedure successfully completed.
(2)发布:创建变更集(Data Set)
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. BEGIN
  4. DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
  5. change_set_name => 'cdc_employee_info_cs',
  6. description => 'Change set for backupuser.employee_info info',
  7. change_source_name => 'HOTLOG_SOURCE',
  8. stop_on_ddl => 'y'
  9. );
  10. END;
  11. Connected.
  12. SQL> 2 3 4 5 6 7 8 9 /
  13. PL/SQL procedure successfully completed.
(3)发布:创建变更表(Change Table)
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. BEGIN
  4. DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
  5. owner => 'cdc_publisher',
  6. change_table_name => 'employee_info_ct',
  7. change_set_name => 'cdc_employee_info_cs',
  8. source_schema => 'backupuser',
  9. source_table => 'employee_info',
  10. column_type_list =>'n number,name varchar(20),address varchar(150)',
  11. capture_values => 'both',
  12. rs_id => 'y',
  13. row_id => 'n',
  14. user_id => 'n',
  15. timestamp => 'n',
  16. object_id => 'n',
  17. source_colmap => 'n',
  18. target_colmap => 'y',
  19. options_string =>'');
  20. END;
  21. 19 /
  22. PL/SQL procedure successfully completed.
(4)发布:激活变更集(Data Set)
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. BEGIN
  4. DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
  5. change_set_name => 'cdc_employee_info_cs',
  6. enable_capture => 'y');
  7. END;
  8. Connected.
  9. SQL> 2 3 4 5 6 /
  10. PL/SQL procedure successfully completed.
(5)授权给订阅者
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. GRANT SELECT ON cdc_publisher.employee_info_ct TO cdc_subscriber;
  4. conn / as sysdba
  5. GRANT CREATE TABLE TO cdc_subscriber;
  6. GRANT CREATE SESSION TO cdc_subscriber;
  7. GRANT CREATE VIEW TO cdc_subscriber;
  8. GRANT UNLIMITED TABLESPACE TO cdc_subscriber;
  9. Grant succeeded.
(6)订阅:创建订阅集
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
  5. change_set_name => 'cdc_employee_info_cs',
  6. description => 'Change data for employee_info',
  7. subscription_name => 'employee_info_sub');
  8. END;
  9. Connected.
  10. SQL> 2 3 4 5 6 7 /
  11. PL/SQL procedure successfully completed.
(7)订阅:开始订阅表信息
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
  5. subscription_name => 'employee_info_sub',
  6. source_schema => 'backupuser',
  7. source_table => 'employee_info',
  8. column_list => 'n,name,address',
  9. subscriber_view => 'employee_info_view');
  10. END;
  11. Connected.
  12. SQL> 2 3 4 5 6 7 8 9 /
  13. PL/SQL procedure successfully completed.
(8)订阅:激活订阅
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
  5. subscription_name => 'employee_info_sub');
  6. END;
  7. Connected.
  8. SQL> 2 3 4 5 /
  9. PL/SQL procedure successfully completed.
(9)订阅:扩展订阅窗口
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
  5. subscription_name => 'employee_info_sub');
  6. END;
  7. Connected.
  8. SQL> 2 3 4 5 /
  9. PL/SQL procedure successfully completed.
(10)订阅:查看订阅视图内容
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. Connected.
  4. SQL> select * from employee_info_view;
  5. no rows selected

2.4 测试发布/订阅

(1)源表employee_info变更
  1. SQL>
  2. conn backupuser/backupuser123
  3. insert into employee_info values(1, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
  4. insert into employee_info values(2, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
  5. insert into employee_info values(3, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
  6. update employee_info set name = 'zhuzi' where n = 2;
  7. delete from employee_info where n = 1;
  8. Connected.
  9. SQL>
  10. 1 row created.
  11. SQL>
  12. 1 row created.
  13. SQL>
  14. 1 row created.
  15. SQL>
  16. 1 row updated.
  17. SQL>
  18. 1 row deleted.
  19. SQL> commit;
  20. Commit complete.
(2)查看数据发布情况
  1. SQL>
  2. conn cdc_publisher/cdc_publisher
  3. Connected.
  4. SQL> select OPERATION$,n,name,address from employee_info_ct;
  5. OP N NAME
  6. -- ---------- --------------------
  7. ADDRESS
  8. --------------------------------------------------------------------------------
  9. I 1 bendsha
  10. lianhang road, shanghai, China
  11. I 2 bendsha
  12. lianhang road, shanghai, China
  13. I 3 bendsha
  14. lianhang road, shanghai, China
  15. OP N NAME
  16. -- ---------- --------------------
  17. ADDRESS
  18. --------------------------------------------------------------------------------
  19. UO 2 bendsha
  20. lianhang road, shanghai, China
  21. UN 2 zhuzi
  22. lianhang road, shanghai, China
  23. D 1 bendsha
  24. lianhang road, shanghai, China
  25. 6 rows selected.
(3)查看数据订阅情况
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
  5. subscription_name => 'employee_info_sub');
  6. END;
  7. Connected.
  8. SQL> 2 3 4 5 /
  9. PL/SQL procedure successfully completed.
  10. SQL> select OPERATION$,n,name,address from employee_info_view;
  11. OP N NAME
  12. -- ---------- --------------------
  13. ADDRESS
  14. --------------------------------------------------------------------------------
  15. I 1 bendsha
  16. lianhang road, shanghai, China
  17. I 2 bendsha
  18. lianhang road, shanghai, China
  19. I 3 bendsha
  20. lianhang road, shanghai, China
  21. OP N NAME
  22. -- ---------- --------------------
  23. ADDRESS
  24. --------------------------------------------------------------------------------
  25. UO 2 bendsha
  26. lianhang road, shanghai, China
  27. UN 2 zhuzi
  28. lianhang road, shanghai, China
  29. D 1 bendsha
  30. lianhang road, shanghai, China
  31. 6 rows selected.
(4)清除变更数据集
  1. SQL>
  2. conn cdc_subscriber/cdc_subscriber
  3. BEGIN
  4. DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
  5. subscription_name => 'employee_info_sub');
  6. END;
  7. Connected.
  8. SQL> 2 3 4 5 /
  9. PL/SQL procedure successfully completed.
  10. SQL> select OPERATION$,n,name,address from employee_info_view;
  11. no rows selected
(5)删除发布数据
  1. SQL> conn cdc_publisher/cdc_publisher
  2. Connected.
  3. SQL> truncate table employee_info_ct;
  4. Table truncated.
  5. SQL> select OPERATION$,n,name,address from employee_info_ct;
  6. no rows selected

3. 常见问题解决方法

3.1 ORA-31466: 未找到发布内容

执行订阅表信息时,提示ORA-31466:未找到发布内容,排查发现是没有将变更表cdc_employee_info的查询权限赋予订阅者用户cdc_subscriber导致。
解决方法:
  1. SQL> conn cdc_publisher/cdc_publisher
  2. Connected.
  3. SQL> grant select on cdc_employee_info to cdc_subscriber;
  4. Grant succeeded.

3.2 激活订阅之后,对源表进行操作,捕获不到数据

我一开始遇到这个问题是监控系统用户SYS用户的employee_info表,没有出现任何异常,就是捕获不到数据,后来我替换给backupuser用户的employee_info表,按照以上步骤操作,就能正常捕获到数据了,官网也没查到相关的文档说明,很奇怪,还需要进一步研究。

4. 参考文档

posted on 2017-05-26 19:27  bendsha  阅读(18019)  评论(0编辑  收藏  举报

导航