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

Gernerate 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.

闪回查询可以让使用者查询到以往某个时间点上的表中的内容,这对于某些场合是非常有用处的,甚至是救命的功能。比如如果不小心误删除了表中的数据,就可以通过闪回查询查到删除时间点之前的数据,这样可以直接将这些数据再恢复回来。

比如这样一个让人惋惜的场景(delete的SQL请千万不要在生产环境中运行)。

现在你有这么一张表,记录了账户姓名和账户金额。

MogDB=# select * from accounts;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
D   |     99
(4 rows)

你本来想执行一条SQL,去删除金额等于99元的账户信息。正常执行的话,应该删除1条记录。为了演示效果,先用select来代替delete。

MogDB=#  select * from accounts where amount=99;
name | amount
------+--------
D   |     99
(1 row)

但是在键盘上减号“-”和等号“=”紧挨着,你的手指太胖了,误按到了减号,所以现在你给数据库发出去的命令是这样的。

delete from accounts where amount-99;

为了演示,还是用select来代替delete。

#  select * from accounts where amount-99;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
(3 rows)

恐怖的事情发生了,除了金额真的等于99的那条数据没有返回,其它所有的数据都返回了。也就意味着,如果这是上面的那条delete命令,你删除了表中所有金额不等于99的账户。

好消息是,在MogDB 3.0中,已经修改了对减号这种危险语法的校验,现在执行同样的SQL,将会报错。

MogDB=# delete from accounts where amount-99;
ERROR: argument of WHERE must be type boolean, not type integer
LINE 1: delete from accounts where amount-99;
                                  ^

但是在社区版的openGauss3.0或者MySQL或者MariaDB中,这样的危险语法仍然是可以正常执行的。

gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:28:23 commit 0 last mr  release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# select version();
                                        version
--------------------------------------------------------------------------------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:28:23 commit 0 last mr release
(1 row)

omm=# create table accounts (name varchar2, amount int);
CREATE TABLE
omm=# insert into accounts values ('A',100),('B',100),('C',100),('D',99);
INSERT 0 4
omm=# delete from accounts where amount-99;
DELETE 3
omm=# select * from accounts;
name | amount
------+--------
D   |     99
(1 row)

不管是什么误操作,如果真的发生了,那么在MogDB 3.0中还有闪回查询可以用。

闪回查询及相关实现在MogDB 3.0中发生了一些变化。

  1. 只对存储引擎为ustore的表起作用,默认的astore已经不再支持闪回查询。
  2. 需要先手工设置undo_retention_time。该参数表示回滚段中旧版本数据的保留时间,等同于允许闪回查询的时间跨度。该参数默认是0,任何闪回查询都会遇到restore point not found报错。

创建一张ustore的accounts表。

MogDB=# create table accounts (name varchar2, amount int) with (storage_type=ustore);
CREATE TABLE

修改undo_retention_time参数,该参数单位是秒,86400表示是24小时。该参数允许最大值2147483647,相当于大约68年。当然你不会设置这么大,因为这会导致回滚段中保留过多旧数据。

MogDB=# alter system set undo_retention_time=86400;
ALTER SYSTEM SET

插入一些测试数据,就用前面提到的账户和金额。

MogDB=#  insert into accounts values ('A',100),('B',100),('C',100),('D',99);
INSERT 0 4

现在,因为某种误操作,你删除了所有金额不等于99的账户记录。

MogDB=# delete from accounts where amount<>99;
DELETE 3

MogDB=# select * from accounts;
name | amount
------+--------
D   |     99
(1 row)

当你发现这个失误的时候,可能是在1分钟以后,也可能是在1小时以后,这没关系,但是不要超过24小时(因为undo_retention_time设置了24小时),否则就找不回来了。

检查当前的时间戳,然后预估你执行误操作时候的时间戳,比如用当前时间戳减去5分钟,为了简便演示,我在发出删除命令之前,检查了系统的时间戳。

MogDB=# select sysdate;
      sysdate
---------------------
2022-09-02 13:19:24
(1 row)

发起闪回查询。

MogDB=# select * from accounts timecapsule TIMESTAMP to_timestamp('2022-09-02 13:19:24','YYYY-MM-DD HH24:MI:SS');
name | amount
------+--------
A   |   100
B   |   100
C   |   100
D   |     99
(4 rows)

真正的恢复步骤,可以是按照删除命令的条件,利用闪回查询将所有被误删除的记录创建为一张临时表,然后再从临时表中将所有记录插入回原表中。

MogDB=# create table tmp_accounts as select * from accounts timecapsule TIMESTAMP to_timestamp('2022-09-02 13:19:24','YYYY-MM-DD HH24:MI:SS') where amount<>99;
INSERT 0 3

MogDB=# select * from tmp_accounts;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
(3 rows)

MogDB=# insert into accounts select * from tmp_accounts;
INSERT 0 3

MogDB=# select * from accounts;
name | amount
------+--------
D   |     99
A   |   100
B   |   100
C   |   100
(4 rows)

Have a look

shuzhi
shuzhi-dark
bg-3

shuzhi

shuzhi (数枝)是一个可以运行在Gnome里的Gnome Shell Extension,它的主页在这里

安装shuzhi非常简单,如果你已经启用了Gnome Shell Extention,只需要在Firefox里访问shuzhi页面,点一下ON就可以了。

shuzhi可以根据设定,在指定的时间里自动刷新桌面壁纸,每一张桌面壁纸都是动态生成的,包含了根据国风配色的底色,还有从古诗词一言API上实时获取的一句中国古代诗词。通过可设定的底色图案以及文字的竖排横排样式,和文字字体,构成了美轮美奂的一张张国风壁纸。

image-20220726112508929

Fonts

字体非常重要,我使用的是方正清刻本悦宋,这是一款可以免费下载的字体。方正字库中还有不少可以免费下载的国风字体,比如方正黄庭坚行书方正赵孟頫楷书