介绍完了EnterpriseDB复制软件基本原理和注意事项，我们接下来进行Oracle数据复制到EntepriseDB advanced Server的实际演练。
先在Oracle实例中创建复制测试所用到的数据：
SQL> drop user source cascade; User dropped. SQL> create user source identified by source; User created. SQL> grant dba to source; Grant succeeded. SQL> grant create any trigger to source; Grant succeeded. SQL> conn source/source Connected. SQL> create table EMP 2 ( 3 EMPNO NUMBER(4) not null, 4 ENAME VARCHAR2(10), 5 JOB VARCHAR2(9), 6 MGR NUMBER(4), 7 HIREDATE DATE, 8 SAL NUMBER(7,2), 9 COMM NUMBER(7,2), 10 DEPTNO NUMBER(2) 11 ) 12 tablespace USERS; Table created. SQL> alter table EMP 2 add constraint pk_empno primary key (EMPNO); Table altered. SQL> create table DEPT 2 ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) 3 4 5 6 7 tablespace USERS; Table created. SQL> alter table DEPT 2 add constraint PK_DEPT primary key (DEPTNO); Table altered. SQL> alter table EMP 2 add constraint fk_deptno foreign key (DEPTNO) 3 references dept (DEPTNO); Table altered. SQL>
同时创建EnterpriseDB Advanced Server中的目标数据库及用户：
edb=# create user subuser password 'subuser'; ERROR: role "subuser" already exists edb=# alter user subuser with Superuser; ALTER ROLE edb=# create database subuser tablespace users; CREATE DATABASE
EnterpriseDB复制服务需要DBA Management Server服务的相关支持，其运作方式如下图：
我们首先需要注册管理服务器，其默认端口为9000,为确保主机上的管理服务已打开可以运行以下命令：
[enterprisedb@rh2 ~]$ source edb_83.env [enterprisedb@rh2 ~]$ cd $EDBHOME [enterprisedb@rh2 edba]$ cd mgmtsvr/bin [enterprisedb@rh2 bin]$ ls attachments DBA_Management_Server.pid jboss_init_redhat.sh mgmtsvr.000 run.conf shutdown.jar twiddle.sh wrapper.log wsrunclient.sh BrowserLauncher.class deployer.sh jboss_init_suse.sh mgmtsvr.sh run.jar shutdown.sh wrapper_83 wsconsume.sh wstools.sh classpath.sh jboss_init_hpux.sh kill_wrapper.sh probe.sh run.sh twiddle.jar wrapper.conf wsprovide.sh [enterprisedb@rh2 bin]$ ./mgmtsvr.sh status DBA Management Server is running (31198). --目前服务已打开 [enterprisedb@rh2 bin]$ ./mgmtsvr.sh stop Stopping DBA Management Server... Stopped DBA Management Server. [enterprisedb@rh2 bin]$ ./mgmtsvr.sh start -- 若未打开，则start Starting DBA Management Server...
接着我们可以从桌面上的application栏打开replication console,并选择注册管理服务(register management Server):
成功注册服务后，我们需要分别在发布服务和订阅服务中配置JVM option,右键点击Publication service选择Advanced JVM options,在该窗口内Insert一条记录,如图：
其内容为-Djava.rmi.server.hostname=$IP, 其中$IP为已注册的DBA Management Server所监听的IP地址。配置完成后分别启动发布与订阅服务。
针对订阅服务也需要进行以上配置，JVM options也添加的条目为-Djava.rmi.server.hostname=$IP。
开始创建发布服务Oracle数据源：
并创建相关的发布服务：
接着创建订阅服务数据库：
创建具体的订阅服务：
上述配置均成功完成后，源端的数据定义默认已复制到订阅端，我们来验证一下：
[enterprisedb@rh2 ~]$ psql subuser subuser
Password for user subuser:
Welcome to psql 8.3.0.112, the EnterpriseDB interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with edb-psql commands
\g or terminate with semicolon to execute query
\q to quit
subuser=# desc source.emp;
Table "source.emp"
Column | Type | Modifiers
----------+-----------------------------+-----------
empno | numeric(4,0) | not null
ename | character varying(10) |
job | character varying(9) |
mgr | numeric(4,0) |
hiredate | timestamp without time zone |
sal | numeric(7,2) |
comm | numeric(7,2) |
deptno | numeric(2,0) |
Indexes:
"pk_empno" PRIMARY KEY, btree (empno)
subuser=# desc source.dept;
Table "source.dept"
Column | Type | Modifiers
--------+-----------------------+-----------
deptno | numeric(2,0) | not null
dname | character varying(14) |
loc | character varying(13) |
Indexes:
"pk_dept" PRIMARY KEY, btree (deptno)
接下来尝试在源端Oracle数据库中产生一定量的数据，并通过快照方式复制到订阅端：
SQL> insert into dept select * from scott.dept;
4 rows created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> begin
2 for i in 1..9999 loop
3 insert into emp values(i,'Maclean','DBA',1,sysdate-365,8888,50,10);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
9999
在点中订阅服务subuser中选择功能栏中的snapshot复制方式：
其复制过程中产生的日志：
Source database connectivity info…
conn =jdbc:oracle:thin:@192.168.0.115:1521:g10r21
user =source
password=******
Target database connectivity info…
conn =jdbc:edb://192.168.0.115:5444/subuser
user =subuser
password=******
Importing redwood schema SOURCE…
Table List: ‘DEPT’,’EMP’
Loading Table Data in 8 MB batches…
Disabling FK constraints & triggers on source.dept before truncate…
Truncating table DEPT before data load…
Disabling indexes on source.dept before data load…
Loading Table: DEPT …
Migrated 4 rows.
Enabling FK constraints & triggers on source.dept…
Enabling indexes on source.dept after data load…
Creating Constraint: PK_DEPT
Table Data Load Summary: Total Time(s): 1.261 Total Rows: 4
Disabling FK constraints & triggers on source.emp before truncate…
Truncating table EMP before data load…
Disabling indexes on source.emp before data load…
Loading Table: EMP …
Migrated 9999 rows.
Enabling FK constraints & triggers on source.emp…
Enabling indexes on source.emp after data load…
Creating Constraint: PK_EMPNO
Table Data Load Summary: Total Time(s): 3.782 Total Rows: 9999 Total Size(MB): 0.494140625
Performing ANALYZE on EnterpriseDB database…
Data Load Summary: Total Time (sec): 5.043 Total Rows: 10003 Total Size(MB): 0.506Schema SOURCE imported successfully.
Migration process completed successfully.
Migration logs have been saved to /s01/edba/mgmtsvr/server/default/deploy/edb-rrep-ws.war/WEB-INF/logs
******************** Migration Summary ********************
Tables: 2 out of 2
Constraints: 2 out of 2Total objects: 4
Successful count: 4
Failure count: 0*************************************************************
可以看到快照成功复制了我们需要的数据，现在我们来尝试使用同步模式(synchronize mode) ,我们先来定义一个持续性的间隔为5s的 Scheduled Task,选中订阅服务并点击功能栏中的Configure Schedule,选择Synchronize和Continuously,间隔时间选择为10s:
我们在源端Oracle数据库中修改员工工资，并观察订阅端EDB数据库中的情况：
-- source database
20:08:51 SQL> select sum(sal) from emp;
SUM(SAL)
----------
88871112
20:09:09 SQL> update emp set sal=sal*1.1 ;
9999 rows updated.
20:09:34 SQL> commit;
Commit complete.
20:09:36 SQL> select sum(sal) from emp;
SUM(SAL)
----------
97758223.2
-- EntepriseDB端
subuser=# select sum(sal) from source.emp;
sum
-------------
97758223.20
(1 row)
好了，以上我们利用EnterpriseDB Replication软件完成了一个由Oracle数据库到EDB advanced server间最简单的数据复制服务的配置。
Comment