CDC异步AutoLog的简单测试

在源端:

$ sqlplus / as sysdba

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

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


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

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


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$ ;

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>

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值