oracle cdc 异步部署文档

Oracle 作者：jinqibingl 时间：2016-04-20 09:59:17 14 删除编辑

一：概念简介

1 CDC是一种数据增量处理技术

2 CDC组件(Change Data Capture 改变数据捕获)的原理：

1） CDC体系结构基于发布者/订阅者模型。
2）发布者捕捉变化数据并提供给订阅者。
3）订阅者使用从发布者那里获得的变化数据。
4）通常，CDC系统拥有一个发布者和多个订阅者。
5）发布者首先需要识别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。
它还使订阅者能够控制对变化数据的访问。
6）订阅者需要清楚自己感兴趣的是哪些变化数据。
7）一个订阅者可能不会对发布者发布的所有数据都感兴趣。
8）订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。

3 CDC有几个重要的基本概念需要先明确一下：

1）源表(Source Table),业务数据库的需要捕获数据的源表
2）目标端变化集(Change Set)，是保证事务一致性的数据集合。一个变化集对应多个变化表
3）目标端变化表(Change Table),保存从源表捕获的变化数据(包括各种DML产生的数据)
4）订阅视图(Subscription View),提供给读取变化表数据的视图
5）订阅窗口(Subscription Window) ,定义了查看变化数据的时间范围.就象一个观察变化数据的滑动窗口。变化数据处理完成后，可以清除订阅窗口。

二：CDC的同步与异步模式
1 同步复制, Synchronous Change Data Capture Configuration（同步复制），实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中
2 异步模式，以Oracle流复制技术为基础，从redo log中读取日志记录来捕捉变化数据,异步模式有三种：
(1) Asynchronous HotLog （异步在线日志CDC）
(2) Asynchronous Distributed HotLog（异步分布式CDC）
(3) Asynchronous AutoLog Mode
（3.1） Asynchronous Autolog Online  Change Data Capture（异步在线日志复制CDC）
（3.2） Asynchronous AutoLog Archive Change Data Capture（异步归档日志CDC）

Asynchronous AutoLog Mode过程为：source table->实例化source table->>>>目标change source->目标change set->change table.当然，这期间可以过滤某些字段，或者某些表。

三：部署实施

1. 环境设置

1.1 网络连接（源库和目标库都要配置）

1.0.1 原库的listener.ora 内容如下：

# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
   (PROGRAM = extproc)
)
  )

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost13.localdomain)(PORT = 1521))
)
  )
ADR_BASE_LISTENER = /data/oracle

#注意：HOST = localhost13.localdomain 根据系统主机名自行配置

1.0.2 原库的sqlnet.ora 内容如下:

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /data/oracle

1.0.3 原库的tnsnames.ora 内容如下:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDC01 =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
)
  )

CDC02 =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
)
  )
#注意：HOST = xxx.xxx 根据系统主机IP自行配置

1.1.1 目标库的listener.ora 内容如下：

# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#ADR_BASE_LISTENER = /data/oracle

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
   (PROGRAM = extproc)
)
  )

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost14.localdomain)(PORT = 1521))
)
  )

ADR_BASE_LISTENER = /data/oracle
#注意：HOST = localhost14.localdomain 根据系统主机名自行配置

1.1.2 目标库的sqlnet.ora 内容如下:

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /data/oracle

1.1.3 目标库的tnsnames.ora 内容如下:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDC02 =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
)
  )

CDC01 =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
)
  )
#注意：HOST = xxx.xxx 根据系统主机IP自行配置

1.2 初始参数
1.2.1 设置两库密码
确保源库和目标库的sys用户名密码要一致；
原库重建密码文件：
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle
建好之后copy到备库的$ORACLE_HOME/dbs 并且改名为备库的$ORACLE_HOME/dbs/orapw$ORACLE_SID ,注意没有后缀.ora

1.2.2 目标库参数修改
sqlplus / as sysdba
show parameter global_names;                               /* 查看global_names的值 */
alter system set global_names=true scope=both;    /* 如果global_names的为false的话，修改global_names为true，both为启动生效 */
show parameter java_pool_size;                               /* 查看java_pool_size当前的值 */
alter system set java_pool_size=50M scope=both;    /* 如果当前值为0的话，设置java_pool_size为50M，否则可以增加50M */
show parameter log_archive_dest_;                         /* 查看当前归档日志使用的情况 */
alter system set log_archive_dest_1 = 'location=D:\app\arch1 mandatory reopen=5 valid_for=(online_logfile,primary_role)' scope=both;  /* 如果log_archive_dest_1没有被使用的话，就使用log_archive_dest_1设置归档路径 */
alter system set log_archive_dest_2 = 'location=D:\app\arch2 mandatory valid_for=(standby_logfile,primary_role)' scope=both;       /* 如果log_archive_dest_2没有被使用的话，就使用log_archive_dest_2设置远程归档日志的路径 */
alter system set log_archive_dest_state_1=enable scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
alter system set log_archive_format="cdc02_%R_%T_%S.arc" scope=spfile;
alter system set job_queue_processes = 2;
show parameter parallel_max_servers;
alter system set parallel_max_servers =15 scope=both;
show parameter remote_login_passwordfile;             /* 查看remote_login_passwordfile的值 */
alter system set remote_login_passwordfile=EXCLUSIVE 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=200m scope=both;                /* 可从v$sgastat中查看streams_pool_size池的使用，根据监控的结果，为streams_pool_size设置合适的值 */
show parameter undo_retention;
alter system set undo_retention=3600 scope=BOTH;
alter system set "_job_queue_interval"=1 scope=spfile; --同时设置_job_queue_interval也是为了提高队列检查时间，防止apply出问题。
alter system set aq_tm_processes=2 scope=both;
/*之上，如果没有/home/oracle/arch/arch_cdc02_dest1和/home/oracle/arch/arch_cdc02_dest2 自己mkdir -p建立*/

/* 启用归档模式 */
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

/* 切换归档模式，确认归档是否成功（在归档日志路径下面是否有归档日志生成） */
alter system switch logfile;

/* 添加远程归档日志 */
sqlplus  sys/oracle@cdc01 as sysdba;    /* 登陆到源库 */
select group#, bytes/1024/1024 size_mb from v$log;    /* 查看源库的redo log组个数（假设为3）和大小（假设为50MB） */
sqlplus sys/oracle@cdc02 as sysdba;    /* 登陆到目标库 */
select max(group#) from v$log; /* 查看当前group的个数，假设当前为3 */

/* 在目标库创建的日志个数要为源库个数+1个，日志文件大小要相同 */
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\app\stdbylogs/slog1a.rdo', 'D:\app\stdbylogs/slog1b.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:\app\stdbylogs/slog2a.rdo', 'D:\app\stdbylogs/slog2b.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:\app\stdbylogs/slog3a.rdo', 'D:\app\stdbylogs/slog3b.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('D:\app\stdbylogs/slog4a.rdo', 'D:\app\stdbylogs/slog4b.rdo') SIZE 50M;

SELECT group#, bytes, status FROM v$standby_log;

1.2.3 源库参数设置
sqlplus / as sysdba
show parameter global_names;                               /* 查看global_names的值 */
alter system set global_names=true scope=both;    /* 如果global_names的为false的话，修改global_names为true，both为启动生效 */
show parameter java_pool_size;                               /* 查看java_pool_size当前的值 */
alter system set java_pool_size=50M scope=both;    /* 如果当前值为0的话，设置java_pool_size为50M，否则可以增加50M */
show parameter log_archive_dest_;                         /* 查看当前归档日志使用的情况 */
alter system set log_archive_dest_1='location=/home/oracle/arch/archive mandatory reopen=5' scope=both;  /* 如果log_archive_dest_1没有被使用的话，就使用log_archive_dest_1设置归档路径 */
alter system set log_archive_dest_2='service=cdc02 lgwr async optional noregister reopen=5 valid_for=(online_logfile,primary_role)' scope=both;  /*异步在线日志,不要指定template*/
#--alter system set log_archive_dest_2='service=cdc02 arch optional noregister reopen=5 template=D:\app\stdbylogs/cdc_stby_%R_%T_%S.archi' scope=both;    /*异步归档日志如果log_archive_dest_2没有被使用的话，就使用log_archive_dest_2设置远程归档目标 */
alter system set log_archive_dest_state_1=enable scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
alter system set log_archive_format="cdc01_%R_%T_%S.arc" scope=spfile;
alter system set job_queue_processes = 2;
show parameter parallel_max_servers;
alter system set parallel_max_servers = 15 scope=both;
show parameter remote_login_passwordfile;             /* 查看remote_login_passwordfile的值 */
alter system set remote_login_passwordfile=EXCLUSIVE 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=200m scope=both;                /* 可从v$sgastat中查看streams_pool_size池的使用，根据监控的结果，为streams_pool_size设置合适的值 */
show parameter undo_retention;
alter system set undo_retention=3600 scope=BOTH;
alter system set "_job_queue_interval"=1 scope=spfile; --同时设置_job_queue_interval也是为了提高队列检查时间，防止apply出问题。
alter system set aq_tm_processes=2 scope=both;

/* 启用归档模式，*/
shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database open;
archive log list;
show parameter log_archive_dest_;

/* 切换归档模式，确认归档是否成功（在归档日志路径下面是否有归档日志生成） */
alter system switch logfile;

-----------------------------------------------------------------------------------------------------------------------

2. 创建用户
2.1 源库用户
sqlplus / as sysdba
/* 创建用户，源表拥有者 */
create 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 TABLESPACE ts_cdcpub DATAFILE 'D:\app\Administrator\oradata\proc\ts_cdcpub.dbf' SIZE 500m;
create user cdc_stg_pub identified by cdc_stg_pub default tablespace ts_cdcpub temporary tablespace temp quota unlimited on system quota unlimited on ts_cdcpub quota 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,dba to cdc_stg_user;

3. CDC启用
3.1 创建源表
sqlplus cdc_source/cdc_source;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
alter table emp add supplemental log data (all) columns;
alter table dept add supplemental log data (all) columns;

/* 登陆源库 */
conn / as sysdba                   /* 登陆源库 */
/* 设置可以输出显示 */
/* 获取源数据SCN值，并记录以后使用 */
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;
/

/* 实例化每个表 */
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 from global_name; /* 查询源数据库的全名 */

3.2 创建变化表
/* 创建修改源 */
sqlplus cdc_stg_pub/cdc_stg_pub@cdc02          /* 以发布者身份登陆目标库 To create an AutoLog online change source:*/
begin
  dbms_cdc_publish.create_autolog_change_source(
change_source_name => 'emp_dept_src',
description          => 'Emp and Dept source',
source_database       => 'ORCL',    --/*source_database是原库global_name名*/
first_scn             => '801450',  -- /*first_scn是原库:f_scn*/
online_log             => 'n');    -- /*此为异步归档模式-*/
end;
/

--#################特别注意: 如果是异步在线模式，最后的一个参数可以用'y' 如下：##################################
/* 创建修改源 */
sqlplus cdc_stg_pub/cdc_stg_pub          /* 以发布者身份登陆目标库 To create an AutoLog archive change source:*/
begin
  dbms_cdc_publish.create_autolog_change_source(
change_source_name       => 'emp_dept_src',
description                => 'Emp and Dept source',
source_database       => 'CDC01',
first_scn                => '1296139',
online_log                => 'y');
end;
/
--#################特别注意: 如果是异步在线模式，最后的一个参数可以用'y' 如上： ##################################

select source_name,
  source_description,
  source_type,
  source_database
from change_sources
where 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',
begin_date    => sysdate,
end_date          => sysdate+5);
end;
/

