EDB Migration Toolkit (MTK) is a powerful command-line tool that offers granular control of the data migration process. EDB LiveCompare makes it easy to validate the consistency of a migrated data set. The article shows how to use MTK and LiveCompare on Oracle to BigAnimal Postgres migration journey.
Prerequisites
1. Installed Migration Toolkit:
./runMTK.sh -version
EnterpriseDB Migration Toolkit (Build 55.2.3)
2. Installed LiveCompare:
livecompare --version
EnterpriseDB LiveCompare 2.3.0
3. Installed the Oracle and EPAS JDBC drivers following the document.
4. Load sample data into Oracle table.
5. Provisioned BigAnimal EPAS 14 cluster.
Run MTK to migrate the data from Oracle to BigAnimal
1. Specifying connection properties, for example:
cat /usr/edb/migrationtoolkit/etc/toolkit.properties
SRC_DB_URL=jdbc:oracle:thin:@$ORA_HOST:1521/XEPDB1
SRC_DB_USER=smith
SRC_DB_PASSWORD=$PWD
TARGET_DB_URL=jdbc:edb://p-96fh23hcnd.0gww9tnhwis9gbln.biganimal.io:5432/edb_admin?sslmode=require
TARGET_DB_USER=edb_admin
TARGET_DB_PASSWORD=$PWD
2. Migrating data from Oracle to BigAnimal, for example:
Oracle sample table:
SQL> select * from city;
ID CITY
---------- --------------------------------------------------
1 London
2 Toronto
3 Shanghai
4 Paris
5 Sydney
The MTK command:
./runMTK.sh -tables CITY SMITH
The output has the following:
Migration process completed successfully.
Total Elapsed Migration Time (sec): 4.356
Migration logs have been saved to ~/.enterprisedb/migration-toolkit/logs
******************** Migration Summary ********************
Tables: 1 out of 1
Total objects: 1
Successful count: 1
Failed count: 0
Invalid count: 0
*************************************************************
Run LiveCompare to validate the data set
1. Set the `.ini` file to use in the project, for example(The password file `.pgpass` is used for Postgres):
cat project.ini
[General Settings]
...
[Oracle Connection]
technology = oracle
host = $ORA_HOST
port = 1521
service = XEPDB1
user = SMITH
password = $PWD
[Postgres Connection]
technology = postgresql
dsn = host=p-96fh23hcnd.0gww9tnhwis9gbln.biganimal.io port=5432 dbname=edb_admin user=edb_admin
start_query = SET search_path = smith
[Output Connection]
dsn = host=p-96fh23hcnd.0gww9tnhwis9gbln.biganimal.io port=5432 dbname=edb_admin user=edb_admin
[Table Filter]
tables = table_name = 'smith.city'
2. Run LiveCompare job:
livecompare project.ini
The outputs are:
LiveCompare session 1, compare mode, successfully finished.
Saved file ./lc_session_1/summary_20221129.out with the complete table summary.
You can also get the table summary by connecting to the output database and executing:
select * from livecompare.vw_table_summary where session_id = 1;
Elapsed time: 0:00:21.508853
Processed 5 rows in 1 tables from 2 connections using 10 workers.
Found 0 inconsistent rows.
`Found 0 inconsistent rows` means the two tables are same.
Next I add one more row into Oracle table:
SQL> insert into city values(6, 'Tokyo');
1 row created.
SQL> commit;
Commit complete.
Then run LiveCompare job again, the difference will be shown:
Outstanding differences:
+--------------+----------------+------------------+----------------------+-------------------+---------------------------+
| table_name | elapsed_time | num_total_rows | num_processed_rows | num_differences | max_num_ignored_columns |
|--------------+----------------+------------------+----------------------+-------------------+---------------------------|
| smith.city | 0:00:01.203512 | 6 | 6 | 1 | 0 |
+--------------+----------------+------------------+----------------------+-------------------+---------------------------+
Saved file ./lc_session_2/summary_20221129.out with the complete table summary.
You can also get the table summary by connecting to the output database and executing:
select * from livecompare.vw_table_summary where session_id = 2;
Elapsed time: 0:00:20.882567
Processed 6 rows in 1 tables from 2 connections using 10 workers.
Found 1 inconsistent rows in 1 tables.
Saved file ./lc_session_2/differences_20221129.out with the list of differences per table.
You can also get a list of differences per table with:
select * from livecompare.vw_open_differences where session_id = 2;
Too see more details on how LiveCompare determined the differences:
select * from livecompare.vw_consensus where session_id = 2;
DML script to be applied on the oracle connection was not generated, because the oracle connection is oracle.
Script ./lc_session_2/apply_on_the_postgres_20221129.sql was generated, which can be applied to the postgres connection and make it consistent with the majority of connections.
You can also get this script with:
select difference_fix_dml from livecompare.vw_difference_fix where session_id = 2 and connection_id = 'postgres' and difference_status = 'D' order by difference_fix_order;
More important, as shown above, a script was generated, which can be applied to the postgres connection and make it consistent with the majority of connections. At this case, the script is:
cat ./lc_session_2/apply_on_the_postgres_20221129.sql
BEGIN;
SET search_path = smith;
SET LOCAL ROLE edb_admin;
INSERT INTO smith.city (id, city) VALUES (6, 'Tokyo');
COMMIT;