Oracle CDC Autolog配置步驟

1. 環境設定

1.1 網路連接（源庫和目標庫都要配置）運行Oracle Net Configuration Assistant添加一個本地服務名，也可以直接修改（tnsnames.ora）檔案；內容如下：PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <主機IP地址>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

H10G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <主機IP地址>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = h10g)
    )
  )1.2 初始參數1.2.1 設定兩庫密碼確保源庫和目標庫的sys使用者名稱密碼要一致；1.2.2 目標庫參數修改sqlplus / as sysdbashow parameter global_names;                                 /* 查看global_names的值 */alter system set global_names=true scope=spfile;       /* 如果global_names的為false的話，修改global_names為true，spfile為啟動生效 */show parameter java_pool_size;                                /* 查看java_pool_size當前的值 */alter system set java_pool_size=50M scope=spfile;     /* 如果當前值為0的話，設定java_pool_size為50M，否則可以增加50M */show parameter log_archive_dest_;                           /* 查看當前歸檔日誌使用的情況 */alter system set log_archive_dest_1 = 'location=<歸檔日誌路徑> mandatory reopen=5 valid_for=(online_logfile,primary_role)' scope=spfile;  /* 如果log_archive_dest_1沒有被使用的話，就使用log_archive_dest_1設定歸檔路徑 */alter system set log_archive_dest_2 = 'location=<遠程日誌歸檔路徑> mandatory valid_for=(standby_logfile,primary_role)' scope=spfile;        /* 如果log_archive_dest_2沒有被使用的話，就使用log_archive_dest_2設定遠程歸檔日誌的路徑 */show parameter remote_login_passwordfile;              /* 查看remote_login_passwordfile的值 */alter system set remote_login_passwordfile=shared scope=spfile;   /* 設定remote_login_passwordfile為shared:只有INTERNAL／SYS帳號能被密碼檔案識別，不允許他們以SYSOPER／SYSDBA的許可權登入 */show parameter streams_pool_size;                                             /* 查看stream_pool_size的值，如果不設定streams_pool_size參數，將用shared_pool_size池十分之一的記憶體 */alter system set streams_pool_size=50M scope=spfile;                   /* 可從v$sgastat中查看streams_pool_size池的使用，根據監控的結果，為streams_pool_size設定合適的值 *//* 啟用歸檔模式 */shutdown immediate;startup mount;alter database archivelog;alter database open;/* 切換歸檔模式，確認歸檔是否成功（在歸檔日誌路徑下面是否有歸檔日誌產生） */alter system switch logfile;/* 添加遠程歸檔日誌 */connect sys/admin@prod as sysdba;      /* 登陸到源庫 */select group#, bytes/1024/1024 size_mb from v$log;     /* 查看源庫的redo log組個數（假設為3）和大小（假設為50MB） */connect sys/admin as sysdba;      /* 登陸到目標庫 */select max(group#) from v$log;   /* 查看當前group的個數，假設當前為3 *//* 在目標庫建立的日誌個數要為源庫個數+1個，記錄檔大小要相同 */alter database add standby logfile group 4 ('<遠程記錄檔名全路徑>') size 50M;alter database add standby logfile group 5 ('<遠程記錄檔名全路徑>') size 50M;alter database add standby logfile group 6 ('<遠程記錄檔名全路徑>') size 50M;alter database add standby logfile group 7 ('<遠程記錄檔名全路徑>') size 50M;
