Ask Maclean

EnterpriseDB Replication，复制Oracle数据测试(2)

介绍完了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.506

Schema 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 2

Total 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间最简单的数据复制服务的配置。

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *