Oracle To Postgresql XDB Replication:-

Online Migration.

When you want to offload reporting stuff from Oracle to PostgreSQL there are several solutions you can go for. There most widely known probably are  oracle gg and shareplex. EnterpriseDB has its own solution for this which is called edb replication server. As soon as you have one this tool is included and can be used to offload from Oracle to PostgreSQL. Lets do a simple replication setup and see how it works.

For this little demo I have to boxes: One running Oracle (12.1.0.2) and the EDB Replication Server publisher and the other one running PostgreSQL 9.6.2 and the EDB Replication server subscriber and the console.

The installation is quite simple: Download the software, unzip and execute as root:

1 2 3 4 5

[root@pgxdb ~]$ cd /var/tmp/ [root@pgxdb tmp]$ ls -la xdbreplicationserver-6.1.0-1-linux-x64.run -rw-r--r--. 1 root root 26934145 May 19 09:01 xdbreplicationserver-6.1.0-1-linux-x64.run [root@pgxdb tmp]$ chmod +x xdbreplicationserver-6.1.0-1-linux-x64.run [root@pgxdb tmp]$ ./xdbreplicationserver-6.1.0-1-linux-x64.run

These are the screenshots for the PostgreSQL box:

Once the installation completed you should be able to see what was installed:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[root@pgxdb tmp]$ su - postgres Last failed login: Sun May 15 06:35:25 CEST 2016 from 192.168.22.1 on ssh:notty There were 6 failed login attempts since the last successful login. -bash-4.2$ ls -la total 7928 drwxr-xr-x. 7 root root    4096 May 19 09:15 . drwxr-xr-x. 3 root root      46 May 19 09:15 .. drwxrwxr-x. 2 root root    4096 May 19 09:15 bin drwxrwxr-x. 3 root root      78 May 19 09:15 etc drwxrwxr-x. 4 root root      43 May 19 09:15 installer drwxrwxr-x. 5 root root    4096 May 19 09:15 lib drwxrwxr-x. 4 root root      44 May 19 09:15 scripts -rwx------. 1 root root 7967605 May 19 09:15 uninstall-xdbreplicationserver -rw-------. 1 root root   26455 May 19 09:15 uninstall-xdbreplicationserver.dat -r--r--r--. 1 root root   64033 Feb 27 12:31 xdb_3rd_party_licenses.txt -r--r--r--. 1 root root   15216 Feb 27 12:30 xdb_license.txt

Two new processes are running, which are basically the subscriber:

1 2 3

-bash-4.2$ ps -ef | grep postgres postgres 13019     1  0 09:15 ?        00:00:00 /bin/bash -c cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin; ./runSubServer.sh  >> /var/log/edb/xdbsubserver/edb-xdbsubserver.log 2>&1 & postgres 13044 13019  0 09:15 ?        00:00:00 /usr/bin/java -XX:-UsePerfData -XX:ErrorFile=/var/log/xdb-6.1/subserver_pid_%p.log -Djava.awt.headless=true -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repserver.jar subserver 9052

For the Oracle box I did exactly the same except for this:

Same picture, two new processes which are the publisher:

1 2

oracle   29542     1  0 09:48 ?        00:00:00 /bin/bash -c cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin; ./runPubServer.sh  >> /var/log/edb/xdbpubserver/edb-xdbpubserver.log 2>&1 & oracle   29567 29542  0 09:48 ?        00:00:00 /usr/bin/java -XX:-UsePerfData -Xms256m -Xmx1536m -XX:ErrorFile=/var/log/xdb-6.1/pubserver_pid_%p.log -Djava.library.path=/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin -Djava.awt.headless=true -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repserver.jar pubserver 9051

Lets start the console to bring up the graphical user interface:

1	-bash-4.2$ bin/runRepConsole.sh

Register a new subscription server (PostgreSQL box):

Register a new publication server (Oracle box):

Before proceeding from here you should download the Oracle JDBC driver and put it to both boxes:

1 2 3 4

-bash-4.2$ pwd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/lib/jdbc -bash-4.2$ ls -l ojdbc*.jar -rw-r--r--. 1 root root 3698857 May 19 10:45 ojdbc7.jar

