oracle触发器实现表的实时同步(支持无主键表)

最新推荐文章于 2024-06-23 20:20:52 发布

MuBaa

最新推荐文章于 2024-06-23 20:20:52 发布

阅读量4.6k

点赞数 1

分类专栏： oracle 文章标签：数据库同步 oracle 数据库触发器表同步

本文链接：https://blog.csdn.net/zhuyunlei_happy/article/details/52504574

版权

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近公司有个项目要实现oracle 11g数据库的同步。开始我们准备采用oracle的ogg（oracle goldengate）来实现。经过一段时间的摸索，基本完成了需求，后来因为对ogg的不可控（ogg的配置项还是比较多的，而且资料较少，国内教程基本千篇一律，怕出现后期故障定位困难）。另外，ogg需要修改源端数据库为日志归档模式，修改了源库的配置，势必会导致一定的维护成本，同时增加了出故障的风险。最终决定尝试采用触发器，国内某家基于单向网闸完成数据交换的厂商，其数据库同步的功能也基于了触发器。

原理及目的

现有两台oracle服务器，源数据库SOURCEDB中的表TABLENAME，作为生产系统由用户业务系统直接完成数据写入，另外有备份同步服务器TARGETDB的TABLENAME，需要完成这两表之间的实时数据同步，另外必须最小化的配置源库（触发器除外）。

1、触发器实现流程

触发器主要完成对源表增删改操作的捕获，并对于不同操作进行相应的处理，本实例中介绍的处理是记录sql语句，以备目标库执行（另外可以将注释部分放开，测试直接执行sql语句）；

2、对于无主键表的处理

对于无主键的表，本实例采用全字段匹配的方式进行记录定位，这种情况对于普通的业务系统可以很好地完成数据同步，但是对于使用navicat这类的数据库管理工具，存在一个问题：举个例子，如果有个一个test表，包含一个字段id（非主键、允许重复），插入10条重复记录，使用navicat/plsql之类的管理工具修改第一条记录，其他不变，这一操作其实只修改了一条记录，对于oracle来说，可以通过隐藏字段rowid来唯一确定一条记录，但在不同数据库之间，字段值即使完全相同，rowid也不一定相同，所以对于这种基于全字段匹配的方式，势必会匹配到所有的记录，并对这些记录进行操作，造成数据的不一致。

3、需求

（1）不能干预、修改源库的数据库配置，即不对用户业系统环境造成改动，除了触发器；

（2）同步包括有主键、无主键的表；

（3）提供可靠同步，考虑同步服务器宕机、网络不通的问题；

（4）暂不处理blob类字段；

一、创建oracle的directory对象，并进行授权；

创建directory，以便将同步的sql语句记录到磁盘文件：

create or replace directory TESTDIR as 'c:\sql_bak\';
grant read,write on directory TESTDIR to SOURCEDB;
GRANT EXECUTE ON utl_file TO SOURCEDB;

二、定义触发器

下面以本地同步（源库、目标库在一起）的情况举例，每5分钟产生一个记录文件，用来记录执行的同步sql语句。另外对于远程同步的情况，可以自行配置dblink，稍微修改下面sql语句即可：

create or replace trigger SOURCEDB.TABLENAME_TRIGGER
  after insert or update or delete on "SOURCEDB".TABLENAME
  for each row
declare
  Out_File UTL_FILE.FILE_TYPE;
  DataHead varchar2(80); 
  FileName varchar2(80);
  msg varchar2(8000);
  file_exist  BOOLEAN;
  file_length NUMBER(10, 2);
  block_size  BINARY_INTEGER;
  
  field_old VARCHAR2(8000);
  field_new VARCHAR2(8000);
  
  sqlval VARCHAR2(8000); 
  sqlval1 VARCHAR2(8000);
  sno NUMBER;
