cdc捕获数据变更

cdc捕获数据有2中方式：
1同步方式
这种方式的变更是通过触发器实现的，这种模式下捕获变更数据是作为事务修改源表的一部分的。
2异步方式
这种方式是通过redolog，cdc在dml语句提交后捕获，不作为事务的一部分。因此对事务来说没有影响。

异步的模式有3中情况，我们只关心autolog online的模式

publisher必须要有下面的权限：
EXECUTE_CATALOG_ROLE privilege
SELECT_CATALOG_ROLE privilege
CREATE TABLE and CREATE SESSION privileges
EXECUTE on the DBMS_CDC_PUBLISH package
对于异步的hotlog模式，还需要下面的权限：
Be granted the CREATE SEQUENCE privilege

Be granted the DBA role

Be the GRANTEE specified in a DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE subprogram issued by the staging database DBA
在staging库上建议为publisher创建单独的表空间。
下面是在autolog模式下，参考
http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm#DWHSG8425

staging参数参考官网文档
异步autolog这种方式发布
cdc使用了stream的downstream来实现，staging 数据库是作为下游数据库，步骤：
1配置源到staging数据库的网络
2设置源上的参数
The following is an example for the AutoLog online option:

compatible = 11.0
log_archive_dest_1 =”location=/oracle/dbs mandatory reopen=5”
log_archive_dest_2 =”service=stagingdb lgwr async optional noregister reopen=5
valid_for=(online_logfile,primary_role)”
log_archive_dest_state_1 = enable
log_archive_dest_state_2 = enable
log_archive_format=”arch_%s_%t_%r.dbf”
2staging dba设置staging库上的参数：
The following is an example for the AutoLog online option:

compatible = 11.0
global_names = true
java_pool_size = 50000000
log_archive_dest_1=”location=/oracle/dbs mandatory reopen=5
valid_for=(online_logfile,primary_role)”
log_archive_dest_2=”location=/usr/oracle/dbs mandatory
valid_for=(standby_logfile,primary_role)”
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=”arch_%s_%t_%r.dbf”
job_queue_processes = 2
parallel_max_servers = + 5
processes = + 7
sessions = + 2
streams_pool_size = + 21 MB
undo_retention = 3600

3设置源数据库
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
在源表上设置要捕获列的日志组
ALTER TABLE sh.products
ADD SUPPLEMENTAL LOG GROUP log_group_products
(PROD_ID, PROD_NAME, PROD_LIST_PRICE) ALWAYS;
如果是要捕获所有的列，那么使用下面的语句
ALTER TABLE sh.products ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
4staging dba，创建standby log
创建的standby log要跟redo log大小一致，2决定standby log的日志组，standby的日志主要比redo log的日志组多一组以上。
3使用下面的命令添加standby 日志组
ALTER DATABASE ADD STANDBY LOGFILE GROUP 3
(‘/oracle/dbs/slog3a.rdo’, ‘/oracle/dbs/slog3b.rdo’) SIZE 500M;

可以查看v$standby_log看standby的信息
5staging给publisher授权
CREATE USER cdcpub IDENTIFIED EXTERNALLY DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT DBA TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
GRANT EXECUTE on DBMS_CDC_PUBLISH TO cdcpub;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => ‘cdcpub’);

6源库dba，构建logmnr的数据字典
cdc在源库上表结构变更的时候，会自动的去更新表结构的变更，保证同步。
先获取scn的值
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;
/
7源库上准备源表
cdc的异步不支持下面的表类型
BFILE
LONG
ROWID
UROWID
object types (for example, XMLType)
虚拟列也不被支持，也不支持引号标识符，关键字

对于下面的表结构也不支持：
源表是临时表
源表是对象表
包含不被支持列的索引组织表，或包含溢出段的索引组织表。
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
TABLE_NAME => ‘sh.products’);
END;
/

8源库dba，获取源库的全局名称
SELECT GLOBAL_NAME FROM GLOBAL_NAME;
9staging数据库上发布者，标识每个变更源库创建变更源
BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_source_name => ‘CHICAGO’,
description => ‘test source’,
source_database => ‘HQDB’,
first_scn => 207722,
online_log => ‘y’);
END;
/
10 staging数据库创建变更集合
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => ‘CHICAGO_DAILY’,
description => ‘change set for product info’,
change_source_name => ‘CHICAGO’,
stop_on_ddl => ‘y’);
END;
/

11staging库创建变更表
publisher为源库上published的表创建一个或多个变更表，指定哪些字段被捕获
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => ‘cdcpub’,
change_table_name => ‘products_ct’,
change_set_name => ‘CHICAGO_DAILY’,
source_schema => ‘SH’,
source_table => ‘PRODUCTS’,
column_type_list => ‘PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50),
PROD_LIST_PRICE NUMBER(8,2)’,
JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)’,
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 => ‘TABLESPACE TS_CHICAGO_DAILY’);
END;
/
12staging数据库，启用变更集合
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => ‘CHICAGO_DAILY’,
enable_capture => ‘y’);
END;
/
13源库，切换日志
ALTER SYSTEM SWITCH LOGFILE;
14staging 库，授权给订阅者
GRANT SELECT ON cdcpub.products_ct TO subscriber1;

订阅者查看数据库变更
1查看有哪些授权的表
select * from dba_source_tables;

2查看授权的变更集合的名字
SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM DBA_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME =’SH’ AND SOURCE_TABLE_NAME = ‘PRODUCTS’;

3创建一个订阅，指定变更集合
BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_set_name => ‘CHICAGO_DAILY’,
description => ‘Change data for PRODUCTS’,
subscription_name => ‘SALES_SUB’);
END;
/
4订阅表，字段
BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => ‘SALES_SUB’,
source_schema => ‘SH’,
source_table => ‘PRODUCTS’,
column_list => ‘PROD_ID, PROD_NAME, PROD_LIST_PRICE’,
subscriber_view => ‘SALES_VIEW’);
END;
/
5激活订阅
BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
subscription_name => ‘SALES_SUB’);
END;
/
6获取下一个变更集合
BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => ‘SALES_SUB’);
END;
/
7读取并查询订阅视图的内容
SELECT PROD_ID, PROD_NAME, PROD_LIST_PRICE FROM SALES_VIEW;

PROD_ID PROD_NAME PROD_LIST_PRICE

---

30 And 2 Crosscourt Tee Kids 14.99
30 And 2 Crosscourt Tee Kids 17.66
10 Gurfield& Murks Pleated Trousers 17.99
10 Gurfield& Murks Pleated Trousers 21.99
8清除变更数据
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name => ‘SALES_SUB’);
END;
/
9取消订阅
BEGIN
DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(
subscription_name => ‘SALES_SUB’);
END;
/