Remember to restart the EDB Replication Server services:

1 2	sudo systemctl restart edb-xdbpubserver.service sudo systemctl restart edb-xdbsubserver.service

After that add the Oracle database (I am using the scott/tiger sample schema here):

Ok, easy to fix:
grant connect,resource,create trigger to scott; Grant succeeded.

Once again:

The next thing is to create the publication itself:

Ok, lets fix it:
alter table scott.bonus add constraint bonus_pk primary key ( ename,job); Table altered.

Ok, lets fix it:
alter table scott.salgrade add constraint salgrade_pk primary key (grade,losal,hisal); Table altered.

Done.

Lets create the subscription, but before doing that make sure that you put the details of the controller database (The Oracle one in my case) to the EDB Replication Server configuration file:
postgres@pgxdb:/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/ [xdbrepl] cat /etc/edb-repl.conf admin_password=VbjDPk5zwu/naXI2fpEbBQ\=\= user=scott port=1521 password=H4rzJCNHH/Q\= type=oracle admin_user=admin host=192.168.22.242 database=servicename\=DB4.it.dbi-services.com

Proceed with creating the subscription database:

All the table are there now in the PostgreSQL instance but empty:
pgxdb/postgres MASTER (postgres@5432) # \d SCOTT.* Table "scott.bonus" ┌────────┬───────────────────────┬───────────┐ │ Column │ Type │ Modifiers │ ├────────┼───────────────────────┼───────────┤ │ ename │ character varying(10) │ not null │ │ job │ character varying(9) │ not null │ │ sal │ numeric │ │ │ comm │ numeric │ │ └────────┴───────────────────────┴───────────┘ Indexes: "bonus_pk" PRIMARY KEY, btree (ename, job)

Index "scott.bonus_pk" ┌────────┬───────────────────────┬────────────┐ │ Column │ Type │ Definition │ ├────────┼───────────────────────┼────────────┤ │ ename │ character varying(10) │ ename │ │ job │ character varying(9) │ job │ └────────┴───────────────────────┴────────────┘ primary key, btree, for table "scott.bonus"

Table "scott.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)

Table "scott.emp" ┌──────────┬───────────────────────┬───────────┐ │ Column │ Type │ Modifiers │ ├──────────┼───────────────────────┼───────────┤ │ empno │ numeric(4,0) │ not null │ │ ename │ character varying(10) │ │ │ job │ character varying(9) │ │ │ mgr │ numeric(4,0) │ │ │ hiredate │ date │ │ │ sal │ numeric(7,2) │ │ │ comm │ numeric(7,2) │ │ │ deptno │ numeric(2,0) │ │ └──────────┴───────────────────────┴───────────┘ Indexes: "pk_emp" PRIMARY KEY, btree (empno)

Index "scott.pk_dept" ┌────────┬──────────────┬────────────┐ │ Column │ Type │ Definition │ ├────────┼──────────────┼────────────┤ │ deptno │ numeric(2,0) │ deptno │ └────────┴──────────────┴────────────┘ primary key, btree, for table "scott.dept"

Index "scott.pk_emp" ┌────────┬──────────────┬────────────┐ │ Column │ Type │ Definition │ ├────────┼──────────────┼────────────┤ │ empno │ numeric(4,0) │ empno │ └────────┴──────────────┴────────────┘ primary key, btree, for table "scott.emp"

Table "scott.salgrade" ┌────────┬─────────┬───────────┐ │ Column │ Type │ Modifiers │ ├────────┼─────────┼───────────┤ │ grade │ numeric │ not null │ │ losal │ numeric │ not null │ │ hisal │ numeric │ not null │ └────────┴─────────┴───────────┘ Indexes: "salgrade_pk" PRIMARY KEY, btree (grade, losal, hisal)

Index "scott.salgrade_pk" ┌────────┬─────────┬────────────┐ │ Column │ Type │ Definition │ ├────────┼─────────┼────────────┤ │ grade │ numeric │ grade │ │ losal │ numeric │ losal │ │ hisal │ numeric │ hisal │ └────────┴─────────┴────────────┘ primary key, btree, for table "scott.salgrade"

pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.salgrade; ┌───────┐ │ count │ ├───────┤ │ 0 │ └───────┘ (1 row)

Time: 0.443 ms pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.depts; ERROR: relation "scott.depts" does not exist LINE 1: select count(*) from SCOTT.depts; ^ Time: 0.554 ms pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.dept; ┌───────┐ │ count │ ├───────┤ │ 0 │ └───────┘ (1 row)

Time: 0.298 ms

To do the initial synchronization do a Snapshot:

All the data is there:
pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.dept; ┌────────┬────────────┬──────────┐ │ deptno │ dname │ loc │ ├────────┼────────────┼──────────┤ │ 10 │ ACCOUNTING │ NEW YORK │ │ 20 │ RESEARCH │ DALLAS │ │ 30 │ SALES │ CHICAGO │ │ 40 │ OPERATIONS │ BOSTON │ └────────┴────────────┴──────────┘ (4 rows)

Time: 0.435 ms pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.emp; ┌───────┬────────┬───────────┬──────┬────────────┬─────────┬─────────┬────────┐ │ empno │ ename │ job │ mgr │ hiredate │ sal │ comm │ deptno │ ├───────┼────────┼───────────┼──────┼────────────┼─────────┼─────────┼────────┤ │ 7369 │ SMITH │ CLERK │ 7902 │ 1980-12-17 │ 800.00 │ NULL │ 20 │ │ 7499 │ ALLEN │ SALESMAN │ 7698 │ 1981-02-20 │ 1600.00 │ 300.00 │ 30 │ │ 7521 │ WARD │ SALESMAN │ 7698 │ 1981-02-22 │ 1250.00 │ 500.00 │ 30 │ │ 7566 │ JONES │ MANAGER │ 7839 │ 1981-04-02 │ 2975.00 │ NULL │ 20 │ │ 7654 │ MARTIN │ SALESMAN │ 7698 │ 1981-09-28 │ 1250.00 │ 1400.00 │ 30 │ │ 7698 │ BLAKE │ MANAGER │ 7839 │ 1981-05-01 │ 2850.00 │ NULL │ 30 │ │ 7782 │ CLARK │ MANAGER │ 7839 │ 1981-06-09 │ 2450.00 │ NULL │ 10 │ │ 7788 │ SCOTT │ ANALYST │ 7566 │ 1987-04-19 │ 3000.00 │ NULL │ 20 │ │ 7839 │ KING │ PRESIDENT │ NULL │ 1981-11-17 │ 5000.00 │ NULL │ 10 │ │ 7844 │ TURNER │ SALESMAN │ 7698 │ 1981-09-08 │ 1500.00 │ 0.00 │ 30 │ │ 7876 │ ADAMS │ CLERK │ 7788 │ 1987-05-23 │ 1100.00 │ NULL │ 20 │ │ 7900 │ JAMES │ CLERK │ 7698 │ 1981-12-03 │ 950.00 │ NULL │ 30 │ │ 7902 │ FORD │ ANALYST │ 7566 │ 1981-12-03 │ 3000.00 │ NULL │ 20 │ │ 7934 │ MILLER │ CLERK │ 7782 │ 1982-01-23 │ 1300.00 │ NULL │ 10 │ └───────┴────────┴───────────┴──────┴────────────┴─────────┴─────────┴────────┘ (14 rows)

To synchronize changes:

To schedule synchronization:

Test it, in Oracle:
insert into scott.dept values ( 50,'My_Dept','Home'); 1 row created. commit; Commit complete.

PostgreSQL:
pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.dept; ┌────────┬────────────┬──────────┐ │ deptno │ dname │ loc │ ├────────┼────────────┼──────────┤ │ 10 │ ACCOUNTING │ NEW YORK │ │ 20 │ RESEARCH │ DALLAS │ │ 30 │ SALES │ CHICAGO │ │ 40 │ OPERATIONS │ BOSTON │ │ 50 │ My_Dept │ Home │ └────────┴────────────┴──────────┘ (5 rows)

Quite easy to setup and to do an initial simple replication. In a future post we’ll look at more specific use cases. Hope this helps to get you started.