两相同方案数据库同步策略(快照)!

本实例已完全通过测试,单向,又向同步都可使用.

--名词说明：源——被同步的数据库                          目的——要同步到的数据库

前6步必须执行,第6以后是一些辅助信息.

 

1、在源和目的数据库上创建要同步的表(所有表最好有主键约束,这样快照才可以快速刷新,可以利用imp工具恢复两个相同方案的数据库，保持表结构一致,以下举一表结构为实例)

drop table test_user;

create table test_user(id number(10) primary key,name varchar2(12),age number(3));

 

2、在目的数据库上，创建dblink

drop  database link dblinkname;

Create  DATABASE LINK dblinkname CONNECT TO username IDENTIFIED BY password USING 'services_name';

--dblinkname 是dblink_name

--username 是 username

--password 是 password

--'services_name' 是远程数据库名

3、在目的数据库上，测试dblink

select * from test_user@dblinkname;    //查询的是源数据库的表 select * from test_user;

4、在源数据库上，创建要同步表的快照日志

Create snapshot log on test_user;

5、在目的数据库上创建快照

Create snapshot sn_test_user as select * from test_user@dblink_orc92_182;

6、设置快照刷新时间(只能选择一种刷新方式,推荐使用快速刷新,这样才可以用触发器双向同步)       

 快速刷新

Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;

--oracle马上自动快速刷新，以后不停的刷新,只能在测试时使用.真实项目要正确权衡刷新时间.

完全刷新

Alter snapshot sn_test_user refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;

--oracle自动在30秒后进行第一次完全刷新，以后每隔30秒完全刷新一次

7、手动刷新快照,在没有自动刷新的情况下,可以手动刷新快照.        

手动刷新方式1 begin dbms_refresh.refresh('sn_test_user'); end;

手动刷新方式2 EXEC DBMS_SNAPSHOT.REFRESH('sn_test_user','F');  //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.

8.修改会话时间格式

 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

9.查看快照最后一次刷新时间

SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

10.查看快照下次执行时间

select last_date,next_date,what from user_jobs order by next_date;

11.打印调试信息

dbms_output.put_line('use '||'plsql');

12.如果你只想单向同步,那么在目的数据库创建以下触发器(当源数据库表改变时,目的数据库表跟着改变,但目的数据库表改变时,源数据库表不改变).

create or replace trigger TRI_test_user_AFR   after  insert or update or delete on sn_test_user   for each row begin   if deleting then       delete from test_user where id=:old.id;   end if;   if inserting then       insert into test_user(id,name)       values(:new.id,:new.name);   end if;   if updating then      update test_user set name=:new.name where id=:old.id;   end if; end TRI_test_user_AFR;

13.如果你想双向同步,请在源数据库中执行前6步,并在双方都创建以下触发器(当源数据库表改变时,目的数据库表跟着改变,目的数据库表改变时,源数据库表也改变)

CREATE OR REPLACE TRIGGER BST114.TRI_TEST_USER_AFR AFTER DELETE OR INSERT OR UPDATE ON BST114.SN_TEST_USER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare     tmp_id number(10):=-1; begin

  dbms_output.put_line('begin');   if inserting then       --select id into tmp_id from test_user where id=:new.id;          for p in(select id from test_user where id=:new.id)       loop         tmp_id:=p.id;       end loop;             dbms_output.put_line(tmp_id||'===------------');       if (tmp_id=-1) then           insert into test_user(id,name,age)           values(:new.id,:new.name,:new.age);       end if;   end if;     if updating then      dbms_output.put_line('updated');      for p in(select name,age from test_user where id=:old.id)      loop          if (p.name!=:new.name) or (p.age!=:new.age) then               update test_user set name=:new.name,age=:new.age where id=:old.id;          end if;      end loop;   end if;     if deleting then       dbms_output.put_line('deleted');       delete from test_user where id=:old.id;   end if;   dbms_output.put_line('end'); end TRI_test_user_AFR;  

--为防止双向同步触发器死循环,所以要在触发器中增加一些判断,阻止死循环.

以上同步原理

1.首先创建一个dblink,可以访问远程数据库

2.在本地创建一个快照,映射远程数据表,当远程数据表有变化时,会反应到快照中.

3.由于快照类似于视图表,所以在本地为快照创建一个触发器,当快照有变化时,会触发相应事件.

4.在触发器中写同步数据的代码.

附:快照刷新时间参数说明

一天的秒数=24小时*60分钟*60钞

所以要想在30秒后刷新,参数应该这样写 sysdate+30/(24*60*60) 1分钟==sysdate+60/(24*60*60)

一天的分钟数=24小时*60分钟

一分钟也可以这样写 sysdate+1/(24*60)

30分钟==sysdate+30/(24*60)

60分钟==sysdate+60/(24*60)

以此类推

1小时==sysdate+1/24==sysdate+60/(24*60)

