如何实现两个数据库的同步_ORacle

 

我们经常希望把各地的数据入库后进行统一的应用。现在可以用复制技术来解决这个问题。但实现数据库复制也是要有一些条件的。

首先，数据库要具备高级复制功能（用system身份登录数据库，查看v$option视图，如果其中Advanced replication为TRUE，则支持高级复制功能；否则不支持）。

如果具备高级复制功能，数据库要进行一些参数初始化。

db_domain = test.com.cn 指明数据库的域名(默认的是WORLD)，这里可以用您公司的域名；global_names = true 它要求数据库链接(database link)和被连接的数据库名称一致，现在全局数据库名：db_name+”.”+db_domain ；

跟数据库job执行有关的参数：
job_queue_processes = 1；
job_queue_interval = 60；
distributed_transactions = 10；
open_links = 4

第一行定义SNP进程的启动个数为n。系统缺省值为0，正常定义范围为0～36，根据任务的多少，可以配置不同的数值。第二行定义系统每隔N秒唤醒该进程一次。系统缺省值为60秒，正常范围为1～3600秒。事实上，该进程执行完当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒。如果修改了以上这几个参数，需要重新启动数据库以使参数生效。

做完了初步的准备，我们来实现数据库同步复制。

假设在Internet上有两个数据库：一个叫中国(China)，一个叫日本(Japan)。
具体配置如下：
数据库名：China、Japan
数据库域名 test.com.cn
数据库sid号 China、Japan
Listener端口号 1521
服务器ip地址 10.1.0.100 10.1.0.200
确认两个数据库之间可以互相访问，在tnsnames.ora里设置数据库连接字符串。
中国这边的数据库连接字符串是以下的格式：
Japan =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Japan)
)
)
运行$tnsping Japan，出现以下提示符：
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521))
OK（n毫秒）
表明中国数据库可以访问日本数据库。在日本那边也同样配置，确认$tnsping China 是通的。
改数据库全局名称，建公共的数据库链接。
用system身份登录China数据库
SQL>alter database rename global_name to China.test.com.cn;
用system身份登录Japan数据库：
SQL>alter database rename global_name to Japan.test.com.cn;
用system身份登录China数据库。
SQL>create public database link Japan.test.com.cn using 'Japan';
测试数据库全局名称和公共的数据库链接。
SQL>select * from global_name@Japan.test.com.cn;
返回结果为Japan.test.com.cn就对了。
用system身份登录Japan数据库：
SQL>create public database link China.test.com.cn using 'China';
测试数据库全局名称和公共的数据库链接。
SQL>select * from global_name@China.test.com.cn;
返回结果为China.test.com.cn就对了。
建立管理数据库复制的用户repadmin，并赋权。
用system身份登录China数据库：
SQL>create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
SQL>execute dbms_defer_sys.register_propagator('repadmin');
SQL>grant execute any procedure to repadmin;
SQL>execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
SQL>grant comment any table to repadmin;
SQL>grant lock any table to repadmin;
同样用system身份登录Japan数据库，运行以上的命令，管理数据库复制的用户repadmin，并赋权。
在数据库复制的用户repadmin下创建私有的数据库链接。
用repadmin身份登录China数据库。
SQL>create database link Japan.test.com.cn connect to repadmin identified 试这个私有的数据库链接：
SQL>select * from global_name@Japan.test.com.cn;
返回结果为Japan.test.com.cn就对了。
用repadmin身份登录Japan数据库。
SQL>create database link China.test.com.cn connect to repadmin identified by repadmin;
测试这个私有的数据库链接：
SQL>select * from global_name@China.test.com.cn;
返回结果为China.test.com.cn就对了。
创建或选择实现数据库复制的用户和对象，给用户赋权，数据库对象必须有主关键字。
用internal身份登录China数据库，创建scott用户并赋权：
SQL>create user scott identified by tiger default tablespace users temporary tablespace temp;
SQL>grant connect, resource to scott;
SQL>grant execute on sys.dbms_defer to scott;
用scott身份登录China数据库，创建表dept ：
SQL>create table dept
(deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13) );
如果数据库对象没有主关键字，可以运行以下SQL命令添加：
SQL>alter table dept add (constraint dept_deptno_pk primary key (deptno));
在China数据库scott用户下创建主关键字的序列号，范围避免和Japan的冲突。
SQL> create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache;
在China数据库scott用户下插入初始化数据
SQL>insert into dept values (dept_no.nextval,'accounting','new york');
SQL>insert into dept values (dept_no.nextval,'research','dallas');
SQL>commit;
在Japan数据库那边同样运行以上①，②，③。
在Japan数据库scott用户下创建主关键字的序列号，范围避免和China的冲突。
SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;
在Japan数据库scott用户下插入初始化数据。
SQL>insert into dept values (dept_no.nextval,'sales','chicago');
SQL>insert into dept values (dept_no.nextval,'operations','boston');
SQL>commit;

