Oracle 10G streams模式级复制
Oracle 10G streams模式级复制
数据库:sc_source 和 sc_dest
要求:将sc_source中的product用户下的所有对象都复制到sc_dest上去
一、准备工作:
1.1 将sc_source和sc_dest数据库都置于归档模式
1.2 如果有些表没有主键,为保证复制的准确性,需要在源数据库(sc_source)上配置辅助日志
alter database add supplemental log data(primary key,unique) columns; |
1.3 源,目标库设置一些参数
job_queue_processes=4 aq_tm_processes=4 global_names=true |
1.4 创建流复制的管理用户源,目标库都需要配置
create tablespace streams_tbs datafile '/Oracle/oradata/streams_tbs01.dbf' size 1024M; create user strmadmin identified by strmadmin default tablespace streams_tbs quota unlimited on streams_tbs; grant dba,select_catalog_role to strmadmin; |
1.5 创建数据库连接
sc_source: create database link sc_dest.net connect to strmadmin identified by strmadmin using 'sc_dest'; sc_dest: create database link sc_source.net connect to strmadmin identified by strmadmin using 'sc_source'; |
二,开始配置streams
sc_source
-----------------------------------
配置捕获进程队列
begin dbms_streams_adm.set_up_queue( queue_table => 'capture_scstab', queue_name => 'capture_scs', queue_user => 'strmadmin'); end; / |
sc_dest
-----------------------------------
配置应用进程队列
begin dbms_streams_adm.set_up_queue( queue_table => 'apply_scdtab', queue_name => 'apply_scd', queue_user => 'strmadmin'); end; / |
sc_source
-----------------------------------
配置捕获进程
begin dbms_streams_adm.add_schema_rules ( schema_name => 'product', streams_type => 'capture', streams_name => 'capture_scstrm', queue_name => 'capture_scs', include_dml => true, include_ddl => true, inclusion_rule => true); end; / |
配置传播进程
begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'product', streams_name => 'pro_source_to_dest', source_queue_name => 'capture_scs', destination_queue_name => 'apply_scd@sc_dest.net', include_dml => true, include_ddl => true, source_database => 'sc_source.net'); end; / |
sc_dest
--------------------------------------------------------
配置应用进程
begin dbms_streams_adm.add_schema_rules ( schema_name => 'product', streams_type => 'apply', streams_name => 'apply_scdtrm', queue_name => 'apply_scd', include_dml => true, include_ddl => true, source_database => 'sc_source.net'); end; / |
|
评论暂时关闭