Highlighting xDB Replication Server

This is the first in a series of posts highlighting common business requirements for managing data I see in the field, and the tools that are available to help meet those requirements.

One common requirement many organizations have is being able to move data around to different database systems. While I deal with a lot of requests for help moving data from Oracle to Postgres, I also assist businesses that routinely push data from Postgres to Oracle. Typically because the corporate BI database is still on Oracle.

This post captures a simple use-case that I encounter often. "We need to move data in Postgres to a staging table in our Oracle BI database." For a job such as this one, no custom scripts are necessary - xDB from EnterpriseDB is the perfect tool.

I can verify the tables do not already exist in Oracle by querying with a oci-dblink connection from Postgres.

edbstore=# select * from cat@edbstore_ora;
 table_name | table_type
------------+------------
(0 rows)

Let's describe the tables in our edbstore database. These tables should look familiar if you've ever taken one of EnterpriseDB's training courses.                               
edbstore=# \dt+
                          List of relations
  Schema  |    Name    | Type  |  Owner   |    Size    | Description
----------+------------+-------+----------+------------+-------------
 edbstore | categories | table | edbstore | 8192 bytes |
 edbstore | cust_hist  | table | edbstore | 2640 kB    |
 edbstore | customers  | table | edbstore | 3936 kB    |
 edbstore | dept       | table | edbstore | 8192 bytes |
 edbstore | emp        | table | edbstore | 8192 bytes |
 edbstore | inventory  | table | edbstore | 464 kB     |
 edbstore | job_grd    | table | edbstore | 16 kB      |
 edbstore | jobhist    | table | edbstore | 8192 bytes |
 edbstore | locations  | table | edbstore | 8192 bytes |
 edbstore | orderlines | table | edbstore | 3104 kB    |
 edbstore | orders     | table | edbstore | 920 kB     |
 edbstore | products   | table | edbstore | 832 kB     |
 edbstore | reorder    | table | edbstore | 0 bytes    |
(13 rows)

Now I'm going to add the edbstore database to our xDB Publication Server.

Next, I'm adding the Oracle target database to the Subscription server using the xDB Replication Console. 

A Publication can be created with the xDB Replication Console. In this case we are only interested in the  customers and orders table. 

Note: Click on the Table Filters tab if you need to add a filter.

Now we can move over the the Subscription Server and subscribe to the newly created Publication from within the same console.

Take a Snapshot.

The Data Load and Migration Summary will be displayed toward the end of the console messages:

[orders] Table Data Load Summary: Total Time(s): 6.379 Total Rows: 12000
Data Load Summary: Total Time (sec): 14.398 Total Rows: 32000 Total Size(MB): 0.0

Schema edbstore imported successfully.

Let's verify the data is in Oracle using SQL*Plus                                                
SQL> select
  2  (select count(*) from customers) AS customers_count,
  3  (select count(*) from orders) AS orders_count
  4  from dual;

CUSTOMERS_COUNT ORDERS_COUNT
--------------- ------------
 20000        12000

Configure Synchronization to whatever schedule suits your needs.                                            

Synchronization can be configured as Daily, Weekly, Monthly or a Cron Expression. Let's go with Continuously. 


From the xDB Replication Console:

Verify that changes are immediately being replicated to the target Oracle Database.
I'm going to COPY 50 records into the Orders table in Postgres and verify the updates are immediately available in Oracle as well.

edbstore=# select
edbstore-# (select count(*) from orders) AS orders_postgres,
edbstore-# (select count(*) from orders@edbstore_ora) AS orders_oracle
edbstore-# from dual;
 orders_postgres | orders_oracle
-----------------+---------------
           12050 |         12050
(1 row)

                                   

PL/Perl on PPAS 9.3 for *EL6

PL/Perl on Postgres Plus Advanced Server 9.3 

The version of Perl supported for Postgres Plus Advanced Server 9.3 is 5.14. "Enterprise Linux 6.5" distros come with Perl 5.10. Most users choose a precompiled Perl distribution from ActiveState. However, ActiveState changed their licensing model, and ActivePerl 5.14 is no longer available for a free download.

The steps below will get you up and running with pl/perl on PPAS 9.3 for RHEL6, OEL6, and CentOS 6.

Step 1: Download Perl from the Perl Archive Network 

[root@localhost tmp]# wget search.cpan.org/CPAN/authors/id/J/JE/JESSE/perl-5.14.0.tar.gz

Step 2: Decompress 

[root@localhost tmp]# gzip -d perl-5.14.0.tar.gz

Step 3: Extract 

[root@localhost tmp]# tar -xvf perl-5.14.0.tar

Step 4: Change current directory 

[root@localhost tmp]# cd perl-5.14.0

Step 5: Run the installation shell script 

[root@localhost perl-5.14.0]# ./Configure

Two non default options are needed, be sure to answer 'y' for 'Building a threading Perl', and 'y' to 'build a shared libperl.so'. This example changes the installation directory to /opt/perl514

Change the default installation directory (optional)
Installation prefix to use? (~name ok) [/usr/local] /opt/perl514

Answer 'y'
If this doesn't make any sense to you, just accept the default 'n'. Build a threading Perl? [n] y

Answer 'y'

Build a shared libperl.so (y/n) [n] y

Step 6: Compile

[root@localhost perl-5.14.0]# make test

[root@localhost perl-5.14.0]# make install

Step 7: Source 

-bash-4.1$ export LD_LIBRARY_PATH=/opt/perl514/lib/5.14.0/x86_64-linux-thread-multi/CORE:$LD_LIBRARY_PATH

Step 8: Restart the PPAS Cluster 

-bash-4.1$ pg_ctl restart -mf -D /opt/PostgresPlus/9.3AS/data/

Step 9: Create language in the desired database 

-bash-4.1$ ./bin/psql -c "create language plperl" geom_repo enterprisedb

Step 10: Test Pl/Perl using the example from EDB's plperl readme

    CREATE OR REPLACE FUNCTION perl_max (integer, integer) RETURNS integer AS
    $$
        if ($_[0] > $_[1])
          { return $_[0]; }
        return $_[1];
    $$ LANGUAGE plperl;

geom_repo=# select perl_max(1,2);
-[ RECORD 1 ]                    
perl_max | 2