基本复制应用实例
（石骁騑 2001年07月20日 02:23）
复制是一种实现数据分布的方法，也就是说把一个系统中的数据通过网络分布到另外一个或者多个地理位置不同的系统中，以适应可伸缩组织的需要、减轻主服务器的工作负荷和提高数据的使用效率。Oracle 8针对数据分布式计算的需要，提供了一整套功能强大的数据库复制解决方案。Oracle 8的数据复制按功能可以分为三类：基本（简单）复制、高级复制和混合复制，而高级复制又可分为多主节点复制和可更新快照复制。在《数据复制中的定时任务机制》介绍了Oracle 8中的定时复制的机制，本文将主要介绍一个Oracle快照复制的实际例子及其技术实现细节。一、业务需求
在一个实际的数据库应用中，如银行、税务等商业应用中通常都采用这样一种解决方案，在一个行政区域内，如一个省或者一个市，在不同的地理位置架设数台数据库服务器，这些不同地理位置的服务器具有同样的后台数据库。为了维护数据库系统的一致性，对于整个行政区域应用的代码表应该保持一致，如果不考虑数据复制，想维护同样的不冲突的代码表是很困难的。下面是一个实际的业务需求，我们用这里例子来说明Oracle快照复制的应用。为了维护整个系统代码表的一致性，客户提出了这样的业务需求，对于系统的代码表采用统一维护，即在一台服务器上维护，如图1所示。在位置1（数据库Ora_db1,用户userA）上维护代码表，其他位置（数据库ora_db2，用户userB；ora_db3，用户userC和ora_db4，用户UserD）可以直接使用这些代码表，也就是说在位置1具有对代码表插入、删除和更新的能力，而在其他地方只能有查询的能力。二、应用设计
针对上述的需求，我们提出了这样一种解决方案，也就是采用Oracle 8的快照复制。具体业务实现方案设计如下：在位置1的数据库Ora_db1维护所有的代码表，在其余数据库建立相对于位置1的所有代码表的快照。为了维护快照的方便，在位置2、3和4创建一个单独的快照表空间和一个模式（schema），系统中的其他用户通过一个私有同义词来存取这些快照。这里私有同义词相对公共同义词要好，这是因为在位置1存在一个同样规范的系统，它的表是通过公共同义词来存取的。对于快照的刷新，采用Oracle系统包DBMS­_REFRESH进行，并将该刷新过程的运行定时在每天早上2:00，这样可以减少网络流量。对于快照的刷新形式，由于对于代码表的维护不是很多而且代码表的数据量相对较少，所以在此选择了完全刷新，这样就避免了管理快照日志的麻烦。下面以一个节点2（ora_db2）为例来说明具体的技术实现细节。三、技术实现细节
除非特别说明，下面的SQL命令都是在数据库ora_db2的SYSETM用户下运行的。假设要复制的代码表有三个：dm_gy_rydm，dm_gy_jgdm和dm_gy_yhdm。1.在数据库2（ora_db2）上增加数据库1（ora_db1）的services name可以直接在tnsnames.ora文件中增加数据库1的services name，包括IP地址，SID以及端口号等。Services name 命名为ora_db1.world。2. 创建一个用于连接数据库1（ora_db1）的数据库连接（dblink）SQL> CREATE PUBLIC DATABASE LINK ora_db1.world
CONNECT TO SYSTEM
IDENTIFIED BY MANAGER USING 'ora_db1.world'; 出于安全考虑，可以采用一个私有数据连接。3. 创建一个名为Snapshot_ts的表空间来存放快照，并创建一个和该表空间有关的名为Snap的用户。SQL > CREATE TABLESPACE snapshot_ts DATAFILE
'c:\orant\dbfiles\prod\snapshot01.dbf' SIZE 30M
DEFAULT STORAGE (INITIAL 30 K
NEXT 15 K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0)
ONLINE
PERMANENT;
SQL > CREATE USER snap
IDENTIFIED BY snap
DEFAULT TABLESPACE snapshot_ts;
SQL > GRANT CONNECT, RESOURCE TO snap; 可以通过下面的SQL语句在ora_db1数据库以userA用户来粗略地估计表空间snapshot_ts的大小。SQL > SELECT SUM(bytes)
FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN
(' dm_gy_rydm',
'dm_gy_jgdm',
'dm_gy_yhdm'); 4. 运行下面的脚本文件snapsql.sql来生成创建ora_db1数据库上userA用户下代码表的快照脚本：/* Snapsql.sql */
spool c:\snap\create_snapshot.sql
SELECT 'CREATE SNAPSHOT SNAP.'
|| TABLE_NAME
|| ' PCTFREE 10 PCTUSED 40 TABLESPACE snapshot_ts '
|| ' STORAGE (INITIAL ' || INITIAL_EXTENT
|| ' NEXT ' || NEXT_EXTENT || ' PCTINCREASE 0 )'
|| ' AS SELECT * FROM userA.'
|| TABLE_NAME
|| '@ora_db1.world;'
FROM USER_TABLES
WHERE TABLE_NAME IN(
(' dm_gy_rydm',
'dm_gy_jgdm',
'dm_gy_yhdm');
spool off
注意上面这个生成所需表快照的脚本有一定的局限性，如果所需生成快照的表中含有类型为long的列，‘select *’在这里就不会起作用，上面的这个SQL脚本就不能自动建立生成所需快照的脚本，必须通过在select列表中显式地添加long型列名来创建表的快照。下面是一个例子，假如我们要创建快照依赖的表dm_gy_note中有一个列note类型为long，就需要单独写出如下的创建快照的脚本：CREATE SNAPSHOT snap.dm_gy_note
PCTFREE 10 PCTUSED 40
TABLESPACE snapcost_ts
STORAGE (INITIAL 40960 NEXT 57344 PCTINCREASE 0 )
AS SELECT dm, dmmc,note
FROM userA.dm_gy_note@ora_db1.worl;
SQL > @snapsql.sql
5. 通过运行第4步创建的脚本文件create_snapshot.sql来创建所有的快照, 在脚本文件中包含下面这样的代码：CREATE SNAPSHOT snap. dm_gy_rydm
PCTFREE 10 PCTUSED 40
TABLESPACE snapshot_ts
STORAGE (INITIAL 163840 NEXT 57344 PCTINCREASE 0)
AS SELECT * FROM userA. dm_gy_rydm @ora_db1.world; 运行脚本文件create_snapshot.sql后，就在模式snap中创建了所需要的快照。下一步就是考虑该如何刷新快照。对于快照的刷新，可以通过一些桌面DBA工具来刷新快照也可以通过系统包dbms_snapshot.refresh来刷新一个快照:SQL > EXECUTE DBMS_SNAPSHOT.REFRESH
('snap.dm_gy_rydm') 6. 创建一个定时刷新过程来定时刷新快照：/*sp_snapshot_refresh.sql */
CREATE OR REPLACE PROCEDURE sp_snapshot_refresh IS
BEGIN
DBMS_REFRESH.MAKE (
NAME=>'tax_dmb_grp',
LIST=>'snap. dm_gy_rydm,
'snap.dm_gy_jgdm',
'snap.dm_gy_yhdm',
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; 7. 在用户userB下创建快照的私有同义词：SQL > CREATE SYNONYM userB.dm_gy_rydm FOR snap.dm_gy_rydm; 8. 以Snap用户向userB用户授与快照可以select的权限。SQL > GRANT SELECT ON dm_gy_rydm TO userB; 同样的步骤在位置3（ora_db2）和位置4（ora_db3）建立位置1（ora_db1）的代码表快照和定时刷新任务。这样就可实现在位置1统一维护代码表，在位置2、3和4使用该代码表的目的。如下面的SQL语句，在位置2（ora_db2）用户UserB浏览在位置1（ora_db1）中的代码表。SQL > SELECT * FROM dm_gy_rydm; 四、日常维护
无论任何时候只要出现网络连接问题，刷新就会失败。这些错误信息可以在alert log文件中找到。下面简单介绍一下对这种问题的处理办法：1. 首先在任务队列中找到刷新快照的的任务编号SQL > SELECT JOB,what FROM DBA_JOBS; 2. 删除该任务SQL > EXECUTE DBMS_JOB.REMOVE (JOBNO)； 3. 删除快照组SQL > EXECUTE DBMS_REFRESH.DESTROY ('tax_dmb_grp')； 4. 重新创建快照组并且重新定时任务来定时刷新快照SQL > EXECUTE sp_snapshot_refresh 五、快照监视快照可以通过下面的SQL语句来监视SQL > SELECT NAME,
TO_CHAR(last_refresh,'DD-MON-YY HH:MM:SS')
FROM DBA_SNAPSHOTS;
（石骁騑 2001年07月20日 02:23）
复制是一种实现数据分布的方法，也就是说把一个系统中的数据通过网络分布到另外一个或者多个地理位置不同的系统中，以适应可伸缩组织的需要、减轻主服务器的工作负荷和提高数据的使用效率。Oracle 8针对数据分布式计算的需要，提供了一整套功能强大的数据库复制解决方案。Oracle 8的数据复制按功能可以分为三类：基本（简单）复制、高级复制和混合复制，而高级复制又可分为多主节点复制和可更新快照复制。在《数据复制中的定时任务机制》介绍了Oracle 8中的定时复制的机制，本文将主要介绍一个Oracle快照复制的实际例子及其技术实现细节。一、业务需求
在一个实际的数据库应用中，如银行、税务等商业应用中通常都采用这样一种解决方案，在一个行政区域内，如一个省或者一个市，在不同的地理位置架设数台数据库服务器，这些不同地理位置的服务器具有同样的后台数据库。为了维护数据库系统的一致性，对于整个行政区域应用的代码表应该保持一致，如果不考虑数据复制，想维护同样的不冲突的代码表是很困难的。下面是一个实际的业务需求，我们用这里例子来说明Oracle快照复制的应用。为了维护整个系统代码表的一致性，客户提出了这样的业务需求，对于系统的代码表采用统一维护，即在一台服务器上维护，如图1所示。在位置1（数据库Ora_db1,用户userA）上维护代码表，其他位置（数据库ora_db2，用户userB；ora_db3，用户userC和ora_db4，用户UserD）可以直接使用这些代码表，也就是说在位置1具有对代码表插入、删除和更新的能力，而在其他地方只能有查询的能力。二、应用设计
针对上述的需求，我们提出了这样一种解决方案，也就是采用Oracle 8的快照复制。具体业务实现方案设计如下：在位置1的数据库Ora_db1维护所有的代码表，在其余数据库建立相对于位置1的所有代码表的快照。为了维护快照的方便，在位置2、3和4创建一个单独的快照表空间和一个模式（schema），系统中的其他用户通过一个私有同义词来存取这些快照。这里私有同义词相对公共同义词要好，这是因为在位置1存在一个同样规范的系统，它的表是通过公共同义词来存取的。对于快照的刷新，采用Oracle系统包DBMS­_REFRESH进行，并将该刷新过程的运行定时在每天早上2:00，这样可以减少网络流量。对于快照的刷新形式，由于对于代码表的维护不是很多而且代码表的数据量相对较少，所以在此选择了完全刷新，这样就避免了管理快照日志的麻烦。下面以一个节点2（ora_db2）为例来说明具体的技术实现细节。三、技术实现细节
除非特别说明，下面的SQL命令都是在数据库ora_db2的SYSETM用户下运行的。假设要复制的代码表有三个：dm_gy_rydm，dm_gy_jgdm和dm_gy_yhdm。1.在数据库2（ora_db2）上增加数据库1（ora_db1）的services name可以直接在tnsnames.ora文件中增加数据库1的services name，包括IP地址，SID以及端口号等。Services name 命名为ora_db1.world。2. 创建一个用于连接数据库1（ora_db1）的数据库连接（dblink）SQL> CREATE PUBLIC DATABASE LINK ora_db1.world
CONNECT TO SYSTEM
IDENTIFIED BY MANAGER USING 'ora_db1.world'; 出于安全考虑，可以采用一个私有数据连接。3. 创建一个名为Snapshot_ts的表空间来存放快照，并创建一个和该表空间有关的名为Snap的用户。SQL > CREATE TABLESPACE snapshot_ts DATAFILE
'c:\orant\dbfiles\prod\snapshot01.dbf' SIZE 30M
DEFAULT STORAGE (INITIAL 30 K
NEXT 15 K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0)
ONLINE
PERMANENT;
SQL > CREATE USER snap
IDENTIFIED BY snap
DEFAULT TABLESPACE snapshot_ts;
SQL > GRANT CONNECT, RESOURCE TO snap; 可以通过下面的SQL语句在ora_db1数据库以userA用户来粗略地估计表空间snapshot_ts的大小。SQL > SELECT SUM(bytes)
FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN
(' dm_gy_rydm',
'dm_gy_jgdm',
'dm_gy_yhdm'); 4. 运行下面的脚本文件snapsql.sql来生成创建ora_db1数据库上userA用户下代码表的快照脚本：/* Snapsql.sql */
spool c:\snap\create_snapshot.sql
SELECT 'CREATE SNAPSHOT SNAP.'
|| TABLE_NAME
|| ' PCTFREE 10 PCTUSED 40 TABLESPACE snapshot_ts '
|| ' STORAGE (INITIAL ' || INITIAL_EXTENT
|| ' NEXT ' || NEXT_EXTENT || ' PCTINCREASE 0 )'
|| ' AS SELECT * FROM userA.'
|| TABLE_NAME
|| '@ora_db1.world;'
FROM USER_TABLES
WHERE TABLE_NAME IN(
(' dm_gy_rydm',
'dm_gy_jgdm',
'dm_gy_yhdm');
spool off
注意上面这个生成所需表快照的脚本有一定的局限性，如果所需生成快照的表中含有类型为long的列，‘select *’在这里就不会起作用，上面的这个SQL脚本就不能自动建立生成所需快照的脚本，必须通过在select列表中显式地添加long型列名来创建表的快照。下面是一个例子，假如我们要创建快照依赖的表dm_gy_note中有一个列note类型为long，就需要单独写出如下的创建快照的脚本：CREATE SNAPSHOT snap.dm_gy_note
PCTFREE 10 PCTUSED 40
TABLESPACE snapcost_ts
STORAGE (INITIAL 40960 NEXT 57344 PCTINCREASE 0 )
AS SELECT dm, dmmc,note
FROM userA.dm_gy_note@ora_db1.worl;
SQL > @snapsql.sql
5. 通过运行第4步创建的脚本文件create_snapshot.sql来创建所有的快照, 在脚本文件中包含下面这样的代码：CREATE SNAPSHOT snap. dm_gy_rydm
PCTFREE 10 PCTUSED 40
TABLESPACE snapshot_ts
STORAGE (INITIAL 163840 NEXT 57344 PCTINCREASE 0)
AS SELECT * FROM userA. dm_gy_rydm @ora_db1.world; 运行脚本文件create_snapshot.sql后，就在模式snap中创建了所需要的快照。下一步就是考虑该如何刷新快照。对于快照的刷新，可以通过一些桌面DBA工具来刷新快照也可以通过系统包dbms_snapshot.refresh来刷新一个快照:SQL > EXECUTE DBMS_SNAPSHOT.REFRESH
('snap.dm_gy_rydm') 6. 创建一个定时刷新过程来定时刷新快照：/*sp_snapshot_refresh.sql */
CREATE OR REPLACE PROCEDURE sp_snapshot_refresh IS
BEGIN
DBMS_REFRESH.MAKE (
NAME=>'tax_dmb_grp',
LIST=>'snap. dm_gy_rydm,
'snap.dm_gy_jgdm',
'snap.dm_gy_yhdm',
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; 7. 在用户userB下创建快照的私有同义词：SQL > CREATE SYNONYM userB.dm_gy_rydm FOR snap.dm_gy_rydm; 8. 以Snap用户向userB用户授与快照可以select的权限。SQL > GRANT SELECT ON dm_gy_rydm TO userB; 同样的步骤在位置3（ora_db2）和位置4（ora_db3）建立位置1（ora_db1）的代码表快照和定时刷新任务。这样就可实现在位置1统一维护代码表，在位置2、3和4使用该代码表的目的。如下面的SQL语句，在位置2（ora_db2）用户UserB浏览在位置1（ora_db1）中的代码表。SQL > SELECT * FROM dm_gy_rydm; 四、日常维护
无论任何时候只要出现网络连接问题，刷新就会失败。这些错误信息可以在alert log文件中找到。下面简单介绍一下对这种问题的处理办法：1. 首先在任务队列中找到刷新快照的的任务编号SQL > SELECT JOB,what FROM DBA_JOBS; 2. 删除该任务SQL > EXECUTE DBMS_JOB.REMOVE (JOBNO)； 3. 删除快照组SQL > EXECUTE DBMS_REFRESH.DESTROY ('tax_dmb_grp')； 4. 重新创建快照组并且重新定时任务来定时刷新快照SQL > EXECUTE sp_snapshot_refresh 五、快照监视快照可以通过下面的SQL语句来监视SQL > SELECT NAME,
TO_CHAR(last_refresh,'DD-MON-YY HH:MM:SS')
FROM DBA_SNAPSHOTS;
create or replace package ref_cursor
as
TYPE service_cursor is REF CURSOR;
end;
2、创建存储过程
create or replace procedure pro_test(
l_cursor out ref_cursor.service_cursor)
as
begin
OPEN l_cursor FOR
select * from yourtable;
end;
3、用存储过程pro_test做数据源即可。当然，select * 可以改为任意的列名。