linux 环境 crontab+shell+sqlplus 调用oracle 存储过程实现数据同步

最新推荐文章于 2021-05-14 08:00:59 发布

gblfy

最新推荐文章于 2021-05-14 08:00:59 发布

阅读量773

点赞数

分类专栏： Oracle 文章标签： sqlplus

本文链接：https://blog.csdn.net/weixin_40816738/article/details/115548758

版权

Oracle 专栏收录该内容

78 篇文章 4 订阅

订阅专栏

需求背景：与第三方厂商合作使用sql_loadv1.0进行数据加载，他们负责推数，我拉数。

执行流程：
1.ODS推数--------》sftp服务器
2.拉数--------》sftp服务器
3.将数据文件解析到临时表中
4.将临时表中的数据通过【存储过程】同步到目标表模型中
5.待全部加载同步完成后，进行逻辑SQL处理

目前情况：
每张表是并行执行的，无法判断哪张表会最后执行，但是每张表执行完成后，会在加载日志表中生成一条成功的记录。

一、设计总思路

声明：此逻辑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_dev

2.3. 编写存储过程

下面会详细介绍

2.4. 编写脚本

vim data_sync.sh

#!bin/bash
line=`cat ./sync_db_conn.unl`
DBUSER=`echo $line|awk -F "|" '{print $1}'`
PASSWORD=`echo $line|awk -F "|" '{print $2}'`
DSN=`echo $line|awk -F "|" '{print $3}'`
echo "starting..."
sqlplus  $DBUSER/$PASSWORD@$DSN @/home/oracle/shell_sync/sql/sync_data.sql<<EOF
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);
BEGIN
 SELECT count(1) into sync_finish_flag
  FROM load_pl_log
 where 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_msg='success';
 if  sync_finish_flag  = 4
 then
      DBMS_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)
  then
     DBMS_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 PROCNAME='EXPORT_LOAN_INFOP';
  COMMIT;
   end if;
  end if; 
--异常错误记录处理
exception
  when others then
    v_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 -e

5.2. 配置执行频次

*/30 * * * *  /bin/sh /home/oracle/shell_sync/data_sync.sh

保存，退出

5.3.重新加载cron服务

/sbin/service crond restart

5.4. 查看当前用户下的定时任务列表

crontab -l

5.5. 监控是否执行

1》可以借助根据日志查看
2》可以借助plsql工具查询