ORACLE STREAMS REPLICATION

Set up below parameters on both databases (STRPROD, STRDEV)

steps are given below:

1. Enable ARCHIVELOG MODE on both database


2. Create Stream administrator User
Source Database: STRPROD
SQL> conn sys@STRPROD as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

Target Database: STRDEV
SQL> conn sys@STRDEV as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

3. Setup INIT parameters
Source Database: STRPROD
SQL> conn sys@STRPROD as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 315 m;

System altered.

Target Database: STRDEV
SQL> conn sys@STRDEV as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 315 m;

System altered.

4. Create Database Link
Target Database: STRPROD
SQL> conn strmadmin/strmadmin@STRPROD
Connected.
SQL> create database link STRDEV
2 connect to strmadmin
3 identified by strmadmin
4 using 'STRDEV';

Database link created.

Source Database: STRDEV
SQL> conn strmadmin/strmadmin@STRDEV
Connected.
SQL> create database link STRPROD
2 connect to strmadmin
3 identified by strmadmin
4 using 'STRPROD';

Database link created.

5. Setup Source and Destination queues
Source Database: STRPROD
SQL> conn strmadmin/strmadmin@STRPROD
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

Target Database: STRDEV
SQL> conn strmadmin/strmadmin@STRDEV
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

6. Setup Schema for streams
Schema: CMSTEXT
Table: TEST
NOTE: Unlock CMSTEXT schema because in 11g CMSTEXT schema is locked by default
Source Database: STRPROD
SQL> conn sys@STRPROD as sysdba
Enter password:
Connected.
SQL> alter user CMSTEXT account unlock identified by Cmstext;

User altered.

SQL> conn CMSTEXT/Cmstext@STRPROD
Connected.
SQL> create table DEMO ( no number primary key,name varchar2(20),ddate date);

Table created.

Target Database: STRDEV
SQL> conn sys@STRDEV as sysdba
Enter password:
Connected.
SQL> alter user CMSTEXT account unlock identified by Cmstext;

User altered.

SQL> conn CMSTEXT/cmstext@STRDEV
Connected.
SQL> create table DEMO ( no number primary key,name varchar2(20),ddate date);

Table created.

7. Setup Supplemental logging at the source database
Source Database: STRPROD
SQL> conn CMSTEXT/cmstext@STRPROD
Connected.
SQL> alter table DEMO
2 add supplemental log data (primary key,unique) columns;
Table altered.

Note:

-- To enable minimal supplemental logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- other supplemental logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- to drop supplemental logging

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

-- to control supplemental logging

SELECT supplemental_log_data_min, supplemental_log_data_pk,

supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all

FROM v$database;

8. Configure capture process at the source database
Source Database: STRPROD
SQL> conn strmadmin/strmadmin@STRPROD
Connected.
SQL> begin dbms_streams_adm.add_schema_rules
2 ( schema_name => ‘CMSTEXT’,
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
11 /

PL/SQL procedure successfully completed.

9. Configure the propagation process
Source Database: STRPROD
SQL> conn strmadmin/strmadmin@STRPROD
Connected.
SQL> begin dbms_streams_adm.add_schema_propagation_rules
2 ( schema_name => ‘CMSTEXT’,
3 streams_name => 'STRPROD_TO_STRDEV',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@STRDEV',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'STRPROD',
9 inclusion_rule => true);
11 end;
11 /

PL/SQL procedure successfully completed.
11. Set the instantiation system change number (SCN)
Source Database: STRPROD
SQL> CONN STRMADMIN/STRMADMIN@STRPROD
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_schema_instantiation_scn@STRDEV
6 ( source_schema_name => 'CMSTEXT',
7 source_database_name => 'STRPROD',
8 instantiation_scn => source_scn);
9 end;
11 /

PL/SQL procedure successfully completed.

11. Configure the apply process at the destination database
Target Database: STRDEV
SQL> conn strmadmin/strmadmin@STRDEV
Connected.
SQL> begin dbms_streams_adm.add_schema_rules
2 ( schema_name => ‘CMSTEXT’,
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'STRPROD',
9 inclusion_rule => true);
11 end;
11 /

PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: STRPROD
SQL> conn strmadmin/strmadmin@STRPROD
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /

PL/SQL procedure successfully completed.
Target Database: STRDEV
SQL> conn strmadmin/strmadmin@STRDEV
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /

PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn CMSTEXT/Cmstext@STRPROD
Connected.
SQL> --DDL operation
SQL> alter table DEMO add (flag char(1));

Table altered.

SQL> --DML operation
SQL> begin
2 insert into DEMO values (1,'rikesh',sysdate,1);
3 commit;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> conn CMSTEXT/Cmstext@STRDEV
Connected.
SQL> --DEMO DDL operation
SQL> desc DEMO
Name Null? Type
----------------------------------------- -------- ----------------------------

NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)

SQL> --DEMO DML operation
SQL> select * from DEMO;

NO NAME DDATE F
---------- -------------------- --------- -
1 rikesh 14-JAN-08 1