Quickly setup a stream environment.

This article is intended to quickly setup a stream environment for playing in which the schema HR in primary side will be in sync with the one in target side.[@more@]

Primary Side. O01DMS0
Target Side. O01LEE3

########################################################################################
1.1> Logon to SYS to create an account for stream at primary side.

SQL> show user
USER is "SYS"
SQL>
SQL> create user strmadmin identified by strmadmin
2 default tablespace tbs_stream temporary tablespace temp;

User created.

SQL>
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;

Grant succeeded.

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

PL/SQL procedure successfully completed.

begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/

########################################################################################

1.2> Logon to SYS to create an account for stream at Target Side.

SQL> show user
USER is "SYS"
SQL>
SQL> create user strmadmin identified by strmadmin
2 default tablespace tbs_stream temporary tablespace temp;

User created.

SQL>
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;

Grant succeeded.

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

PL/SQL procedure successfully completed.

begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/

########################################################################################

2 Create DB Link.

tnsnames.ora at primary side.

O01LEE3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom)
(PROTOCOL = tcp)
(Host = 10.248.34.226)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = O01LEE3)
))
O01LEE3.LOCAL.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom)
(PROTOCOL = tcp)
(Host = 10.248.34.226)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = O01LEE3)
))

tnsnames.ora at Target Side.

O01DMS0 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcpcom)(PROTOCOL = tcp)(Host = 10.248.38.47)(Port = 1521))
)
(CONNECT_DATA =
(SID = O01DMS0)
)
)

O01DMS0.LOCAL.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcpcom)(PROTOCOL = tcp)(Host = 10.248.38.47)(Port = 1521))
)
(CONNECT_DATA =
(SID = O01DMS0)
)
)

2.1> Switch to strmadmin to create database link at primary side.

SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link O01LEE3.LOCAL.COM connect to strmadmin identified by strmadmin using 'O01LEE3';

Database link created.

SQL>
SQL>
SQL> select count(*) from ;

COUNT(*)
----------
0
########################################################################################

2.2> Switch to strmadmin to create database link at Target Side.

SQL> create database link O01DMS0.LOCAL.COM connect to strmadmin identified by s
trmadmin using 'O01DMS0';

Database link created.

SQL> select count(*) from ;

COUNT(*)
----------
0

########################################################################################

3.1> create queue at primary side.

SQL> show user
USER is "STRMADMIN"
SQL>
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table => 'primary_queue_table',
4 queue_name => 'primary_queue');
5 end;
6 /

PL/SQL procedure successfully completed.

begin
dbms_streams_adm.set_up_queue(
queue_table => 'primary_queue_table',
queue_name => 'primary_queue');
end;
/

########################################################################################

3.2> create backup queue at Target Side.

SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table => 'backup_queue_table',
4 queue_name => 'backup_queue');
5 end;
6 /

PL/SQL procedure successfully completed.

begin
dbms_streams_adm.set_up_queue(
queue_table => 'backup_queue_table',
queue_name => 'backup_queue');
end;
/

###########################################################################################
4> Create capture process at Primary Side.

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'hr',
streams_type => 'capture',
streams_name => 'capture_primary',
queue_name => 'strmadmin.primary_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'O01DMS0.LOCAL.COM',
inclusion_rule => true);
end;
/

###########################################################################################

5> create the schema at Target Side that will be in sync to primary.

SQL> create user hr identified by hr default tablespace hr_data;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.

C:Documents and Settingse493677>exp file=hrforstream.dmp object_
consistent=y rows=y

C:Documents and Settingse493677>imp system/oracle file=hrforstream.dmp ignore=
y commit=y streams_instantiation=y fromuser=hr touser=hr

###########################################################################################

6> Create propagation process at primary side.

SQL> begin
2 dbms_streams_adm.add_schema_propagation_rules(
3 schema_name => 'hr',
4 streams_name => 'dms_to_lee',
5 source_queue_name => 'strmadmin.primary_queue',
6 destination_queue_name => ,
7 include_dml => true,
8 include_ddl => true,
9 include_tagged_lcr => false,
10 source_database => 'O01DMS0.LOCAL.COM',
11 inclusion_rule => true);
12 end;
13 /

PL/SQL procedure successfully completed.

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'hr',
streams_name => 'dms_to_lee',
source_queue_name => 'strmadmin.primary_queue',
destination_queue_name => ,
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'O01DMS0.LOCAL.COM',
inclusion_rule => true);
end;
/

###########################################################################################

7> Set propagation schedule at primary side.

SQL> begin
2 dbms_aqadm.alter_propagation_schedule(
3 queue_name => 'primary_queue',
4 destination => 'O01LEE3.LOCAL.COM',
5 latency => 0);
6 end;
7 /

PL/SQL procedure successfully completed.

begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'primary_queue',
destination => 'O01LEE3.LOCAL.COM',
latency => 0);
end;
/

###########################################################################################

8> Create apply process at Target Side.

SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'hr',
4 streams_type => 'apply',
5 streams_name => 'apply_lee',
6 queue_name => 'strmadmin.backup_queue',
7 include_dml => true,
8 include_ddl => true,
9 include_tagged_lcr => false,
10 source_database => 'O01DMS0.LOCAL.COM',
11 inclusion_rule => true);
12 end;
13 /

PL/SQL procedure successfully completed.

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'hr',
streams_type => 'apply',
streams_name => 'apply_lee',
queue_name => 'strmadmin.backup_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'O01DMS0.LOCAL.COM',
inclusion_rule => true);
end;
/

###########################################################################################
9 Start the stream.

9.1> Start apply process at Target Side.

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

PL/SQL procedure successfully completed.

begin
dbms_apply_adm.start_apply(
apply_name => 'apply_lee');
end;
/

9.2> Start capture process at primary side.

SQL> begin
2 dbms_capture_adm.start_capture(
3 capture_name => 'capture_primary');
4 end;
5 /

PL/SQL procedure successfully completed.

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

###########################################################################################

10 Stop the stream.

10.1> Stop the capture process at primary side.

SQL> begin
2 dbms_capture_adm.stop_capture(
3 capture_name => 'capture_primary');
4 end;
5 /

PL/SQL procedure successfully completed.

begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_primary');
end;
/

10.2> Stop the apply process at Target Side.

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

PL/SQL procedure successfully completed.

begin
dbms_apply_adm.stop_apply(
apply_name => 'apply_lee');
end;
/

###########################################################################################

11 Remove stream environmnet.

11.1> Remove stream configuration at primary side.

exec DBMS_STREAMS_ADM.remove_streams_configuration();
###########################################################################################

11.2> Remove stream configuration at Target Side.

exec DBMS_STREAMS_ADM.remove_streams_configuration();

###########################################################################################

###########################################################################################
Views to check.
###########################################################################################

At Primary Side.

SELECT CAPTURE_NAME,
QUEUE_NAME,
RULE_SET_NAME,
NEGATIVE_RULE_SET_NAME,
STATUS
FROM DBA_CAPTURE;

set linesize 120
col CAPTURE_NAME for a22 trunc
col QUEUE_NAME for a22 trunc
col CAPTURED_SCN for 9999999999999999
col APPLIED_SCN for 99999999999999999
col gaps for 999999999999
col status for a8
SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN,
APPLIED_SCN,CAPTURED_SCN-APPLIED_SCN gaps
FROM DBA_CAPTURE;