Steps For Implementing STREAMS in Oracle

SOURCE DATABASE:- dbbackup
TARGET DATABASE:- TEST

1. ENABLE ARCHIVE LOG IN BOTH THE DATABASES.
2. CONNECT TO SOURCR DATABASE(DBBACKUP).

sql> CREATE USER STREAMADMIN IDENTIFIED BY STREAMADMIN; GRNAT CONNECT , RESOURCE,DBA TO STREAMADMIN;
sql> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'STREAMADMIN',GRANT_PRIVILEGES=>TRUE); END;/
sql> GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY TO STREAMADMIN;

3. CONNECT TO TARGET DATABASE(TEST):-

sql> CREATE USER STREAMADMIN IDENTIFIED BY STREAMADMIN; GRNAT CONNECT , RESOURCE,DBA TO STREAMADMIN;
sql> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'STREAMADMIN',GRANT_PRIVILEGES=>TRUE); END;\
sql> GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY TO STREAMADMIN;

4.CONNECT TO SOURCR DATABASE(DBBACKUP).
sql>alter system set global_names=true;
sql>alter system set streams_pool_size = 100 m;

5. CONNECT TO TARGET DATABASE(TEST):-

sql>alter system set global_names=true;
sql>alter system set streams_pool_size = 100 m;

6. CONNECT TO SOURCR DATABASE(DBBACKUP) AS STREAMADMIN USER.

sql>CREATE DATABASE LINK TEST CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'TEST';

7.CONNECT TO TARGET DATABASE(TEST) AS STREAMADMIN USER:-

sql>CREATE DATABASE LINK DBBACKUP CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'DBBACKUP';

8. SET UP SOURCE & DESTINATION QUEUE:-

A)CONNECT TO SOURCE DATABASE AS STREAMADMIN USER:-

sql>EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

B)CONNECT TO TARGET DATABASE(TEST) AS STREAMADMIN USER:-
sql>EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();


9. CONNECT TO SOURCE DATABASE(dbbackup) AS SCOTT USER:-
sql> create table STREAM_TABLE ( no number primary key,name varchar2(20),ddate date);
sql>alter table STREAM_TABLE add supplemental log data (primary key,unique) columns;

10. CONNECT TO TARGET DATABASE(TEST) AS SCOTT USER:-

sql> create table STREAM_TABLE ( no number primary key,name varchar2(20),ddate date);
sql>alter table STREAM_TABLE add supplemental log data (primary key,unique) columns;

11. CONFIGURE CAPTURE PROCESS AT SOURCE DATABASE(dbbackup) AS STREAMADMIN USER:-

Begin DBMS_STREAMS_ADM.SET_UP_QUEUE (
QUEUE_TABLE=>'STREAMS_QUEUE_TABLE',
QUEUE_NAME =>'STREAMS_QUEUE',
QUEUE_USER=>'STREAMADMIN');
END;
/


sql> begin dbms_streams_adm.add_table_rules
( table_name => 'scott.STREAM_TABLE',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name=> 'STREAMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

12. CONFIGURE PROPOGATION PROCESS AT SOURCE DATABASE(DBBACKUP) AS STREAMADMIN USER:-

sql> begin dbms_streams_adm.add_table_propagation_rules
( table_name => 'scott.STREAM_TABLE',
streams_name => 'DBBACKUP_TO_TEST',
source_queue_name => 'STREAMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STREAMADMIN.STREAMS_QUEUE@TEST',
include_dml => true,
include_ddl => true,
source_database => 'DBBACKUP',
inclusion_rule => true);
end;
/


13. Set the instantiation system change number (SCN). CONNECT TO SOURCE DATABASE(DBBACKUP) AS STREAMADMIN USER:-
sql> declare source_scn number;
begin
source_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@TEST
( source_object_name => 'scott.STREAM_TABLE',
source_database_name => 'DBBACKUP',
instantiation_scn => source_scn);
end;/


14.CONFIGURE APPLY PROCESS AS DESTINATION DATABASE(TEST). CONNEC TO THE TEST DATABASE AS STREAMADMIN USER:-

Begin DBMS_STREAMS_ADM.SET_UP_QUEUE (
QUEUE_TABLE=>'STREAMS_QUEUE_TABLE',
QUEUE_NAME =>'STREAMS_QUEUE',
QUEUE_USER=>'STREAMADMIN');
END;
/


sql>begin dbms_streams_adm.add_table_rules
( table_name => 'scott.STREAM_TABLE',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'STREAMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DBBACKUP',
inclusion_rule => true);
end;


15. START CAPTURE & APPLY PROCESS AT SOURCE DATABASE(DBBACKUP). CONNECT TO THE DATABASE AS STREAMADMIN USER:-

sql> begin dbms_capture_adm.start_capture
( capture_name => 'capture_stream');
end;
/


16. START APPLY PROCESS AS DESTINATION DATABASE(TEST). CONNEC TO THE TEST DATABASE AS STREAMADMIN USER:-

SQL>begin dbms_apply_adm.set_parameter
( apply_name => 'apply_stream',
parameter => 'disable_on_error',
value => 'n');
end;
/


sql>begin
dbms_apply_adm.start_apply
( apply_name => 'apply_stream');
end;