一次通過stream複製解決資料單向複製的案例

primary db：asmnl29 aix6.1雙機oracle11.1 rac

Standby db：asmnl19 windows2008 oracle11.1單機

其中29和19是2臺db server的ip尾數：

環境簡要介紹，我們需要把asmnl29上使用者asmnlirc下的40多張表適時同步複製到asmnl19下的asmnlirc使用者下，權衡各種因素最終選擇了oracle的stream複製技術，其實實現了上面的同步技術之後另外一個客戶還需要把這40張表通過mv的方式重新整理到他們的系統中最終使用，這次不再涉及mv的過程，因為這個過程我不負責實施，下面是stream詳細配置步驟：

[@more@]

1．修改主、從db的相關引數：

alter system set aq_tm_processes=2 scope=both;

alter system set global_names=true scope=both;

alter system set streams_pool_size=24M scope=spfile;

2.在19上建立stream複製使用者asmnlirc

create user asmnlirc identified by irc default tablespace tbs_stream;
grant dba to asmnlirc;

--下面建立db link是為了實現第9步中例項化資料用
create database link asmnl29 connect to asmnlirc identified by ircasmnl using 'ASMNL29';--=========================
3.在29和19上分別建立stream複製使用者同時授權
create user strmadmin identified by strmadmin default tablespace tbs_stream ;--======================
grant connect,resource,dba,aq_administrator_role to strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/--=================
4.在29上建立db link：

create database link asmnl19 connect to strmadmin identified by strmadmin using 'asmnl19';
5.在19上建立db link：
create database link asmnl29 connect to strmadmin identified by strmadmin using 'asmnl29';--=================
6.在29上建立捕獲佇列

begin
dbms_streams_adm.set_up_queue(
queue_table => 'asmnl29_queue_table',
queue_name => 'asmnl29_queue');
end;
/--=====================
7.在19上建立應用佇列

begin
dbms_streams_adm.set_up_queue(
queue_table => 'asmnl19_queue_table',
queue_name => 'asmnl19_queue');
end;
/--=====================--批量處理
8.在29上建立捕獲程式

select '
begin
dbms_streams_adm.add_table_rules(
table_name => ''ASMNLIRC.'||table_name||''',
streams_type => ''capture'',
streams_name => ''capture_asmnl29'',
queue_name => ''strmadmin.asmnl29_queue'',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/'
from dba_tables
where table_name in (select * from asmnlirc.t_stream)--======================

--單獨處理
begin
dbms_streams_adm.add_table_rules(
table_name => 'ASMNLIRC.TABLE_NAME',
streams_type => 'capture',
streams_name => 'capture_asmnl29',
queue_name => 'strmadmin.asmnl29_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/--=========================
9.例項化資料（把29上要同步的表和資料匯入到19上）

impdp asmnlirc/irc@asmnl19 network_link=ASMNL29 STREAMS_CONFIGURATION=Y tables=(ENTP,ENTP_PSN,ENTP_PSN_CRT,DNG_DCLR_RPT_SND_CMPR,DNG_SBMT_DCMNT_STT,DNG_BATH_CRG_TYP_CMPR,SRC_BATH,SRC_PPDM,SRC_BTH_BLNG,SRC_PT,BLBM_PRT_CD,BLBM_VSL_NAT_CD,DNG_SGN_CMPR,DNG_PCK_TYP_CD,DNG_MVTNK_ATT,DNG_IMDG_ATT,DNG_IMDG_INFC_MTRL,DNG_IMDG_ORGN_PRXD,DNG_IMDG_FRWRK,DNG_IMDG_PRHB_MTRL,DNG_IMDG_SLR_MTRL,DNG_IMDG_VW,DNG_ISBC_CD,DNG_ILBC_CD,DNG_IOC_CD,DNG_IGC_CD,DNG_SHP_CRT,DNG_SHP_CRT_CLS,DNG_CRG_EVL_RPRT,DNG_SBMT_SJZ_INF,DNG_SYS_PRMTR_ST,DNG_DCLR_INPT_ST,DNG_DCLR_INPT_CNT,DNG_AT_ADT_ST,BLBM_MART_ORG_CD,CLBM_CODE,CLBM_CODE_RECORD,DCLR_HLDY_STUP,ENTP_BSS_SCP,T_TEST)
impdp asmnlirc/irc@asmnl19 network_link=ASMNL29 STREAMS_CONFIGURATION=Y tables=DYN_SHP remap_tablespace=(TBS_VESM:TBS_COMMON)--==========================

10.在19上disable trigger：

SELECT 'alter trigger "'||trigger_name||'" disable;' FROM USER_TRIGGERS
--===============================
11.在29上建立傳播程式：

--批量處理

select '
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => ''ASMNLIRC.'||table_name||''',
streams_name => ''asmnl29_to_asmnl19'',
source_queue_name => ''strmadmin.asmnl29_queue'',
destination_queue_name => ''strmadmin.asmnl19_queue@asmnl19'',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => ''asmnl29'',
inclusion_rule => true);
end;
/'
from dba_tables where table_name in (select * from asmnlirc.t_stream)--========================

--單獨處理
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'ASMNLIRC.TABLE_NAME',
streams_name => 'asmnl29_to_asmnl19',
source_queue_name => 'strmadmin.asmnl29_queue',
destination_queue_name => 'strmadmin.asmnl19_queue@asmnl19',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'asmnl29',
inclusion_rule => true);
end;
/--=======================

12.在29上設定適時同步

begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'asmnl29_queue',
destination => 'asmnl19',
latency => 0);
end;
/--=============================
13.在19上建立應用程式

--批量處理
select '
begin
dbms_streams_adm.add_table_rules(
table_name => ''ASMNLIRC.'||table_name||''',
streams_type => ''apply'',
streams_name => ''apply_asmnl19'',
queue_name => ''strmadmin.asmnl19_queue'',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => ''asmnl29'',
inclusion_rule => true);
end;
/'
from dba_tables where table_name in (select * from asmnlirc.t_stream)--=============================

--單獨處理
begin
dbms_streams_adm.add_table_rules(
table_name => 'ASMNLIRC.TABLE_NAME',
streams_type => 'apply',
streams_name => 'apply_asmnl19',
queue_name => 'strmadmin.asmnl19_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'asmnl29',
inclusion_rule => true);
end;
/--=====================
14.在19上啟動應用程式

begin
dbms_apply_adm.start_apply(
apply_name => 'apply_asmnl19');
end;
/--=======================
15.在29上啟動捕獲程式

begin
dbms_capture_adm.start_capture(
capture_name => 'capture_asmnl29');
end;
/--=======================

16.在29上enable傳播程式
exec dbms_propagation_adm.start_propagation('asmnl29_to_asmnl19');--=========================
17.移除stream配置資訊（如果不想使用stream複製環境了）

EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1038318/，如需轉載，請註明出處，否則將追究法律責任。