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

本文源自：https://www.cnblogs.com/bendsha/p/cdc_bend.html

摘要

最近由于工作需要，花时间研究了一下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;
3. BANNER
4. --------------------------------------------------------------------------------
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
6. PL/SQL Release 11.2.0.1.0 - Production
7. CORE 11.2.0.1.0 Production
8. TNS for Linux: Version 11.2.0.1.0 - Production
9. NLSRTL Version 11.2.0.1.0 - Production

（2）配置数据库参数

1. SQL> alter system set streams_pool_size=50m;
3. System altered.
5. SQL> alter system set java_pool_size=50m;
7. System altered.
9. SQL> alter system set undo_retention=3600;
11. System altered.
13. SQL> show parameter streams_pool
15. NAME TYPE VALUE
16. ------------------------------------ ----------- ------------------------------
17. streams_pool_size big integer 52M
18. SQL> show parameter java_pool
20. NAME TYPE VALUE
21. ------------------------------------ ----------- ------------------------------
22. java_pool_size big integer 52M
23. SQL> show parameter undo_re
25. NAME TYPE VALUE
26. ------------------------------------ ----------- ------------------------------
27. 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;
10. Database altered.
12. SQL> alter database add supplemental log data;
14. Database altered.
16. SQL> select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
18. LOG_MODE FOR SUPPLEME
19. ------------ --- --------
20. 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;
3. Table created.
5. SQL> insert into employee_info values(1, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
7. 1 row created.
9. SQL> insert into employee_info values(2, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
11. 1 row created.

2.2 创建发布者和订阅者

（1）创建发布者并授权

1. SQL> create tablespace cdc_datafile datafile '/u01/app/oracle/orcl/cdc_datafile.dbf' size 1G;
3. Tablespace created.
5. SQL> create user cdc_publisher identified by cdc_publisher default tablespace cdc_datafile temporary tablespace temp;
7. User created.
9. SQL> grant create session TO cdc_publisher;
11. Grant succeeded.
13. SQL> grant create table TO cdc_publisher;
15. Grant succeeded.
17. SQL> grant create sequence TO cdc_publisher;
19. Grant succeeded.
21. SQL> grant create procedure TO cdc_publisher;
23. Grant succeeded.
25. SQL> grant create any job TO cdc_publisher;
27. Grant succeeded.
29. SQL> grant execute_catalog_role TO cdc_publisher;
31. Grant succeeded.
33. SQL> grant select_catalog_role TO cdc_publisher;
35. Grant succeeded.
37. SQL> grant execute ON dbms_cdc_publish TO cdc_publisher;
39. Grant succeeded.
41. SQL> grant execute ON dbms_lock TO cdc_publisher;
43. Grant succeeded.
45. SQL> grant unlimited tablespace TO cdc_publisher;
47. Grant succeeded.
49. SQL> execute dbms_streams_auth.grant_admin_privilege('CDC_PUBLISHER');
51. PL/SQL procedure successfully completed.
53. SQL> grant all on backupuser.employee_info to cdc_publisher;
55. Grant succeeded.

（2）创建订阅者并授权

1. SQL> create user cdc_subscriber identified by cdc_subscriber default tablespace cdc_datafile temporary tablespace temp;
3. User created.
5. SQL> grant create session TO cdc_subscriber;
7. 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 /
9. 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 /
14. 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 /
23. 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 /
11. 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;
10. 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 /
12. 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 /
14. 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 /
10. 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 /
10. PL/SQL procedure successfully completed.

（10）订阅：查看订阅视图内容

1. SQL>
2. conn cdc_subscriber/cdc_subscriber
3. Connected.
4. SQL> select * from employee_info_view;
6. 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.
12. SQL>
13. 1 row created.
15. SQL>
16. 1 row created.
18. SQL>
19. 1 row updated.
21. SQL>
22. 1 row deleted.
24. SQL> commit;
26. Commit complete.

（2）查看数据发布情况

1. SQL>
2. conn cdc_publisher/cdc_publisher
3. Connected.
4. SQL> select OPERATION$,n,name,address from employee_info_ct;
6. OP N NAME
7. -- ---------- --------------------
8. ADDRESS
9. --------------------------------------------------------------------------------
10. I 1 bendsha
11. lianhang road, shanghai, China
13. I 2 bendsha
14. lianhang road, shanghai, China
16. I 3 bendsha
17. lianhang road, shanghai, China
20. OP N NAME
21. -- ---------- --------------------
22. ADDRESS
23. --------------------------------------------------------------------------------
24. UO 2 bendsha
25. lianhang road, shanghai, China
27. UN 2 zhuzi
28. lianhang road, shanghai, China
30. D 1 bendsha
31. lianhang road, shanghai, China
34. 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 /
10. PL/SQL procedure successfully completed.
12. SQL> select OPERATION$,n,name,address from employee_info_view;
14. OP N NAME
15. -- ---------- --------------------
16. ADDRESS
17. --------------------------------------------------------------------------------
18. I 1 bendsha
19. lianhang road, shanghai, China
21. I 2 bendsha
22. lianhang road, shanghai, China
24. I 3 bendsha
25. lianhang road, shanghai, China
28. OP N NAME
29. -- ---------- --------------------
30. ADDRESS
31. --------------------------------------------------------------------------------
32. UO 2 bendsha
33. lianhang road, shanghai, China
35. UN 2 zhuzi
36. lianhang road, shanghai, China
38. D 1 bendsha
39. lianhang road, shanghai, China
42. 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 /
10. PL/SQL procedure successfully completed.
12. SQL> select OPERATION$,n,name,address from employee_info_view;
14. no rows selected

（5）删除发布数据

1. SQL> conn cdc_publisher/cdc_publisher
3. Connected.
5. SQL> truncate table employee_info_ct;
7. Table truncated.
9. SQL> select OPERATION$,n,name,address from employee_info_ct;
11. 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;
5. Grant succeeded.

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

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

4. 参考文档

Oracle 10.2 CDC：http://docs.oracle.com/cd/B19306_01/server.102/b14223/cdc.htm

Oracle 11g CDC：http://docs.oracle.com/cd/B28359_01/server.111/b28313/cdc.htm#CHDEHIIE

DBMS_CDC_PUBLISH：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_cdcpub.htm#ARPLS023

DBMS_CDC_SUBSCRIBE：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_cdcsub.htm#ARPLS024