CONFIGURE_STREAMS_1
CONFIGURE_STREAMS_1
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/@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;