Oracle 9i设置步骤：
源数据库SID：source
目标数据库SID：target
一。设置如下初始化参数：
AQ_TM_PROCESSES = 1－周忱10z
GLOBAL_NAMES = true
JOB_QUEUE_PROCESSES >=2
COMPATIBLE = 9.2.0 or higher
LOG_PARALLELISM = 1
二。源数据库必须工作在自动归档模式：
*.log_archive_start=true
*.log_archive_dest_1='LOCATION=/tpdata/admin/source/arc'
三。在source社团宣传海报上建strmadmin用户：
create ur strmadmin identified by strmadmin default tablespace DATA诚实的什么
grant connect,resource,lect_catalog_role to strmadmin
grant execute on dbms_aqadm to strmadmin
grant execute on dbms_capture_adm to strmadmin
grant execute on dbms_propagation_adm to strmadmin
grant execute on dbms_streams_adm to strmadmin
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/
四。在source上建streams queue
connect strmadmin/strmadin@source
SQL> exec dbms_streams_adm.t_up_queue();
PL/SQL procedure successfully completed
五。建source到target的dblink
create databa link target connect to strmadmin identified by strmadmin using 'target'
风起云淡六。在target上建strmadmin用户
create ur strmadmin identified by strmadmin default tablespace DATA
grant connect,resource,lect_catalog_role to strmadmin
grant all st to strmadmin
（其中wy.test是target库里面的target table）
grant execute on dbms_apply_adm to strmadmin
grant execute on dbms_aqadm to strmadmin
grant execute on dbms_streams_adm to strmadmin
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
儿童脑筋急转弯大全/
七。在target上建streams queue
connect strmadmin/strmadin@target
SQL> exec dbms_streams_adm.t_up_queue();
PL/SQL procedure successfully completed
八。在source上建LogMiner tablespace
create tablespace logmnrts datafile '/tpdata/app/oracle/oradata/source/logmnrts.dbf' size 100M
SQL> exec dbms_logmnr_d.t_tablespace('logmnrts');
PL/SQL procedure successfully completed
九。在source上打开source st的supplenmental logging
alter st add supplemental log group log_group_jobs_pk (id) always
十。在source上配置Propagation：
conn strmadmin/strmadin@source
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'wy.test',
streams_name => 'source_to_target',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@target',
include_dml => true,
include_ddl => true,
source_databa => 'source');
END;
/
十一。在source上配置Capture:
conn strmadmin/strmadin@source
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'wy.test',
streams_type => 'capture',
streams_name => 'capture_source',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true);
END;
/
十二。Instantiate步骤：
这个步骤假设source和target上，表wy.test都存在，而且结构一致，做instantiate实际上是一个export/import的步骤，在这个例子里，会把表wy.test的instantiate SCN倒出来，再导入到target上，保证两表instantiate SCN的一致，这是target新春快乐祝福语上的apply开始正常工作的必须前提
exp wy/wy@source file=wy_test.dmp tables=test object_consistent=y rows=n
imp wy/wy@target file=wy_test.dmp ignore=y commit=y streams_instantiation=y
十三。drop掉target上wy.test的supplenmental log group
will的过去式conn wy/wy@target
alter st drop supplemental log group log_group_jobs_pk
十四。在target上配置Apply
conn strmadmin/strmadmin@target
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'wy.test',
streams_type => 'apply',
streams_name => 'apply_target',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_databa => 'source');
END;
/
十五。启动target上的Apply
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_target',
parameter => 'disable_on_error',
value => 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_target');
END;
/
十六。启动source上的Capture
conn strmadmin/strmadin@source
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_source');
END;
/
十七。验证同步效果
同步1000行数据 3秒
同步10000行数据 10秒
同步100000行数据普通话资料 10分钟
