oracle stream之schema級複製

SQL> create tablespace stream_tbs datafile 'd:apporadataorclstream_tbs.dbf' size 100m;

Tablespace created.

SQL> create user strmadmin identified by strmadmin default tablespace stream_tbs;

User created.

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.

停止apply程式

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

PL/SQL procedure successfully completed.

測試指令碼

================================================================================

begin

dbms_streams_adm.set_up_queue(
queue_table=> 'source_queue_table',
queue_name=> 'source_queue'
);
end;


begin
dbms_streams_adm.set_up_queue(
queue_table=> 'target_queue_table',
queue_name=> 'target_queue'
);
end;

===========================
source database:
exec dbms_streams_adm.set_up_queue();



target database:
exec dbms_streams_adm.set_up_queue();



grant connect,resource,dba,aq_administrator_role to strmadmin;

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

------------------------------------------------------------------------------------------------------------------------------

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;



===========================

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.source_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;

------------------------------------------------------------------------------------------------------------------------------

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.streams_queue',
destination_queue_name=> 'strmadmin.streams_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;



=============================

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.source_queue',
destination_queue_name=> 'strmadmin.target_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;

------------------------------------------------------------------------------------------------------------------------------

connect strmadmin/strmadmin@oracle10g
set serveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;


connect strmadmin/strmadmin
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=> 'scott',
source_database_name => 'oracle10g',
instantiation_scn => &iscn);
END;



===========================================
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@test.com.cn(
source_object_name => 'scott',
source_database_name => 'oracle10g.com.cn',
instantiation_scn => iscn);
END;

------------------------------------------------------------------------------------------------------------------------------

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'apply',
streams_name=> 'apply_stream',
queue_name=> 'strmadmin.target_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true,
source_database=> 'oracle10g.com.cn');
end;

------------------------------------------------------------------------------------------------------------------------------

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

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


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

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


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


exec DBMS_STREAMS_ADM.remove_streams_configuration();