Performing Asynchronous HotLog Publishing in CDC Oracle

Step 1 Source Database DBA: Set the database initialization parameters:
compatible = 10.1.0
java_pool_size = 50000000;
job_queue_processes = 2

parallel_max_servers = + 5
processes = + 7
sessions = + 2
streams_pool_size = + 21 MB
undo_retention = 3600

Step 2 Source Database DBA: Alter the source database:
The second is required. The first and third are optional, but recommended. It is assumed that the database is currently running in ARCHIVELOG mode.
1.ALTER DATABASE FORCE LOGGING;
2.ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
3.This example captures rows in the HR.JOB_HISTORY table only. The source database DBA would repeat this step for each source table for which change tables will be created.
ALTER TABLE HR.JOB_HISTORY
ADD SUPPLEMENTAL LOG GROUP log_group_jobhist
(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS;Logging all columns incurs more overhead than logging selected columns.
ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;


Step 3 Source Database DBA: Create and grant privileges to the publisher:
CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
GRANT CONNECT, RESOURCE, DBA TO cdcpub;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub');

Step 4 Source Database DBA: Prepare the source tables:
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'hr.job_history');
END;

Step 5 Staging Database Publisher: Create change sets:
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
description => 'Change set for job history info',
change_source_name => 'HOTLOG_SOURCE',
stop_on_ddl => 'y',
begin_date => sysdate,
end_date => sysdate+5);
END;
/
Step 6 Staging Database Publisher: Create the change tables that will contain the changes to the source tables:
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdcpub',
change_table_name => 'job_hist_ct',
change_set_name => 'CHICAGO_DAILY',
source_schema => 'HR',
source_table => 'JOB_HISTORY',
column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE,END_DATE DATE,
JOB_ID VARCHAR(10), DEPARTMENT_ID NUMBER(4)',
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 TS_CDCPUB');
END;
/
Step 7 Staging Database Publisher: Enable the change set:
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
/
Step 8 Staging Database Publisher: Grant access to subscribers:
GRANT SELECT ON cdcpub.job_hist_ct TO subscriber1;