当前位置:数据库 > Oracle >>

Oracle如何实现两个数据库的同步(用实体化视图实现)(oracle快照实例)

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(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. 删除快照组
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,