Data Replication from Oracle logical standby to EDB Postgres

At EnterpriseDB (EDB), we are sometimes presented with individual customer use cases or requests that call for the development of innovative solutions to support their deployment of the EDB Postgres Platform. For example, one of our customers  wanted to leverage Oracle Logical standby for replication to EDB Postgres. And they wanted to know if the EDB Postgres Replication Server supported Oracle logical replication to EDB Postgres.

DBAs and database architects can use the EDB Postgres Replication Server for replicating data from Oracle Logical replication to the EDB Postgres Platform. However, the process of setting up the replication,requires extra steps. Because this was a very interesting use case, I thought a blog containing the recommended steps for using the EDB Postgres Replication Server with Oracle Logical Standby would provide some guidance for many end users. There are instances where this could support strategic replication deployments where EDB Postgres is complementing a legacy solution

For this blog, we will use following IP addresses and hostnames:

  1. EDB Postgres server: 172.17.0.4
  2. Oracle Logical Standby server: 172.17.0.3
  3. Oracle primary server: 172.17.0.2

To use EDB Postgres Replication Server 6.0, users should make sure Oracle Logical Standby is in standby guard status. The following is a list of guard status for Oracle Logical Standby:

 ALTER DATABASE GUARD mode
Mode Detail
ALL (Default) No users (except SYS) can make changes to logical standby data
STANDBY Users may modify logical standby data unless it’s being maintained by local LSPn processes (e.g. via Logical Standby)
NONE Normal security rules are applied; any user with appropriate privileges can modify logical standby data

Following a SQL command, DBAs can use the following to check the guard_status in Oracle:

SQL> SELECT guard_status FROM v$database;
GUARD_S
-------

STANDBY

After confirming the guard status in Oracle Logical Sandby, create a database user with the following privileges:

CREATE USER pubuser IDENTIFIED BY oracle;
GRANT DBA TO pubuser;
GRANT CREATE ANY TRIGGER TO pubuser;
GRANT SELECT ANY TABLE TO pubuser;
GRANT LOCK ANY TABLE TO pubuser;

Let’s create the EDB Postgres Replication Server between Oracle Logical Standby and EDB Postgres. We will be leveraging the EDB Postgres Replication Server command line interface for building publications and subscriptions. If you are not familiar with the EDB Postgres Replication Server command line interface options, please refer to the following link for more information:

https://www.enterprisedb.com/docs/en/6.0/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.59.html#

  1. Add Oracle as the publication database in the EDB Postgres Replication Server. The following command can be used to add the database:
[root@epas95 /]# . /usr/ppas-xdb-6.0/etc/sysconfig/xdbReplicationServer-60.config
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
    -addpubdb \
>   -repsvrfile ${PUBCONF} \
>   -dbtype oracle \
>   -dbhost 172.17.0.3 \
>   -dbport 1521 \
>   -dbuser ${ORAUSER} \
>   -dbpassword ${ORA_ENCRYPT_PASSWD}  \
>   -database ORCL
Adding publication database...
Publication database added successfully. Publication database id:1

Please note the publication database id: 1 and -dbhost: 172.17.0.3 which is the Logical Standby IP Address

  1. Execute the following anonymous PL/SQL block in Oracle Logical Standby as SYSDBA:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/

/

The DBA/User should execute the above PL/SQL block in Oracle Logical Standby to make sure triggers fire when a process changes the base table. By default, in Oracle Logical Standby, triggers never get fired properly in STANDBY guard status.

  1. Add the subscription database in EDB Postgres Replication Server. The following command can be used for adding the subscription database. In our case, we have the EDB Postgres database add:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-addsubdb \
>   -repsvrfile ${SUBCONF} \
>   -dbtype enterprisedb \
>   -dbhost 172.17.0.4 \
>   -dbport 5432 \
>   -dbuser ${PGUSER} \
>   -dbpassword ${PG_ENCRYPT_PASSWD}  \
>   -database orcl
Adding Subscription Database...
Subscription database added successfully. Subscription Database id:1006

Please note the subscription database id: 1006 and -dbhost: 172.17.0.3, which is the EDB Postgres server IP address.

  1. Before creating a publication and subscription, first change the GUARD status to NONE in the Oracle Logical Standby as given below:
SQL> ALTER DATABASE GUARD NONE;

Database altered.

