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 applyingEmail: 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.
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.
The failed views are about grouping_id function and json_table function which MogDB not implemented yet.
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.