快照、刷新-[置顶] Oracle如何实现两个数据库的同步（用实体化视图实现）（oracle快照实例）-by小雨
新手发帖，很多方面都是刚入门，有错误的地方请大家见谅，欢迎批评指正
一、术技实现细节
除非别特说明，面下的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(table_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,: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;
--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,: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
文章结束给大家分享下程序员的一些笑话语录：
马云喜欢把自己包装成教主，张朝阳喜欢把自己包装成明星，李彦宏喜欢把自己包装成的很知性，丁磊喜欢把自己包装的有创意，李开复总摆出一副叫兽的样子。看来的。其实我想说，缺啥补啥，人之常情。
浙公网安备 33010602011771号