1天==sysdate+1

一个月==sysdate+30

 

注：如果想实现全库所有表的同步，可以利用过程来完成创建快照、快照日志和触发器的功能.

第一步： 在源库中建立如下过程(用于创建快照日志):  

CREATE OR REPLACE PROCEDURE PRO_WF_SNAPSHOST_LOG (TABLESPACE_NAME  IN VARCHAR2) AS CURSOR CUR_TABLE IS SELECT TABLE_NAME FROM USER_TABLES WHERE SUBSTR(TABLE_NAME,1,2) = 'T_' OR SUBSTR(TABLE_NAME,1,4) = 'TAX_';  LV_SQL            LONG; BEGIN FOR V_TABLE IN CUR_TABLE LOOP   LV_SQL := 'CREATE SNAPSHOT LOG ON  '||V_TABLE.TABLE_NAME||'     TABLESPACE  '||TABLESPACE_NAME||'  STORAGE (INITIAL 100K NEXT 150K PCTINCREASE 0)';         EXECUTE IMMEDIATE LV_SQL; END LOOP; END PRO_WF_SNAPSHOST_LOG; /

 

第二步： 在目的数据库中建立如下函数(供以下过程调用)。  

CREATE OR REPLACE FUNCTION FUN_CUR_GET_PK_CONDIT_EQUAL  (AV_SCHEME        IN VARCHAR2, AV_TABLE         IN VARCHAR2,  CONDITION        VARCHAR2, PK_KIND          VARCHAR2, IS_POINT         VARCHAR2 ) RETURN VARCHAR2 AS CURSOR CUR_COL IS SELECT COLUMN_NAME CN FROM ALL_CONS_COLUMNS WHERE OWNER = AV_SCHEME AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE OWNER = AV_SCHEME AND TABLE_NAME = AV_TABLE  AND  CONSTRAINT_TYPE = 'P');   LV_PKSTRING     VARCHAR2(4000);  BEGIN      LV_PKSTRING := '';         FOR V_COL IN CUR_COL LOOP            IF IS_POINT = 'Y' THEN             LV_PKSTRING := LV_PKSTRING||' '||PK_KIND|| ' '||V_COL.CN||' = '||CONDITION||'.'||V_COL.CN;           ELSE               LV_PKSTRING := LV_PKSTRING||' '||PK_KIND|| ' '||V_COL.CN||' = '||V_COL.CN;            END IF;    END LOOP;    IF IS_POINT = 'Y' THEN          LV_PKSTRING := FUN_CUR_SUBSTR(LV_PKSTRING,'L',5,0);    ELSE          LV_PKSTRING := FUN_CUR_SUBSTR(LV_PKSTRING,'L',3,0);    END IF;

   --去掉左边5个字符' AND '    RETURN LV_PKSTRING; END  FUN_CUR_GET_PK_CONDIT_EQUAL; / CREATE OR REPLACE FUNCTION FUN_CUR_GET_FIELD_CONDIT_EQUAL (AV_SCHEME VARCHAR2,AV_TABLE VARCHAR2,CONDITION VARCHAR2, KIND VARCHAR2) RETURN LONG AS  CURSOR CUR_FIELD IS    SELECT COLUMN_NAME CNAME FROM ALL_TAB_COLUMNS     WHERE OWNER = AV_SCHEME AND TABLE_NAME = AV_TABLE    MINUS    SELECT COLUMN_NAME CNAME FROM ALL_CONS_COLUMNS   WHERE OWNER = AV_SCHEME AND CONSTRAINT_NAME IN     (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS      WHERE OWNER = AV_SCHEME AND TABLE_NAME = AV_TABLE  AND      CONSTRAINT_TYPE = 'P');  CURSOR CUR_ALL_FIELD IS     SELECT COLUMN_NAME CNAME FROM ALL_TAB_COLUMNS       WHERE OWNER = AV_SCHEME AND TABLE_NAME = AV_TABLE        ORDER BY COLUMN_ID; LV_FIELDSTRING LONG; BEGIN     LV_FIELDSTRING := '';     IF KIND='Y' THEN         FOR V_FIELD IN CUR_FIELD LOOP              LV_FIELDSTRING := LV_FIELDSTRING ||','||V_FIELD.CNAME||' = '||CONDITION||'.'||V_FIELD.CNAME;        END LOOP;     ELSE          FOR V_ALL_FIELD IN CUR_ALL_FIELD LOOP            LV_FIELDSTRING := LV_FIELDSTRING ||','||CONDITION||'.'||V_ALL_FIELD.CNAME;          END LOOP;

   END IF;

     RETURN FUN_CUR_SUBSTR(LV_FIELDSTRING,'L',1,0);    --加上',',返回 END FUN_CUR_GET_FIELD_CONDIT_EQUAL;

 

