河北省住房和城乡建设厅网站查,餐馆网站模板,网站建设在哪里办公,设计wordpress需求背景#xff1a;与第三方厂商合作使用sql_loadv1.0进行数据加载#xff0c;他们负责推数#xff0c;我拉数。 执行流程#xff1a; 1.ODS推数--------》sftp服务器 2.拉数--------》sftp服务器 3.将数据文件解析到临时表中 4.将临时表中的数据通过【存储过程】同步到目… 需求背景与第三方厂商合作使用sql_loadv1.0进行数据加载他们负责推数我拉数。 执行流程 1.ODS推数--------》sftp服务器 2.拉数--------》sftp服务器 3.将数据文件解析到临时表中 4.将临时表中的数据通过【存储过程】同步到目标表模型中 5.待全部加载同步完成后进行逻辑SQL处理 目前情况 每张表是并行执行的无法判断哪张表会最后执行但是每张表执行完成后会在加载日志表中生成一条成功的记录。 文章目录一、设计总思路1.1. 总流程设计原则1.2. 方案选取1.3. 评估方案利弊及影响范围1.4. 总流程概要设计1.5. 存储过程概要设计二、监听和shell脚本2.1. 添加监听串2.2. 编写配置文件2.3. 编写存储过程2.4. 编写脚本2.5. 编写sql文件2.6. 手动执行脚本三、表设计3.1. 加载日志表3.2. 数据同步日志表四、存储过程4.1. 存储过程实现流程4.2. 存储过程案例五、定时调度5.1. 编辑定时文件5.2. 配置执行频次5.3.重新加载cron服务5.4. 查看当前用户下的定时任务列表5.5. 监控是否执行一、设计总思路 声明此逻辑SQL处理一天执行一次每次同步需要清除数据 1.1. 总流程设计原则
定时启动轮训调度
场景判断分支流转
日志记录进度追踪
容错机制实时定位1.2. 方案选取 第一种方案 首先和厂商交流分析每天数据同步的数据量有多少最多能达到多少最长数据加载的时长。 然后评估一下执行逻辑SQL有没有时间限制比如说上午10点之前必须同步处理完成。 最后评估一下影响范围。 假设数据加载开始时间为凌晨6点最大1000000数据量最长时长为1小时执行逻辑SQL需要1小时在上午10点之前执行完成那么我们可以写一个定时任务一天执行一次上午8点定时调用逻辑SQL。
第二种方案 定时轮训以当前日期为条件查询加载日志表的数据量当满足条件时在查询日志表是否已经执行过因此执行之前要有轨迹记录方便进度追踪执行执行逻辑SQL完毕后更新日志表。
1.3. 评估方案利弊及影响范围
第一种方案分析 正常场景可以满足但是如果遇到时间超长简言之超过了评估的时间范围就会在数据加载尚未完成之前提前执行逻辑SQL导致生成错误数据后期要进行数据治理。
第二种方案分析(推荐使用) 定时轮训调度为了在数据加载之后再进行逻辑SQL处理确保优先级不会漏批 轨迹记录和轨迹更新是为了进度追踪 分支判断为了满足场景的条件 容错机制处理为了快速解决问题
1.4. 总流程概要设计
已知每张表执行完成后会在load_pl_log生成一条成功的记录假设有4张表数据加载完成后在load_pl_log日志表就会生成4条为当前日期的记录。
1.以当前日志为条件查询load_pl_log表的总条数是否为4
2.场景分支走向流转1》满足总条数为4时执行下一步处理2》当不满足总条数为4时执行跳过下一步处理直接结束
3.根据已知逻辑SQL处理一天只执行一次因此需要创建一张SYNC_FINISH_LOG同步轨迹表记录是否执行过1》如果执行过就跳过逻辑SQL处理直接结束。2》当尚未执行过2.1》日志记录2.2》清除数据2.3》逻辑SQ流处理2.4》轨迹更新2.5》容错处理 1.5. 存储过程概要设计
1.判断什么时间跑存储过程标志是什么
2.判断跑步跑的
3.执行存储工程之前要先落库留存轨迹方便进度跟踪
4.执行逻辑SQL
5.完成之后更新轨迹表
6.在执行过程中出现异常要有容错机制处理二、监听和shell脚本
2.1. 添加监听串
# 进入oracle客户端监听串的目录
cd /app/oracle/product/11.2.0/db_1/network/admin/samples
# 编辑监听文件
vim tnsnames.ora# 添加监听串xxx_dev
(DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST 数据库ip地址)(PORT 1521)))(CONNECT_DATA (SERVICE_NAME xxx))
)2.2. 编写配置文件
创建sync_db_conn.unl连接数据库配置文件
# 格式:用户名|密码|连接串
username|password|xxx_dev2.3. 编写存储过程
下面会详细介绍
2.4. 编写脚本
vim data_sync.sh#!bin/bash
linecat ./sync_db_conn.unl
DBUSERecho $line|awk -F | {print $1}
PASSWORDecho $line|awk -F | {print $2}
DSNecho $line|awk -F | {print $3}
echo starting...
sqlplus $DBUSER/$PASSWORD$DSN /home/oracle/shell_sync/sql/sync_data.sqlEOF
EOF
echo end...2.5. 编写sql文件
sync_data.sql
# 存储过程名称
call C_C_G_CP();2.6. 手动执行脚本
sh data_sync.sh三、表设计
3.1. 加载日志表
LOAD_PL_LOG 记录成功轨迹 LOAD_PL_ERROR_LOG 记录异常轨迹
CREATE TABLE LOAD_PL_LOG
(s_procname VARCHAR2(64),s_time DATE not null,s_msg VARCHAR2(4000),s_user VARCHAR2(64)
);
CREATE TABLE LOAD_PL_ERROR_LOG
(S_PROCNAME VARCHAR2(64),S_TIME DATE NOT NULL,S_MSG VARCHAR2(4000),S_USER VARCHAR2(64)
);3.2. 数据同步日志表
SYNC_FINISH_LOG记录成功轨迹 SYNC_FINISH_ERROR_LOG 记录异常轨迹
DROP TABLE SYNC_FINISH_LOG;
CREATE TABLE SYNC_FINISH_LOG
(
PROCNAME VARCHAR2(64),START_DATE DATE,END_DATE DATE,SYNC_FLAG NUMBER(1),SYNC_MSG VARCHAR2(4000),PRIMARY KEY(PROCNAME,START_DATE)
);DROP TABLE SYNC_FINISH_ERROR_LOG;
CREATE TABLE SYNC_FINISH_ERROR_LOG
(S_PROCNAME VARCHAR2(64),S_TIME DATE not null,S_MSG VARCHAR2(4000),S_USER VARCHAR2(64)
);四、存储过程
4.1. 存储过程实现流程
1.判断什么时间跑存储过程标志是什么
2.判断跑步跑的
3.执行存储工程之前要先落库留存轨迹方便进度跟踪
4.执行逻辑SQL
5.完成之后更新轨迹表
6.在执行过程中出现异常要有容错机制处理4.2. 存储过程案例
DROP PROCEDURE PERSONP ;
CREATE OR REPLACE PROCEDURE PERSONP as
v_backtrace varchar2(1000); --返回错误行
v_error_cont varchar2(1000); --整合错误内容
sync_finish_flag number(2); --是否开启同步标识
SYNC_MARK number(2);
BEGINSELECT count(1) into sync_finish_flagFROM load_pl_logwhere to_date(to_char(S_TIME, YYYY-MM-DD), YYYY-MM-DD) to_date(to_char(sysdate, YYYY-MM-DD), YYYY-MM-DD) and s_msgsuccess;if sync_finish_flag 4thenDBMS_OUTPUT.PUT_LINE(这是第1层的if);SELECT count(1) into SYNC_MARK FROM SYNC_FINISH_LOG sl where to_date(to_char(sl.END_DATE, YYYY-MM-DD), YYYY-MM-DD) to_date(to_char(sysdate, YYYY-MM-DD), YYYY-MM-DD) ;if (SYNC_MARK 0)thenDBMS_OUTPUT.PUT_LINE(这是第2层的if);insert into SYNC_FINISH_LOG sfb (PROCNAME, START_DATE, END_DATE, SYNC_FLAG,SYNC_MSG) values(PERSONP,sysdate,,0,数据同步中);commit;delete from PERSONP;commit;insert into PERSONP (select * from PERSONP2);commit;UPDATE SYNC_FINISH_LOG f SET f.END_DATE SYSDATE,f.SYNC_FLAG 1,f.SYNC_MSG 同步完成 WHERE 1 1 AND to_date(to_char(f.START_DATE, YYYY-MM-DD), YYYY-MM-DD) to_date(to_char(sysdate, yyyy/mm/dd), yyyy/mm/dd) AND PROCNAMEEXPORT_LOAN_INFOP;COMMIT;end if;end if;
--异常错误记录处理
exceptionwhen others thenv_backtrace:dbms_utility.format_error_backtrace;--回滚未提交部分rollback;v_error_cont:异常错误为||sqlerrm||--||sqlcode||--||v_backtrace;insert into SYNC_FINISH_ERROR_LOG values(PERSONP,sysdate,v_error_cont,user);commit;
end PERSONP;
/五、定时调度
5.1. 编辑定时文件
crontab -e5.2. 配置执行频次
*/30 * * * * /bin/sh /home/oracle/shell_sync/data_sync.sh保存退出
5.3.重新加载cron服务
/sbin/service crond restart5.4. 查看当前用户下的定时任务列表
crontab -l5.5. 监控是否执行
1》可以借助根据日志查看 2》可以借助plsql工具查询
