Using MTK to migrate Oracle sample schema to MogDB

Get the latest version MTK

wget https://cdn-mogdb.enmotech.com/mtk/v2.6.3/mtk_2.6.3_linux_amd64.tar.gz
tar -xvf mtk_2.6.3_linux_amd64.tar.gz

Generate MTK trial license online

The trial license lasts for 1 month, every mail address can only gernerate one license, except the mail address domain is “enmotech.com”, using “enmotech.com” mail can repeatly genarate license. So if the clients want to try MTK more after 1 month, should contact the sales or pre-sales from Enmotech, to ask for another 1 month license.

[kamus@altlinux10 mtk_2.6.3_linux_amd64]$ ./mtk license gen
License File Not Found (default license.json)
The License code is invalid, start applying
✔ Email: kamus@enmotech.com█
Start applying for email kamus@enmotech.com authorization.
Start parsing the interface to return data.
Successful application for authorization. Please check the mail and save it as license.json.

When get the mail, upload the attached license.json file to the MTK directory. Use mtk -v to check the license validation.

[kamus@altlinux10 mtk_2.6.3_linux_amd64]$ ./mtk -v
Using license file: /home/kamus/mogdb-tools/mtk_2.6.3_linux_amd64/license.json
Name: kamus@enmotech.com
Expiry: 2022-10-24 12:08:58.751194162 +0800 +0800
License key verified!
License checks OK!

MMMMMMMM               MMMMMMMMTTTTTTTTTTTTTTTTTTTTTTTKKKKKKKKK   KKKKKKK
M:::::::M             M:::::::MT:::::::::::::::::::::TK:::::::K   K:::::K
M::::::::M           M::::::::MT:::::::::::::::::::::TK:::::::K   K:::::K
M:::::::::M         M:::::::::MT:::::TT:::::::TT:::::TK:::::::K   K::::::K
M::::::::::M       M::::::::::MTTTTTT T:::::T TTTTTTKK::::::K K:::::KKK
M:::::::::::M     M:::::::::::M       T:::::T         K:::::K K:::::K
M:::::::M::::M   M::::M:::::::M       T:::::T         K::::::K:::::K
M::::::M M::::M M::::M M::::::M       T:::::T         K:::::::::::K
M::::::M M::::M::::M M::::::M       T:::::T         K:::::::::::K
M::::::M   M:::::::M   M::::::M       T:::::T         K::::::K:::::K
M::::::M   M:::::M   M::::::M       T:::::T         K:::::K K:::::K
M::::::M     MMMMM     M::::::M       T:::::T       KK::::::K K:::::KKK
M::::::M               M::::::M     TT:::::::TT     K:::::::K   K::::::K
M::::::M               M::::::M     T:::::::::T     K:::::::K   K:::::K
M::::::M               M::::::M     T:::::::::T     K:::::::K   K:::::K
MMMMMMMM               MMMMMMMM     TTTTTTTTTTT     KKKKKKKKK   KKKKKKK

Release version: v2.6.3
Git Commit hash: da0ed8ee
Git Commit Date: 2022-09-22T01:17:49Z
Git Tag       : v2.6.3
Build timestamp: 20220922011907

Install Oracle instant client

MTK needs Oracle instant client to migrate Oracle objects to MogDB, in this tutorial, we will download Oracle Instant Client for Linux x86-64 Basic Package. Unzip the downloaded file, set the proper LD_LIBRARY_PATH parameter.

export LD_LIBRARY_PATH=/home/kamus/instantclient_21_7:$LD_LIBRARY_PATH

We will migrate a sample schema “customer_orders” to MogDB in this tutorial. the db-sample-schemas scipts for Oracle can be downloaded from github.

image-20220924140451621

Initialize migration project

./mtk init-project -s oracle -t mogdb -n ora2mogdb

Modify MTK configuration file

Modify the example MTK confiuration file stored in project_name_dir/config directory, check MTK documentation for the details of every parameter. The essenssial config sections for MTK is source, target, object .

source section: is the connection defination for source database, MTK needs to query Oracle database dictionary to get DDL, so typically we should use DBA user, the default system user will be OK.

target section: is the connection defination for target database.

object section: for migrating all the objects in one schema, we just need to put schema name in schemas section.

The mtk.json config file will looks like:

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "119.3.182.31",
      "user": "system",
      "port": 15221,
      "password": "oracle",
      "dbName": "ORACLE21C",
      "charset": "",
      "clientCharset": ""
    }
  },
  "target": {
    "type": "mogdb",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "co",
      "port": 26000,
      "password": "Enmo@123",
      "dbName": "postgres",
      "charset": "",
      "clientCharset": ""
    }
  },
  "object": {
    "tables": [],
    "schemas": ["co"],
    "excludeTable": {
    },
    "tableSplit": {
    }
  },
  "dataOnly": false,
  "schemaOnly": false
}

