Downstream Capture Configuration

1.Setting the initial Configuration, Global_names, tnsnames.ora
2.Set up Stream User and Tablespaces

CREATE TABLESPACE streamsts DATAFILE '/oradata2/DataFile/streams01.dbf' SIZE 25M AUTOEXTEND ON MAXSIZE
UNLIMITED;
create user strmadmin identified by strmadmin default tablespace streamsts temporary tablespace temp;

grant dba,select_catalog_role to strmadmin;

exec dbms_streams_auth.grant_admin_privilege('strmadmin',true);

3.Prepare for Downstream Capture- In DS01

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DS02.ARJU.NET LGWR ASYNC OPTIONAL NOREGISTER
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='send' SCOPE=SPFILE;

4.Prepare Downstream Database -In DS02

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbs1 MANDATORY
VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='receive' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('location/slog3a.rdo', 'location/slog3b.rdo') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('location/slog4.rdo', 'location/slog4b.rdo') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('location/slog5.rdo', 'location/slog5b.rdo') SIZE 500M;

SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

5.Create Queue and Database Link in DS02

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TAB_DS02',
queue_name => 'STREAMS_QUEUE_DS02',
queue_user => 'STRMADMIN');
END;
/

CREATE DATABASE LINK DF01.ARJU.NET CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'DF01.ARJU.NET';

6.Create Capture Process at DS02

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'strmadmin.streams_queue_ds02',
capture_name => 'real_time_capture_ds02',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'DS01.ARJU.NET',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/

BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'real_time_capture_ds02',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
table_name => 'PROD7',
streams_type => 'capture',
streams_name => 'real_time_capture_ds02',
queue_name => 'strmadmin.streams_queue_ds02',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'DS01.ARJU.NET',
inclusion_rule => true);
END;
/

7.Perform Instantiation-at DS02

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@DS01.ARJU.NET;
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@DF01.ARJU.NET(
source_schema_name => 'PROD7',
source_database_name => 'DS01.ARJU.NET',
instantiation_scn => iscn,
recursive => true);
END;
/

8.Configure Propagation in DS02

begin
dbms_streams_adm.add_schema_propagation_rules (
schema_name => 'PROD7',
streams_name => 'ds02_to_df01',
source_queue_name => 'strmadmin.streams_queue_ds02',
destination_queue_name => 'strmadmin.streams_queue@DF01.ARJU.NET',
include_dml => true,
include_ddl => true,
source_database => 'DS01.ARJU.NET');
end;
/

9.Set up Queue in DF01

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TAB_DF01',
queue_name => 'STREAMS_QUEUE_DF01',
queue_user => 'STRMADMIN');
END;
/

10.Configure Apply Process in DF01

begin
dbms_streams_adm.add_schema_rules (
schema_name => 'PROD7',
streams_type => 'apply',
streams_name => 'APPLY_STREAM_DF01',
queue_name => 'strmadmin.STREAMS_QUEUE_DF01',
include_dml => true,
include_ddl => true,
source_database => 'DS02.ARJU.NET');
end;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APPLY_STREAM_DF01',
parameter => 'disable_on_error',
value => 'n');
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APPLY_STREAM_DF01',
parameter => 'allow_duplicate_rows',
value => 'y');
END;
/