The above change is needed because the EDB Postgres Replication Server acquires a LOCK on tables, managed by logical standby, for creating triggers for publication.

  1. Create publication for table: REPLICATION.REPLICATION_TABLE:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createpub replication_table \
>            -repsvrfile ${PUBCONF} \
>            -pubdbid 1 \
>            -reptype t \
>            -tables REPLICATION.REPLICATION_TABLE \
>            -repgrouptype s
Creating publication...
Tables:[[REPLICATION.REPLICATION_TABLE, TABLE]]
Filter clause:[]
Publication created.
  1. After creating the publication, please use the following anonymous block to set the EDB Postgres Replication Server trigger property to fire every time there is a change to the base table.
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. Create subscription for publication: replication_table (created in step 5) using following command:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createsub replication_table \
>   -subsvrfile ${SUBCONF} \
>   -subdbid 1006 \
>   -pubsvrfile ${PUBCONF} \
>   -pubname replication_table
Creating subscription...
Subscription created successfully
  1. After creating the subscription, re-execute the anonymous block to set EDB Postgres Replication Server trigger property:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. After creating a subscription, rollback the GUARD status to STANDBY in Oracle Logical Standby as shown below:
SQL> ALTER DATABASE GUARD STANDBY;

Database altered.

Now, we are ready to replicate data from Oracle Logical Standby to EDB Postgres.

Let’s first take the snapshot of data from Oracle Logical Standby to EDB Postgres using the following command:

