Oracle CDC autolog configuration steps

1. Environment Settings

1.1 Network Connection (both the source and target databases must be configured)Run Oracle Net Configuration Assistant to add a local service name. You can also directly modify the (tnsnames. ora) file. The content is as follows: PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = )
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)

H10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = )
(CONNECT_DATA =
(SERVICE_NAME = h10g)
)
)1.2 initial parameters1.2.1 set two-Database PasswordEnsure that the sys username and password of the source and target databases are consistent;1.2.2 target database Parameter ModificationSqlplus/As sysdbashow parameter global_names;/* view the value of global_names */alter system set global_names = true scope = spfile;/* If global_names is false, change global_names to true, spfile is effective for start */show parameter java_pool_size;/* view the current value of java_pool_size */alter system set java_pool_size = 50 m scope = spfile;/* if the current value is 0, set java_pool_size to 50 m; otherwise, 50 m */show parameter log_archive_dest _; /* view the current usage of archived logs */alter system set log_archive_dest_1 = 'location = <archive Log Path> mandatory reopen = 5 valid_for = (online_logfile, primary_role) 'scope = spfile; /* If log_archive_dest_1 is not used, use log_archive_dest_1 to set the archive path */alter system set lifecycle = 'location = <remote log archiving path> mandatory valid_for = (standby_logfile, primary_role) 'scope = spfile;/* If log_archive_dest_2 is not used, use log_archive_dest_2 to set the path for remote log archiving */show parameter remote_login_passwordfile; /* view the value of remote_login_passwordfile */alter system set remote_login_passwordfile = shared scope = spfile;/* Set remote_login_passwordfile to shared: only the internal/sys account can be identified by the password file, they are not allowed to log on to */show parameter streams_pool_size;/* With sysoper/sysdba permissions to view the stream_pool_size value. If the streams_pool_size parameter is not set, the shared_pool_size pool will be used for 10 of the memory */alter system set streams_pool_size = 50 m scope = spfile;/* You can view the streams_pool_size pool usage from V $ sgastat. Based on the monitoring results, set an appropriate value for streams_pool_size * // * enable archive mode */shutdown immediate; Startup Mount; Alter database archivelog; Alter database open;/* Switch archive mode, check whether the archive is successful (whether archive logs are generated under the archive Log Path) */alter system switch logfile;/* add remote archive logs */connect sys/admin @ Prod as sysdba; /* log on to the source database */select group #, Bytes/1024/1024 size_mb from V $ log;/* view the number of redo log groups of the source database (assume 3) and size (assuming 50 MB) */connect sys/admin as sysdba;/* log on to the target database */select max (group #) from V $ log; /* view the number of the current group. Assume that the current number is 3 * // * The number of logs created in the target database must be the number of the source database + 1, the log file size should be the same */alter database add standby logfile Group 4 ('<full path of the remote Log File Name>') size 50 m; alter database add standby logfile group 5 ('<full path of remote Log File Name>') size 50 m; Alter database add standby logfile group 6 ('<full path of remote Log File Name> ') size 50 m; Alter database add standby logfile group 7 ('<full path of remote Log File Name>') size 50 m;
1.2.3 source database parameter settingsSqlplus/As sysdbashow parameter log_archive_dest _;/* view the current usage of archived logs */alter system set log_archive_dest_1 = 'location = <archiving log path> 'scope = both; /* use log_archive_dest_1 to set the archive path */alter system set handler = 'service = h10g lgwr async optional noregister reopen = 5 valid_for = (online_logfile, primary_role) 'scope = both;/* If log_archive_dest_2 is not used, use log_archive_dest_2 to set the remote archiving target */show parameter remote_login_passwordfile; /* view the value of remote_login_passwordfile */alter system set remote_login_passwordfile = shared scope = spfile;/* Set remote_login_passwordfile to shared: only the internal/sys account can be identified by the password file, do not allow them to log on to * // * With sysoper/sysdba permissions to enable archive mode. If archive mode is already used, you do not need to execute */shutdown immediate; Startup Mount; Alter database archivelog; alter database open;/* Switch archive mode to check whether the archive is successful (whether archive logs are generated under the archive Log Path) */alter system switch logfile;2. Create a user2.1 source database usersSqlplus/as sysdbacreate user cdc_source identified by cdc_source default tablespace users temporary tablespace temp;/* create a user, source table owner */grant connect, resource, select any table to cdc_source; /* grant permissions */2.2 target database userSqlplus/as sysdba
/* Create a publisher and grant permissions */create user cdc_stg_pub identified by cdc_stg_pub default tablespace users temporary tablespace temp quota unlimited on system quota unlimited on usersQuote
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 login to cdc_stg_pub; execute privileges (grantee => 'cdc _ stg_pub');/* create a subscriber and grant the permission */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 Enabled3.1 create a source tableSqlplus cdc_source/cdc_source;/* log on to the source database */create table emp as select * from scott. emp;/* from scott. emp table create emp table */create table dept as select * from scott. dept;/* from scott. create a dept table in the dept table */alter table emp add supplemental log data (all) columns;/* add supplemental log to the emp table */alter table dept add supplemental log data (all) columns;/* Add supplemental log */sqlplus/as sysdba/* log on to the source database */set serveroutput on to the dept table; /* set the output to display * // * obtain the source data SCN value, and use */variable f_scn number after recording; begin: f_scn: = 0; dbms_capture_adm.build (: f_scn ); dbms_output.put_line ('the first_scn value is '|: f_scn); end; // * instantiate each table */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;/* query the full name of the source database */3.2 Create a change table/* Create and modify the source */sqlplus cdc_stg_pub/* log on to the target database as a publisher */begin dbms_cdc_publish.create_autolog_change_source (change_source_name => 'emp_dept_src ', description => 'EMP and dept source', source_database => '<full name of the source database>', first_scn => '<source database SCN> ', online_log => 'y'); end;/select source_name, source_description, source_type, source_databasefrom change_sourceswhere source_name = 'EMP _ dept_src ';/* check whether the creation is successful, note that the conditions in Where are case sensitive * // * Create a editing change set */sqlplus cdc_stg_pub/* log on to the target database as a publisher */begin dbms_cdc_publish.create_change_set ', description => 'emp' and dept change set', change_source_name => 'emp_dept_src ', stop_on_ddl => 'y'); end ;/
/* Verify whether the creation is successful */sqlplus cdc_stg_pub/* log on to the target database as a publisher */select set_name, set_description, change_source_name, apply_name, queue_name, queue_table_namefrom change_setswhere publisher = 'cdc _ stg_pub' and set_name = 'emp_dept_set ';/
/* Check CDC */sqlplus cdc_stg_pub/* to log on to the target database as a publisher */select app. apply_name, q. name, app. status, qt. queue_tablefrom dba_apply app, dba_queues q, dba_queue_tables qtwhere 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 % ';
/* Create a change table: EMP table */sqlplus cdc_stg_pub/* log on to the target database as a publisher */begin dbms_cdc_publish.create_change_table (owner => 'cdc _ stg_pub ', change_table_name => 'empt_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;/* grant permissions to subscriber */
/* Create a change table: dept table */sqlplus cdc_stg_pub/* log on to the target database as a publisher */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;/* grant permissions to subscribers */
/* Verify */sqlplus cdc_stg_pub/* log on to the target database as a publisher */select change_table_name, change_set_name, source_schema_name, required change_tableswhere 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_databasefrom dba_streams_table_ruleswhere rule_owner = 'cdc _ stg_pub 'and table_owner = 'cdc _ source' order by table_name, rule_type, streams_type ;/3.3 activate CDCSqlplus cdc_stg_pub/* log on to the target database as a publisher */begin databases (change_set_name => 'emp_dept_set ', enable_capture => 'y'); end ;/
/* Verify */select apply_name, statusfrom dba_applywhere apply_user = 'cdc _ stg_pub' and apply_name like '% emp_dept % '; /* log on to the source database */sqlplus sys/admin as sysdbaalter system switch logfile;/* log on to the target database and verify */select capture_name, state, total_messages_capturedfrom v $ streams_capturewhere capture_name like '% emp_dept %'; select group #, thread #, sequence #, archived, status from V $ standby_log;3.4 create a subscriptionSqlplus cdc_stg_user/cdc_stg_userbegin aggregate (change_set_name => 'emp_dept_set ', description => 'emp' and dept change subset', 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 ;/
/* Activate subscription */begin dbms_cdc_subscribe.activate_subscription (
SUBSCRIPTION_NAME => 'EMP _ dept_sub1 '); end;
/* Verify */select S. SUBSCRIPTION_NAME, S. set_name, S. description, St. source_schema_name, St. source_table_name, St. view_name, SC. column_namefrom user_subscriptions S, user_subscribed_tables St, user_subscribed_columns scwhere 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_nameorder by St. source_schema_name, St. source_table_name, SC. column_name;4. query changed data4.1 view the changed data in the change tableSqlplus cdc_stg_pub/* log on to the target database as a publisher */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 view subscribersSqlplus cdc_stg_user/* log on to the target database as a subscriber */begin login (SUBSCRIPTION_NAME => 'emp_dept_sub1 '); end; // * Verify */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 $;
/* Delete the change set */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 $;