关于Change Data Capture（五）Oracle CDC Autolog online 例子

1.cdc 模式在源库是集群的情况下！ 源库的
log_archive_dest_2='SERVICE=alcdcstg lgwr async optional noregister reopen=5 valid_for=(online_logfile,primary_role) sid=’1’'
log_archive_dest_2='SERVICE=alcdcstg lgwr async optional noregister reopen=5 valid_for=(online_logfile,primary_role) sid=’2’'

一.CDC autolog online


1.前提条件:
(1).数据库版本必须是企业版.
(2).源库和中间库所在的操作系统版本必须相同.
(3).源库和中间库的SYS口令必须相同.
(4).源库和中间库可以通过tnsnames.ora互相访问.

=============================================================================================

2.数据库参数配置:
(1).stagdb端:
--开启本地归档的路径 和 接受远程归档或远程redo日志的路径:
alter system set global_names=true;
alter system set java_pool_size=50M;
alter system set streams_pool_size=50M;
alter system set remote_login_passwordfile=shared scope=spfile;
alter system set log_archive_dest_1='LOCATION=/u01/oracle/oradata/hua/archivelog mandatory reopen=5 valid_for=(online_logfile,primary_role)';
alter system set log_archive_dest_2='LOCATION=/u01/oracle/oradata/hua/staglog mandatory valid_for=(standby_logfile,primary_role)'; 
 --将stagdb置于归档模式:
shutdown immediate;
startup mount;
alter database archivelog ;
alter database open ;
alter system switch logfile;
--在stagdb中添加standby logfile,用来接收源端传输过来的日志,所以stagdb的standby logfile个数应该大于源端的online redo个数.每个standby logfile的大小应该>=源湍的online log大小.
alter database add standby logfile group 5 ('<path to standby redo log>') size ***M;
......

(2).源端:
--设置本地归档和远程归档:
alter system set remote_login_passwordfile=shared scope=spfile;
alter system set log_archive_dest_1='LOCATION=/u01/oracle/oradata/yun/archivelog';
alter system set log_archive_dest_2='SERVICE=hua lgwr async optional noregister reopen=5 valid_for=(online_logfile,primary_role)';
--开归档,force logging,附加日志:
shutdown immediate;
startup mount;
alter database archivelog;
alter database open ;
alter system switch logfile;
alter database add supplemental log data (primary key, unique index) columns;
alter database force logging;
=============================================================================================

3.创建用户:
(1).源端:
conn sys/oracleabcd@yun as sysdba

drop user cdc_admin cascade;

create user cdc_admin identified by cdc_admin;
grant create session to cdc_admin;
grant create table to cdc_admin;
grant create tablespace to cdc_admin;
grant unlimited tablespace to cdc_admin;
grant select_catalog_role to cdc_admin;
grant execute_catalog_role to cdc_admin;
grant execute on dbms_cdc_publish to cdc_admin;

(2).stagdb端:
conn sys/oracleabcd@hua as sysdba

drop user cdc_admin cascade;

create user cdc_admin identified by cdc_admin;
grant create session to cdc_admin;
grant create table to cdc_admin;
grant create tablespace to cdc_admin;
grant create sequence to cdc_admin;
grant select_catalog_role to cdc_admin;
grant execute_catalog_role to cdc_admin;
grant unlimited tablespace to cdc_admin;
grant select_catalog_role to cdc_admin;
grant execute_catalog_role to cdc_admin;
grant connect, resource, dba to cdc_admin;
grant execute on dbms_cdc_publish to cdc_admin;
execute dbms_streams_auth.grant_admin_privilege(grantee => 'cdc_admin');

=============================================================================================
4.配置源数据库:
(1).创建测试表:
-- create source table.
connect CDC_ADMIN/CDC_ADMIN@yun

CREATE TABLE CDC_HEARTBEAT_DW
(
CDC_NAME VARCHAR2(128) CONSTRAINT CHB_COL01_NN NOT NULL,
LATEST_DATETIME TIMESTAMP(6) DEFAULT SYSTIMESTAMP CONSTRAINT CHB_COL02_NN NOT NULL
);

alter table CDC_HEARTBEAT_DW add constraint pk_CDC_HEARTBEAT_DW primary key(CDC_NAME) using index;

alter Table cdc_heartbeat_dw ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

(2).查看记录的SCN:
-- Run the following and record the SCN.

conn sys/oracleabcd@yun as sysdba

SET SERVEROUTPUT ON
DECLARE
l_logminer_scn NUMBER;
BEGIN
dbms_capture_adm.build ( l_logminer_scn );
DBMS_OUTPUT.PUT_LINE('SCN: '|| l_logminer_scn);
END;
/

eg: 12045675269343

(3).将源表实例化:
-- Prepare table for instantiation.

conn sys/oracleabcd@yun as sysdba

EXECUTE dbms_capture_adm.prepare_table_instantiation ( 'CDC_ADMIN.CDC_HEARTBEAT_DW' );

(4).查看源端的global_name:
conn sys/oracleabcd@yun as sysdba

select global_name from global_name ;

=============================================================================================

5.配置stagdb：