[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar\
-dosnapshot replication_table \
-repsvrfile ${SUBCONF} \
-verboseSnapshotOutput true
Performing snapshot...
Running EnterpriseDB Migration Toolkit (Build 49.1.5) ...
Source database connectivity info...
conn =jdbc:oracle:thin:@172.17.0.3:1521:ORCL
user =pubuser
password=******
Target database connectivity info...
conn =jdbc:edb://172.17.0.4:5432/orcl?loginTimeout=60&connectTimeout=30
user =pubuser
password=******
Connecting with source Oracle database server...
Connected to Oracle, version 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options'
Connecting with target EnterpriseDB database server...
Connected to EnterpriseDB, version '9.5.5.10'
Importing redwood schema REPLICATION...
Table List: 'REPLICATION_TABLE'
Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on replication.replication_table before truncate...
Truncating table REPLICATION_TABLE before data load...
Disabling indexes on replication.replication_table before data load...
Loading Table: REPLICATION_TABLE ...
[REPLICATION_TABLE] Migrated 10 rows.
[REPLICATION_TABLE] Table Data Load Summary: Total Time(s): 0.04 Total Rows: 10
Enabling FK constraints & triggers on replication.replication_table...
Enabling indexes on replication.replication_table after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.191 Total Rows: 10 Total Size(MB): 0.0
Schema REPLICATION imported successfully.
Migration process completed successfully.
Migration logs have been saved to /var/log/xdb-6.0
******************** Migration Summary ********************
Tables: 1 out of 1
Total objects: 1
Successful count: 1
Failed count: 0
Invalid count: 0
*************************************************************
Snapshot taken successfully.

After taking an initial snapshot, let’s modify the replication table from Oracle primary:

18-JAN-17 REPLICATION@ ORCL PRIMARY> UPDATE replication_table SET id=11 WHERE id=10;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> COMMIT;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

Elapsed: 00:00:00.00

After making the changes on Oracle primary, we can execute SELECT command on Oracle Logical Standby to verify if the data change was replicated by Oracle in logical standby mode.

18-JAN-17 REPLICATION@ ORCL STANDBY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

10 rows selected.

Elapsed: 00:00:00.00
  1. Now perform the manual sync to verify EDB Postgres Replication Server can replicate the above changes in EDB Postgres.
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar  -dosynchronize  replication_table -repsvrfile ${SUBCONF} -repgrouptype s
Performing synchronize...
Synchronize done successfully.
[root@epas95 /]# psql orcl
psql.bin (9.5.5.10)
Type "help" for help.
orcl=# select * from replication.replication_table ;
id |   col
----+---------
1 | FIRST
2 | SECOND
3 | THIRD
4 | FOURTH
5 | FIFTH
6 | SIXTH
7 | SEVENTH
8 | EIGHTH
9 | NINTH
11 | TENTH

(10 rows)

The above snapshot shows that after performing -dosynchronize, the EDB Postgres Replication Server was able to replicate incremental changes on Oracle Logical Standby to EDB Postgres.

xDB Replication from Oracle to PPAS

EnterpriseDB Replication tool has been now modified and add new functionality in it.

Previous Replication Console, which had been made, was using the DBA Management server and Publication & Subscriptions were dependent.

Now, xDB Replication has been divided into two components:
1. Publication Server : (Master Server)
2. Subscription Server: (Slave Server)

Some Background of Publication Server. Publication Server has following components:
1. JAVA Based Daemon.
2. MetaDatabase of Publication Server.

Publication Server keeps its information (Information about the Primary Database, Tables Details etc.) in the MetaDatabase under schema _edb_replicator_pub

Publication server is independent and has no dependency on Subscriptions Server. By default publication server uses the 9011 port. However, if user wants, [S]he can use different ports too.

For running Publication Server on different port, use the following command:

$EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $EDBHOME/bin/edb-repserver.jar pubserver <port>  

Similarly, Subscription Server has two components:
1. JAVA Based Daemon
2. MetaDatabase of Publication. (which Subscription server uses for Keeping the information of Subscriptions)

In Metadatabase, xDB Subscription server keeps the information in _edb_replicator_sub.

By Default xDB Replication runs on port 9012. However if user wants [S]he can run the daemon on different port too, by using following command:

$EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $EDBHOME/bin/edb-repserver.jar subserver <port>  

Now, Questions comes, How to use the daemon for Publication and Subscriptions?

EnterpriseDB provides a jar file edb-repcli.jar in $EDBHOME/bin which can be use.

edb-repcli.jar has been exhausted with lots of options using which user can Create a Publication/Subcriptions, can add the database etc.

To find more of its options, user can use following command:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -help  

With above, user has to create a two configuration files:
1. Publication Configuration file
2. Subscription Configuration file.

Format of Configuration files of Publication and Subcription are same.
Following is a snapshot and description of configuration file:

user=enterprisedb # User using which xDB Publication/Subscription can connect with MetaDAtabase  
port=9011 # Port on  which Publication/Subscription Daemon is running.  
password= <encrypted password="">  
type=enterprisedb # There are two of database which xDB Replication Daemon can use one EnterpriseDB databases (Postgres Plus Advanced Or postgres (PostgreSQL)  
host=localhost # Host on which Publication Daemon is running  

With above configuration file of Publication/Subscription configuration files, there is one more xDB Server configuration file. This file by default exists in /etc/edb-repl.conf. This file contains the information of xDB Metadabase.
Following is a snapshot of this file:

user=enterprisedb  
port=5445 (port on which xDB Replication is running)  
type=enterprisedb  
host=localhost  
database=edb  

Few Handy Commands of edb-repcli.jar is given below:

1. To view list the publications, user can use following command:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpublist -repsvrfile <publication configuration="" file="">  

2. To view the published tables of a publication, following command:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpublishedtables <publication name=""> -repsvrfile <publication configuration="" file="">  

3. To list the subscriptions of a particular subscription database, following command can be use:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printsublist -repsvrfile <subscription configuration="" file=""> -subdbid <subscription database="" id="">  

4. To print the subscribed database ids following command can be use:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printsubdbidsdetails -repsvrfile <subscription configuration="" file="">  

5. To add the Databases [Oracle|EnterpriseDB] for publication, following command can be use:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -addpubdb -repsvrfile <publication configuration="" file=""> -dbtype {oracle | enterprisedb | postgresql} -dbhost <host> -dbport <port> -dbuser <user> -dbpassword <encrypted password=""> -database {<database> | <service>} [-oraconnectiontype {sid | servicename}]  

6. To print the published Databases in Publication Server, following command can be use:

$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpubdbidsdetails -repsvrfile <publication configuration="" file="">  

To control the behavior/performance of Publication/Subscription daemon, EnterpriseDB provides a two important files.
1. xdb_pubserver.conf
2. xdb_subserver.con

Location of above two files is $EDBHOME/etc.

Information of the contents of these two files can be find in following link:
http://www.enterprisedb.com/docs/en/8.4/repserver/Postgres_Plus_Advanced_Server_Replication_Server_Users_Guide-29.htm#TopOfPage

Details of Permitted Source and Target databases can be find in following link:
http://www.enterprisedb.com/docs/en/8.4/repserver/Postgres_Plus_Advanced_Server_Replication_Server_Users_Guide-27.htm#TopOfPage