CDC异步AutoLog的简单测试

最新推荐文章于 2023-11-22 11:33:53 发布

原创最新推荐文章于 2023-11-22 11:33:53 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #database #user #system #schema #list

本文介绍了一个使用 Oracle 数据库实现变更数据捕获 (CDC) 的详细案例。从创建用户、分配权限到建立变更数据捕获环境，包括源端和目标端的具体步骤。演示了如何通过 SQL 语句设置补充日志、创建变更表、订阅变更集并激活订阅。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在源端：

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Feb 27 14:32:18 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user cdc_source
2 identified by cdc_source
3 default tablespace users
4 temporary tablespace temp ;

User created.

SQL> grant connect, resource, select any table to cdc_source ;

Grant succeeded.

SQL> create user cdc_source_pub
2 identified by cdc_source_pub
3 default tablespace users
temporary tablespace temp
4 5 quota unlimited on system
6 quota unlimited on users ;
create user cdc_source_pub
*
ERROR at line 1:
ORA-01920: user name 'CDC_SOURCE_PUB' conflicts with another user or role name

SQL> alter user cdc_source_pub quota unlimited on sysaux ;

User altered.

SQL> grant create session, create table, create database link,
2 select_catalog_role, execute_catalog_role, dba to
3 cdc_source_pub ;

Grant succeeded.

SQL> grant execute on dbms_aqadm to cdc_source_pub ;

Grant succeeded.

SQL> grant execute on dbms_capture_adm to cdc_source_pub ;

Grant succeeded.

SQL> grant execute on dbms_apply_adm to cdc_source_pub ;

Grant succeeded.

SQL> grant execute on dbms_propagation_adm to cdc_source_pub ;

Grant succeeded.

SQL> grant execute on dbms_streams_adm to cdc_source_pub ;

Grant succeeded.

SQL> begin
2 dbms_rule_adm.grant_system_privilege (
3 privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
4 grantee => 'cdc_source_pub',
5 grant_option => FALSE);
6 dbms_rule_adm.grant_system_privilege (
7 privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
8 grantee => 'cdc_source_pub',
9 grant_option => FALSE);
10 END;
11 /

PL/SQL procedure successfully completed.

SQL> conn CDC_SOURCE_PUB/cdc_source_pub
Connected.
SQL>
SQL> create database link owb.sun.net
2 connect to cdc_stg_pub
3 identified by cdc_stg_pub
4 using 'owb.sun.net' ;

Database link created.

SQL> select * from dual@owb.sun.net;

D
-
X

SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.SUN.NET

SQL> conn CDC_SOURCE/CDC_SOURCE
Connected.
SQL> create table emp as select * from scott.emp ;
create table dept as select * from scott.dept ;
Table created.

SQL>

Table created.

SQL> -- ensure empno is always logged for updates against emp
SQL> alter table emp add supplemental log group log_group_emp(empno)
2 always ;
-- ensure deptno is always logged for updates against dept
alter table dept add supplemental log group
log_group_dept(deptno) always ;
Table altered.

SQL> SQL> 2

Table altered.

SQL> show user
USER is "CDC_SOURCE"
SQL> update emp
2 set sal = 1.1 * sal
3 where job = 'SALESMAN' ;

4 rows updated.

SQL> commit ;

Commit complete.

SQL> insert into emp values
2 (8000, 'DOE', 'ANALYST', 7839, trunc(sysdate), 4000, null, 10)
3 /

1 row created.

SQL> -- Close the operations department
SQL> delete from dept where dname = 'OPERATIONS' ;

1 row deleted.

SQL> commit ;

Commit complete.

SQL> update emp
2 set comm = 0.05 * sal
3 where job = 'CLERK'
4 and deptno =
5 (select deptno from dept where loc = 'DALLAS') ;

2 rows updated.

SQL> commit ;

Commit complete.

*********************************************************************

在目标端：

$ sqlplus sys/sys@owb as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Feb 27 14:35:12 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user cdc_stg_pub
2 identified by cdc_stg_pub
3 default tablespace users
4 temporary tablespace temp
5 quota unlimited on system
6 quota unlimited on users
7 quota unlimited on sysaux ;

User created.

SQL> grant create session, create table, create sequence,
2 select_catalog_role, execute_catalog_role, create database
3 link, dba to cdc_stg_pub ;
grant execute on dbms_aqadm to cdc_stg_pub ;

Grant succeeded.

SQL> grant execute on dbms_capture_adm to cdc_stg_pub ;

Grant succeeded.

SQL>
Grant succeeded.

SQL> grant execute on dbms_apply_adm to cdc_stg_pub ;

Grant succeeded.

SQL> grant execute on dbms_propagation_adm to cdc_stg_pub ;

Grant succeeded.

SQL> grant execute on dbms_streams_adm to cdc_stg_pub ;

Grant succeeded.

SQL> begin
2 dbms_rule_adm.grant_system_privilege (
3 privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
4 grantee => 'cdc_stg_pub',
5 grant_option => FALSE);
6 dbms_rule_adm.grant_system_privilege (
7 privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
8 grantee => 'cdc_stg_pub',
9 grant_option => FALSE);
10 end ;
11 /

PL/SQL procedure successfully completed.

SQL> create user cdc_stg_user
2 identified by cdc_stg_user
3 default tablespace users
4 temporary tablespace temp ;

User created.

SQL> grant connect, resource to cdc_stg_user ;

Grant succeeded.

SQL> conn CDC_STG_PUB/CDC_STG_PUB@owb
Connected.
SQL> create database link orcl.sun.net
2 connect to cdc_source_pub
3 identified by cdc_source_pub
4 using 'orcl.sun.net' ;

Database link created.

SQL> select * from dual@orcl.sun.net;

D
-
X

