DBMS_CDC_PUBLISH:capture and publish change data from one or more Oracle relational source tables

于 2013-03-31 18:19:34 发布
阅读量1.8k 收藏
点赞数
分类专栏： Oracle Oracle EBS

oracle学习--CDC 研究(2)

      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
from: http://hi.baidu.com/dbsqlmaster/item/15a5ef48ab3f56af60d7b9ff
 
 
refer:
DBMS_CDC_PUBLISH:
http://docs.oracle.com/cd/B13789_01/appdev.101/b10802/d_logpub.htm
aicon
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值