Oracle不同数据库之间同步处理方案

　　背景：项目中遇到的问题，需要二区两台数据库之间同步一些表，以及导出sql文件同步至三区数据库。1 SQL文件目录
　　新建SQL文件生成的目录D:mptmsudataDNLTBDIR。
　　二区数据库中执行下列语句： create or replace directory DNL_TB_DIR as ＂D:mptmsudataDNLTBDIR＂;2 数据库修改
　　1）新建二区数据库服务器到二区数据库服务器的数据库DATABASE LINK文件。
　　二区数据库中执行下列语句： create public database link XQDNJL connect to YPTMS using ＂192.168.10.8/mptmsu＂;
　　2）二区数据库服务器新建表DNL_TBJL。
　　二区数据库中执行下列语句： -- Create table create table DNL_TBJL ( id VARCHAR2(25) not null, tables VARCHAR2(25) not null, czlx VARCHAR2(25), sql VARCHAR2(2000) not null, time DATE not null, bak VARCHAR2(128) not null ) tablespace MPTMSU pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column DNL_TBJL.id is ＂ID＂; comment on column DNL_TBJL.tables is ＂同步表名＂; comment on column DNL_TBJL.czlx is ＂操作类型＂; comment on column DNL_TBJL.sql is ＂SQL内容＂; comment on column DNL_TBJL.time is ＂操作时间＂;3 触发器
　　在二区数据库服务器说新建相关表的触发器，触发器实现的功能是东区2区向西区直接通过DBLINK直接执行；东区2区向东区3区通过生成SQL文件到D:mptmsudataDNLTBDIR目录下；触发器的每次操作都记录到表格DNL_TBJL(id, tables,czlx, SQL, TIME, BAK)内。
　　下面以AEMTINFO表为例，具体的触发器代码如下：create or replace trigger SYN_AEMTINFO after insert or update or delete on AEMTINFO for each row /*------------------------------------------------------------------ 功 能：东区2区向西区、东区3区传送数据库变化 功能说明:东区2区向西区直接通过DBLINK直接执行；东区2区向东区3区通过生成SQL文件到 D:mptmsudataDNLTBDIR目录下；触发器的每次操作都记录到表格DNL_TBJL (id, tables,czlx, SQL, TIME, BAK)内。 -------------------------------------------------------------*/ declare integrity_error exception; errno integer; errmsg char(200); tmp varchar2(1000); tmpval varchar2(1000); val varchar2(4000); id varchar2(20); dnltb utl_file.file_type; begin if inserting then insert into AEMTINFO@XQDNJL (EMTID, SID, SNAME, STIME, ETIME, INTERVAL, EARGS, ADDDATE, REMARK, FLAG) values (:NEW.EMTID, :NEW.SID, :NEW.SNAME, :NEW.STIME, :NEW.ETIME, :NEW.INTERVAL, :NEW.EARGS, :NEW.ADDDATE, :NEW.REMARK, :NEW.FLAG); select ＂insert into AEMTINFO (EMTID,SID,SNAME,STIME,ETIME,INTERVAL,EARGS,ADDDATE,REMARK,FLAG) values (＂ ||＂＂＂＂||:NEW.EMTID||＂＂＂＂||＂,＂ ||＂＂＂＂||:NEW.SID||＂＂＂＂||＂,＂ ||＂＂＂＂||:NEW.SNAME||＂＂＂＂||＂,＂ ||＂to_date(＂||＂＂＂＂||to_char(:NEW.STIME,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂to_date(＂||＂＂＂＂||to_char(:NEW.ETIME,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂＂＂＂||:NEW.INTERVAL||＂＂＂＂||＂,＂ ||＂＂＂＂||:NEW.EARGS||＂＂＂＂||＂,＂ ||＂to_date(＂||＂＂＂＂||to_char(:NEW.ADDDATE,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂＂＂＂||:NEW.REMARK||＂＂＂＂||＂,＂ ||＂＂＂＂||:NEW.FLAG||＂＂＂＂||＂);＂ into val from dual; select (select to_char(sysdate, ＂yyyyMMddHH24miss＂) from dual) || lpad(DNL_TBXL.nextval, 3, ＂0＂) into id from dual; insert into DNL_TBJL (id, tables,czlx, SQL, TIME, BAK) values (id,＂AEMTINFO＂, ＂INSET＂, val, sysdate, id); dnltb := utl_file.fopen(＂DNL_TB_DIR＂, ＂INSET-＂||＂AEMTINFO-＂||id || ＂.sql＂, ＂w＂); utl_file.put_line(dnltb, val); utl_file.put_line(dnltb, ＂commit;＂); utl_file.put_line(dnltb, ＂exit;＂); utl_file.fclose(dnltb); elsif updating then update AEMTINFO@XQDNJL set EMTID = :NEW.EMTID, SID = :NEW.SID, SNAME = :NEW.SNAME, STIME = :NEW.STIME, ETIME = :NEW.ETIME, INTERVAL = :NEW.INTERVAL, EARGS = :NEW.EARGS, ADDDATE = :NEW.ADDDATE, REMARK = :NEW.REMARK, FLAG = :NEW.FLAG where emtid = :OLD.emtid; select ＂update AEMTINFO set ＂ ||＂EMTID = ＂||＂＂＂＂||:NEW.EMTID||＂＂＂＂||＂,＂ ||＂SID = ＂||＂＂＂＂||:NEW.SID||＂＂＂＂||＂,＂ ||＂SNAME = ＂||＂＂＂＂||:NEW.SNAME||＂＂＂＂||＂,＂ ||＂STIME = ＂||＂to_date(＂||＂＂＂＂||to_char(:NEW.STIME,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂ETIME = ＂||＂to_date(＂||＂＂＂＂||to_char(:NEW.ETIME,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂INTERVAL = ＂||＂＂＂＂||:NEW.INTERVAL||＂＂＂＂||＂,＂ ||＂EARGS = ＂||＂＂＂＂||:NEW.EARGS||＂＂＂＂||＂,＂ ||＂ADDDATE = ＂||＂to_date(＂||＂＂＂＂||to_char(:NEW.ADDDATE,＂yyyy-mm-dd hh24:mi:ss＂)||＂＂＂＂||＂,＂||＂＂＂yyyy-mm-dd hh24:mi:ss＂＂＂||＂),＂ ||＂REMARK = ＂||＂＂＂＂||:NEW.REMARK||＂＂＂＂||＂,＂ ||＂FLAG = ＂||＂＂＂＂||:NEW.FLAG||＂＂＂＂|| ＂where emtid = ＂||:OLD.emtid||＂;＂ into val from DUAL; select (select to_char(sysdate, ＂yyyyMMddHH24miss＂) from dual) || lpad(DNL_TBXL.nextval, 3, ＂0＂) into id from dual; insert into DNL_TBJL (id, tables,czlx, SQL, TIME, BAK) values (id,＂AEMTINFO＂, ＂UPDATE＂, val, sysdate, id); dnltb := utl_file.fopen(＂DNL_TB_DIR＂, ＂UPDATE-＂||＂AEMTINFO-＂||id || ＂.sql＂, ＂w＂); utl_file.put_line(dnltb, val); utl_file.put_line(dnltb, ＂commit;＂); utl_file.put_line(dnltb, ＂exit;＂); utl_file.fclose(dnltb); elsif deleting then delete from AEMTINFO@XQDNJL where emtid = :OLD.emtid; tmp := :OLD.emtid; tmpval := ＂delete from AEMTINFO where emtid=＂; val := concat(tmpval, tmp || ＂;＂); select (select to_char(sysdate, ＂yyyyMMddHH24miss＂) from dual) || lpad(DNL_TBXL.nextval, 3, ＂0＂) into id from dual; insert into DNL_TBJL (id, tables,czlx, SQL, TIME, BAK) values (id,＂AEMTINFO＂, ＂DELETE＂, val, sysdate, id); dnltb := utl_file.fopen(＂DNL_TB_DIR＂, ＂DELETE-＂||＂AEMTINFO-＂||id || ＂.sql＂, ＂w＂); utl_file.put_line(dnltb, val); utl_file.put_line(dnltb, ＂commit;＂); utl_file.put_line(dnltb, ＂exit;＂); utl_file.fclose(dnltb); end if; exception when integrity_error then raise_application_error(errno, errmsg);  end; 4 sql文件入库批处理
　　通过Windows系统自带的计划任务执行下面批处理实现数据入库
　　1）三区区数据库服务器新建表DNL_SQLJL。
　　三区数据库中执行下列语句： -- Create table create table DNL_SQLJL ( id VARCHAR2(25), tables VARCHAR2(25), czlx VARCHAR2(25), sqlml VARCHAR2(2000), sql VARCHAR2(2000), time DATE, bak VARCHAR2(128) ) tablespace MPTMSU pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column DNL_SQLJL.id is ＂ID＂; comment on column DNL_SQLJL.tables is ＂同步表名＂; comment on column DNL_SQLJL.czlx is ＂操作类型＂; comment on column DNL_SQLJL.sqlml is ＂SQL文件目录＂; comment on column DNL_SQLJL.sql is ＂SQL内容＂; comment on column DNL_SQLJL.time is ＂操作时间＂;
　　2）三区区数据库服务器新建计划任务定期执行DQ3QRK-ZXWJ.bat批处理。
　　3）DQ3QRK-ZXWJ.bat批处理调用DQ3QRK-ZXSQL.bat来执行入库操作。
　　DQ3QRK-ZXWJ.bat语句如下： @echo off REM set Log=logDQ3QRKSQL-%date:~0,4%.%date:~5,2%.%date:~8,2%.log REM rem 第一步 dir D:mptmsudataDNLTBDIR* /b /a|findstr ＂.sql>＂ >D:mptmsudataDNLTBDIRDQ3QRKWJ.TXT rem cd /d %~dp0 && for /f ＂tokens=1-3 delims=-. ＂ %%i in (D:mptmsudataDNLTBDIRDQ3QRKWJ.TXT) do %~dp0DQ3QRK-ZXSQL.bat %%i %%j %%k >>%Log% 2>&1 pause exit
　　DQ3QRK-ZXSQL.bat语句如下： @echo off REM REM set Log=logDQ3QRKSQL-%date:~0,4%.%date:~5,2%.%date:~8,2%.log set RQ=%date:~0,4%%date:~5,2% set SQLWJ=D:mptmsudataDNLTBDIR%1-%2-%3.sql set SQLJL=D:mptmsudataDNLTBDIRsqljl.sql REM sqlplus -s yptms/yptms@mptmsu @%SQLWJ% for /f ＂tokens=1 delims=;＂ %%i in (%SQLWJ%) do (set tmpsql=%%i goto aa) :aa echo insert into DNL_SQLJL (ID,TABLES,CZLX,SQLML,SQL,TIME) values(＂%3＂,＂%2＂,＂%1＂,＂D:mptmsudataDNLTBDIR%RQ%%1-%2-%3.sql＂,＂%tmpsql%＂,SYSDATE); >%SQLJL% echo commit; >>%SQLJL% echo exit; >>%SQLJL% sqlplus -s yptms/yptms@mptmsu @D:mptmsudataDNLTBDIRsqljl.sql del /f /q D:mptmsudataDNLTBDIRsqljl.sql IF NOT EXIST D:mptmsudataDNLTBDIR%RQ% md D:mptmsudataDNLTBDIR%RQ% move /Y %SQLWJ% D:mptmsudataDNLTBDIR%RQ%