共計 8770 個字符，預計需要花費 22 分鐘才能閱讀完成。
今天就跟大家聊聊有關 Oracle_CDC 該怎么部署，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。
1. CDC 的實施步驟 (異步在線日志)1.1. 數據庫初始化
SQL
alter system set
job_queue_processes = 100;
alter system set
java_pool_size = 50m;
alter system set
streams_pool_size=50m;
alter system set
undo_retention=3600;
alter database
force logging;
alter database
add supplemental log data;
select
LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
1.2. 準備測試表
SQL
create table scott.test(id
int,name varchar2(30),mark varchar2(50));
1.3. 創建發布者
SQL
conn / as
sysdba;
create
tablespace cdc_tbsp;
create user
cdc_publisher identified by cdc_publisher default tablespace cdc_tbsp temporary
tablespace temp;
GRANT CREATE
SESSION TO cdc_publisher;
GRANT CREATE TABLE
TO cdc_publisher;
grant create
sequence TO cdc_publisher;
grant create
procedure TO cdc_publisher;
grant create any
job TO cdc_publisher;
GRANT CREATE
TABLESPACE TO cdc_publisher;
GRANT UNLIMITED
TABLESPACE TO cdc_publisher;
GRANT
SELECT_CATALOG_ROLE TO cdc_publisher;
GRANT
EXECUTE_CATALOG_ROLE TO cdc_publisher;
GRANT EXECUTE ON
DBMS_CDC_PUBLISH TO cdc_publisher;
grant execute ON
dbms_lock TO cdc_publisher;
execute
dbms_streams_auth.grant_admin_privilege(CDC_PUBLISHER
grant all on
scott.test to cdc_publisher;
grant dba to
cdc_publisher;
1.4. 創建訂閱者
SQL
create user
cdc_subscriber identified by cdc_subscriber default tablespace cdc_tbsp
temporary tablespace temp;
grant create
session TO cdc_subscriber;
grant resource
to cdc_subscriber;
grant connect to
cdc_subscriber;
GRANT CREATE
TABLE TO cdc_subscriber;
GRANT CREATE
VIEW TO cdc_subscriber;
GRANT UNLIMITED
TABLESPACE TO cdc_subscriber;
1.5. 發布數據 1.5.1. 發布 - 準備源表
SQL
conn
cdc_publisher/cdc_publisher;
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME
= scott.test
END;
/
1.5.2. 發布 - 創建變更集
SQL
conn
cdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name
= CDC_SCOTT_TEST ,
description
= Change set for product info ,
change_source_name
= HOTLOG_SOURCE ,
stop_on_ddl
= y
END;
/
注意：
change_source_name 參數:
同步模式中必須為：SYNC_SOURCE
異步在線日志模式必須為：HOTLOG_SOURCE
1.5.3. 發布 - 創建變更表
SQL
conn
cdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner = cdc_publisher ,
change_table_name = cdc_test ,
change_set_name = CDC_SCOTT_TEST ,
source_schema = SCOTT ,
source_table = TEST ,
column_type_list = ID NUMBER(5), NAME VARCHAR2(30),MARK
VARCHAR2(50) ,
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 CDC_TBSP
END;
/
注意：
owner 是指發布用戶
source_schema 是源表所屬用戶
同步模式需加參數 ddl_markers
= n
options_string 指定改變表的存儲參數，可以使用除 partition 以外的所有 create table 中指定的存儲參數，如 tablespace、pctfree 等。
1.5.4. 發布 - 激活變更集
SQL
conn
cdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name = CDC_SCOTT_TEST ,
enable_capture = y
END;
/
1.5.5. 授權變更表給訂閱者
conn
cdc_publisher/cdc_publisher;
grant select
on cdc_test to cdc_subscriber;
備注：
到此 cdc_subscriber 用戶已經可以檢測到 scott.test 表的變更了
測試：
$ sqlplus
scott/tiger
SQL
insert into scott.test
values(1, beijing , 11
commit;
update scott.test
set name= shanghai where id=1;
commit;
delete scott.test
where id=1;
commit;
SQL
conn
cdc_subscriber/cdc_subscriber
SQL select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;
OPERATION$
COMMIT_TIMESTAMP$ ID NAME
MARK
———-
———- —— ———– ——————————
I 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
備注：operation 的意思
此列中的值可以是下列任何一個腳 1:
I: 指示此行表示插入操作
: 指示此行表示以下情況下更新的源表行的前映像: UO
異步更改數據捕獲
當更改表包括基于主鍵的對象 ID, 而不是主鍵的捕獲列已更改時, 同步更改數據捕獲。
UU: 指示此行表示更新的源表行的前圖像, 用于同步更改數據捕獲, 而不是由. UO.
UN: 指示此行表示更新的源表行的后映像。
D: 指示此行表示刪除操作。
當發布者發布了相關的改變表后，會生成一個惟一的發布 id(publication ID)，可以查閱視圖 ALL_PUBLISHED_COLUMNS 以獲取已經發布的表及字段信息
SQL conn
CDC_PUBLISHER/CDC_PUBLISHER;
select
change_set_name,pub_id,source_table_name from ALL_PUBLISHED_COLUMNS ;
CHANGE_SET_NAME PUB_ID SOURCE_TABLE_NAME
——————————
———- ——————————
CDC_SCOTT_TEST 91956 TEST
CDC_SCOTT_TEST 91956 TEST
CDC_SCOTT_TEST 91956 TEST
1.6. 訂閱 1.6.1. 訂閱 - 創建訂閱集
SQL
conn
cdc_subscriber/cdc_subscriber
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name= CDC_SCOTT_TEST ,
description= cdc scott subx ,
subscription_name= CDC_SCOTT_SUB
END;
/
備注：
一次訂閱與改變集對應, 由于改變集與源表之間是一對多的關系, 所以一次訂閱就可以訂閱多張表.
1.6.2. 訂閱 - 開始訂閱數據
SQL
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name= CDC_SCOTT_SUB ,
source_schema= SCOTT ,
source_table= TEST ,
column_list= ID, NAME,MARK ,
subscriber_view= TEST_TEMP
END;
/
SQL select
view_name,text from user_views;
VIEW_NAME TEXT
—————
—————————————-
TEST_TEMP SELECT
OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, DDLDES
備注：
如果改變集中有多個表, 需要操作多次
1.6.3. 訂閱 - 激活訂閱
SQL
BEGIN
dbms_cdc_subscribe.activate_subscription(
subscription_name= CDC_SCOTT_SUB
END;
/
1.6.4. 訂閱 - 擴展訂閱窗口
SQL
conn
cdc_subscriber/cdc_subscriber;
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name= CDC_SCOTT_SUB
END;
/
備注：
訂閱調用 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW 過程取得改變數據的集合, 如果第一次執行, 就取得激活訂閱后所有改變數據. 每次執行 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW 后, 擴展窗口只看到上次執行 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW 至今的數據.
1.6.5. 查看訂閱內容
SQL
conn
cdc_subscriber/cdc_subscriber;
SQL select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$
COMMIT_TIMESTAMP$ ID NAME MARK
———-
—————– —— ——————————
————————————————–
I 2018/2/8 20:04:58 1
beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
1.7. 測試 1.7.1. 源表變更
SQL conn
scott/tiger;
insert into test
values(2, renqinglei , aa
commit;
update test set
mark= tt where id=2;
commit;
delete test
where id=2;
commit;
1.7.2. 查詢數據發布情況
SQL conn
cdc_publisher/cdc_publisher
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;
OP
COMMIT_TIMESTAMP$ ID NAME MARK
—
—————— ———- ——————————
I 13-JAN-16 1 beijing 11
UO
13-JAN-16 1
beijing 11
UN
13-JAN-16 1
shanghai 11
D 13-JAN-16 1 shanghai 11
I 13-JAN-16 2 renqinglei aa
UO
13-JAN-16 2
renqinglei aa
UN
13-JAN-16 2
renqinglei tt
D 13-JAN-16 2 renqinglei tt
1.7.3. 查詢數據訂閱情況
SQL conn
cdc_subscriber/cdc_subscriber
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OP
COMMIT_TIMESTAMP$ ID NAME MARK
—
—————— ———- ——————————
I 13-JAN-16 1 beijing 11
UO
13-JAN-16 1
beijing 11
UN
13-JAN-16 1 shanghai 11
D 13-JAN-16 1 shanghai 11.
1.7.4. 發現訂閱的數據沒有變化, 擴展一下訂閱窗口:
SQL conn
cdc_subscriber/cdc_subscriber
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name= CDC_SCOTT_SUB
END;
/
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$
COMMIT_TIMESTAMP$ ID
NAME MARK
———-
—————– —— —————————— ———-
I 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
I 2018/2/8 20:26:01 2 renqinglei aa
UO 2018/2/8 20:26:01 2 renqinglei aa
UN 2018/2/8 20:26:01 2 renqinglei tt
D 2018/2/8 20:26:01 2 renqinglei tt
1.7.5. 清除變更數據集
SQL conn
cdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name = CDC_SCOTT_SUB
END;
/
查看訂閱數據為空
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
備注:
擴展窗口的數據可以進行清空操作，避免改變數據過多帶來的系統負載。
1.7.6. 重新生成變化數據
conn scott/tiger;
insert into test
values(3, shandong , hh
insert into test
values(4, diankeyuan , hh
commit;
查看發布信息
conn
cdc_publisher/cdc_publisher
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;
OPERATION$
COMMIT_TIMESTAMP$ ID NAME MARK
———-
—————– —— —————————— —————— 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
I 2018/2/8 20:26:01 2 renqinglei aa
UO 2018/2/8 20:26:01 2 renqinglei aa
UN 2018/2/8 20:26:01 2 renqinglei tt
D 2018/2/8 20:26:01 2 renqinglei tt
I 2018/2/8 20:33:48 3 shandong hh
I 2018/2/8 20:33:48 4 diankeyuan hh
SQL conn
cdc_subscriber/cdc_subscriber;
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name= CDC_SCOTT_SUB
END;
/
查看訂閱信息
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$
COMMIT_TIMESTAMP$ ID NAME MARK
———-
———- —— ————– ———————————
I 2018/2/8 20:33:48 3 shandong hh
I 2018/2/8 20:33:48 4 diankeyuan hh
1.7.7. 刪除發布的數據
SQL
conn
cdc_publisher/cdc_publisher
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;
OP
COMMIT_TIMESTAMP$ ID NAME MARK
—
—————— ———- ——————————
I 13-JAN-16 1 beijing 11
UO 13-JAN-16 1 beijing 11
UN
13-JAN-16 1
shanghai 11
D 13-JAN-16 1 shanghai 11
I 13-JAN-16 2 renqinglei aa
UO
13-JAN-16 2
renqinglei aa
UN
13-JAN-16 2
renqinglei tt
D 13-JAN-16 2 renqinglei tt
I 13-JAN-16 3 shandong hh
I 13-JAN-16 4 diankeyuan hh
rows selected.
不可 truncate
SQL truncate
cdc_test;
ERROR at line 1:
ORA-03290:
Invalid truncate command – missing CLUSTER or TABLE keyword
刪除后無記錄
SQL
delete cdc_test;
commit;
select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;
看完上述內容，你們對 Oracle_CDC 該怎么部署有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。
