异步AutoLog CDC 配置
异步AutoLog CDC 配置
CDC有同步模式和异步模式
1. CDC同步模式:
通过trigger来实现。
2. CDC异步模式:
2.1 异步HotLog模式
2.2 异步分布式HotLog模式
2.3 异步AutoLog模式。
2.3.1 异步Autolog模式-online redo log
2.3.2 异步Autolog模式-archive log
异步Autolog模式(Asynchronous AutoLog Mode)则是先将日志文件从source database传递到staging database,然后在staging database执行日志分析。这样可以将对source database的压力减到最小。日志的传递通过Redo transport services服务来实现,是不是对这个服务名很熟悉?在Data Guard中也是通过该服务将主库的日志传递到备库的,实际上该模式对于日志的处理和Data Guard中基本上是同样的机制。所以这里也需要在source database中设置相应的LOG_ARCHIVE_DEST_n参数来实现日志的传递。
异步AutoLog模式既可以使用联机日志,也可以使用归档日志来获得增量数据信息。
AUTOLOG CDC 使用 Oracle Streams downstream capture 实施异步autolog发布,异步Autolog online redo log 使用Streams real-time downstream capture,异步Autolog archive log 使用 Streams archived-log downstreams capture。
实施异步Autolog archive log 模式
- --1.设置source端初始化参数
- [oracle@source ~]$ slqlplus / as sysdba
- SQL> show parameter undo_retention;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- undo_retention integer 900
- SQL> alter system set undo_retention=3600 scope=BOTH;
- System altered.
- SQL> alter system set log_archive_dest_1 ="location=/home/oracle/archive mandatory reopen=2"; scope=both;
- System altered.
- SQL> alter system set log_archive_dest_2 = "service=targdb arch optional noregister reopen=2 template=/home/oracle/archive2/arch_%s_%t_%r.arc" scope=both;
- System altered.
- SQL> alter system set log_archive_dest_state_1=enable scope=both;
- System altered.
- SQL> alter system set log_archive_dest_state_2=enable scope=both;
- System altered.
- SQL> alter system set log_archive_format='arch_%s_%t_%r.arc' scope=spfile;
- System altered.
- SQL> show parameter global_names
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- global_names boolean FALSE
- SQL> alter system set global_names=TRUE scope=BOTH;
- System altered.
- SQL> alter system set remote_login_passwordfile=shared
- 2 scope =spfile;
- System altered.
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 276824064 bytes
- Fixed Size 2020160 bytes
- Variable Size 92277952 bytes
- Database Buffers 180355072 bytes
- Redo Buffers 2170880 bytes
- Database mounted.
- SQL> archive log list
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /home/oracle/archive
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 2
- --2.设置target端初始化参数
- [oracle@target ~]$ sqlplus / as sysdba
- SQL> alter system set global_names=TRUE scope=BOTH;
- System altered.
- SQL> alter system set undo_retention=3600 scope=BOTH;
- System altered.
- SQL> show parameter remote_archive_enable
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- remote_archive_enable string true
- SQL> alter system set log_archive_dest_1="location=/home/oracle/archive" scope=both;
- System altered.
- SQL> alter system set remote_login_passwordfile=shared scope =spfile;
- System altered.
- SQL> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 276824064 bytes
- Fixed Size 2020160 bytes
- Variable Size 92277952 bytes
- Database Buffers 180355072 bytes
- Redo Buffers 2170880 bytes
- Database mounted.
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /home/oracle/archive
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 2
- SQL> alter database open;
- Database altered.
- SQL> archive log list
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /home/oracle/archive
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 2
- SQL> alter system switch logfile;
- System altered.
- SQL> archive log list
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /home/oracle/archive
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 3
- --3.source端设置FORCE LOGGING日志模式 与 最小的数据库级SUPPLEMENTAL LOG
- SQL> alter database force logging;
- Database altered.
- SQL> alter database add supplemental log data;
- Database altered.
- SQL> alter database open;
- Database altered.
- SQL> alter system switch logfile;
- System altered.
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /home/oracle/archive
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 3
- SQL> col log_min format a7
- SQL> col log_pk format a6
- SQL> col log_pk format a6
- SQL> col log_ui format a6
- SQL> col log_fk format a6
- SQL> col log_all format a7
- SQL> col force_log format a9
- SQL> SELECT supplemental_log_data_min LOG_MIN, supplemental_log_data_pk LOG_PK, supplemental_log_data_ui LOG_UI, supplemental_log_data_fk LOG_FK,
- supplemental_log_data_all LOG_ALL, force_logging FORCE_LOG
- FROM v$database;
- LOG_MIN LOG_PK LOG_UI LOG_FK LOG_ALL FORCE_LOG
- ------- ------ ------ ------ ------- ---------
- YESNO NO NO NO YES
- --4.target端,创建发布者并授权
- QL> create tablespace ts_cdcpub
- 2 datafile '/home/oracle/oradata/targ/ts_cdcpub01.dbf' size 100M;
- Tablespace created.
- SQL> CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
- QUOTA UNLIMITED ON SYSTEM
- QUOTA UNLIMITED ON SYSAUX; 2 3
- User created.
- SQL> GRANT CREATE SESSION TO cdcpub;
- Grant succeeded.
- SQL> GRANT CREATE TABLE TO cdcpub;
- Grant succeeded.
- SQL> GRANT CREATE TABLESPACE TO cdcpub;
- Grant succeeded.
- SQL> GRANT UNLIMITED TABLESPACE TO cdcpub;
- Grant succeeded.
- SQL> GRANT SELECT_CATALOG_ROLE TO cdcpub;
- Grant succeeded.
- SQL> GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
- Grant succeeded.
- SQL> GRANT DBA TO cdcpub;
- Grant succeeded.
- SQL> GRANT CREATE SEQUENCE TO cdcpub;
- Grant succeeded.
- SQL> GRANT EXECUTE on DBMS_CDC_PUBLISH TO cdcpub;
- Grant succeeded.
- SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');
- PL/SQL procedure successfully completed.
- --5.source端生成LogMiner数据字典,之后才能执行第6步
- SQL> SET SERVEROUTPUT ON
- VARIABLE f_scn NUMBER;
- BEGIN
- :f_scn := 0;
- DBMS_CAPTURE_ADM.BUILD(:f_scn);
- DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :f_scn);
- END;
- /SQL> SQL> 2 3 4 5 6
- The first_scn value is 544891
- PL/SQL procedure successfully completed.
- --6.source端准备源表
- SQL> SQL> desc scott.e dept
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- DEPTNO NOT NULL NUMBER(2)
- DNAME VARCHAR2(14)
- LOC VARCHAR2(13)
- SQL> BEGIN
- DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
- TABLE_NAME => 'scott.dept');
- END;
- / 2 3 4 5
- PL/SQL procedure successfully completed.
- --7.source端,获取source端的global_name,用于在target端创建AutoLog改变源。
- SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME;
- GLOBAL_NAME
- --------------------------------------------------------------------------------
- SOUR.REGRESS.RDBMS.DEV.US.ORACLE.COM
- --8.target端,识别改变源数据库并创建改变源
- SQL> BEGIN
- DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
- change_source_name => 'CHICAGO',
- description => 'test source',
- source_database => 'SOUR.REGRESS.RDBMS.DEV.US.ORACLE.COM',
- first_scn => 544891);
- END;
- / 2 3 4 5 6 7 8
- PL/SQL procedure successfully completed.
- --9.target端,创建改变集
- SQL> BEGIN
- DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
- change_set_name => 'CHICAGO_DAILY',
- description => 'change set for dept info',
- change_source_name => 'CHICAGO',
- stop_on_ddl => 'y');
- END;
- / 2 3 4 5 6 7 8
- PL/SQL procedure successfully completed.
- --10.target端,创建改变表
- SQL> BEGIN
- 2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
- owner => 'cdcpub',
- change_table_name => 'dept_ct',
- 3 change_set_name => 'CHICAGO_DAILY',
- source_schema => 'SCOTT',
- 4 source_table => 'DEPT',
- column_type_list => 'DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)',
- capture_values => 'both',
- rs_id => 'y',
- row_id => 'n',
- 5 user_id => 'n',
- 6 timestamp => 'n',
- object_id => 'n',
- 7 source_colmap => 'n',
- target_colmap => 'y',
- options_string => 'TABLESPACE ts_cdcpub');
- 8 END;
- /
- 9 10 11 12 13 14 15 16 17 18 19
- PL/SQL procedure successfully completed.
- --11.target端,开启改变集,这时oracle的流捕获和应用进程启动
- SQL> SQL> BEGIN
- DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
- change_set_name => 'CHICAGO_DAILY',
- enable_capture => 'y');
- END;
- / 2 3 4 5 6
- PL/SQL procedure successfully completed.
- --12.source端,切换重做日志,oracle开始捕获数据。
- SQL> alter system switch logfile;
- System altered.
- --13.target端,创建订阅者并赋权
- SQL> create user subs identified by subs default tablespace users;
- User created.
- SQL> grant connect,resource to subs;
- Grant succeeded.
- SQL> grant select on cdcpub.dept_ct to subs;
- Grant succeeded.
- ------------------------------------------------------------------------------------------------------------------------
- 如何订阅改变数据(target端)
- --1.查找订阅者能够访问的源表
- SQL> SELECT * FROM ALL_SOURCE_TABLES;
- SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME
- ------------------------------ ------------------------------
- SCOTT DEPT
- --2.查找订阅者能够访问改变集名称与列信息
- SQL> SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
- FROM ALL_PUBLISHED_COLUMNS
- WHERE SOURCE_SCHEMA_NAME ='SCOTT' AND SOURCE_TABLE_NAME = 'DEPT';
- 2 3
- CHANGE_SET_NAME COLUMN_NAME PUB_ID
- ------------------------------ ------------------------------ ----------
- CHICAGO_DAILY DEPTNO 51401
- CHICAGO_DAILY DNAME 51401
- CHICAGO_DAILY LOC 51401
- --3.创建订阅
- SQL> BEGIN
- DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
- change_set_name => 'CHICAGO_DAILY',
- description => 'Change data for DEPT',
- subscription_name => 'DEPT_SUB');
- END;
- /
- 2 3 4 5 6 7
- PL/SQL procedure successfully completed.
- --4.订阅表
- SQL> BEGIN
- DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
- subscription_name => 'DEPT_SUB',
- source_schema => 'SCOTT',
- source_table => 'DEPT',
- column_list => 'DEPTNO , DNAME, LOC',
- subscriber_view => 'DEPT_VIEW');
- END;
- / 2 3 4 5 6 7 8 9
- PL/SQL procedure successfully completed.
- --5.激活订阅
- SQL> BEGIN
- DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
- subscription_name => 'DEPT_SUB');
- END;
- / 2 3 4 5
- PL/SQL procedure successfully completed.
- ------------------------------------------------------------------------------------------------------------------------
- 验证
- --source端
- SQL> insert into scott.dept values(51,'hello',wo 'world');
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> alter system switch logfile;
- System altered.
- --target端,获取改变数据
- SQL> BEGIN
- DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
- subscription_name => 'DEPT_SUB');
- END;
- / 2 3 4 5
- PL/SQL procedure successfully completed.
- SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT_VIEW;
- DEPTNO DNAME LOC
- ---------- -------------- -------------
- 51 hello world
此外,订阅者可以使用DBMS_CDC_SUBSCRIBE.PURGE_WINDOW过程purge不需要的数据,订阅者可以使用DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure停止订阅。
source端与target端的初始化参数的具体配置可以参考oracle官方文档,target端要注意配置java_pool_size与streams_pool_size。
更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12
评论暂时关闭