1.2.3 源庫參數設定sqlplus / as sysdbashow parameter log_archive_dest_;                           /* 查看當前歸檔日誌使用的情況 */alter system set log_archive_dest_1 = 'location=<歸檔日誌路徑>' scope=both;  /* 如果log_archive_dest_1沒有被使用的話，就使用log_archive_dest_1設定歸檔路徑 */alter system set log_archive_dest_2='service=h10g lgwr async optional noregister reopen=5 valid_for=(online_logfile,primary_role)' scope=both;        /* 如果log_archive_dest_2沒有被使用的話，就使用log_archive_dest_2設定遠程歸檔目標 */show parameter remote_login_passwordfile;              /* 查看remote_login_passwordfile的值 */alter system set remote_login_passwordfile=shared scope=spfile;   /* 設定remote_login_passwordfile為shared:只有INTERNAL／SYS帳號能被密碼檔案識別，不允許他們以SYSOPER／SYSDBA的許可權登入 *//* 啟用歸檔模式，如果已經是歸檔模式，一下可以不用執行 */shutdown immediate;startup mount;alter database archivelog;alter database open;/* 切換歸檔模式，確認歸檔是否成功（在歸檔日誌路徑下面是否有歸檔日誌產生） */alter system switch logfile;2. 建立使用者2.1 源庫使用者sqlplus / as sysdbacreate user cdc_source identified by cdc_source default tablespace users temporary tablespace temp;   /* 建立使用者，源表擁有者 */grant connect, resource, select any table to cdc_source;    /* 賦予許可權 */2.2 目標庫使用者sqlplus / as sysdba
/* 建立發行者，並賦予許可權 */create user cdc_stg_pub identified by cdc_stg_pub default tablespace users temporary tablespace temp quota unlimited on system quota unlimited on users quote
unlimited on sysaux; grant create session, create table, create tablespace, create sequence, select_catalog_role, execute_catalog_role, dba to cdc_stg_pub;grant unlimited tablespace to cdc_stg_pub;grant execute on dbms_cdc_publish to cdc_stg_pub;execute dbms_streams_auth.grant_admin_privilege(grantee => 'cdc_stg_pub');/* 建立訂閱者，並賦予許可權 */create user cdc_stg_user identified by cdc_stg_user default tablespace users temporary tablespace temp; grant connect, resource to cdc_stg_user;3. CDC啟用3.1 建立源表sqlplus cdc_source/cdc_source;                                       /* 登陸來源資料庫 */create table emp as select * from scott.emp;                    /* 從scott.emp表建立emp表 */create table dept as select * from scott.dept;                    /* 從scott.dept表建立dept表 */alter table emp add supplemental log data (all) columns;    /* 為emp表添加supplemental log */alter table dept add supplemental log data (all) columns;    /* 為dept表添加supplemental log */sqlplus / as sysdba                    /* 登陸源庫 */set serveroutput on;                  /* 設定可以輸出顯示 *//* 擷取來源資料SCN值，並記錄以後使用 */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;//* 執行個體化每個表 */begin  dbms_capture_adm.prepare_table_instantiation(table_name => 'cdc_source.emp');  dbms_capture_adm.prepare_table_instantiation(table_name => 'cdc_source.dept');end;/select global_name form global_name;    /* 查詢來源資料庫的全名 */3.2 建立變化表/* 建立修改源 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */begin  dbms_cdc_publish.create_autolog_change_source(    change_source_name => 'emp_dept_src',    description                 => 'Emp and Dept source',    source_database        => '<來源資料庫的全名>',    first_scn                    => '<來源資料庫的SCN>',    online_log                 => 'y');end;/select source_name,   source_description,   source_type,  source_databasefrom change_sourceswhere source_name = 'EMP_DEPT_SRC';    /* 驗證是否建立成功，注意where中的條件區分大小寫 *//* 建立編變化集 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */begin  dbms_cdc_publish.create_change_set(    change_set_name           => 'emp_dept_set',    description                     => 'Emp and dept change set',    change_source_name     => 'emp_dept_src',    stop_on_ddl                   => 'y');end;/
/* 驗證是否建立成功 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */select set_name,  set_description,  change_source_name,  apply_name,  queue_name,  queue_table_namefrom change_setswhere publisher = 'CDC_STG_PUB'  and set_name = 'EMP_DEPT_SET';/
/* 檢查CDC */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */select app.apply_name,  q.name,  app.status,  qt.queue_tablefrom dba_apply app,  dba_queues q,  dba_queue_tables qtwhere app.apply_user = 'CDC_STG_PUB'  and q.owner = 'CDC_STG_PUB'  and qt.owner = 'CDC_STG_PUB'  and q.name = app.queue_name  and qt.queue_table = q.queue_table  and app.apply_name like '%EMP_DEPT%';
/* 建立變化表：emp表 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */begin  dbms_cdc_publish.create_change_table(    owner                        => 'cdc_stg_pub',    change_table_name    => 'emp_ct',    change_set_name       => 'emp_dept_set',    source_schema           => 'cdc_source',    source_table               => 'emp',    column_type_list         => 'empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), sal number(7,2), comm number(7,2), deptno number(2)',     capture_values           => 'both',    rs_id                         => 'y',    row_id                       => 'n',    user_id                      => 'n',    timestamp                  => 'y',    object_id                    => 'n',    source_colmap            => 'n',    target_colmap             => 'y',    options_string              => null);end;/
grant select on emp_ct to cdc_stg_user;                     /* 為訂閱者付許可權 */
/* 建立變化表：dept表 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */begin  dbms_cdc_publish.create_change_table(    owner                        => 'cdc_stg_pub',    change_table_name    => 'dept_ct',    change_set_name       => 'emp_dept_set',    source_schema           => 'cdc_source',    source_table               => 'dept',    column_type_list         => 'deptno number(2), dname varchar2(14), loc varchar2(13)',     capture_values           => 'both',    rs_id                         => 'y',    row_id                       => 'n',    user_id                      => 'n',    timestamp                  => 'y',    object_id                    => 'n',    source_colmap            => 'n',    target_colmap             => 'y',    options_string              => null);end;/grant select on dept_ct to cdc_stg_user;                     /* 為訂閱者付許可權 */
/* 驗證 */sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */select change_table_name,  change_set_name,  source_schema_name,  source_table_namefrom change_tableswhere change_table_schema = 'CDC_STG_PUB'  and change_set_name = 'EMP_DEPT_SET'order by change_table_name;/select streams_name,  streams_type,  table_owner,  table_name,  rule_type,  source_databasefrom dba_streams_table_ruleswhere rule_owner = 'CDC_STG_PUB'  and table_owner = 'CDC_SOURCE'order by table_name,  rule_type,  streams_type;/3.3 啟用CDCsqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */begin  dbms_cdc_publish.alter_change_set(    change_set_name => 'emp_dept_set',    enable_capture     => 'Y');end;/
/* 驗證 */select apply_name,  statusfrom dba_applywhere apply_user = 'CDC_STG_PUB'  and apply_name like '%EMP_DEPT%';/* 登陸源庫 */sqlplus sys/admin as sysdbaalter system switch logfile;/* 登陸目標庫，驗證 */select capture_name,  state,  total_messages_capturedfrom v$streams_capturewhere capture_name like '%EMP_DEPT%';select group#, thread#, sequence#, archived, status from v$standby_log;3.4 建立訂閱sqlplus cdc_stg_user/cdc_stg_userbegin  dbms_cdc_subscribe.create_subscription(    change_set_name            => 'emp_dept_set',    description                       => 'Emp and dept change subscription',    subscription_name            => 'emp_dept_sub1');end;/begin  dbms_cdc_subscribe.subscribe(    subscription_name        => 'emp_dept_sub1',    source_schema            => 'cdc_source',    source_table                => 'emp',    column_list                  => 'empno, ename, job, mgr, sal, comm, deptno',    subscriber_view           => 'emp_chg_view');end;/begin  dbms_cdc_subscribe.subscribe(    subscription_name        => 'emp_dept_sub1',    source_schema            => 'cdc_source',    source_table                => 'dept',    column_list                  => 'deptno, dname, loc',    subscriber_view           => 'dept_chg_view');end;/
/* 啟用訂閱 */begin  dbms_cdc_subscribe.activate_subscription(
    subscription_name => 'emp_dept_sub1');end;
