一次通过stream复制解决数据单向复制的案例

最新推荐文章于 2024-05-12 23:05:05 发布

clt3617

最新推荐文章于 2024-05-12 23:05:05 发布

阅读量221

点赞数

文章标签：数据库操作系统

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复制环境了）