We are planning to migrate all the objects in “CO” schema from Oracle database to the same user in MogDB, for testing purpose, we will not create a new database in MogDB, we create a new user “co” in default database postgres.

[omm@altlinux10 ~]$ gsql -d postgres -p 26000 -r
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:37:49 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# create user co identified by "Enmo@123";
CREATE ROLE

Start migration

Now, we can start migration.

./mtk -c ora2mogdb/config/mtk.json

Check migration report

Migration result report will be generated in project report directory, both in pure text format and HTML format, till now the HTML report is in Chinese, so I put the text format result into this tutorial.

-----------------------
ObjectName Type Summary
-----------------------

+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|Schema |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |1 |1 |0 |0 |0 |282 ms |
|Sequence |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |0 |0 |0 |0 |0 |210 ms |
|ObjectType |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |0 |0 |0 |0 |0 |356 ms |
|Queue |2022-09-24 15:12:36|2022-09-24 15:12:37|finish |0 |0 |0 |0 |0 |177 ms |
|Table |2022-09-24 15:12:37|2022-09-24 15:12:47|finish |7 |7 |0 |0 |0 |9 s 952 ms |
|TableData |2022-09-24 15:12:47|2022-09-24 15:12:53|finish |7 |7 |0 |0 |0 |6 s 743 ms |
|Index |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |7 |7 |0 |0 |0 |1 ms |
|Constraint |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |24 |23 |0 |1 |0 |51 ms |
|DBLink |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |0 |0 |0 |0 |0 |67 ms |
|View |2022-09-24 15:12:53|2022-09-24 15:12:54|finish |4 |2 |0 |2 |0 |723 ms |
|MaterializedView |2022-09-24 15:12:54|2022-09-24 15:12:54|finish |0 |0 |0 |0 |0 |138 ms |
|Function |2022-09-24 15:12:54|2022-09-24 15:12:54|finish |0 |0 |0 |0 |0 |113 ms |
|Procedure |2022-09-24 15:12:54|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |109 ms |
|Package |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |77 ms |
|Trigger |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |404 ms |
|Synonym |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |74 ms |
|TableDataCom |2022-09-24 15:12:55|2022-09-24 15:12:56|finish |7 |7 |0 |0 |0 |810 ms |
|AlterSequence |2022-09-24 15:12:56|2022-09-24 15:12:56|finish |0 |0 |0 |0 |0 |71 ms |
|CollStatistics |2022-09-24 15:12:56|2022-09-24 15:12:56|finish |7 |7 |0 |0 |0 |29 ms |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+

We can see all the tables and table data are successfully migrated to MogDB without any error, but for constraint, there is 1 failed, and for view, there are 2 failed.

The failed constraint is a JSON check constraint. MogDB dosn’t has this type of constraint.

image-20220924153609138

The failed views are about grouping_id function and json_table function which MogDB not implemented yet.

image-20220924154621375

Run the sample queries

/* 5 products with the highest revenue
With their corresponding order rank */
select p.product_name,
count(*) number_of_orders,
sum ( oi.quantity * oi.unit_price ) total_value,
rank () over (
order by count(*) desc
) order_count_rank
from products p
join order_items oi
on p.product_id = oi.product_id
group by p.product_name
order by sum ( oi.quantity * oi.unit_price ) desc
fetch first 5 rows only;
MogDB=> select p.product_name,
MogDB-> count(*) number_of_orders,
MogDB-> sum ( oi.quantity * oi.unit_price ) total_value,
MogDB-> rank () over (
MogDB(> order by sum ( oi.quantity * oi.unit_price ) desc
MogDB(> ) revenue_rank
MogDB-> from products p
MogDB-> join order_items oi
MogDB-> on p.product_id = oi.product_id
MogDB-> group by p.product_name
MogDB-> order by count(*) desc
MogDB-> fetch first 5 rows only;
product_name | number_of_orders | total_value | revenue_rank
-----------------------+------------------+-------------+--------------
Girl's Trousers (Red) | 148 | 15794.76 | 1
Boy's Hoodie (Grey) | 100 | 3754.08 | 35
Men's Pyjamas (Blue) | 100 | 3274.61 | 36
Men's Coat (Red) | 98 | 4230.30 | 31
Boy's Socks (White) | 98 | 3081.12 | 38
(5 rows)

Conclusion

Migrating tables/table data/indexes from Oracle to MogDB normally has no issue, but for views/procedures/functions/packages, we still have to made some modification to the source code.

Leave a Reply

Your email address will not be published. Required fields are marked *