利用oracle快照实现两台数据库服务器表同步

利用oracle快照实现两台数据库服务器表同步

利用oracle快照实现两台数据库服务器表同步。
举例，如源数据库A，目标数据库B，数据库B需要同步数据库A的表CROSS，具体步骤如下：
1、首先在数据库B中创建DBLINK；
-- Create database link
create database link DB_LINK_TEST
connect to ITS_L identified by ITSADMIN
using 'orcl_fwq';
2、在数据库A中创建需要被同步的表，如表CROSS，语句就不写了，在数据库B中无需创建表，用快照即可，第5步说明；

3、在数据库B中测试DBLINK
select * from CROSS@DB_LINK_TEST;
如能正常执行就说明DBLINK没有问题；

4、在数据库A中创建快照日志（当快照的刷新方式为 fast 的时候需要创建，具体刷新方式见最后说明）
Create snapshot log on CROSS;

5、在数据库B中创建相应的快照
Create snapshot CROSS as select * from CROSS@DB_LINK_TEST;
这里的快照“CROSS”就可以当做数据库B的同步表使用，数据可删除修改；
在第2步骤中省略了在数据库B中创建表在这里补上了；

6、设置快照刷新时间，可定时触发
Alter snapshot cross refresh fast Start with sysdate+1/1440 next sysdate+1/1440;
设置快照刷新在一分钟后执行，并且每隔1分钟执行一次；
一天的秒数=24小时*60分钟*60钞，应为oracle默认加减时间是天。

至此快照同步创建完毕，当数据库A的表CROSS发生数据变化，则相应的数据库B的快照CROSS也会相应的发生变化，时间延期为1分钟。
以上测试环境为oracle 10g。

相关：
1、手动刷新快照,在没有自动刷新的情况下,可以手动刷新快照.
手动刷新方式1
begin
dbms_refresh.refresh('cross');
end;
手动刷新方式2
EXEC DBMS_SNAPSHOT.REFRESH('sn_test_user','F'); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.
2、查看快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;
3、查看快照下次执行时间
select last_date,next_date,what from user_jobs order by next_date;

说明：
1，ORACLE的快照刷新方式refresh有三种:
fast 快速刷新，用snapshot log，只更新时间段变动部分
complete 完全刷新，运行SQL语句
force 自动判断刷新，介于fast和complete之间
快照技术提供给我们三种刷新机制,分别是:
1) Complete 完全刷新机制,即对表的所有数据进行刷新,如果表的数据量十分庞大的,此法会消耗相当的时间;
2) Fast 快速刷新,即只对数据增量进行刷新;
3) Force 强制刷新,首先判断是否能用快速刷新机制,如不行则用完全刷新机制。
2，若在数据库B上创建快速刷新，则要在数据库A上创建快照日志对象，否则没这个必要，即第4步。

========================

==================================================================================

Oracle快照及dblink使用(两台服务器数据同步)
2013-09-16 10:12:09 0个评论
收藏我要投稿

Oracle快照及dblink使用(两台服务器数据同步)

--名词说明：源——被同步的数据库
-- 目的——要同步到的数据库
/*一、创建dblink：*/
--1、在目的数据库上，创建dblin

drop database link dblink_anson;
Create public
database link dblink_anson
Connect to lg identified by lg using 'SDLGDB';

--源数据库的用户名、密码、服务器名k
/*二、创建快照：*/
--1、在源和目的数据库上同时执行一下语句，创建要被同步的表

drop table tb_anson;
create table tb_anson(c1 varchar2(12));
alter table tb_anson add constraint pk_anson primary key (C1);

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

select * from tb_anson@dblink_anson;
select * from tb_anson;

--3、在目的数据库上，创建要同步表的快照日志

Create snapshot log on tb_anson;

--4、创建快照，快照(被同步(源)数据库服务必须启动)

Create snapshot sn_anson as select * from tb_anson@dblink_anson;

　--5、设置快照刷新时间

Alter snapshot anson refresh fast Start with sysdate+1/24*60 next sysdate+10/24*60;

--oracle自动在1分钟后进行第一次快速刷新，以后每隔10分钟快速刷新一次

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

--oracle自动在30钞后进行第一次完全刷新，以后每隔1天完全刷新一次
--6、手动刷新快照

begin
dbms_refresh.refresh('"CS"."SN_ANSON"');
end;

===================================================================================================
Oracle如何实现两个数据库的同步（用实体化视图实现）（oracle快照实例）
2013-04-18 14:22:32 0个评论收藏我要投稿
Oracle如何实现两个数据库的同步（用实体化视图实现）（oracle快照实例）

一、技术实现细节

除非特别说明，下面的SQL命令都是在数据库ora_db2的SYSETM用户下运行的。

假设要复制(或同步)另一服务器上数据库ora_db1中用户db1的所有表。

1. 创建一个用于连接数据库1（ora_db1）的数据库连接（dblink）

1.1 只有先建立用户db1指定表的快照日志，才能在快照中执行快速刷新。

SQL> select 'create snapshot log on '||table_name||';' from user_tables; --获取用户表的create snapshot语句,如下：

