EnterpriseDB Replication,复制Oracle数据测试(2)
介绍完了EnterpriseDB复制软件基本原理和注意事项,我们接下来进行Oracle数据复制到EntepriseDB advanced Server的实际演练。
先在Oracle实例中创建复制测试所用到的数据:
我们首先需要注册管理服务器,其默认端口为9000,为确保主机上的管理服务已打开可以运行以下命令:
成功注册服务后,我们需要分别在发布服务和订阅服务中配置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数据源:
并创建相关的发布服务:
接着创建订阅服务数据库:
创建具体的订阅服务:
上述配置均成功完成后,源端的数据定义默认已复制到订阅端,我们来验证一下:
其复制过程中产生的日志:
我们在源端Oracle数据库中修改员工工资,并观察订阅端EDB数据库中的情况:
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 DATABASEEnterpriseDB复制服务需要DBA Management Server服务的相关支持,其运作方式如下图:
![Postgres_Plus_Advanced_Server_Replication_Server_Users_Guide-6](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/Postgres_Plus_Advanced_Server_Replication_Server_Users_Guide-6.jpg)
[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):
![replication1](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/replication1.jpg)
![JVM1](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/JVM1.jpg)
![pub1](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub1.jpg)
![pub2](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub2.jpg)
![pub3](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub3.jpg)
![pub4](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub4.jpg)
[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复制方式:
![pub5](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub5.jpg)
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:
![pub6](http://www.oracledatabase12g.com/wp-content/uploads/2010/05/pub6.jpg)
-- 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 on 2010-05-21 19:04 Oracle和MySQL 阅读(282) 评论(0) 编辑 收藏 举报