--#################################################
#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_name
from change_sets
where 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_table
from dba_apply app,
  dba_queues q,
  dba_queue_tables qt
where 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_name
from change_tables
where 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_database
from dba_streams_table_rules
where rule_owner = 'CDC_STG_PUB'
  and table_owner = 'CDC_SOURCE'
order by table_name,
  rule_type,
  streams_type;

3.3 激活CDC
sqlplus 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,
  status
from dba_apply
where apply_user = 'CDC_STG_PUB'
  and apply_name like '%EMP_DEPT%';

/*find the source tables for which the subscriber has access privileges.*/
sqlplus cdc_stg_pub/cdc_stg_pub
SELECT * FROM ALL_SOURCE_TABLES;

/*Find the change set names and columns for which the subscriber has access privileges.*/
sqlplus cdc_stg_pub/cdc_stg_pub
SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM ALL_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='CDC_SOURCE' AND SOURCE_TABLE_NAME = 'EMP';  --EMP注意大写
SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM ALL_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='CDC_SOURCE' AND SOURCE_TABLE_NAME = 'DEPT'; --DEPT注意大写

/* 登陆源库 */
sqlplus sys/oracle as sysdba
alter system switch logfile;

/* 登陆目标库，验证 */
sqlplus cdc_stg_pub/cdc_stg_pub
select capture_name,
  state,
  total_messages_captured
from v$streams_capture
where capture_name like '%EMP_DEPT%';

select group#, thread#, sequence#, archived, status from v$standby_log;

3.4 创建订阅
sqlplus cdc_stg_user/cdc_stg_user
begin
  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_name
from user_subscriptions s,
  user_subscribed_tables st,
  user_subscribed_columns sc
where 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_name
order 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$;

注意: standby redo时候，在目标stage site建立路径，注意和在source site的dest2一致！

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9606200/viewspace-2084502/ ，如需转载，请注明出处，否则将追究法律责任。

上一篇： oracle10g后面的聚合运算函数：cube、rollup等

下一篇： vmware conveter5的使用操作指导-加上我的实验记录

请登录后发表评论登录

全部评论

jinqibingl

注册时间：2010-08-19

博文量
154
访问量

325264

ITPub博客

oracle cdc 异步部署文档

最新文章