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 sSandby, 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:
- 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
- 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
DBMS_DDL.set_trigger_firing_property(
trig_owner => 'PUBUSER',
trig_name => s.trigger_name,
fire_once => FALSE);
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.
- 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.
- 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.
- 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.
- 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
DBMS_DDL.set_trigger_firing_property(
trig_owner => 'PUBUSER',
trig_name => s.trigger_name,
fire_once => FALSE);
END LOOP;
END;
/
- 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
- 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
DBMS_DDL.set_trigger_firing_property(
trig_owner => 'PUBUSER',
trig_name => s.trigger_name,
fire_once => FALSE);
END LOOP;
END;
/
- 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
- 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.
Vibhor Kumar is Director, Solution Architecture, at EnterpriseDB.
This post originally appeared on Vibhor's personal blog.