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

2022-01-20 你还想怎么样啊

背景：项目中遇到的问题，需要二区两台数据库之间同步一些表，以及导出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%