oracle STREAM 單向使用者配置流程步驟總結

alter system set aq_tm_processes=2 scope=both;
alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set undo_retention=3600 scope=both;
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=25M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;

在source與slave主機建立db_link。

主SOURCE stream佇列：
begin
dbms_streams_adm.set_up_queue(
queue_table => 'RACDB_queue_table',
queue_name => 'RACDB_queue');
end;
/

主source capture捕獲程式：
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'capture',
streams_name => 'capture_RACDB',
queue_name => 'strmadmin.RACDB_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/

主source propagation傳播程式：
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'STREAM',
streams_name => 'RACDB_to_ORAPRI',
source_queue_name => 'strmadmin.RACDB_queue',
destination_queue_name => ,
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'RACDB',
inclusion_rule => true);
end;
/
從slave receive 接收佇列：
begin
dbms_streams_adm.set_up_queue(
queue_table => 'ORAPRI_queue_table',
queue_name => 'ORAPRI_queue');
end;
/

從slave APPLY程式：
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'STREAM',
streams_type => 'apply',
streams_name => 'apply_ORAPRI',
queue_name => 'strmadmin.ORAPRI_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'RACDB',
inclusion_rule => true);
end;
/

從slave start：
begin
dbms_apply_adm.start_apply(
apply_name => 'apply_ORAPRI');
end;
/

主source start：
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_RACDB');
end;
/

修改LCR實現實時應用(可能報錯11g)：
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'RACDB_queue',
destination => 'ORAPRI',
latency => 0);
end;
/
修改LCR實現實時應用(11g)：
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'RACDB_queue',
destination => 'ORAPRI',
destination_queue => 'ORAPRI_queue',
latency => 0);
end;
/

 

 

 

主：
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'racdb_queue',
destination => 'orapri',
latency => 0);
end;
/
主：
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'racdb_queue',
destination => 'orapri',
destination_queue => 'orapri_queue',
latency => 5);
end;
/