通过数据订阅功能,您可以实时订阅源库的增量数据,轻松帮助业务实现轻量级缓存更新、业务异步解耦、含ETL逻辑的数据实时同步等场景。本文为您介绍如何创建Oracle数据订阅通道。
前提条件
- 自建Oracle数据库的版本为9i、10g、11g或12c版本。
- 自建Oracle数据库已开启Supplemental Logging,且要求supplemental_log_data_pk,supplemental_log_data_ui已开启,详情请参见Supplemental Logging。
- 自建Oracle数据库已开启ARCHIVELOG(归档模式),设置合理的归档日志保持周期且归档日志能够被访问,详情请参见ARCHIVELOG。
注意事项
- 由于使用gh-ost或pt-online-schema-change等类似工具执行在线DDL变更不会被订阅到,客户端在将消费到的数据写入目标表时可能会因为表结构不一致而导致写入失败。
- 如果订阅的源数据库还处于其他任务中(例如该数据库还存在于某个正在运行的数据迁移任务中),您可能会订阅到订阅对象以外的数据,此类场景中,您需要手动在订阅客户端中过滤掉不需要的数据。
准备工作
登录待订阅的Oracle数据库,创建用于数据订阅的数据库账号并授授予DBA权限。
说明 如果不允许授予DBA权限,您可以参照以下内容为数据库账号授予更精细化的权限。
create session; connect; resource; execute on sys.dbms_logmnr; select on v_$logmnr_contents; select on v_$log; select on v_$logfile; select on v_$archived_log; select on v_$logmnr_logs; select on v_$parameter; select on v_$database; select on all_objects; select on all_tab_cols; select on dba_registry; select any table; select any transaction; select on v_$active_instances; select on v_$instance; select on sys.USER$; select on SYS.OBJ$; select on SYS.COL$; select on SYS.IND$; select on SYS.ICOL$; select on SYS.CDEF$; select on SYS.CCOL$; select on SYS.TABPART$; select on SYS.TABSUBPART$; select on SYS.TABCOMPART$; select on gv_$listener_network;
#需要对PDB和CDB同时授权 #PDB权限: create session; connect; resource; select on all_objects; select on all_tab_cols; select on dba_registry; select any table; select any transaction; select on v_$log; select on v_$logfile; select on v_$archived_log; select on v_$parameter; select on v_$database; select on v_$active_instances; select on v_$instance; select on V_$PDBS; select on sys.USER$; select on SYS.OBJ$; select on SYS.COL$; select on SYS.IND$; select on SYS.ICOL$; select on SYS.CDEF$; select on SYS.CCOL$; select on SYS.TABPART$; select on SYS.TABSUBPART$; select on SYS.TABCOMPART$; #CDB权限: create session; LOGMINING; select on v_$logmnr_contents;
#本示例以名称为dtstest的数据库账号为例介绍授权命令 create user dtstest IDENTIFIED BY rdsdt_dtsacct; grant create session to dtstest; grant connect to dtstest; grant resource to dtstest; grant execute on sys.dbms_logmnr to dtstest; grant select on v_$logmnr_contents to dtstest; grant select on v_$log to dtstest; grant select on v_$logfile to dtstest; grant select on v_$archived_log to dtstest; grant select on v_$logmnr_logs to dtstest; grant select on v_$parameter to dtstest; grant select on v_$database to dtstest; grant select on all_objects to dtstest; grant select on all_tab_cols to dtstest; grant select on dba_registry to dtstest; grant select any table to dtstest; grant select any transaction to dtstest; grant select on v_$active_instances to dtstest; grant select on v_$instance to dtstest; grant select on sys.USER$ to dtstest; grant select on SYS.OBJ$ to dtstest; grant select on SYS.COL$ to dtstest; grant select on SYS.IND$ to dtstest; grant select on SYS.ICOL$ to dtstest; grant select on SYS.CDEF$ to dtstest; grant select on SYS.CCOL$ to dtstest; grant select on SYS.TABPART$ to dtstest; grant select on SYS.TABSUBPART$ to dtstest; grant select on SYS.TABCOMPART$ to dtstest; grant select on gv_$listener_network to dtstest;
#本示例以名称为dtstest的数据库账号为例介绍授权命令,需要对PDB和CDB同时授权 #PDB授权示例: create user dtstest IDENTIFIED BY rdsdt_dtsacct; grant create session to dtstest; grant connect to dtstest; grant resource to dtstest; grant select on all_objects to dtstest; grant select on all_tab_cols to dtstest; grant select on dba_registry to dtstest; grant select any table to dtstest; grant select any transaction to dtstest; -- v$log privileges grant select on v_$log to dtstest; -- v$logfile privileges grant select on v_$logfile to dtstest; -- v$archived_log privileges grant select on v_$archived_log to dtstest; -- v$parameter privileges grant select on v_$parameter to dtstest; -- v$database privileges grant select on v_$database to dtstest; -- v_$active_instances privileges grant select on v_$active_instances to dtstest; -- v_$instance privileges grant select on v_$instance to dtstest; -- V$PDBS privileges grant select on V_$PDBS to dtstest; grant select on sys.USER$ to dtstest; grant select on SYS.OBJ$ to dtstest; grant select on SYS.COL$ to dtstest; grant select on SYS.IND$ to dtstest; grant select on SYS.ICOL$ to dtstest; grant select on SYS.CDEF$ to dtstest; grant select on SYS.CCOL$ to dtstest; grant select on SYS.TABPART$ to dtstest; grant select on SYS.TABSUBPART$ to dtstest; grant select on SYS.TABCOMPART$ to dtstest; #CDB授权示例: create user dtstest IDENTIFIED BY rdsdt_dtsacct; grant create session to dtstest; grant LOGMINING TO dtstest; -- v$logmnr_contents privileges grant select on v_$logmnr_contents to dtstest;
在文档使用中是否遇到以下问题
更多建议
匿名提交