DBVISIT from Oracle to Postgres

As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool.

Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the redo log, an external file called PLOG is generated and transferred to the target.

The architecture is quite easy to understand, you have a MINE process on the source server, looking at the redo logs for changed data, and an APPLY process which applies SQL on the target database.

The configuration is easy to implement but must not be under estimated:=)

Before running dbvrep, be sure you can connect with psql from the Oracle server to the postgreSQL server !! I needed to install a postgres client own the Oracle host and to define the PATH properly.

Finally by running dbvrep on the Oracle server, you run the setup wizard, and you enter your configuration settings, this menu is quite easy to understand. The setup wizard is defined in 4 steps:

– Step 1: describe databases

– Step 2: Replicate Pairs

– Step 3: Replicated tables

– Step 4: Process Configuration

oracle@localhost:/home/oracle/ora2pg/ [PSI] dbvrep
Initializing......done
Dbvisit Replicate version 2.9.02
Copyright (C) Dbvisit Software Limited. All rights reserved.
No DDC file loaded.
Run "setup wizard" to start the configuration wizard or try "help"
to see all commands available.
dbvrep>setup wizard
This wizard configures Dbvisit Replicate.
The setup wizard creates configuration scripts, which need to be run
after the wizard ends. Nochanges to the databases are made before that.
The progress is saved every time a list of databases, replications, etc.
is shown. It will bere-read if wizard is restarted and the same DDC
name and script path is selected.
Run the wizard now? [Yes]Accept end-user license agreement? (View/Yes/No) [No]yes
Before starting the actual configuration, some basic information is needed. The DDC name and
script path determines where all files created by the wizard go
(and where to reread them ifwizard is rerun) and the license key
determines which options are available for this
configuration.
(DDC_NAME) - Please enter a name for this replication: []ora2pg(LICENSE_KEY) - Please enter your license key: [(trial)]Which Replicate edition do you want to trial (LTD/XTD/MAX): [MAX]
(SETUP_SCRIPT_PATH) - Please enter a directory for location of
configuration scripts on this
machine: [/home/oracle/Documents/ora2pg]/home/oracle/ora2pg
Network configuration files were detected on this system in these locations:
/u00/app/oracle/network/admin
/u00/app/oracle/product/12.2.0.1/dbhome_1/network/admin
(TNS_ADMIN) - Please enter TNS configuration directory for this machine: [/u00/app/oracle/network/admin]
Read 2 described databases from previous wizard run.
Step 1 - Describe databases
========================================
The first step is to describe databases used in the replication.
There are usually two of them
(source and target).
Following databases are now configured:
1: Oracle PSI, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/,
ASM:No, TZ: +02:00
2: Postgres postgres, dbvrep_admin/***, dbvrep_admin/***, dbvrep/***,
/, dbvrep/, ASM:n/a, TZ:
Enter the number of the database to modify it, or "add", or "done": [done]
Read 1 replication pairs from previous wizard run.
Step 2 - Replication pairs
========================================
The second step is to set source and targets for each replication pair.
Enter number of replication pair to modify it, or "add", or "done": [done]1Do you want to "edit" the replication pair or "delete" it? [edit]edit
Let's configure the replication pair, selecting source and target.
Following databases are described:
1: PSI#DBVREP (Oracle)
2: DBNAME=POSTGRES;HOST=PG1#DBVREP (Postgres)
(cannot be source: not an Oracle database)
Select source database: [1]Select target database: [2]Will limited DDL replication be enabled? (Yes/No) [Yes]Use fetcher to offload the mining to a different server? (Yes/No) [No]
Should where clauses (and Event Streaming) include all columns,
not just changed and PK? (Yes/No) [No]Would you like to encrypt the data across the network? (Yes/No) [No]Would you like to compress the data across the network? (Yes/No) [No]
How long do you want to set the network timeouts.
Recommended range between 60-300 seconds [60]
Lock and copy the data initially one-by-one or at a single SCN?
one-by-one : Lock tables one by one and capture SCN
single-scn : One SCN for all tables
ddl-only : Only DDL script for target objects
resetlogs : Use SCN from last resetlogs operation
(standby activation, rman incomplete
recovery)
no-lock : Do not lock tables. Captures previous SCN of oldest active
transaction. Requires pre-requisite running of pre-all.sh script (one-by-one/single-scn/ddl-only/resetlogs/no-lock) [single-scn]
What data instantiation script to create?
ddl_file : DDL file created (APPLY.sql)
ddl_run : DDL is automatically executed on target
load : All replicated data is created and loaded automatically
none (ddl_file/ddl_run/load/none) [ddl_run]ddl-file
Following replication pairs are now configured:
1: PSI (Oracle) ==> postgres (Postgres), DDL: Yes, fetcher: No,
process suffix: (no suffix),
compression: No, encryption: No, network timeout: 60, prepare type:
single-scn,:
ddl-run
Enter number of replication pair to modify it, or "add", or "done": [done]
Read 1 replication pairs from previous wizard run.
Step 3 - Replicated tables
========================================
The third step is to choose the schemas and tables to be replicated.
If the databases arereachable, the tables are checked for existence,
datatype support, etc., schemas are queried for tables.
Note that all messages are merely hints/warnings and may be ignored
if issues are rectified before the scripts are actually executed.
Following tables are defined for replication pairs:
1: PSI (Oracle) ==> postgres (Postgres), DDL: Yes, suffix: (no suffix),
prepare: single-scn
PSI(tables)
Enter number of replication pair to modify it, or "done": [done]
Read 2 replication pairs from previous wizard run.
Step 4 - Process configuration
========================================
The fourth step is to configure the replication processes for each
replication.
Following processes are defined:
1: MINE on PSI
Host: cloud13c, SMTP: No, SNMP: No
2: APPLY on postgres
Host: pg1, SMTP: No, SNMP: No
Enter number of process to modify it, or "done": [done]1
Fully qualified name of the server for the process (usually co-located
with the database, unless mine is offloaded using fetcher): [cloud13c]Server type (Windows/Linux/Unix): [Linux]Enable email notifications about problems? (Yes/No) [No]Enable SNMP traps/notifications about problems? (Yes/No) [No]
Directory with DDC file and default where to create log files etc.
(recommended: same as global setting, if possible)? [/home/oracle/ora2pg]
Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[MINE_REMOTE_INTERFACE]: Network remote interface: cloud13c:7901
[MINE_DATABASE]: Database TNS: PSI
[TNS_ADMIN]: tnsnames.ora path: /u00/app/oracle/network/admin
[MINE_PLOG]: Filemask for generated plogs: /home/oracle/ora2pg/mine/%S.%E
(%S is sequence, %T thread, %F original filename (stripped extension),
%P process type, %N process name, %E default extension)
[LOG_FILE]: General log file: /home/oracle/ora2pg/log/dbvrep_%N_%D.%E
[LOG_FILE_TRACE]: Error traces:
/home/oracle/ora2pg/log/trace/dbvrep_%N_%D_%I_%U.%E
Checking that these settings are valid...
Do you want to change any of the settings? [No]
Following processes are defined:
1: MINE on PSI
Host: cloud13c, SMTP: No, SNMP: No
2: APPLY on postgres
Host: pg1, SMTP: No, SNMP: No
Enter number of process to modify it, or "done": [done]2
Fully qualified name of the server for the process (usually co-located
with the database, unless mine is offloaded using fetcher): [pg1]Server type (Windows/Linux/Unix): [Linux]Enable email notifications about problems? (Yes/No) [No]Enable SNMP traps/notifications about problems? (Yes/No) [No]
Directory with DDC file and default where to create log files etc.
(recommended: same as global setting, if possible)? [/home/oracle/ora2pg]
Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[APPLY_REMOTE_INTERFACE]: Network remote interface: pg1:7902
[APPLY_DATABASE]: Database Postgres connection string: dbname=postgres;
host=pg1
[TNS_ADMIN]: tnsnames.ora path: /u00/app/oracle/network/admin
[APPLY_SCHEMA]: Dbvisit Replicate database (schema): dbvrep
[APPLY_STAGING_DIR]: Directory for received plogs: /home/oracle/ora2pg/apply
[LOG_FILE]: General log file: /home/oracle/ora2pg/log/dbvrep_%N_%D.%E
[LOG_FILE_TRACE]: Error traces:
/home/oracle/ora2pg/log/trace/dbvrep_%N_%D_%I_%U.%E
Checking that these settings are valid...
Do you want to change any of the settings? [No]
Following processes are defined:
1: MINE on PSI
Host: cloud13c, SMTP: No, SNMP: No
2: APPLY on postgres
Host: pg1, SMTP: No, SNMP: No
Enter number of process to modify it, or "done": [done]
Created file /home/oracle/ora2pg/ora2pg-APPLY.ddc.
Created file /home/oracle/ora2pg/ora2pg-MINE.ddc.
Created file /home/oracle/ora2pg/config/ora2pg-setup.dbvrep.
Created file /home/oracle/ora2pg/config/ora2pg-dbsetup_PSI_DBVREP.sql.
Created file /home/oracle/ora2pg/config/ora2pg-dbsetup_DBNAME_POSTGRES_HOST_PG1_DBVREP.sql.
Created file /home/oracle/ora2pg/config/ora2pg-grants_PSI_DBVREP.sql.
Created file /home/oracle/ora2pg/config/ora2pg-grants_DBNAME_POSTGRES_HOST_PG1_DBVREP.sql.
Created file /home/oracle/ora2pg/config/ora2pg-onetime.ddc.
Created file /home/oracle/ora2pg/start-console.sh.
Created file /home/oracle/ora2pg/ora2pg-run-cloud13c.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-cloud13c-start-MINE.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-cloud13c-stop-MINE.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-cloud13c-dbvrep-MINE.sh.
Created file /home/oracle/ora2pg/scripts/systemd-dbvrep-MINE_ora2pg.service.
Created file /home/oracle/ora2pg/scripts/upstart-dbvrep-MINE_ora2pg.conf.
Created file /home/oracle/ora2pg/ora2pg-run-pg1.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-pg1-start-APPLY.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-pg1-stop-APPLY.sh.
Created file /home/oracle/ora2pg/scripts/ora2pg-pg1-dbvrep-APPLY.sh.
Created file /home/oracle/ora2pg/scripts/systemd-dbvrep-APPLY_ora2pg.service.
Created file /home/oracle/ora2pg/scripts/upstart-dbvrep-APPLY_ora2pg.conf.
Created file /home/oracle/ora2pg/Nextsteps.txt.
Created file /home/oracle/ora2pg/ora2pg-all.sh.
============================================================================
Dbvisit Replicate wizard completed
Script /home/oracle/ora2pg/ora2pg-all.sh created.
This runs all the above created scripts. Please exit out of dbvrep,
review and run script as current user to setup and start Dbvisit Replicate.
============================================================================
Optionally, the script can be invoked now by this wizard.
Run this script now? (Yes/No) [No]dbvrep>exit

As it is asked at the end of the setup wizard, we run the ora2pg_all.sh :

(postgres@[local]:5432) [postgres] > select * from psi.employe;
name | salary
-------+--------
Larry | 10000
Bill | 2000
John | 50000
(3 rows)
As previously we have choosen the single-scn and ddl-run option, we had to run the APPLY.sql script from the Oracle server in order to create the tables on the postgres side, you can also choose in Step 2 of the configuration wizard, the load option (all replicated data is created and loaded automatically):

Lock and copy the data initially one-by-one or at a single SCN?
one-by-one : Lock tables one by one and capture SCN
single-scn : One SCN for all tables
ddl-only : Only DDL script for target objects
resetlogs : Use SCN from last resetlogs operation (standby activation, rman incomplete
recovery)
no-lock : Do not lock tables. Captures previous SCN of oldest active transaction. Requires
pre-requisite running of pre-all.sh script (one-by-one/single-scn/ddl-only/resetlogs/no-lock) [single-scn]
What data instantiation script to create?
ddl_file : DDL file created (APPLY.sql)
ddl_run : DDL is automatically executed on target
load : All replicated data is created and loaded automatically
none (ddl_file/ddl_run/load/none) [ddl_run]load
Do you want to (re-)create the tables on target or keep them (they are already created)? (create/keep) [keep]create

In this case you can visualize that each Oracle table is replicated to the Postgres server.

Despite some problems at the beginning of my tests, the replication from Oracle to PostgreSQL is working fine and fast. There are many possibilities with Dbvisit Replicate I will try to test in the following weeks.