Setup Streams for table replication

Step by step Oracle Streams one way configuration

On Source: (set DB in archive log mode if it is in noarchive log mode)

select name, log_mode from v$database;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

-- creating a sample user AJ whose table will be replicated
select username from dba_users where username like 'AJ';

CREATE USER AJ IDENTIFIED BY aj
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO AJ;
GRANT CONNECT, RESOURCE TO AJ;

-- check the value of parameters
show parameter JOB_QUEUE_PROCESSES
show parameter AQ_TM_PROCESSES
show parameter GLOBAL_NAMES

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;

select username from dba_users where upper(username) like 'STRMADMAJ';

CREATE USER STRMADMAJ IDENTIFIED BY STRMADMAJ
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

Grant all required privileges:
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO STRMADMAJ;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMAJ;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMAJ; 
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMAJ; 
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMAJ;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMAJ;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMAJ;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMAJ',
grant_option => FALSE);
END;
/
On src:
SELECT LOG_MODE FROM V$DATABASE;

- configure tnsnames.ora on source and target host
Src:
DBSRC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bluemoon1.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBSRC.localdomain)
    )
  )
Trgt:
DBTRGT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bluemoon2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBTRGT.localdomain)
    )
  )

col owner for a30
col db_link for a60
col username for a20
set lines 160 pages 999
select owner,db_link,username from dba_db_links;

grant dba to STRMADMAJ;

Creating Links in both SrcDB and DesDB:

On Src:
conn STRMADMAJ/STRMADMAJ@DBSRC

CREATE DATABASE LINK DBTRGT.localdomain CONNECT TO STRMADMAJ IDENTIFIED BY STRMADMAJ USING 'DBTRGT';

select sysdate from dual@DBTRGT;

On Trgt:
conn STRMADMAJ/STRMADMAJ@DBTRGT

CREATE DATABASE LINK DBSRC.localdomain CONNECT TO STRMADMAJ IDENTIFIED BY STRMADMAJ USING 'DBSRC';

select sysdate from dual@DBSRC;

On Src :
conn aj/aj@DBSRC
create table test (id number, value varchar2(20));
alter table test add constraint test_pk primary key (id);
alter table aj.test add supplemental log group my_test_all(id, value) ALWAYS;
alter table aj.test drop supplemental log group my_test_all;


On Trgt:
conn aj/aj@DBTRGT
create table test (id number, value varchar2(20));
alter table test add constraint test_pk primary key (id);

On Src and Trgt: DBSRC DBTRGT
conn / as sysdba

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
 queue_table => 'STRMADMAJ.STREAMS_QUEUE_TABLE',
 queue_name => 'STRMADMAJ.STREAMS_QUEUE',
 queue_user => 'STRMADMAJ');
END;
/


-:CONFIGURE PROPAGATION PROCESS :-
On Src db:

CONN STRMADMAJ/STRMADMAJ@DBSRC
begin
  dbms_streams_adm.add_table_propagation_rules(
 table_name       => 'AJ.TEST',
 streams_name   => 'STRMADMAJ_PROP',
 source_queue_name  => 'STRMADMAJ.STREAMS_QUEUE',
 destination_queue_name => 'STRMADMAJ.STREAMS_QUEUE@DBTRGT',
 include_dml       => true,
 include_ddl       => false,
 include_tagged_lcr  => false,
 source_database   => 'DBSRC');
end;
/
-:CONFIGURE CAPTURE PROCESS :-
On Src db:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
 table_name      => 'AJ.TEST',
 streams_type    => 'CAPTURE',
 streams_name    => 'STRMADMAJ_CAPTURE',
 queue_name      => 'STRMADMAJ.STREAMS_QUEUE',
    include_dml     => true,   
    include_ddl     => false,
    source_database => 'DBSRC');
END;
/


-:CONFIGURE APPLY PROCESS:-
On Dest db: DBTRGT

conn STRMADMAJ/STRMADMAJ@DBTRGT

BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name => 'AJ.TEST',
        streams_type => 'APPLY',
        streams_name => 'STRMADMAJ_APPLY',
        queue_name => 'STRMADMAJ.STREAMS_QUEUE',
        include_dml => true,
        include_ddl => false,
        source_database => 'DBSRC');
END;
/

BEGIN 
  DBMS_APPLY_ADM.ALTER_APPLY( 
    apply_name => 'STRMADMAJ_APPLY', 
    apply_user => 'AJ'); 
END; 
/  

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

host exp USERID=SYSTEM/sysc0nf@DBSRC TABLES=AJ.TEST FILE=TEST.dmp LOG=TEST_exp.log OBJECT_CONSISTENT=Y STATISTICS = NONE 
host imp USERID=SYSTEM/sysc0nf@DBTRGT CONSTRAINTS=Y FULL=Y FILE=TEST.dmp IGNORE=Y COMMIT=Y LOG=TEST_imp.log STREAMS_INSTANTIATION=Y

/* Start Apply and capture */
conn STRMADMAJ/STRMADMAJ@DBTRGT

BEGIN 
DBMS_APPLY_ADM.START_APPLY( 
apply_name => 'STRMADMAJ_APPLY'); 
END; 
/ 

conn STRMADMAJ/STRMADMAJ@DBSRC

BEGIN 
DBMS_CAPTURE_ADM.START_CAPTURE( 
capture_name => 'STRMADMAJ_CAPTURE'); 
END; 
/  
---------------- STREAMS CONFIGURATION DONE ----------------