基于linux中登录oracle数据库的CDC部署

对oracle数据库中scoot用户表的增量数据变化进行CDC部署监控

登录oracle数据库dba用户
su - oracle
sqlplus / as sysdba;

1. 环境的配置准备
   1.1. 数据库版本
   select * from v$version;

1.2. 配置数据库参数
show parameter job_que
show parameter streams_pool_size
show parameter sga_targ
show parameter memory_targ
show parameter java_pool
alter system set streams_pool_size=50m ;
show parameter java_pool
alter system set java_pool_size=50m;
show parameter undo_ret
alter system set undo_retention=3600;
show parameter streams_pool
show parameter java_pool
show parameter undo_re

1.3开启归档及补充日志
archive log list –查看
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archivelog_dest
Oldest online log sequence 401
Next log sequence to archive 403
Current log sequence 403
–如果是上面显示就继续往下执行，不是也继续执行
alter database force logging;
alter database add supplemental log data;
select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
LOG_MODE FOR SUPPLEME

---

ARCHIVELOG YES YES
—如果查询结果是上面显示则开启成功跳过下面归档命令，如果不是执行下面代码,可以先不用执行，直接跳过

---

* alter system set log_archive_start=true scope=spfile; *
* shutdown immediate; *
* startup mount; *
* alter database archivelog; *
* alter database open; *
* archive log list; *
* —-查看显示下列信息则开启成功 *
* Database log mode Archive Mode *
* Automatic archival Enabled *
* Archive destination /u02/archivelog_dest *
* Oldest online log sequence 401 *
* Next log sequence to archive 403 *
* Current log sequence 403 *

---

1.4. 准备测试的表
conn scott/tiger
create table test(id int,name varchar2(30),mark varchar2(50));
select * from tab;–查看创建的表名是否存在
create tablespace cdc_tbsp datafile ‘/u02/app/oradata/ORCL/cdc_tbsp01.dbf’ size 200m;
create user cdc_publisher identified by cdc_publisher default tablespace cdc_tbsp temporary tablespace temp;
—授予非DBA的其他权限
grant create session TO cdc_publisher;
grant create table TO cdc_publisher;
grant create sequence TO cdc_publisher;
grant create procedure TO cdc_publisher;
grant create any job TO cdc_publisher;
grant execute_catalog_role TO cdc_publisher;
grant select_catalog_role TO cdc_publisher;
grant execute ON dbms_cdc_publish TO cdc_publisher;
grant execute ON dbms_lock TO cdc_publisher;
GRANT UNLIMITED TABLESPACE TO cdc_publisher;
grant connect, resource, select_catalog_role to cdc_publisher;
grant create any index, create any procedure, create any sequence, create any table, create any trigger, create any view to cdc_publisher;
grant delete any table, execute any procedure, drop any table, drop any trigger, insert any table to cdc_publisher;
grant lock any table, select any sequence, select any table, update any table to cdc_publisher;
grant alter any table to cdc_publisher;
grant select any dictionary to cdc_publisher;
grant select on user_role_privs to cdc_publisher with grant option;
grant select on sys.v_archived_log to cdc_publisher with grant option;
grant select on sys.v_database to cdc_publisher with grant option;
grant select on sys.v_controlfile to cdc_publisher with grant option;
grant select on sys.v_log to cdc_publisher with grant option;
grant select on sys.v_log_history to cdc_publisher with grant option;
grant select on sys.v_logfile to cdc_publisher with grant option;
grant select on sys.v_mystat to cdc_publisher with grant option;
grant select on sys.v_session to cdc_publisher with grant option;
grant select on sys.v_version to cdc_publisher with grant option;
grant select on sys.v_transaction to cdc_publisher with grant option;
grant select on sys.all_coll_types to cdc_publisher with grant option;
grant select on sys.all_cons_columns to cdc_publisher with grant option;
grant select on sys.all_constraints to cdc_publisher with grant option;
grant select on sys.all_type_attrs to cdc_publisher with grant option;
grant select on sys.dba_constraints to cdc_publisher with grant option;
grant select on sys.dba_cons_columns to cdc_publisher with grant option;
grant select on sys.dba_indexes to cdc_publisher with grant option;
grant select on sys.dba_ind_columns to cdc_publisher with grant option;
grant select on sys.dba_mviews to cdc_publisher with grant option;
grant select on sys.dba_objects to cdc_publisher with grant option;
grant select on sys.dba_profiles to cdc_publisher with grant option;
grant select on sys.dba_roles to cdc_publisher with grant option;
grant select on sys.dba_rollback_segs to cdc_publisher with grant option;
grant select on sys.dba_sequences to cdc_publisher with grant option;
grant select on sys.dba_tab_columns to cdc_publisher with grant option;
grant select on sys.dba_tables to cdc_publisher with grant option;
grant select on sys.dba_tablespaces to cdc_publisher with grant option;
grant select on sys.dba_users to cdc_publisher with grant option;
grant select on sys.dba_sys_privs to cdc_publisher;
grant select on sys.dba_tab_privs to cdc_publisher;
grant select on sys.dba_tab_comments to cdc_publisher;
grant select on sys.dba_col_comments to cdc_publisher;
grant select on sys.nls_database_parameters to cdc_publisher with grant option;
grant select on sys.resource_cost to cdc_publisher with grant option;
grant select on sys.attrcol to cdc_publisher with grant option;
grant select on sys.ccol to cdc_publisher with grant option;
grant select on sys.cdef to cdc_publisher with grant option;
grant select on sys.col to cdc_publisher with grant option;
grant select on sys.coltype to cdc_publisher with grant option;
grant select on sys.hist_head to cdc_publisher with grant option;
grant select on sys.icol to cdc_publisher with grant option;
grant select on sys.ind to cdc_publisher with grant option;
grant select on sys.lob to cdc_publisher with grant option;
grant select on sys.obj to cdc_publisher with grant option;
grant select on sys.tab to cdc_publisher with grant option;
grant select on sys.user to cdc_publisher with grant option;
execute dbms_streams_auth.grant_admin_privilege(‘CDC_PUBLISHER’);
grant all on scott.test to cdc_publisher;

