configure_streams

 

connect SYS/&sys_pwd_source@STRM1.NET as SYSDBA

create user STRMADMIN identified by STRMADMIN;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

connect SYS/&sys_pwd_dest@STRM2.NET as SYSDBA

create user STRMADMIN identified by STRMADMIN;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

 

 

/* Step 2 - Connected as the Streams Administrator, create the streams queue and the database link that will be used for propagation at STRM1.NET. */

conn strmadmin/strmadmin@strm1.net 

BEGIN 

DBMS_STREAMS_ADM.SET_UP_QUEUE( 

queue_name => 'STREAMS_QUEUE', 

queue_table =>'STREAMS_QUEUE_TABLE', 

queue_user => 'STRMADMIN'); 

END; 

/

conn sys/oracle@strm1.net as sysdba

create public database link STRM2.NET using 'strm2.net'; 

conn strmadmin/strmadmin@strm1.net 

create database link STRM2.NET connect to strmadmin identified by strmadmin;

check your database link using   select * from dual@STRM2.NET

/* Step 3 - Connect as the Streams Administrator in the targetsite STRM2.NET and create the streams queue */

conn strmadmin/strmadmin@strm2.net 

BEGIN 

DBMS_STREAMS_ADM.SET_UP_QUEUE( 

queue_name => 'STREAMS_QUEUE', 

queue_table =>'STREAMS_QUEUE_TABLE', 

queue_user => 'STRMADMIN'); 

END; 

/

/*Step 4 -Connected to STRM1.NET, create CAPTURE and PROPAGATION rules for HR.EMPLOYESS */ 

conn strmadmin/strmadmin@strm1.net 

BEGIN  

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(  

table_name => 'HR.EMPLOYEES',  

streams_name => 'STRMADMIN_PROP',  

source_queue_name => 'STRMADMIN.STREAMS_QUEUE',  

destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@STRM2.NET',  

include_dml => true,  

include_ddl => true,  

source_database => 'STRM1.NET');  

END;  

/  

BEGIN    

DBMS_STREAMS_ADM.ADD_TABLE_RULES(    

  table_name     => 'HR.EMPLOYEES',    

  streams_type    => 'CAPTURE',    

  streams_name    => 'STRMADMIN_CAPTURE',    

  queue_name      => 'STRMADMIN.STREAMS_QUEUE',    

  include_dml     => true,    

  include_ddl     => true,    

  source_database => 'STRM1.NET');    

END;    

/  

 

/*Step 5 - Connected as STRMADMIN at STRM2.NET, create APPLY rules for HR.EMPLOYEES */

conn STRMADMIN/STRMADMIN@strm2.net 

BEGIN 

DBMS_STREAMS_ADM.ADD_TABLE_RULES( 

table_name => 'HR.EMPLOYEES', 

streams_type => 'APPLY', 

streams_name => 'STRMADMIN_APPLY', 

queue_name => 'STRMADMIN.STREAMS_QUEUE', 

include_dml => true, 

include_ddl => true, 

source_database => 'STRM1.NET'); 

END; 

/

BEGIN  

  DBMS_APPLY_ADM.ALTER_APPLY(  

    apply_name => 'STRMADMIN_APPLY',  

    apply_user => 'HR');  

END;  

/  

BEGIN  

  DBMS_APPLY_ADM.SET_PARAMETER(  

    apply_name => 'STRMADMIN_APPLY',  

    parameter  => 'disable_on_error',  

    value      => 'n');  

END;  

/  

 

/*Step 6 - Take an export of the table at STRM1.NET */

exp USERID=SYSTEM/oracle@strm1.net TABLES=HR.EMPLOYEES FILE=hr.dmp 

LOG=hr_exp.log OBJECT_CONSISTENT=Y STATISTICS = NONE 

/*Step 7 - Transfer the export dump file to STRM2.NET and import */

imp USERID=SYSTEM/<password>@strm2.net CONSTRAINTS=Y FULL=Y FILE=hr.dmp 

IGNORE=Y COMMIT=Y LOG=hr_imp.log STREAMS_INSTANTIATION=Y

/*Step 8 - Start Apply and capture */

conn strmadmin/strmadmin@strm2.net  

BEGIN  

DBMS_APPLY_ADM.START_APPLY(  

apply_name => 'STRMADMIN_APPLY');  

END;  

/ 

conn strmadmin/strmadmin@strm1.net  

BEGIN  

DBMS_CAPTURE_ADM.START_CAPTURE(  

capture_name => 'STRMADMIN_CAPTURE');  

END;  

/ 

 

For bidirectional streams setup, Please run steps 1 through 8 after interchanging Db1 and Db2. Caution should be exercised while setting the instantiation SCN this time as one maynot want to export and import the data. Export option ROWS=N can be used for the instantiation of objects from DB2--> DB1.

 

 

SAMPLE OUTPUT

/* Perform changes HR.EMPLOYEES and confirm that these are applied to tables on the destination */

conn hr/hr@strm1.net 

insert into hr.Employees values (99999,'TEST','TEST','TEST@oracle','1234567',sysdate,'ST_MAN',null,null,null,null); 

commit;

conn hr / hr@strm2.net 

select  APPLY_NAME, ERROR_MESSAGE  from dba_apply_error;

select * From employees where employee_id=99999;