SQL> begin
2 dbms_cdc_publish.create_hotlog_change_source(
3 change_source_name => 'emp_dept_src',
4 description => 'EMP and DEPT source',
5 source_database => 'orcl.sun.net') ; -- database link name
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_publish.create_change_set(
3 change_set_name => 'emp_dept_set',
4 description => 'EMP and DEPT change set',
5 change_source_name => 'emp_dept_src') ;
6 end ;
7 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_publish.create_change_table(
3 owner => 'cdc_stg_pub',
4 change_table_name => 'emp_ct',
5 change_set_name => 'emp_dept_set',
6 source_schema => 'cdc_source',
7 source_table => 'emp',
8 column_type_list => 'empno number(4), ename varchar2(10),
9 job varchar2(9), mgr number(4), sal number(7,2), comm
10 number(7,2), deptno number(2)',
11 capture_values => 'both',
12 rs_id => 'y',
13 row_id => 'n',
14 user_id => 'n',
15 timestamp => 'y',
16 object_id => 'n',
17 source_colmap => 'n',
18 target_colmap => 'y',
19 options_string => null) ;
20 end;
21 /

PL/SQL procedure successfully completed.

SQL> grant select on emp_ct to cdc_stg_user ;

Grant succeeded.

SQL> begin
2 dbms_cdc_publish.create_change_table(
3 owner => 'cdc_stg_pub',
4 change_table_name => 'dept_ct',
5 change_set_name => 'emp_dept_set',
6 source_schema => 'cdc_source',
7 source_table => 'dept',
8 column_type_list => 'deptno number(2), dname varchar2(14),
9 loc varchar2(13)',
10 capture_values => 'both',
11 rs_id => 'y',
12 row_id => 'n',
13 user_id => 'n',
14 timestamp => 'y',
15 object_id => 'n',
16 source_colmap => 'n',
17 target_colmap => 'y',
18 options_string => null) ;
19 end ;
20 /
grant select on dept_ct to cdc_stg_user ;
PL/SQL procedure successfully completed.

SQL>

Grant succeeded.

SQL> begin
2 dbms_cdc_publish.alter_change_set(
3 change_set_name => 'emp_dept_set',
4 enable_capture => 'Y') ;
5 end ;
6 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_publish.alter_hotlog_change_source(
3 change_source_name => 'emp_dept_src',
4 enable_source => 'Y') ;
5 end ;
6 /

PL/SQL procedure successfully completed.

SQL> conn CDC_STG_USER/CDC_STG_USER@owb
Connected.
SQL> begin
2 dbms_cdc_subscribe.create_subscription(
3 change_set_name => 'emp_dept_set',
4 description => 'EMP and DEPT change subscription',
5 subscription_name => 'emp_dept_sub1') ;
6 end ;
7 /
begin
dbms_cdc_subscribe.subscribe(
subscription_name => 'emp_dept_sub1',
source_schema => 'cdc_source',

PL/SQL procedure successfully completed.

SQL> 2 3 4 5 source_table => 'emp',
6 column_list => 'empno, ename, job, mgr, sal, comm,
7 deptno',
8 subscriber_view => 'emp_chg_view') ;
9 end ;
10 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_subscribe.subscribe(
3 subscription_name => 'emp_dept_sub1',
4 source_schema => 'cdc_source',
5 source_table => 'dept',
6 column_list => 'deptno, dname, loc',
7 subscriber_view => 'dept_chg_view') ;
8 end ;
9 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_cdc_subscribe.activate_subscription(
3 subscription_name => 'emp_dept_sub1') ;
4 end ;
5 /

PL/SQL procedure successfully completed.

SQL> select operation$ operation
2 , to_char(timestamp$,'dd-mon-yyyy hh24:mi:ss') this_time
3 , empno, ename, sal, comm
4 from emp_ct
5 order by timestamp$ ;
from emp_ct
*
ERROR at line 4:
ORA-00942: table or view does not exist

SQL> conn CDC_STG_PUB/CDC_STG_PUB@owb
Connected.
SQL> /

OP THIS_TIME                       EMPNO ENAME             SAL       COMM
-- -------------------------- ---------- ---------- ---------- ----------
UO 27-feb-2009 14:46:08             7499 ALLEN            1600        300
UN 27-feb-2009 14:46:08             7499 ALLEN            1760        300
UO 27-feb-2009 14:46:08             7521 WARD             1250        500
UN 27-feb-2009 14:46:08             7521 WARD             1375        500
UO 27-feb-2009 14:46:08             7654 MARTIN           1250       1400
UN 27-feb-2009 14:46:08             7654 MARTIN           1375       1400
UO 27-feb-2009 14:46:08             7844 TURNER           1500          0
UN 27-feb-2009 14:46:08             7844 TURNER           1650          0
I 27-feb-2009 14:46:19             8000 DOE              4000
UO 27-feb-2009 14:46:26             7369 SMITH             800
UN 27-feb-2009 14:46:26             7369 SMITH             800         40

OP THIS_TIME                       EMPNO ENAME             SAL       COMM
-- -------------------------- ---------- ---------- ---------- ----------
UO 27-feb-2009 14:46:26             7876 ADAMS            1100
UN 27-feb-2009 14:46:26             7876 ADAMS            1100         55

13 rows selected.

SQL> conn CDC_STG_USER/CDC_STG_USER@owb
Connected.
SQL> begin
2 dbms_cdc_subscribe.extend_window(
3 subscription_name => 'emp_dept_sub1') ;
4 end ;
5 /

PL/SQL procedure successfully completed.

SQL> select operation$ operation
2 , to_char(timestamp$,'dd-mon-yyyy hh24:mi:ss') this_time
3 , empno, ename, sal, comm
4 from emp_chg_view
5 order by timestamp$ ;

13 rows selected.

SQL>