/* 驗證 */select s.subscription_name,  s.set_name,  s.description,  st.source_schema_name,  st.source_table_name,  st.view_name,  sc.column_namefrom user_subscriptions s,  user_subscribed_tables st,  user_subscribed_columns scwhere s.subscription_name = 'EMP_DEPT_SUB1'  and st.handle   = s.handle  and sc.handle   = s.handle  and st.source_schema_name  = sc.source_schema_name  and st.source_table_name      = sc.source_table_nameorder by st.source_schema_name,  st.source_table_name, sc.column_name;4. 查詢變化資料4.1 查看變化表中的變化資料sqlplus cdc_stg_pub/cdc_stg_pub             /* 以發行者身份登陸目標庫 */select operation$ operation, to_char(timestamp$, 'dd-mon-yyyy hh24:mi:ss') this_time, empno, ename, sal, comm from emp_ct order by timestamp$;select operation$ operation, to_char(timestamp$, 'dd-mon-yyyy hh24:mi:ss') this_time, deptno, dname, loc from dept_ct order by timestamp$;4.2 訂閱者視圖查看sqlplus cdc_stg_user/cdc_stg_user             /* 以訂閱者身份登陸目標庫 */begin  dbms_cdc_subscribe.extend_window(    subscription_name => 'emp_dept_sub1');end;//* 驗證 */select operation$ operation, to_char(timestamp$, 'dd-mon-yyyy hh24:mi:ss') this_time, empno, ename, sal, comm from emp_chg_view order by timestamp$;select operation$ operation, to_char(timestamp$, 'dd-mon-yyyy hh24:mi:ss') this_time, deptno, dname, loc from dept_chg_view order by timestamp$;
/* 刪除變化集 */begin
  dbms_cdc_subscribe.purge_window(
  subscription_name => 'emp_dept_sub1');end;/select operation$ operation, to_char(timestamp$, 'dd-mon-yyyy hh24:mi:ss') this_time, deptno, dname, loc from dept_chg_view order by timestamp$;