begin
  sno := floor(TO_CHAR(SYSDATE,'MI')/5)*5;
  FileName := 'sql_' || to_char(sysdate, 'yyyy-mm-dd_HH24-') || sno ||'.txt';
  utl_file.fgetattr('TESTDIR',
                    FileName,
                    file_exist,
                    file_length,
                    block_size);
  Out_File := UTL_FILE.fopen('TESTDIR', FileName, 'a');
  IF utl_file.is_open(Out_File) THEN
		IF (:old.QLR is not NULL) THEN 
			field_old := '  and QLR='''||:old.QLR||'''';
		ELSE
			field_old := '  and QLR is null';
		end IF;
		IF (:old.ZSLB is not NULL) THEN 
			field_old := field_old || ' and ZSLB='''||:old.ZSLB||'''';
		ELSE
			field_old := field_old || ' and ZSLB is null';		
		end IF;
		IF (:old.ZL is not NULL) THEN 
			field_old := field_old || ' and ZL='''||:old.ZL||'''';
		ELSE
			field_old := field_old || ' and ZL is null';		
		end IF;
		IF (:old.ZXYY is not NULL) THEN 
			field_old := field_old || ' and ZXYY='''||:old.ZXYY||'''';
		ELSE
			field_old := field_old || ' and ZXYY is null';
		end IF;
		IF (:old.DBR is not NULL) THEN 
			field_old := field_old || ' and DBR='''||:old.DBR||'''';
		ELSE
			field_old := field_old || ' and DBR is null';		
		end IF;
		IF (:old.DBRQ is not NULL) THEN 
			field_old := field_old || ' and DBRQ='||'to_date('''||TO_CHAR(:old.DBRQ, 'SYYYY-MM-DD HH24:MI:SS')||''',''SYYYY-MM-DD HH24:MI:SS'')';
		ELSE
			field_old := field_old || ' and DBRQ is null';		
		end IF;
		IF (:old.SLRQ is not NULL) THEN 
			field_old := field_old || ' and SLRQ='||'to_date('''||TO_CHAR(:old.SLRQ, 'SYYYY-MM-DD HH24:MI:SS')||''',''SYYYY-MM-DD HH24:MI:SS'')';
		ELSE
			field_old := field_old || ' and SLRQ is null';		
		end IF;
		IF (:old.SLR is not NULL) THEN 
			field_old := field_old || ' and SLR='''||:old.SLR||'''';
		ELSE
			field_old := field_old || ' and SLR is null';
		end IF;
		IF (:old.ZXLX is not NULL) THEN 
			field_old := field_old || ' and ZXLX='''||:old.ZXLX||'''';
		ELSE
			field_old := field_old || ' and ZXLX is null';		
		end IF;
		IF (:old.EXP_TIME is not NULL) THEN 
			field_old := field_old || ' and EXP_TIME='''||:old.EXP_TIME||'''';
		ELSE
			field_old := field_old || ' and EXP_TIME is null';		
		end IF;
		IF (:old.EXP_STATUS is not NULL) THEN 
			field_old := field_old || ' and EXP_STATUS='''||:old.EXP_STATUS||'''';
		ELSE
			field_old := field_old || ' and EXP_STATUS is null';		
		end IF;		

    IF (:new.QLR is not NULL) THEN 
			field_new := ''''||:new.QLR||'''';
		ELSE
			field_new := 'null';
		end IF;
		
		IF (:new.ZSLB is not NULL) THEN 
			field_new := field_new || ','''||:new.ZSLB||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;
		IF (:new.ZL is not NULL) THEN 
			field_new := field_new || ','''||:new.ZL||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;
    	IF (:new.ZXYY is not NULL) THEN 
			field_new := field_new || ','''||:new.ZXYY||'''';
		ELSE
			field_new := field_new || ',null';
		end IF;
		IF (:new.DBR is not NULL) THEN 
			field_new := field_new || ','''||:new.DBR||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;
		IF (:new.DBRQ is not NULL) THEN 
			field_new := field_new || ',to_date('''||TO_CHAR(:new.DBRQ, 'SYYYY-MM-DD HH24:MI:SS')||''',''SYYYY-MM-DD HH24:MI:SS'')';
		ELSE
			field_new := field_new || ',null';		
		end IF;
		IF (:new.SLRQ is not NULL) THEN 
			field_new := field_new || ',to_date('''||TO_CHAR(:new.SLRQ, 'SYYYY-MM-DD HH24:MI:SS')||''',''SYYYY-MM-DD HH24:MI:SS'')';
		ELSE
			field_new := field_new || ',null';		
		end IF;
    	IF (:new.SLR is not NULL) THEN 
			field_new := field_new || ','''||:new.SLR||'''';
		ELSE
			field_new := field_new || ',null';
		end IF;
		IF (:new.ZXLX is not NULL) THEN 
			field_new := field_new || ','''||:new.ZXLX||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;
		IF (:new.EXP_TIME is not NULL) THEN 
			field_new := field_new || ','''||:new.EXP_TIME||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;
		IF (:new.EXP_STATUS is not NULL) THEN 
			field_new := field_new || ','''||:new.EXP_STATUS||'''';
		ELSE
			field_new := field_new || ',null';		
		end IF;

    case
      when inserting THEN
      sqlval:='insert into TARGETDB.TABLENAME(YWH ,BDCZH,BDCDYH ,QLR ,ZSLB ,ZL ,ZXYY ,DBR ,DBRQ,SLRQ,SLR,ZXLX , EXP_TIME, EXP_STATUS) values('''||:new.YWH||''','''||:new.BDCZH||''','''||:new.BDCDYH||''','||field_new||');';    
	  UTL_FILE.put_line(Out_File,sqlval);
	  --sqlval1:='insert into TARGETDB.TABLENAME(YWH ,BDCZH,BDCDYH ,QLR, ZSLB ,ZL ,ZXYY ,DBR ,DBRQ,SLRQ,SLR,ZXLX , EXP_TIME, EXP_STATUS) values('''||:new.YWH||''','''||:new.BDCZH||''','''||:new.BDCDYH||''','||field_new||')';        
	  --execute immediate sqlval1;

      when DELETING THEN
       sqlval := 'delete from TARGETDB.TABLENAME  WHERE YWH='''||:old.YWH||'''and BDCZH='''||:old.BDCZH||''' and BDCDYH='''||:old.BDCDYH||''''||field_old||';';
	   UTL_FILE.put_line(Out_File, sqlval);
	   --sqlval1 := 'delete from TARGETDB.TABLENAME  WHERE YWH='''||:old.YWH||'''and BDCZH='''||:old.BDCZH||''' and BDCDYH='''||:old.BDCDYH||''''||field_old;
	   --execute immediate sqlval1;

      when updating THEN
	  msg := 'delete from TARGETDB.TABLENAME  WHERE YWH='''||:old.YWH||'''and BDCZH='''||:old.BDCZH||''' and BDCDYH='''||:old.BDCDYH||''''||field_old||';';
	  sqlval:='insert into GGUSER.TABLENAME(YWH ,BDCZH,BDCDYH ,QLR ,ZSLB ,ZL ,ZXYY ,DBR ,DBRQ,SLRQ,SLR,ZXLX , EXP_TIME, EXP_STATUS) values('''||:new.YWH||''','''||:new.BDCZH||''','''||:new.BDCDYH||''','||field_new||');';    
	  UTL_FILE.put_line(Out_File, msg);
	  UTL_FILE.put_line(Out_File,sqlval);
	  --sqlval1 := 'delete from TARGETDB.TABLENAME  WHERE YWH='''||:old.YWH||'''and BDCZH='''||:old.BDCZH||''' and BDCDYH='''||:old.BDCDYH||''''||field_old;
	  --execute immediate sqlval1;
	  --sqlval1:='insert into TARGETDB.TABLENAME(YWH ,BDCZH,BDCDYH ,QLR, ZSLB ,ZL ,ZXYY ,DBR ,DBRQ,SLRQ,SLR,ZXLX , EXP_TIME, EXP_STATUS) values('''||:new.YWH||''','''||:new.BDCZH||''','''||:new.BDCDYH||''','||field_new||')';        
	  --execute immediate sqlval1;
    end case; 
	   
  END IF;
  utl_file.fclose(Out_File);
  
  EXCEPTION
	WHEN others THEN
		dbms_output.put_line(SQLERRM);
end;

触发器中包含大量的sql语句拼接部分主要是同步表中包含varchar2类型字段，在对这类字段进行查询是会有可能有以下两类写法，所以需要根据这类字段是否有值进行情况处理：

select * from TABLENAME where YWH is null；<span style="white-space:pre">		</span>--为null情况
select * from TABLENAME where YWH ='12345678'；<span style="white-space:pre">		</span>--有值情况

三、其他说明

当然，通过触发器进行数据库表的同步毕竟不是一个很好的思路，触发器的性能相对较低，另外触发器作为事务提交，对于触发动作执行失败，会影响源库业务系统的正常写入，如果对异常捕获处理，又无法保证数据的一致性，所以本文仅供大家学习和交流讨论。

MuBaa

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
oracle触发器实现表的实时同步(支持无主键表)

最近公司项目有个oracle 11g数据库同步的需求，开始我们准备采用oracle的ogg（oracle goldengate）来实现。经过一段时间的摸索，基本完成了需求，后来应为对ogg的不可控（ogg的配置项还是比较多的，而且资料较少，国内教程基本千篇一律）。另外，ogg同步的源端对原理及目的现有两台oracle服务器，源数据库TARGETDB中的表TABLENAM
复制链接

扫一扫

专栏目录