9.中间DB的PUBLISHER:创建CHANGE SET
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;
10.中间DB的PUBLISHER:创建CHANGE TABLE
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'staging_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', -- 如果是9.2或者10.1版本,必须设置为n
user_id => 'n', -- 如果是9.2或者10.1版本,必须设置为n
timestamp => 'n',
object_id => 'n', -- 必须设置为n
source_colmap => 'n', -- 必须设置为n
target_colmap => 'y',
options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
11.中间DB的PUBLISHER:enable change source
BEGIN
DBMS_CDC_PUBLISH.ALTER_HOTLOG_CHANGE_SOURCE(
change_source_name => 'CHICAGO',
enable_source => 'Y');
END;
12.中间DB的PUBLISHER:enable change set
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
13.中间DB的PUBLISHER:授权给SUBSCRIBER
GRANT SELECT ON staging_cdcpub.products_ct TO subscriber1;
4.执行异步autolog publishing
1)源DB和中间DB都是单独的系统
2)步骤: -- 假定两个DB的DBA和PUBLISHER都是单独的
1.Source Database DBA:准备从源DB拷贝redo log file参数
1)AUTOLOG ONLINE
compatible = 10.2.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="arch1_%s_%t_%r.dbf"
remote_login_passwordfile=shared
2)AUTOLOG ARCHIVE
compatible = 10.2.0
log_archive_dest_1="location=/oracle/dbs mandatory reopen=5"
log_archive_dest_2 = "service=stagingdb arch optional noregister reopen=5
template=/usr/oracle/dbs/arch1_%s_%t_%r.dbf"
log_archive_dest_state_1 = enable
log_archive_dest_state_2 = enable
log_archive_format="arch1_%s_%t_%r.dbf"
remote_login_passwordfile=shared
2.Staging Database DBA:设置DB初始化参数
1)AUTOLOG ONLINE
compatible = 10.2.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=/oracle/stdby mandatory
valid_for=(standby_logfile,primary_role)"
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format="arch2_%s_%t_%r.dbf"
job_queue_processes = 2
parallel_max_servers = <current_value> + 5
processes = <current_value> + 7
remote_login_passwordfile = shared
sessions = <current value> + 2
streams_pool_size = <current_value> + 21 MB
undo_retention = 3600
2)AUTOLOG ARCHIVE
compatible = 10.2.0
global_names = true
java_pool_size = 50000000
job_queue_processes = 2
parallel_max_servers = <current_value> + 5
processes = <current_value> + 7
remote_login_passwordfile = shared
sessions = <current value> + 2
streams_pool_size = <current_value> + 21 MB
undo_retention = 3600
3.Source Database DBA:修改源DB
1)ALTER DATABASE FORCE LOGGING;
2)ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- 必须的
3)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;
4.Staging Database DBA:创建备用REDO LOG FILE -- 只对AutoLog online设置
1)确定源DB大小,因为备用REDO LOG FILE必须等于或者大于源DB的REDO LOG FILE,可以查询V$LOG看大小尺寸
2)确定在中间DB上需要多少备用REDO LOG 组
3)ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/oracle/dbs/slog3a.rdo', '/oracle/dbs/slog3b.rdo') SIZE 500M; -- 增加备用LOG FILE
SELECT * FROM V$STANDBY_LOG -- 查询是否创建成功
5.Staging Database DBA:创建和授权PUBLISHER
CREATE USER cdcpub IDENTIFIED BY cdcpub 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.Source Database DBA:建立LogMiner数据字典
1)设置数据字典定义,只是在开始之前扑捉数据变化
2)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;
-- 值将使用到STEP 9中的first scn
7.Source Database DBA:准备SOURCE TABLE
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
TABLE_NAME => 'sh.products');
END;
8.Source Database DBA:获得SOURCE DB的GLOBAL NAME
SELECT GLOBAL_NAME FROM GLOBAL_NAME;
9.Staging Database Publisher:识别每个CHANGE SOURCE DB 和创建 CHANGE SOURCE
SELECT * FROM V$ARCHIVED_LOG;
1)AUTOLOG ONLINE
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;
2)AUTOLOG ARCHIVE
BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_source_name => 'CHICAGO',
description => 'test source',
source_database => 'HQDB',
first_scn => 207722);
END;
10.Staging Database Publisher:创建CHANGE SET
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;
11.Staging Database Publisher:创建CHANGE table
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;
12.Staging Database Publisher:enable change set
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
13.Source Database DBA:在SOURCE DB上转换REDO LOG FILE
ALTER SYSTEM SWITCH LOGFILE;
14.Staging Database Publisher:授权给SUBSCRIBER
GRANT SELECT ON cdcpub.products_ct TO subscriber1;
7)Subscribing to Change Data -- 需要使用SUBSCRIBER 用户
1)指定SOURCE TABLE 和想要的COLUMN
2)指定想要的PUBLICATION ID 和 COLUMN
3)步骤:
1.查找SOURCE TABLE,并且对于SUBSCRIBER有访问权限
SELECT * FROM ALL_SOURCE_TABLES;
2.查找CHANGE SET NAME 和COLUMN ,对于SUBSCRIBER有访问权限
select * from ALL_PUBLISHED_COLUMNS;
3.创建SUBSCRIPTION
BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_set_name => 'CHICAGO_DAILY',
description => 'Change data for PRODUCTS',
subscription_name => 'SALES_SUB');
END;
4.在源表上订阅SOURCE TABLE 和 列
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;
如果需要订阅的COLUMN的PUB_ID不同,需要分别指定:
BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'MULTI_PUB',
publication_id => 34885, -- 使用STEP 2查找出来的
column_list => 'PROD_ID, PROD_NAME', -- 相同字段,但是不同PUB_ID
subscriber_view => 'prod_idname');
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'MULTI_PUB',
publication_id => 34883,
column_list => 'PROD_ID, PROD_LIST_PRICE',
subscriber_view => 'prod_price');
END;
5.激活SUBSCRIPTION
BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
subscription_name => 'SALES_SUB');
END;
6.获得下一组变化的数据
BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => 'SALES_SUB');
END;
7.读取和插叙SUBSCRIBER 的内容
select * from cdcpub.SALES_VIEW;
8.删除不再需要的变化数据
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name => 'SALES_SUB');
END;
9.重复做STEP 6 TO STEP 8
10.结束SUBSCRIPTION,这是必须的
BEGIN
DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(
subscription_name => 'SALES_SUB');
END;
4.管理PUBLISHER DATA
1)管理异步 CHANGE SOURCE
1.Enabling And Disabling Asynchronous Distributed HotLog Change Sources
BEGIN
DBMS_CDC_PUBLISH.ALTER_HOTLOG_CHANGE_SOURCE(
change_source_name => 'PRODUCTS_SOURCE',
enable_source => 'y'); -- no 代表disble
END;
2)Managing Asynchronous Change Sets
1.Creating Asynchronous Change Sets with Starting and Ending Dates -- HotLog and AutoLog,可以指定开始和结束时间,Distributed HotLog不可指定时间
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => 'PRODUCTS_SET',
description => 'Products Application Change Set',
change_source_name => 'HQ_SOURCE',
stop_on_ddl => 'Y',
begin_date => sysdate+2);
END;
2.Enabling and Disabling Asynchronous Change Sets -- 同步CHANGE SET不能被关闭,始终处于开启状态
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'PRODUCTS_SET',
enable_capture => 'y'); -- n 代表关闭
END;
3.Stopping Capture on DDL for Asynchronous Change Sets
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'PRODUCTS_SET',
stop_on_ddl => 'y'); -- n 表示不停止
END;
-- 下列DDL语句,是当stop_on_ddl设置为Y的时候,不引起扑捉变化数据的:
ANALYZE TABLE
LOCK TABLE
GRANT privileges to access a table
REVOKE privileges to access a table
COMMENT on a table
COMMENT on a column
4.Recovering from Errors Returned on Asynchronous Change Sets
ORA-31514: change set disabled due to capture error -- 产生的错误
DBA_APPLY_ERROR -- 查看error信息
1)An Error Due to Running Out of Disk Space -- 由于耗尽磁盘空间导致的错误
SELECT ERROR_MESSAGE FROM DBA_APPLY_ERROR WHERE APPLY_NAME =(SELECT APPLY_NAME FROM CHANGE_SETS WHERE SET_NAME ='CHICAGO_DAILY');
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
recover_after_error => 'y');
END; -- 恢复CHANGE SET,恢复成功的时候,必须enable CHANGE SET ,
2)An Error Due to Stopping on DDL -- 在DDL上导致的错误
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'PRODUCTS_SET',
recover_after_error => 'y',
remove_ddl => 'y');
END;
5.Managing Change Tables
1)Creating Change Tables
1.不在系统表空间创建 CHANGE SET
2.第一,创建所有的CHANGE SET在一个单独的表空间;第二,允许PUBLISHER对每个CHANGE SET指定不同的表空间
CREATE USER cdcpub DEFAULT TABLESPACE ts_cdcpub;
2)Understanding Change Table Control Columns -- 包括元数据和它自己本身的数据
3)Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values
SELECT * FROM ALL_TAB_COLUMNS;
4)Controlling Subscriber Access to Change Tables
1.授予SELECT权限给SUBSCRIBER
2.最好不要授予DML权限给SUBSCRIBER
5)Purging Change Tables of Unneeded Data
1.SUBSCRIBER
DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW
2.CDC
DBMS_JOB
DBMS_CDC_PUBLISH.PURGE
3.PUBLISHER
DBMS_CDC_PUBLISH.PURGE -- 在中间DB上清除所有的CHANGE TABLES,相当于自动执行PURGE操作
DBMS_CDC_PUBLISH.PURGE_CHANGE_SET -- 在命名的CHANGE SET里清除所有的CHANGE TABLE
DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE -- 清除命名的CHANGE TABLE
4.Dropping Change Tables
DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE -- 不可使用DROP TABLE删除CHANGE TABLE
6.Exporting and Importing Change Data Capture Objects Using Oracle Data Pump -- 导入导出 CDC object
1)Restrictions on Using Oracle Data Pump with Change Data Capture
2)Examples of Oracle Data Pump Export and Import Commands
3)Publisher Considerations for Exporting and Importing Change Tables
4)Re-Creating AutoLog Change Data Capture Objects After an Import Operation
oracle学习--CDC 研究(2)
refer:
DBMS_CDC_PUBLISH: