Oracle DBLink 将远程数据库的数据拷贝到本地数据库

需求场景：

远程数据库B，本地数据库A，A和B的数据库结构完全相同。

现在需要从远程机器数据库B上取数据，写入到本地数据库A中，保证本地数据库具有最新的数据，oracle自动执行此任务，可在断网恢复后自动恢复。

涉及的技术点：

Oracle的DBLink、快照、物化视图等

以下示例仅演示单个job自动同步单表数据

代码示例：

--删除DBLink
drop public database link dblink_temp;
--1.本地服务器A 创建DBLink,DBLink可以理解为一个快捷方式指向远地数据库
create public database link dblink_temp  --link名称，自定义
connect to orclUser  --远程机器上的oracle 用户名
identified by orclPwd --远程机器上的oracle 密码
using 'ORCL_10G_192.168.7.15';  --本地服务器上建立的访问远程oracle的Net服务名
  --（注意，如果装了oracle服务端和客户端，那么都要配置这个Net服务名，防止提示错误:ora-12154 无法解析指定的连接描述符）
  

--查询远程数据示例代码
--在select查询完后一定要使用COMMIT或ROLLBACK来释放锁。切记
select t.asd_id,t.asd_arrive_time from [email protected]_temp t;
commit;--注意，在使用dblink时一定要commit，这个与普通查询不同，要注意

--TbAsd表的主键是asd_id


--注意，dblink模式下查询blob等大数据类型时会出错，要注意避免查询lob类型（如clob和blob）
--select * from [email protected]_temp t;

--如果在使用dblink进行查询时因网络问题导致查询失败，那么oracle会返回ora-02068和ora-03113错误。
--对比远地数据库和本地数据库中TbAsd的数据
select bjb.asd_id, bjb.asd_arrive_time bjb_time,s.asd_arrive_time s_time ,s.asd_id
from [email protected]_temp bjb
full join TbAsd s on bjb.asd_arrive_time=s.asd_arrive_time
where bjb.asd_id is null or s.asd_id is null order by bjb.asd_arrive_time;

select count(*) from [email protected]_temp;--查询远地数据库中TbAsd表的数据条数
select count(*) from TbAsd;--查询本地数据库中TbAsd表的数据条数


--查询远地数据
select t.asd_id,t.asd_arrive_time,t.ai_no from [email protected]_temp t order by t.asd_arrive_time desc;

--########################请注意，这句代码是在远程执行###############################
--2a.切换到远程服务器上，在远程服务器上创建快照，!!!!!!!!!!!注意此时要切换到远程服务器的用户登录
create snapshot log on TbAsd;

--3a.在本地数据库创建快照----PLSQLDeveloper耗时2秒
create snapshot sn_asd as 
select asd_id, asd_arrive_time
from [email protected]_temp t;

--4a.设置快照刷新时间--快速刷新模式  30秒后开始，然后每间隔30秒刷新一次,--oracle时间运算的基本单位是天，如果要得到秒，那么1s就是1/(1天*24小时*60分钟*60秒)
alter snapshot sn_asd refresh fast start with sysdate next sysdate+30/(1*24*60*60);

--注意，在这里设置完快照的刷新时间后，oracle自动为此快照创建一个job，无需我们自己单独创建job


--查看jobs
select * from dba_jobs;
select * from user_jobs;
select * from dba_jobs_running;
--获取远程数据库时间只能通过在远程数据库编写函数或视图来返回远程数据库的时间。

--5a.本地数据库中，针对快照创建触发器（这里可以直接将快照看作一个表，可理解为在表上创建触发器）
create or replace trigger TRI_SN_ASD
  after insert on sn_asd
  for each row
declare
  sameCount number(5) default(0);
begin
    --注意，这里仅仅进行TbAsd的插入，aei_detail_data在这里不处理，因为当TbAsd在Insert后
    --根据快照中远程数据库记录id，查看本地数据库中是否有重复，若无重复则进行新增
    select count(asd_id) into sameCount from TbAsd t where t.asd_id=:new.asd_id;
    if(sameCount=0) 
    then    
      insert into TbAsd(asd_id,  asd_arrive_time)    
      values( :new.asd_id,  :new.asd_arrive_time);    
    end if;
end;

--由于本地数据库和远地数据库之间的网络连接不稳定，那么这里单独增加一个存储过程和一个job监视快照的那个job，便于其在断网恢复后自动恢复
--A1-创建存储过程查看job是否broke为Y，若为Y，则开启
create or replace procedure proc_check_sn_job_broke
as
begin
  for curJob in(select * from user_jobs) loop
      dbms_output.put_line(curJob.Job||'-'||instr(curJob.What,'proc_check_sn_job_broke'));
      --查询job中已broken的，并且job不是自身的job(防止死循环)
      if(curJob.Broken='Y' and instr(curJob.What,'proc_check_sn_job_broke')=0) then
        begin
        --启动job
          dbms_job.broken(curJob.Job,false);
        exception
          WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
          rollback;
        end;
      end if ;
  end loop;
end;
/



--A2-创建job定期执行存储过程
declare
  jobNo number;
begin
  sys.dbms_job.submit(jobNo,
                      'proc_check_sn_job_broke;',
                      sysdate,
                       'sysdate+1/(1*24*60)');
  commit;
end;
/

select * from dba_jobs_running;
 
 
-- 运行job
begin
   dbms_job.run(XXX);--XXX是jobID， 和select * from user_jobs; 中的job值对应，看what对应的过程
end; 
 
-- 删除一个job
begin
   dbms_job.remove(XXX);--XXX是jobID，和select * from user_jobs; 中的job值对应，看what对应的过程
end;