实现Oracle数据库复制(二)
--------------------------------------------------------------------------------

在Japan数据库那边同样运行以上①，②，③。

在Japan数据库scott用户下创建主关键字的序列号，范围避免和China的冲突。

SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;

在Japan数据库scott用户下插入初始化数据。

SQL>insert into dept values (dept_no.nextval,'sales','chicago');
SQL>insert into dept values (dept_no.nextval,'operations','boston');

SQL>commit;

创建要复制的组scott_mg，加入数据库对象，产生对象的复制支持。

用repadmin身份登录China数据库，创建主复制组scott_mg：
SQL> execute dbms_repcat.create_master_repgroup('scott_mg');

在复制组scott_mg里加入数据库对象：

SQL>execute dbms_repcat.create_master_repobject(sname=>'scott',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'scott_mg');

参数说明：

sname 实现数据库复制的用户名称；
oname 实现数据库复制的数据库对象名称；
type 实现数据库复制的数据库对象类别；
use_existing_object true表示用主复制节点已经存在的数据库对象；
gname 主复制组名；

对数据库对象产生复制支持：
SQL>execute dbms_repcat.generate_replication_support('scott','dept','table');

确认复制的组和对象已经加入数据库的数据字典：
SQL>select gname, master, status from dba_repgroup;
SQL>select * from dba_repobject;

创建主复制节点：

用repadmin身份登录China数据库，创建主复制节点：
SQL>execute dbms_repcat.add_master_database
(gname=>'scott_mg',master=>'Japan.test.com.cn',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'asynchronous');

参数说明：

gname 主复制组名；
master 加入主复制节点的另一个数据库；
use_existing_object true表示用主复制节点已经存在的数据库对象；
copy_rows false表示第一次开始复制时不用和主复制节点保持一致；
propagation_mode 异步地执行；

确认复制的任务队列已经加入数据库的数据字典：
SQL>select * from user_jobs;
使同步组的状态由停顿(quiesced )改为正常(normal)：

用repaa数据库，运行以下命令：
SQL> execute dbms_repcat.resume_master_activity('scott_mg',false);

确认同步组的状态为正常(normal)：
SQL> select gname, master, status from dba_repgroup;

如果这个①命令不能使同步组的状态为正常(normal)，可能有一些停顿的复制，运行以下命令再试试(建议在紧急的时候才用)：
SQL> execute dbms_repcat.resume_master_activity('scott_mg',true);

创建复制数据库的时间表，10分钟复制一次。

用repadmin身份登录China数据库，运行以下命令：
SQL>begin
dbms_defer_sys.schedule_push (
destination => 'Japan.test.com.cn',
interval => 'sysdate + 10/1440',
next_date => sysdate);
end;
/
SQL>begin
dbms_defer_sys.schedule_purge (
next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
rollback_segment => '');
end;
/
用repadmin身份登录Japan数据库，运行以下命令：
SQL>begin
dbms_defer_sys.schedule_push (
destination => ' China.test.com.cn ',
interval => 'sysdate + 10 / 1440',
next_date => sysdate);
end;
/
SQL>begin
dbms_defer_sys.schedule_purge (
next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
rollback_segment => '');
end;
/