create snapshot log on 表1;

附删除表快照日志：

SQL> select 'drop snapshot log on '||substr(table_name,INSTR(table_name,'$_')+2,length(ta

ble_name))||';' from user_tables where table_name like '%MLOG$_%';

--通过上面获取用户表快照日志的删除语句,如下：

drop snapshot log on 表1;

1.2

SQL> CREATE PUBLIC DATABASE LINK testLK CONNECT TO db1 identified by db1

using

'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))

)

(CONNECT_DATA =

(service_name=ora_db1)

)

)';

**出于安全考虑，可以采用一个私有数据连接。

2. 创建一个名为Snapshot_ts的表空间来存放快照，并创建一个和该表空间有关的名为db2的用户。

SQL > CREATE TABLESPACE snapshot_ts DATAFILE

'd:\db\snapshot_ts.dbf' SIZE 30M

DEFAULT STORAGE (INITIAL 30 K

NEXT 15 K

MINEXTENTS 1

MAXEXTENTS 100

PCTINCREASE 0)

ONLINE

PERMANENT;

SQL > CREATE USER db2

IDENTIFIED BY db2

DEFAULT TABLESPACE snapshot_ts;

SQL > GRANT CONNECT, RESOURCE TO db2;

可以通过下面的SQL语句在ora_db1数据库以db1用户来粗略地估计表空间snapshot_ts的大小。

SQL >SELECT SUM(bytes)

FROM USER_SEGMENTS

WHERE SEGMENT_NAME IN

(select table_name from user_tables);

3. 运行下面的脚本来生成创建ora_db1数据库上db1用户下代码表的快照脚本：

注意：在db1下运行下面select ,获得的文件create_snapshot.sql 脚本在db2下运行。

SQL > spool d:\snap\create_snapshot.sql

SQL >SELECT 'CREATE SNAPSHOT db2.' || TABLE_NAME || ' PCTFREE 10 PCTUSED 40 TABLESPACE snapshot_ts ' || ' STORAGE (INITIAL ' || INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT || ' PCTINCREASE 0 )' || ' AS SELECT * FROM db1.' || TABLE_NAME ||'@testLK;' FROM USER_TABLES;

SQL >spool off ;

注意上面这个生成所需表快照的脚本有一定的局限性，如果所需生成快照的表中含有类型为long的列，‘select *'在这里就不会起作用，上面的这个SQL脚本就不能自动建立生成所需快照的脚本，必须通过在select列表中显式地添加long型列名来创建表的快照。下面是一个例子，假如我们要创建快照依赖的表table1中有一个列note类型为long，就需要单独写出如下的创建快照的脚本：

SQL >CREATE SNAPSHOT db2.table1 PCTFREE 10 PCTUSED 40 TABLESPACE snapcost_ts STORAGE (INITIAL 40960 NEXT 57344 PCTINCREASE 0 ) AS SELECT * FROM db1.table1@testLK where TABLE_NAME not like '%$_%' ;

4. 通过运行第3步创建的脚本文件create_snapshot.sql来创建所有的快照, 在create_snapshot.sql脚本文件中包含下面这样的代码：

CREATE SNAPSHOT db2.表名

PCTFREE 10 PCTUSED 40

TABLESPACE snapshot_ts

STORAGE (INITIAL 163840 NEXT 57344 PCTINCREASE 0)

AS SELECT * FROM db1.table1@testLK where TABLE_NAME not like '%$_%' ;

运行脚本文

件create_snapshot.sql后，就在模式snap中创建了所需要的快照。下一步就是考虑该如何刷新快照。对于快照的刷新，可以通过一些桌面DBA工具来刷新快照也可以通过系统包dbms_snapshot.refresh来刷新一个快照:

手动刷新方式1

SQL > begin

