Performing Asynchronous AutoLog Publishing in CDC Oracle

Step 1 Source Database DBA: Prepare to copy redo log files from the source database:
compatible = 10.1.0
java_pool_size = 50000000
log_archive_dest_1="location=/oracle/dbs mandatory reopen=5"
log_archive_dest_2 = "service=stagingdb arch optional noregister reopen=5
template = /usr/oracle/dbs/arch1_%s_%t_%r.dbf"
log_archive_dest_state_1 = enable
log_archive_dest_state_2 = enable
log_archive_format="arch1_%s_%t_%r.dbf"
remote_login_passwordfile=shared

Step 2 Staging Database DBA: Set the database initialization parameters:
compatible = 10.1.0
global_names = true
java_pool_size = 50000000
job_queue_processes = 2
parallel_max_servers = + 5
processes = + 7
remote_login_passwordfile = shared
sessions = + 2
streams_pool_size = + 21 MB
undo_retention = 3600

Step 3 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_job_hist (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 4 Staging 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 CONNECT, RESOURCE, DBA TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');

Step 5 Source Database DBA: Build the LogMiner data dictionary:
The source database DBA builds a LogMiner data dictionary at the source database so that log transport services can transport this data dictionary to the staging database.When building the LogMiner data dictionary, the source database DBA should get the SCN value of the data dictionary build. In Step 8, when the publisher creates a change source, he or she will need to provide this value as the first_scn parameter.
SET SERVEROUTPUT ON
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;
/
The first_scn value is 207722

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

Step 7 Source Database DBA: Get the global name of the source database:
SELECT GLOBAL_NAME FROM GLOBAL_NAME;

Step 8 Staging Database Publisher: Identify each change source database and create the change sources:
BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_source_name => 'CHICAGO',
description => 'test source',
source_database => 'HQDB',
first_scn => 207722);
END;
/

Step 9 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 => 'CHICAGO',
stop_on_ddl => 'y');
END;
/

Step 10 Staging Database Publisher: Create the change 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 VARCHAR2(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_CHICAGO_DAILY');
END;
/

Step 11 Staging Database Publisher: Enable the change set:
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
/

Step 12 Source Database DBA: Switch the redo log files at the source database:
ALTER SYSTEM ARCHIVE LOGFILE;

Step 13 Staging Database Publisher: Grant access to subscribers:
GRANT SELECT ON cdcpub.job_hist_ct TO subscriber1;