添加或修改两边数据库的记录，跟踪复制过程。如果你想立刻看到添加或修改后数据库的记录的变化，可以在两边repadmin用户下找到push的job_number，然后运行：

SQL>exec dbms_job.run(job_number);

异常情况的处理：

检查复制工作正常否，可以在repadmin 用户下查询user_jobs
SQL>select job,this_date,next_date,what, broken from user_jobs;

正常的状态有两种：任务闲——this_date为空，next_date为当前时间后的一个时间值；任务忙——this_date不为空，next_date为当前时间后的一个时间值。异常状态也有两种：任务死锁——next_date为当前时间前的一个时间值；任务死锁——next_date为非常大的一个时间值，例如：4001-01-01。这可能因为网络中断照成的死锁。解除死锁的办法：

$ps –ef|grep orale

找到死锁的刷新快照的进程号ora_snp*，用kill –9 命令删除此进程，然后进入repadmin 用户SQL>操作符下，运行命令：

SQL>exec dbms_job.run(job_number);
说明：job_number 为用select job,this_date,next_date,what from user_jobs;命令查出的job编号。

增加或减少复制组的复制对象：

停止主数据库节点的复制动作，使同步组的状态由正常(normal)改为停顿(quiesced )。用repadmin身份登录China数据库，运行以下命令：

SQL>execute dbms_repcat.suspend_master_activity (gname => 'scott_mg');
在复制组scott_mg里加入数据库对象，保证数据库对象必须有主关键字。

SQL>execute dbms_repcat.create_master_repobject(sname=>'scott',oname=>'emp', type=>'table',use_existing_object=>true,gname=>'scott_mg');

对加入的数据库对象产生复制支持：
SQL>execute dbms_repcat.generate_replication_support('scott','emp','table');

在复制组scott_mg里删除数据库对象：
SQL>execute dbms_repcat.drop_master_repobject ('scott','dept','table');
重新使同步组的状态由停顿(quiesced )改为正常(normal)。

SQL> execute dbms_repcat.resume_master_activity('scott_mg',false);
复制是一种实现数据分布的方法，也就是说把一个系统中的数据通过网络分布到另外一个或者多个地理位置不同的系统中，以适应可伸缩组织的需要、减轻主服务器的工作负荷和提高数据的使用效率。

Ora Oracle8针对数据分布式计算的需要，提供了一整套功能强大的数据库复制解决方案。 Oracle8的数据复制按功能可以分为三类：基本（简单）复制、高级复制和混合复制，而高级复制又可分为多主节点复制和可更新快照复制。在 《数据复制中的定时任务机制》 介绍了Oracle8中的定时复制的机制，本文将主要介绍一个 Oracle 快照复制的实际例子及其技术实现细节。

一、业务需求
在一个实际的数据库应用中，如银行、税务等商业应用中通常都采用这样一种解决方案，在一个行政区域内，如一个省或者一个市，在不同的地理位置架设数台数据库服务器，这些不同地理位置的服务器具有同样的后台数据库。为了维护数据库系统的一致性，对于整个行政区域应用的代码表应该保持一致，如果不考虑数据复制，想维护同样的不冲突的代码表是很困难的。下面是一个实际的业务需求，我们用这里例子来说明 Oracle快照复制的应用。

为了维护整个系统代码表的一致性，客户提出了这样的业务需求，对于系统的代码表采用统一维护，即在一台服务器上维护，如图1所示。在位置1（数据库Ora_db1,用户userA）上维护代码表，其他位置（数据库ora_db2，用户userB；ora_db3，用户userC和ora_db4，用户UserD）可以直接使用这些代码表，也就是说在位置1具有对代码表插入、删除和更新的能力，而在其他地方只能有查询的能力。



二、应用设计
针对上述的需求，我们提出了这样一种解决方案，也就是采用 Oracle8的快照复制。具体业务实现方案设计如下：

在位置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;