-- create Change source
conn CDC_ADMIN/CDC_ADMIN@hua
begin
dbms_cdc_publish.create_autolog_change_source(
change_source_name => 'dw',
description => 'Source Database is yun',
source_database => 'yun',
first_scn => 12045675269343,
online_log => 'y');
end;
/
参数说明:
The source_database parameter has to have the global_name for the source database.
first_scn must be the value you captured from the source database.

-- create change set
begin
dbms_cdc_publish.create_change_set(
change_set_name => 'CS05',
description => 'Source Database is SDTAIS with change set CS05',
change_source_name => 'dw',
stop_on_ddl => 'y');
end;
/

-- Create change table.
begin
dbms_cdc_publish.create_change_table(
owner => 'CDC_ADMIN',
change_table_name => 'CT_CDC_HEARTBEAT_CS05',
change_set_name => 'CS05',
source_schema => 'CDC_ADMIN',
source_table => 'CDC_HEARTBEAT_DW',
column_type_list => 'CDC_NAME VARCHAR2(128), LATEST_DATETIME TIMESTAMP(6)',
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 ;
/
说明:在调用create_change_table时需要JVM,如果stage数据库未安装JVM,则会报错.最好在源端和目标端都安装JVM.


-- alter few tuning parameters:
EXECUTE dbms_capture_adm.alter_capture( capture_name => 'CDC$C_CS05', checkpoint_retention_time => 1 );
EXECUTE dbms_capture_adm.set_parameter( capture_name => 'CDC$C_CS05', parameter => 'DISABLE_ON_LIMIT', value => 'Y');
EXECUTE dbms_capture_adm.set_parameter( capture_name => 'CDC$C_CS05', parameter => 'MAXIMUM_SCN', value => 'INFINITE');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => 'COMMIT_SERIALIZATION', value => 'NONE');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => 'DISABLE_ON_ERROR', value => 'Y');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => 'DISABLE_ON_LIMIT', value => 'Y');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => 'PARALLELISM', value => '1');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => '_DYNAMIC_STMTS', value => 'Y');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => '_HASH_TABLE_SIZE', value => '1000000');
EXECUTE dbms_apply_adm.set_parameter( apply_name => 'CDC$A_CS05', parameter => '_TXN_BUFFER_SIZE', value => '10');


-- Active change_set:
begin
dbms_cdc_publish.alter_change_set(
change_set_name=>'CS05',
enable_capture=>'y');
end;
/

=============================================================================================
5.源端切换日志,将源端的数据字典传输至stagdb,让捕获进程初始化:
-- switch logfile:
alter system switch logfile;
=============================================================================================

6.测试:

connect sys/oracleabcd@yun as sysdba

INSERT INTO cdc_heartbeat_dw( CDC_NAME, LATEST_DATETIME ) VALUES ( 'Iddfdfd2st', SYSDATE );
commit;


ALTER SYSTEM archive log current;

conn CDC_ADMIN/CDC_ADMIN@hua
select count(*) from CT_CDC_HEARTBEAT_CS05;
select * from CT_CDC_HEARTBEAT_CS05;

===============================================================================================

7.配置验证:
1.验证change source是否已经创建成功,如果是 autolog online模式,则source_type的值为AUTOLOG ONLINE:
select source_name, source_description, source_type, source_database
  from change_sources
where source_name = 'DW';

2.验证change set:
    change set will create an associated, still disabled, streams apply process, an apply queue and apply queue table.
select set_name,
       set_description,
       change_source_name,
       apply_name,
       queue_name,
       queue_table_name
  from change_sets
where set_name = 'CS05';

--检查底层的stream apply进程的定义:
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_ADMIN'
   and q.owner = 'CDC_ADMIN'
   and qt.owner = 'CDC_ADMIN'
   and q.name = app.queue_name
   and qt.queue_table = q.queue_table
该语句会显示底层streams的apply进程的状态.

3.验证change table:
     change tables will creates the Streams apply rules as well as the Streams capture rules on the staging database.
select change_table_name,
       change_set_name,
       source_schema_name,
       source_table_name
  from change_tables
where change_table_schema = 'CDC_ADMIN'
order by change_table_name;

--检查底层的streams capture和apply的规则:
select streams_name,
       streams_type,
       table_owner,
       table_name,
       rule_type,
       source_database
  from dba_streams_table_rules
where rule_owner = 'CDC_ADMIN'
order by table_name, rule_type, streams_type;
说明:
    至此,底层的streams配置完成,但capture、propagation、apply进程仍是inactive状态.

4.当change set激活后,检查apply进程的状态是否为enable:
select apply_name, status from dba_apply where apply_user = 'CDC_ADMIN';

5.检查capture的状态:
select capture_name, state, total_messages_captured from v$streams_capture;
说明:
    STATE can show several values until it shows CAPTURING CHANGES. Until state is CAPTURING CHANGES you will not see the value for TOTAL_MESSAGES_CAPTURED increase.

6.检查autolog online是否正确的被激活:
select group#, thread#, sequence#, archived, status from v$standby_log;
说明:
    如果autolog online正确的被激活,则至少有一个standby logfile的状态为ACTIVE.

===============================================================================================
说明: autolog online方式在10.2.0.4基于AIX,在创建change set时报遇到BUG. BUG:11768445.