Step by Step config ORACLE streams

 Step by Step config ORACLE streams
ENV:ORACLE DATABASE 11.2,RHEL AS 4

1,At the source and destionation database,issuse the followings;
SQL>create tablespace streams_tbs datafile '/u01/streams_tbs01.dbf' size 100m
    autoextend on next 100m autoextend on
    extent management local autoallocate
    segment space management auto;
SQL>create user streamsadmin identified by streamsadmin
    default tablespace streams_tbs;

SQL>grant connect,resource,dba to streamsadmin;
SQL>grant execute on dbms_lock to streamsadmin;
SQL>exec dbms_streams_auth.grant_admin_privilege('STREAMSADMIN');

 

2,At the source database,issuse the followings;
SQL>alter system set global_names=true;
SQL>alter database rename global_name to st1.com;
SQL>select * from global_name;
add the destionation database entry to tnsnames.ora

3,At the destination database,issuse the followings;
SQL>alter system set global_names=true;
SQL>alter database rename global_name to st2.com;
SQL>select * from global_name;
add the source database entry in tnsnames.ora

 

4,At the destination database,logon as streamsadmin;
SQL>create database link st1.com connect to streamsadmin indentified by streamsadmin using 'st1.com';

add the queue,
SQL>begin
   dbms_streams_adm.set_up_queue
  (queue_name=>'ST_D_Q',
   queue_user=>'STREAMSADMIN');
   end;

add the apply rule,
SQL>begin
dbms_streams_adm.add_schema_rules(
schema_name=>'FRANK',
streams_name=>'STA',
streams_type=>'APPLY',
include_ddl=>true,
include_dml=>true,
queue_name=>'ST_D_Q',
inclusion_rule=>true,
source_database=>'ST1.COM');
end;

5,At the source database,logon as streamsadmin;

SQL>create database link st2.com connect to streamsadmin indentified by streamsadmin using 'st2.com';

add the queue,
SQL>begin
dbms_streams_adm.set_up_queue
  (queue_name=>'ST_S_Q',
   queue_user=>'STREAMSADMIN');
end;

add capture rule,

SQL>begin
dbms_streams_adm.add_schema_rules(
schema_name=>'FRANK',
streams_name=>'STC',
streams_type=>'CAPTURE',
include_ddl=>true,
include_dml=>true,
queue_name=>'ST_S_Q',
inclusion_rule=>true,
source_database=>'ST1.COM');
end;

add propagation rule,
SQL>begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=>'FRANK',
streams_name=>'STP',
source_queue_name=>'ST_S_Q',
destination_queue_name=>'[e-mail]',
include_ddl=>true,
include_dml=>true,
queue_to_queue=>true,
source_database=>'ST1.COM');
end;

 

6,exp the schema at the source and imp to destination
display the scn at source database
SQL>select current_scn from v$database;

export,
expdp streamsadmin/streamsadmin directory=u file=01.dmp schemas=frank flashback_scn=809756

at the destation,imp schema data,
impdp streamsadmin/streamsadmin directory=u file=01.dmp schemas=frank

7,at destination,start apply

SQL>exec dbms_apply_adm.start_apply('STA');

 

8,at source,start capture and propagation,

SQL>exec dbms_capture_adm.start_capture('STC');
SQL>exec dbms_propagation_adm.start_propagation('STP');