两个数据库间数据不一致,修复存过
问题描述:
channel创建dealer,流程channel-->ccbm-->evc-->ccbm-->channel,但是有时候ccbm调用evc的webservice,evc处理需要60秒,每个月一两次的样子,然后照常返回,但是CCBM侧超时报错,dealer信息没有创建,页面报超时失败.再次创建,由于EVC侧代理商已经存在,会报代理商已经存在,导师EVC侧存在代理商,Channel侧没有.
EVC由于用的mml消息,无法判断是否超时,webservice超时时间配置长又很危险,CCBM侧一直说是EVC问题,客户一直追着要解决,X疼,写了个修复脚本
创建一些备份表及索引
----------------------------------
--创建备份表
create table EV_DL_MASTER_TimeOut as select * from EV_DL_MASTER where 1!=1;
-- Create table
create table EV_DL_MASTER_TIMEOUT_CNT
(
HP_NO VARCHAR2(24),
PARENT_HP_NO VARCHAR2(24),
CHANNEL_CNT NUMBER(10),
MAIN_HP_NO VARCHAR2(24),
CHANNEL_ALLCNT NUMBER(10),
CREATED_DT VARCHAR2(14),
INSERT_DATE DATE
)
tablespace SMPDATATBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
--创建索引
--创建索引
--创建索引
CREATE INDEX IDX_EDM_CREATED_DT ON EV_DL_MASTER(CREATED_DT)
tablespace SMPIDXTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 384K
next 1M
minextents 1
maxextents unlimited
);
CREATE INDEX IDX_EDM_status ON EV_DL_MASTER (status)
tablespace SMPIDXTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 320K
next 1M
minextents 1
maxextents unlimited
);
修复存储过程
-----------------------------------------------------------------------------------------------
create or replace procedure Dealer_CT_TimeOut as
--查询从现在时间点起,过去10分钟到20分钟之内创建的dealer,在channel侧不存在的
--配置存过运行时间的话应该小于10分钟
--1/144 = 10分钟
cursor dealers is
select *
from ev_dl_master a
where a.created_dt between
(select to_char(sysdate - 150, 'YYYYMMDDHH24MISS') from dual) and
(select to_char(sysdate - 1 / 144, 'YYYYMMDDHH24MISS') from dual)
and a.status = 1
and not exists
(select 1 from agent_msisdn@DB_CRM_LIVE where msisdn = a.hp_no);
dealer dealers%rowtype;
t_channel_cnt number(10);
t_channel_allcnt number(10);
begin
--更新最后一次存过开始时间
update EV_DL_MASTER_TimeOut_cnt set insert_date = sysdate where hp_no='money_start';
commit;
for dealer in dealers loop
--插入备份表
insert into EV_DL_MASTER_TimeOut
select *
from EV_DL_MASTER a
where a.hp_no = dealer.hp_no
and a.created_dt=dealer.created_dt;
--如果是普通代理商,备份直接上级和根上级的子代理商数量
if (dealer.dl_level = 2) then
--取父代理商和根代理商的数量
select channel_cnt into t_channel_cnt from ev_dl_master where hp_no=dealer.parent_hp_no;
select channel_allcnt into t_channel_allcnt from ev_dl_master where hp_no=dealer.main_hp_no;
--备份
insert into EV_DL_MASTER_TimeOut_cnt(hp_no,parent_hp_no,channel_cnt,main_hp_no,channel_allcnt,Created_Dt,insert_date)
select dealer.hp_no,dealer.parent_hp_no,t_channel_cnt,dealer.main_hp_no,t_channel_allcnt,dealer.Created_Dt,sysdate from dual;
--更新
--update EV_DL_MASTER a set a.channel_cnt = a.channel_cnt - 1 where a.hp_no=dealer.parent_hp_no;
--update EV_DL_MASTER a set a.channel_allcnt = a.channel_allcnt - 1 where a.hp_no=dealer.main_hp_no;
end if;
--删除原表
--delete from ev_dl_master a
--where a.hp_no = dealer.hp_no and a.created_dt = dealer.created_dt;
commit;
end loop;
--更新最后一次存过执行结束时间
update EV_DL_MASTER_TimeOut_cnt set insert_date = sysdate where hp_no='money_stop';
commit;
end;