DBMS_SNAPSHOT.REFRESH(‘快照名'); //或者'用户.快照名'

end;

手动刷新方式2

EXEC DBMS_SNAPSHOT.REFRESH(‘快照名'); //或者 '用户.快照名'

或

EXEC DBMS_SNAPSHOT.REFRESH(‘表1’,'F’); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.

5. 创建一个定时刷新过程来定时刷新快照：

Alter snapshot db2.表名1 refresh fast Start with sysdate+1/1440 next sysdate+1/144;

（此SQL语句的意思为:设定oracle自动在1分钟 (1/24*60)后进行第一次快速刷新，以后每隔10分钟 (10/24*60)快速刷新一次。）

或者

SQL > CREATE OR REPLACE PROCEDURE sp_snapshot_refresh

IS

BEGIN

DBMS_REFRESH.MAKE ( NAME=>'快照名', LIST=>'snap.表1, 'snap.表2', 'snap.表3', NEXT_DATE=>TRUNC (SYSDATE+1)+2/24, INTERVAL=>'(SYSDATE+1)', IMPLICIT_DESTROY=>FALSE, LAX=>TRUE);

END; /

SQL > EXECUTE sp_snapshot_refresh

这样就创建了一个定时任务来每天早晨2:00定时刷新快照。运行下面的SQL语句就可以看到刚刚加入的这个任务。

SQL > SELECT JOB, WHAT FROM DBA_JOBS;

6. 在用户db3下创建快照的私有同义词：

SQL > CREATE SYNONYM db3.表1 FOR db2.表1;

7. 以db2用户向db3用户授与快照可以select的权限。

SQL > GRANT SELECT ON 表1 TO db3;

同样的步骤在位置3（ora_db2）和位置4（ora_db3）建立位置1（ora_db1）的代码表快照和定时刷新任务。这样就可实现在位置1统一维护代码表，在位置2、3和4使用该代码表的目的。如下面的SQL语句，在位置2（ora_db2）用户UserB浏览在位置1（ora_db1）中的代码表。

SQL > SELECT * FROM 表1;

二、日常维护

无论任何时候只要出现网络连接问题，刷新就会失败。这些错误信息可以在alert log文件中找到。下面简单介绍一下对这种问题的处理办法：

1. 首先在任务队列中找到刷新快照的的任务编号

SQL > SELECT JOB,what FROM DBA_JOBS;

2. 删除该任务

SQL > EXECUTE DBMS_JOB.REMOVE (JOBNO)；

3. 删除快照组

SQL >drop snapshot 快照1；

4. 重新创建快照组并且重新定时任务来定时刷新快照

SQL > EXECUTE sp_snapshot_refresh

五、快照监视

快照可以通过下面的SQL语句来监视

SQL > SELECT NAME,

TO_CHAR(last_refresh,'DD-MON-YY HH:MM:SS')

FROM DBA_SNAPSHOTS;

补充：

-

-1修改会话时间格式

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

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

SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

--3.查看快照下次执行时间

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

--4.打印调试信息

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

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

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,:https://www.360docs.net/doc/3419118808.html,);

end if;

if updating then

update test_user set name=:https://www.360docs.net/doc/3419118808.html, where id=:old.id;

end if;

end TRI_test_user_AFR;

--6.如果你想双向同步,请在源数据库中执行前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,:https://www.360docs.net/doc/3419118808.html,,: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 (https://www.360docs.net/doc/3419118808.html,!=:https://www.360docs.net/doc/3419118808.html,) or (p.age!=:new.age) then

update test_user set name=:https://www.360docs.net/doc/3419118808.html,,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

======================================================================================
利用oracle 快照dblinks 解决数据库表同步问题
原创 2013年08月07日 15:47:02 562
1、在目的数据库上，创建dblink
drop public database link dblink_orc92_182;
create public DATABASE LINK dblink_orc92_182CONNECT TO bst114 IDENTIFIED BYpassword USING ''orc92_192.168.254.111'';

--dblink_orc92_182 是dblink_name
--bst114 是 username
--password 是 password
--''orc92_192.168.254.111'' 是远程数据库名

2、在源和目的数据库上创建要同步的表(最好有主键约束,快照才可以快速刷新)
drop table test_user;
create table test_user(id number(10) primary key,name varchar2(12),age number(3));

3、在目的数据库上，测试dblink
select * from test_user@dblink_orc92_182; //查询的是源数据库的表
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;

Alter snapshot sn_test_user refresh complete Start with sysdate next trunc(sysdate+1)+ 1/24;

每天1点执行

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

========================================================================

Oracle数据库的快照是一个表2008-09-17 15:35 爱搞机我要评论(0)字号：T|T

Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。

以下是我建立的Snapshot，目的是从业务数据库上将数据Copy到处理数据库上，是不同的两个服务器之间对数据copy。

第一步：在处理服务器上的Oracle终端，建立database link，业务数据库服务器SID为TEST

create database link TEST_https://www.360docs.net/doc/3419118808.html,
connect to AMICOS identified by AMICOS
using 'test';

第二步：在业务数据库上对应的表建立快照日志

Create sn

apshot log on A_Table;

第三步：建立Snapshot 快照名称为：Test_SnapShot

Create snapshot Test_SnapShot
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24
as select * from A_Table@TEST_DBLINK

说明：REFRESH是刷新方法

刷新方式有：COMPLETE和FAST两种，而START WITH是说明开始执行的时间。

Next是下次执行的时间

而AS以后是构成快照的查询方法。

相关的方法：

更改快照

ALTER SNAPSHOT Test_SnapShot
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/2;

手动刷新快照在命令界面执行：

　EXEC DBMS_SNAPSHOT.REFRESH('Test_SnapShot ','C');

第一个参数是要刷新的快照名

第二个参数是刷新的方式，F----FAST, C---COMPLETE

查看快照最后刷新的日期

SELECT NAME,LAST_REFRESH
FROM ALL_SNAPSHOT_REFRESH_TIMES;

最后非常的方案：

1：为需要做Snapshot的表建立Snapshot日志

create snapshot log on t1 with rowid; 这里使用ROWID建立日记的参数

2：采用Fast的方式建立快照，使用rowid做为参考参数

create snapshot fb_test_b refresh fast with rowid start with sysdate next sysdate+1/1440 as select * from fb_test_b@my_dblink;

最好能按照rowid来建立快照。要不然就必须要为表建立Primary Key。