一、技术实现细节 除非特别说明,下面的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 *
FROMdb1.table1@testLKwhere 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