2.2. 创建用户/授权
create user cdc_subscriber identified by cdc_subscriber default tablespace cdc_tbsp temporary tablespace temp;
grant create session TO cdc_subscriber;

1. 准备测试具体数据
   3.1. 准备源表(Source Table)
   BEGIN
   DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
   TABLE_NAME => ‘scott.test’);
   END;
   /
   再按回车可以执行

3.2. 创建变更集(Data Set)
conn cdc_publisher/cdc_publisher
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => ‘CDC_SCOTT_TEST’,
description => ‘Change set for product info’,
change_source_name => ‘HOTLOG_SOURCE’,
stop_on_ddl => ‘y’,
begin_date => sysdate,
end_date => sysdate+5);
END;
/

3.3. 创建变更表
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => ‘cdc_publisher’,
change_table_name => ‘cdc_test’,
change_set_name => ‘CDC_SCOTT_TEST’,
source_schema => ‘SCOTT’,
source_table => ‘TEST’,
column_type_list => ‘ID NUMBER(5), NAME VARCHAR2(30),MARK VARCHAR2(50)’,
capture_values => ‘both’,
rs_id => ‘y’,
row_id => ‘n’,
user_id => ‘n’,
timestamp => ‘n’,
object_id => ‘n’,
source_colmap => ‘n’,
target_colmap => ‘y’,
options_string => ‘TABLESPACE CDC_TBSP’);
END;
/
—-注意：上面的字段必须要有，没有值的可以给空

3.4. 激活变更集
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => ‘CDC_SCOTT_TEST’,
enable_capture => ‘y’);
END;
/

grant select on cdc_test to cdc_subscriber;

备注：其实到此cdc_subscriber用户已经可以检测到scott.test表的变更了

4测试

conn scott/tiger
insert into test values(1,’beijing’,’11’);
commit;
update test set name=’shanghai’ where id=1;
commit;
delete test where id=1;
commit;
conn cdc_subscriber/cdc_subscriber
select t.operation