第三步：在目的数据库中建立如下过程(创建快照与触发器)。

  CREATE OR REPLACE PROCEDURE PRO_WF_SNAPSHOST (DBLINK           IN VARCHAR2,--dblink名称 TABLESPACE_NAME  IN VARCHAR2,--表空间名称  REFRESH_KIND     IN VARCHAR2,--参数值为COMPLETE,FORCE,FAST三种  STARTDATE        IN OUT    VARCHAR2,--默认为10分钟刷新一次，可不输入  NEXTDATE         IN OUT    VARCHAR2--默认为10分钟刷新一次，可不输入) AS CURSOR CUR_TABLE IS SELECT TABLE_NAME FROM USER_TABLES WHERE SUBSTR(TABLE_NAME,1,2) = 'T_' OR SUBSTR(TABLE_NAME,1,4) = 'TAX_';  LV_SQL            LONG; LV_FIELD          VARCHAR2(4000); LV_FIELD_UPDATE   LONG; LV_PK             VARCHAR2(100); LV_SCHEME         VARCHAR2(60); LV_FIELD_VALUE    LONG; LV_TABLE_NAME     VARCHAR2(30); BEGIN    IF STARTDATE IS NULL THEN    STARTDATE := 'SYSDATE+10/(24*60)';--也可以写成SYSDATE+600/(24*60*60)每十分钟刷新一次 END IF;   IF NEXTDATE IS NULL THEN      NEXTDATE := 'SYSDATE+10/(24*60)';--也可以写成SYSDATE+600/(24*60*60)每十分钟刷新一次 END IF;     FOR V_TABLE IN CUR_TABLE LOOP          LV_TABLE_NAME := V_TABLE.TABLE_NAME;

  IF LENGTH(LV_TABLE_NAME)>20 THEN      LV_TABLE_NAME := SUBSTR(LV_TABLE_NAME,0,20);   END IF;      LV_SQL := ' CREATE SNAPSHOT SN_'||LV_TABLE_NAME||' STORAGE (INITIAL 100K NEXT 150K PCTINCREASE 0)    TABLESPACE  '|| TABLESPACE_NAME ||' REFRESH  '||REFRESH_KIND||'  START WITH  '||STARTDATE||    '  NEXT '|| NEXTDATE||'     AS SELECT * FROM '||V_TABLE.TABLE_NAME||'@'||DBLINK;

 EXECUTE IMMEDIATE LV_SQL;

     SELECT USERNAME INTO LV_SCHEME FROM USER_USERS;

LV_PK := FUN_CUR_GET_PK_CONDIT_EQUAL(LV_SCHEME,V_TABLE.TABLE_NAME,':OLD','AND','Y');     LV_FIELD_UPDATE := FUN_CUR_GET_FIELD_CONDIT_EQUAL(LV_SCHEME,V_TABLE.TABLE_NAME,':NEW','Y');   LV_FIELD := FUN_CUR_GET_ALL_TABLE_FIELD(LV_SCHEME,V_TABLE.TABLE_NAME);    LV_FIELD := SUBSTR(LV_FIELD,1,LENGTH(LV_FIELD)-1);   LV_FIELD := FUN_CUR_SUBSTR(LV_FIELD,'L',1,0);   LV_FIELD_VALUE := FUN_CUR_GET_FIELD_CONDIT_EQUAL(LV_SCHEME,V_TABLE.TABLE_NAME,':NEW','N');   IF LV_FIELD_UPDATE IS NULL THEN      LV_FIELD_UPDATE := FUN_CUR_GET_PK_CONDIT_EQUAL(LV_SCHEME,V_TABLE.TABLE_NAME,NULL,',','N');   END IF; LV_SQL := 'CREATE OR REPLACE TRIGGER TR_SN_'||LV_TABLE_NAME||'_AFR   AFTER  INSERT OR UPDATE OR DELETE ON SN_'||LV_TABLE_NAME||'   FOR EACH ROW     BEGIN       IF DELETING THEN           DELETE FROM  '||V_TABLE.TABLE_NAME||' WHERE '||LV_PK||';       END IF;       IF INSERTING THEN           INSERT INTO '||V_TABLE.TABLE_NAME||' ('||LV_FIELD||')           VALUES('||LV_FIELD_VALUE||');       END IF;         IF UPDATING THEN            UPDATE '||V_TABLE.TABLE_NAME||' SET '||LV_FIELD_UPDATE||' WHERE '||LV_PK||';         END IF;     

  END TR_SN_'||LV_TABLE_NAME||'_AFR;';   EXECUTE IMMEDIATE LV_SQL;   END LOOP; END PRO_WF_SNAPSHOST;

 

最后： 执行PRO_WF_SNAPSHOST_LOG过程创所有表的快照日志，然后再执行PRO_WF_SNAPSHOST过程来完成快照的建立和触发器的创建即可完成所有操作。