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:=)
My configuration is the following:
- Oracle server named cloud13c, with PSI database version 12.2.0.1
- Postgres server named pg_essentials_p1 with Postgres version 9.6
At first, we create a user in the Postgres database:
postgres@pg_essentials_p1:/home/postgres/ [PG1] createuser -d -e -E -l -P -r -s dbvrep_admin
Enter password for new role: Enter it again: CREATE ROLE dbvrep_admin ENCRYPTED PASSWORD 'md5e3c4e8f1b4f8e388eef4fe890d6bdb36' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
We edit the configuration file /u02/pgdata/postgresql.conf in order to allow non-localhost connections:
postgres@pg1:/u02/pgdata/PG1/ [PG1] cp postgresql.conf postgresql.conf.save postgres@pg1:/u02/pgdata/PG1/ [PG1] sed -i "s/^#\( listen_addresses = '\)localhost'/\1*'\t/" postgresql.conf
We also enable connections on non-localhost address:
postgres@pg1:/u02/pgdata/PG1/ [PG1] cp pg_hba.conf pg_hba.conf.save postgres@p1:/u02/pgdata/PG1/ [PG1] echo -e "host\tall\t\tall\t\t0.0.0.0/0\t\tmd5" >> pg_hba.conf
cat pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.22.201/24 md5 # IPv6 local connections: host all all ::1/128 md5 host all barman 192.168.1.101/24 md5 host replication barman_streaming 192.168.1.101/24 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication postgres md5 host replication postgres 127.0.0.1/32 md5 host replication postgres ::1/128 md5 host all all 0.0.0.0/0 md5
We have to restart the postgres server:
postgres@pg1:/u02/pgdata/PG1/ [PG1] pgrestart
waiting for server to shut down.... done
server stopped
server starting
postgres@pg1:/u02/pgdata/PG1/ [PG1] 2017-07-17 13:52:52.350 CEST
- 1 - 3106 -
- @ LOG: redirecting log output to logging collector process
2017-07-17 13:52:52.350 CEST - 2 - 3106 -
- @ HINT: Future log output will appear in directory
"/u01/app/postgres/admin/PG1/pg_log".
postgres@pg_essentials_p1:/u02/pgdata/PG1/ [PG1]
postgres@pg_essentials_p1:/u02/pgdata/PG1/ [PG1] alias | grep pgrestart
alias pgrestart='pg_ctl -D ${PGDATA} restart -m fast'
Then we install dbvisit replicate:
We download the dbvisit_replicate-2.9.00-el5.x86_64.rpm and we install it:
[root@localhost software]# rpm -ivh dbvisit_replicate-2.9.00-el5.x86_64.rpm Preparing... ################################# [100%] Updating / installing... 1:dbvisit_replicate-2.9.00-el5 ################################# [100%]
To make it work properly, I had to modify the sqlnet.ora file as follows in order to avoid the following error message:
ERR-11: Could not connect as dbvrep to database PSI, error is ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)
sqlnet.ora: SQLNET.SQLNET_ALLOWED_LOGON_VERSION=11 SQLNET.ALLOWED_LOGON_VERSION_CLIENT =11 SQLNET.ALLOWED_LOGON_VERSION_SERVER =11
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] 1 Do 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 :
oracle@localhost:/home/oracle/ora2pg/ [PSI] . ora2pg-all.sh Setting up Dbvisit Replicate configuration Configure database PSI... This check fails if the DBID is not the expected one... Ok, check passed. Configure database dbname=postgres Object grants for database PSI... Object grants for database dbname=postgres Setting up the configuration Initializing......done DDC loaded from database (0 variables). Dbvisit Replicate version 2.9.02 Copyright (C) Dbvisit Software Limited. All rights reserved. DDC file /home/oracle/ora2pg/config/ora2pg-onetime.ddc loaded. MINE: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists) APPLY: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists) dbvrep> #clear the no-DDC-DB-available warning dbvrep> process clear previous warnings dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR EXIT Variable ON_ERROR set to EXIT for process *. dbvrep> dbvrep> # Configuring default processes dbvrep> choose process MINE Process type MINE set to: MINE. dbvrep> choose process APPLY Process type APPLY set to: APPLY. dbvrep> PROCESS SWITCH_REDOLOG Redo log switch requested. dbvrep> PROCESS SETUP MINE DROP DICTIONARY 0 dictionary objects dropped. dbvrep> PROCESS SETUP MINE CREATE DICTIONARY dbvrep> PROCESS SETUP MINE LOAD DICTIONARY Oldest active transaction SCN: 2054212 (no active transaction) Supplemental logging on database set. dbvrep> PROCESS SETUP APPLY DROP DICTIONARY 0 dictionary objects dropped. dbvrep> PROCESS SETUP APPLY CREATE DICTIONARY dbvrep> PROCESS SETUP APPLY LOAD DICTIONARY dbvrep> PROCESS SETUP PAIR MINE AND APPLY Applier SCN set (start=2054228, current=2054228). dbvrep> SET APPLY.INSTANTIATE_SCN NOW Variable INSTANTIATE_SCN set to NOW for process APPLY. dbvrep> SET MINE._PREPARE_SUPLOG_TYPE PK Variable _PREPARE_SUPLOG_TYPE set to PK for process MINE. dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables created by Compression Advisor Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables created by Compression Advisor Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.SCHEDULER$_% #Ignore tables created by Oracle scheduler (also used by schema/full expdp/impdp) Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.CMP1$% #Ignore tables created by Compression Advisor since 11.2.0.4 Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.CMP2$% #Ignore tables created by Compression Advisor since 11.2.0.4 Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.CMP3$% #Ignore tables created by Compression Advisor since 11.2.0.4 Exclude rule created. dbvrep> EXCLUDE CREATE TABLE %.CMP4$% #Ignore tables created by Compression Advisor since 11.2.0.4 Exclude rule created. dbvrep> memory_set IGNORE_APPLY_DDL_DIFFERENCES Yes Variable IGNORE_APPLY_DDL_DIFFERENCES set to YES for process *. dbvrep> SET PREPARE_SCHEMA_EXCEPTIONS none Variable PREPARE_SCHEMA_EXCEPTIONS set to none for process *. dbvrep> PROCESS SUPPLEMENTAL LOGGING SCHEMA "PSI" ENABLE PRIMARY KEY dbvrep> PROCESS SWITCH_REDOLOG Redo log switch requested. dbvrep> PROCESS WAIT_SCN_FLIP Waited 1 seconds until scn_to_timestamp changed. dbvrep> #single-scn instantiation: lock all tables and schemas dbvrep> PROCESS LOCK SCHEMAS "PSI" Locking all schemas. ...locked 2 of 2 tables from PSI schema. Lock done. dbvrep> #single-scn instantiation: unlock all tables and schemas, but keep the SCN dbvrep> PROCESS LOCK RELEASE LOCKS Engine locks released. dbvrep> dbvrep> #prepare the tables (we use OFFLINE as neither MINE nor APPLY is running; with OFFLINE we won't wait on network timeout) dbvrep> PREPARE OFFLINE SCHEMA "PSI" Table PSI.EMPLOYE instantiated at SCN 2056800 Table PSI.OFFICE instantiated at SCN 2056800 dbvrep> dbvrep> #single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE statements) dbvrep> PROCESS LOCK CLEAR SCN dbvrep> PROCESS SWITCH_REDOLOG Redo log switch requested. dbvrep> #prepare script for instantiation dbvrep> PROCESS PREPARE_DP WRITE DDL_FILE FILE /home/oracle/ora2pg/APPLY.sql USERID SYSTEM/manager@PSI File /home/oracle/ora2pg/APPLY.sql has been written successfully. Created DDL script /home/oracle/ora2pg/APPLY.sql. dbvrep> create ddcdb from ddcfile DDC loaded into database (430 variables). dbvrep> load ddcdb DDC loaded from database (430 variables). dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR SKIP Variable ON_ERROR set to SKIP for process *. OK-0: Completed successfully. WARN-1850: No DDC DB available, dictionary table does not exist. These steps are required after the ora2pg-all.sh script runs: 1) Create the necessary directory(ies) on the servers: cloud13c: /home/oracle/ora2pg pg1: /home/oracle/ora2pg 2) Copy the DDC files to the server(s) where the processes will run: pg1: /home/oracle/ora2pg/ora2pg-APPLY.ddc cloud13c: /home/oracle/ora2pg/ora2pg-MINE.ddc Ensure that the parameter TNS_ADMIN (in the ddc file) is pointing to the correct TNS_ADMIN path on each of the servers. 3) Review that path to dbvrep executable is correct in the run scripts: /home/oracle/ora2pg/ora2pg-run-cloud13c.sh /home/oracle/ora2pg/ora2pg-run-pg1.sh 4) Copy the run script to the server(s) where the processes will run: cloud13c: /home/oracle/ora2pg/ora2pg-run-cloud13c.sh pg1: /home/oracle/ora2pg/ora2pg-run-pg1.sh 5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes. 6) Make sure the data on apply are in sync as of time when setup was run. Scripts for Data Pump/export/DDL were created as requested: Create referenced database links (if any) before running the scripts. /home/oracle/ora2pg/APPLY.sql 7) Start the replication processes on all servers: cloud13c: /home/oracle/ora2pg/ora2pg-run-cloud13c.sh pg1: /home/oracle/ora2pg/ora2pg-run-pg1.sh 8) Start the console to monitor the progress: /home/oracle/ora2pg/start-console.sh
As explained you have to copy two files on the postgres server : /home/oracle/ora2pg/ora2pg-APPLY.ddc and /home/oracle/ora2pg/ora2pg-run-pg1.sh
As I choosed the option ddl_only, we have to first create the tables on the postgres server. In order to do this we can use the APPLY:sql file from the Oracle server.
The next step consist in running the MINE process on the Oracle server:
oracle@localhost:/home/oracle/ora2pg/ [PSI] . ora2pg-run-cloud13c.sh Initializing......done DDC loaded from database (430 variables). Dbvisit Replicate version 2.9.02 Copyright (C) Dbvisit Software Limited. All rights reserved. DDC file /home/oracle/ora2pg/ora2pg-MINE.ddc loaded. Starting process MINE...started
And we launch the APPLy process on the postgres server:
postgres@pg_essentials_p1:/home/oracle/ora2pg/ [PG1] . ora2pg-run-pg1.sh Initializing......done DDC loaded from database (431 variables). Dbvisit Replicate version 2.9.02 Copyright (C) Dbvisit Software Limited. All rights reserved. DDC file /home/oracle/ora2pg/ora2pg-APPLY.ddc loaded. Starting process APPLY...Created directory /home/oracle/ora2pg/ddc_backup Created directory /home/oracle/ora2pg/log/ Created directory /home/oracle/ora2pg/log/trace/ Created directory /home/oracle/ora2pg/apply started
Initially I had two tables in my PSI oracle database belonging to the psi schema: EMPLOYE and OFFICE. I used the APPLY.sql script to create the tables in the postgres environment.
To visualize the activity we run start_console.sh on the Oracle server:
oracle@localhost:/home/oracle/ora2pg/ [PSI] . start-console.sh Initializing......done DDC loaded from database (431 variables). Dbvisit Replicate version 2.9.02 Copyright (C) Dbvisit Software Limited. All rights reserved. | Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days MINE is running. Currently at plog 120 and SCN 2060066 (11/07/2017 15:27:57). APPLY is running. Currently at plog 120 and SCN 2060021 (11/07/2017 15:27:45). Progress of replication ora2pg:MINE->APPLY: total/this execution ------------------------------------------------------------------------------------------------- PSI.EMPLOYE/psi.employe: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:07/11/2017 15:20:06/OK PSI.OFFICE/psi.office: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:07/11/2017 15:21:36/OK ------------------------------------------------------------------------------------------------- 2 tables listed.
And we can validate that each insert in the employe or office table is replicated on the postgres server:
From the postgres database;
(postgres@[local]:5432) [postgres] > select * from psi.employe; name | salary -------+-------- Larry | 10000 Bill | 2000 (2 rows)
From the Oracle server:
SQL> insert into employe values ('John', 50000);
1 row created.
SQL> commit;
Commit complete.
The console is giving us correct informations:
/ Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days MINE is running. Currently at plog 120 and SCN 2075526 (11/07/2017 16:44:17). APPLY is running. Currently at plog 120 and SCN 2075494 (11/07/2017 16:44:08). Progress of replication ora2pg:MINE->APPLY: total/this execution ------------------------------------------------------------------------------------------------- PSI.EMPLOYE/psi.employe: 100% Mine:3/3 Unrecov:0/0 Applied:3/3 Conflicts:0/0 Last:07/11/2017 16:18:41/OK PSI.OFFICE/psi.office: 100% Mine:3/3 Unrecov:0/0 Applied:3/3 Conflicts:0/0 Last:07/11/2017 15:37:02/OK ------------------------------------------------------------------------------------------------- 2 tables listed. And the result is apllied on the postgres database:
(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.
From the oracle server:
SQL> create table salary (name varchar2(10));
Table created.
SQL> insert into salary values ('Larry');
1 row created.
SQL> commit;
Commit complete.
The dbvist console displays correct informations:
\ Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days MINE is running. Currently at plog 135 and SCN 2246259 (11/27/2017 14:44:24). APPLY is running. Currently at plog 135 and SCN 2246237 (11/27/2017 14:44:18). Progress of replication replic:MINE->APPLY: total/this execution -------------------------------------------------------------------------------------- REP.SALARY: 100% Mine:1/1 Unrecov:0/0 Applied:1/1 Conflicts:0/0 Last:27/11/2017 14:01:25/OK --------------------------------------------------------------------------------------------- 1 tables listed.
From the postgres server:
(postgres@[local]:5432) [postgres] > select * from rep.salary; name ------- Larry (1 row)
The plog files generated in the postgres server contains the strings we need:
The plot files are generated on the postgres server in the directory /home/oracle/replic/apply
-bash-4.2$ ls 122.plog.gz 124.plog.gz 126.plog 128.plog.gz 130.plog.gz 132.plog.gz 134.plog 123.plog.gz 125.plog.gz 127.plog 129.plog.gz 131.plog.gz 133.plog.gz 135.plog -bash-4.2$ strings 135.plog | grep -l larry -bash-4.2$ strings 135.plog | grep -i larry Larry -bash-4.2$ strings 135.plog | grep -i salary SALARY create table salary (name varchar2(10)) SALARY SALARY
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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)