摘要
最近由于工作需要,花时间研究了一下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)确认数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
(2)配置数据库参数
SQL> alter system set streams_pool_size=50m;
System altered.
SQL> alter system set java_pool_size=50m;
System altered.
SQL> alter system set undo_retention=3600;
System altered.
SQL> show parameter streams_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 52M
SQL> show parameter java_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 52M
SQL> show parameter undo_re
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 3600
(3)开启归档及补充日志
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 487
Next log sequence to archive 489
Current log sequence 489
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
LOG_MODE FOR SUPPLEME
------------ --- --------
ARCHIVELOG YES YES
(4)准备测试表employee_info
SQL> create table employee_info(n number,name varchar(20),address varchar(150),department varchar(120),organization varchar(150)) tablespace datafile1;
Table created.
SQL> insert into employee_info values(1, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
1 row created.
SQL> insert into employee_info values(2, 'bendsha', 'lianhang road, shanghai, China', 'AnyBackup', 'EISOO');
1 row created.
2.2 创建发布者和订阅者
(1)创建发布者并授权
SQL> create tablespace cdc_datafile datafile '/u01/app/oracle/orcl/cdc_datafile.dbf' size 1G;
Tablespace created.
SQL> create user cdc_publisher identified by cdc_publisher default tablespace cdc_datafile temporary tablespace temp;
User created.
SQL> grant create session TO cdc_publisher;
Grant succeeded.
SQL> grant create table TO cdc_publisher;
Grant succeeded.
SQL> grant create sequence TO cdc_publisher;
Grant succeeded.
SQL> grant create procedure TO cdc_publisher;
Grant succeeded.
SQL> grant create any job TO cdc_publisher;
Grant succeeded.
SQL> grant execute_catalog_role TO cdc_publisher;
Grant succeeded.
SQL> grant select_catalog_role TO cdc_publisher;
Grant succeeded.
SQL> grant execute ON dbms_cdc_publish TO cdc_publisher;
Grant succeeded.
SQL> grant execute ON dbms_lock TO cdc_publisher;
Grant succeeded.
SQL> grant unlimited tablespace TO cdc_publisher;
Grant succeeded.
SQL> execute dbms_streams_auth.grant_admin_privilege('CDC_PUBLISHER');
PL/SQL procedure successfully completed.
SQL> grant all on backupuser.employee_info to cdc_publisher;
Grant succeeded.
(2)创建订阅者并授权
SQL> create user cdc_subscriber identified by cdc_subscriber default tablespace cdc_datafile temporary tablespace temp;
User created.
SQL> grant create session TO cdc_subscriber;
Grant succeeded.
2.3 发布/订阅具体数据
(1)发布:准备源表(Source Table)
SQL>
conn cdc_publisher/cdc_publisher
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'backupuser.employee_info');
END;
Connected.
SQL> 2 3 4 /
PL/SQL procedure successfully completed.
(2)发布:创建变更集(Data Set)
SQL>
conn cdc_publisher/cdc_publisher
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => 'cdc_employee_info_cs',
description => 'Change set for backupuser.employee_info info',
change_source_name => 'HOTLOG_SOURCE',
stop_on_ddl => 'y'
);
END;
Connected.
SQL> 2 3 4 5 6 7 8 9 /
PL/SQL procedure successfully completed.
(3)发布:创建变更表(Change Table)
SQL>
conn cdc_publisher/cdc_publisher
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'employee_info_ct',
change_set_name => 'cdc_employee_info_cs',
source_schema => 'backupuser',
source_table => 'employee_info',
column_type_list =>'n number,name varchar(20),address varchar(150)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string =>'');
END;
19 /
PL/SQL procedure successfully completed.
(4)发布:激活变更集(Data Set)
SQL>
conn cdc_publisher/cdc_publisher
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'cdc_employee_info_cs',
enable_capture => 'y');
END;
Connected.
SQL> 2 3 4 5 6 /
PL/SQL procedure successfully completed.
(5)授权给订阅者
SQL>
conn cdc_publisher/cdc_publisher
GRANT SELECT ON cdc_publisher.employee_info_ct TO cdc_subscriber;
conn / as sysdba
GRANT CREATE TABLE TO cdc_subscriber;
GRANT CREATE SESSION TO cdc_subscriber;
GRANT CREATE VIEW TO cdc_subscriber;
- GRANT UNLIMITED TABLESPACE TO cdc_subscriber;
Grant succeeded.
(6)订阅:创建订阅集
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_set_name => 'cdc_employee_info_cs',
description => 'Change data for employee_info',
subscription_name => 'employee_info_sub');
END;
Connected.
SQL> 2 3 4 5 6 7 /
PL/SQL procedure successfully completed.
(7)订阅:开始订阅表信息
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'employee_info_sub',
source_schema => 'backupuser',
source_table => 'employee_info',
column_list => 'n,name,address',
subscriber_view => 'employee_info_view');
END;
Connected.
SQL> 2 3 4 5 6 7 8 9 /
PL/SQL procedure successfully completed.
(8)订阅:激活订阅
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
subscription_name => 'employee_info_sub');
END;
Connected.
SQL> 2 3 4 5 /
PL/SQL procedure successfully completed.
(9)订阅:扩展订阅窗口
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => 'employee_info_sub');
END;
Connected.
SQL> 2 3 4 5 /
PL/SQL procedure successfully completed.
(10)订阅:查看订阅视图内容
SQL>
conn cdc_subscriber/cdc_subscriber
Connected.
SQL> select * from employee_info_view;
no rows selected
2.4 测试发布/订阅
(1)源表employee_info变更
SQL>
conn backupuser/backupuser123
insert into employee_info values(1, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
insert into employee_info values(2, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
insert into employee_info values(3, 'bendsha', 'lianhang road, shanghai, China', 'SmartData', 'SmartDB');
update employee_info set name = 'zhuzi' where n = 2;
delete from employee_info where n = 1;
Connected.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row updated.
SQL>
1 row deleted.
SQL> commit;
Commit complete.
(2)查看数据发布情况
SQL>
conn cdc_publisher/cdc_publisher
Connected.
SQL> select OPERATION$,n,name,address from employee_info_ct;
OP N NAME
-- ---------- --------------------
ADDRESS
--------------------------------------------------------------------------------
I 1 bendsha
lianhang road, shanghai, China
I 2 bendsha
lianhang road, shanghai, China
I 3 bendsha
lianhang road, shanghai, China
OP N NAME
-- ---------- --------------------
ADDRESS
--------------------------------------------------------------------------------
UO 2 bendsha
lianhang road, shanghai, China
UN 2 zhuzi
lianhang road, shanghai, China
D 1 bendsha
lianhang road, shanghai, China
6 rows selected.
(3)查看数据订阅情况
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => 'employee_info_sub');
END;
Connected.
SQL> 2 3 4 5 /
PL/SQL procedure successfully completed.
SQL> select OPERATION$,n,name,address from employee_info_view;
OP N NAME
-- ---------- --------------------
ADDRESS
--------------------------------------------------------------------------------
I 1 bendsha
lianhang road, shanghai, China
I 2 bendsha
lianhang road, shanghai, China
I 3 bendsha
lianhang road, shanghai, China
OP N NAME
-- ---------- --------------------
ADDRESS
--------------------------------------------------------------------------------
UO 2 bendsha
lianhang road, shanghai, China
UN 2 zhuzi
lianhang road, shanghai, China
D 1 bendsha
lianhang road, shanghai, China
6 rows selected.
(4)清除变更数据集
SQL>
conn cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name => 'employee_info_sub');
END;
Connected.
SQL> 2 3 4 5 /
PL/SQL procedure successfully completed.
SQL> select OPERATION$,n,name,address from employee_info_view;
no rows selected
(5)删除发布数据
SQL> conn cdc_publisher/cdc_publisher
Connected.
SQL> truncate table employee_info_ct;
Table truncated.
SQL> select OPERATION$,n,name,address from employee_info_ct;
no rows selected
3. 常见问题解决方法
3.1 ORA-31466: 未找到发布内容
执行订阅表信息时,提示ORA-31466:未找到发布内容,排查发现是没有将变更表cdc_employee_info的查询权限赋予订阅者用户cdc_subscriber导致。
解决方法:
SQL> conn cdc_publisher/cdc_publisher
Connected.
SQL> grant select on cdc_employee_info to cdc_subscriber;
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
DBMS_CDC_SUBSCRIBE:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_cdcsub.htm#ARPLS024