oracle stream 同步

testingWJZ

前提：可以在一台设备创建两个数据库：主prod，从h10g.备注，创建数据库时选取归档模式         第一步主从数据库操作一样，SYSDBA用户)     alter  system set aq_tm_processes=2 scope=both;      alter system set global_names=true scope=both;     alter system set job_queue_processes=20 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=200M scope=spfile;     alter system set utl_file_dir='*' scope=spfile;     alter system set open_links=4 scope=spfile;     alter system set log_archive_dest='C:\arch' scope=spfile;     alter system set log_archive_start=TRUE scope=spfile;     alter system set log_archive_format='arch%t_%s_%r.arc' scope=spfile;第二步：(主从数据库操作一样，SYSDBA用户)1、创建stream空间表create  tablespace tbs_stream datafile 'C:/tbs_stream01.dbf'size 100m autoextend on next 100m maxsize 1000m segmentspace  management auto;2、迁移表execute dbms_logmnr_d.set_tablespace('tbs_stream');3、创建stream用户create  user strmadmin identified by strmadmin default tablespace tbs_streamtemporary tablespace temp;4、授权1grant  connect,resource,dba,aq_administrator_role tostrmadmin;5、授权2begindbms_streams_auth.grant_admin_privilege(  grantee => 'strmadmin',grant_privileges => true);end;    /第三步：启用追加日志(主从数据库操作一样，SYSDBA用户)     alter  database add supplemental log data;    ALTER  DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARYKEY, UNIQUE, FOREIGN KEY) COLUMNS;第四步：创建link到从（主数据库操作）    conn strmadmin/strmadmin@prod;    create  database link h10gconnect to strmadmin identified by strmadmin using 'h10g';第五步：创建link到主（从数据库操作）    conn  strmadmin/strmadmin@h10g         create database link prod connect to strmadminidentified by strmadmin using  'prod';第六步：创建源队列流（主数据库操作）connect  strmadmin/strmadmin@prodbegindbms_streams_adm.set_up_queue(queue_table => 'prod_queue_table',queue_name => 'prod_queue');end;第七步：创建从队列流（从数据库操作）connect  strmadmin/strmadmin@h10g        begin              dbms_streams_adm.set_up_queue(        queue_table => 'h10g_queue_table',        queue_name => 'h10g_queue');        end;        /    第八步：CMD下运行    1、exp userid=strmadmin/strmadmin@prod file='D:\strmadmin7.dmp'object_consistent=y rows=y    2、imp  userid=strmadmin/strmadmin@h10g file='D:\strmadmin7.dmp' ignore=y commit=y log='D:\tstrmadmin.log'streams_instantiation=y  fromuser=strmadmin touser=strmadmin （报错可以忽略）   第九步：源创建捕获进程（主数据库）connect  strmadmin/strmadmin@prod begindbms_streams_adm.add_schema_rules(schema_name => 'strmadmin',streams_type => 'capture',streams_name => 'capture_prod',queue_name => 'strmadmin.prod_queue',include_dml => true,include_ddl => true,include_tagged_lcr =>false,source_database => null,inclusion_rule => true);end;/第十步：源创建传播进程（主数据库）connect  strmadmin/strmadminbegindbms_streams_adm.add_schema_propagation_rules(schema_name => 'strmadmin',streams_name => 'prod_to_h10g',source_queue_name => 'strmadmin.prod_queue',destination_queue_name => 'strmadmin.h10g_queue@h10g',include_dml => true,include_ddl => true,include_tagged_lcr => false,source_database => 'prod',inclusion_rule => true);end;/第十一步：从创建应用进程（从数据库）connect  strmadmin/strmadmin@h10gbegindbms_streams_adm.add_schema_rules(schema_name => 'strmadmin',streams_type => 'apply',streams_name => 'apply_h10g',queue_name => 'strmadmin.h10g_queue',include_dml => true,include_ddl => true,include_tagged_lcr => false,source_database => 'prod',inclusion_rule => true);end;/第十二步：从数据库启动应用进程connect  strmadmin/strmadmin@h10gbegindbms_apply_adm.start_apply(apply_name => 'apply_h10g');end;/第十三步：主数据库启动捕获进程connect  strmadmin/strmadmin@prodbegindbms_capture_adm.start_capture(capture_name => 'capture_prod');end;/测试：主数据库CREATE TABLE TTT(id NUMBER PRIMARY KEY,nameVARCHAR2(50) ) ;insertinto ttt values (2,'sdfsdfsdfsdf');  commit; 等待1分钟左右进入从数据库（select * from TTT; ）有相应结果。附：查看捕获进程是否正常（主数据库）SELECTCAPTURE_NAME,QUEUE_NAME,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROMDBA_CAPTURE;查看是否传播数据（主数据库）SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN,APPLIED_SCN FROM DBA_CAPTURE;查看应用进程是否正常（从数据库）           SELECT apply_name,apply_captured, status FROM dba_apply;停止捕获进程（主数据库）connect strmadmin/strmadmin@prodbegin dbms_capture_adm.stop_capture(capture_name =>'capture_prod'); end; /停止应用进程（从数据库）connect strmadmin/strmadmin@h10gbegin dbms_apply_adm.stop_apply( apply_name =>'apply_h10g'); end; /清楚配置信息：connectstrmadmin/strmadmin@prod（主数据库）execDBMS_STREAMS_ADM.remove_streams_configuration();connectstrmadmin/strmadmin@h10g（从数据库）execDBMS_STREAMS_ADM.remove_streams_configuration();