Oracle CDC Autolog配置步骤

来源：互联网发布：图像处理器软件下载编辑：程序博客网时间：2024/04/29 00:53

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 sysdba

show 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 sysdba

show 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的值 */

/* 启用归档模式，如果已经是归档模式，一下可以不用执行 */

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

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

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 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_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');

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%';

/* 登陆源库 */

sqlplus sys/admin as sysdba

alter system switch logfile;

/* 登陆目标库，验证 */

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