一、环境概述
本次是进行Oracle到MogDB测试迁移，具体生产迁移，还需考虑更多步骤细节，请查看MogDB官方文档。
| 操作系统版本 | 内核版本 | 数据库类型 | 数据库版本 | 字符集 | 数据库端口 | |
|---|---|---|---|---|---|---|
| 源端 | CentOS release 6.8 (Final) | 2.6.32-642.el6.x86_64 | 单机 | Oracle 11.2.0.4 | GBK | 1521 |
| 目标端 | CentOS Linux release 7.9.2009 (Core) | 3.10.0-1160.80.1.el7.x86_64 | 单机 | MogDB 3.0.3 | GBK | 26000 |
本次迁移，选择采用MTK工具进行迁移，MTK版本 v2.7.2。
MogDB迁移配置文件mtk.json里配置的源端Oracle用户必须能访问
DBA_*/ALL_*视图和查询迁移表权限，该用户必须拥有以下权限。
- connect
- select any dictionary
- select any table
- select_catalog_role
MTK 从 2.3.0 以后访问
DBA_*视图,不在查询ALL_*视图.
二、目标端配置
MogDB目标端，为防止目标端迁移时产生大量归档日志，迁移前建议关闭目标端数据库归档。
– 可通过如下方式查询MogDB是否开启归档
[omm@opensource-db ~]$ cat /db/mogdb/data/postgresql.conf |grep archive_mode #archive_mode = off # allows archiving to be done 或者 [omm@opensource-db ~]$ gsql -d postgres -p 26000 -r gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:41 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. MogDB=# show archive_mode; archive_mode -------------- off (1 row) -- 若archive_mode=on 表示开启了数据库归档
2.1 安装Oracle客户端
-- 本次选择使用rpm包来安装Oracle客户端
-- 目标端下载如下四个 oracle-client rpm包
[root@opensource-db soft]# ls -lrt oracle-instantclient11.2-*.rpm
-rw-r--r-- 1 omm omm 828333 Dec 2 14:31 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 omm omm 59865008 Dec 2 14:36 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 omm omm 610222 Dec 2 14:37 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 1564169 Dec 2 17:39 oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
-- root 用户安装rpm包
[root@opensource-db soft]# rpm -ivh oracle*.rpm
-- rpm 包 安装目录在 /usr/lib/oracle 目录下
-- 配置环境变量
-- omm 用户
[omm@opensource-db ~]$ cat >>/home/omm/.bash_profile<<EOF
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/db/mogdb/app/lib
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
EOF
source /home/omm/.bash_profile
-- 此时执行sqlplus会报错
[omm@opensource-db ~]$ sqlplus
-bash: sqlplus: command not found
# 问题原因，因为安装sqlplus客户端后，会链接生成/bin/sqlplus64
[omm@opensource-db ~]$ sqlplus64
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 2 14:51:34 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name:
[omm@opensource-db ~]$ which sqlplus64
/bin/sqlplus64
[omm@opensource-db ~]$ ls -lrt /bin/sqlplus64
lrwxrwxrwx 1 root root 41 Dec 2 14:40 /bin/sqlplus64 -> /usr/lib/oracle/11.2/client64/bin/sqlplus
-- 解决办法，使用root用户执行如下操作
[root@opensource-db ~]# ln -s /usr/lib/oracle/11.2/client64/bin/sqlplus /bin/sqlplus
[root@opensource-db ~]# su - omm
Last login: Fri Dec 2 14:50:48 CST 2022 on pts/2
[omm@opensource-db ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 2 14:52:50 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name:
-- 此时可通过sqlplus使用tnsnames.ora字符串连接远程源端Oracle数据库。
-- 关于linux安装客户端并配置tnsnames.ora信息可参照我墨天轮文章 https://www.modb.pro/db/572611[Linux 服务器安装Oracle instance client 客户端 rpm 包并远程连接数据库故障与解决办法【亲测有效】]
2.2 下载解压MTK
-- 本次选择使用MTK最新版本2.7.2,选择
[omm@opensource-db ~]$ wget https://cdn-mogdb.enmotech.com/mtk/v2.7.2/mtk_2.7.2_linux_amd64.tar.gz
--2022-12-02 13:29:19-- https://cdn-mogdb.enmotech.com/mtk/v2.7.2/mtk_2.7.2_linux_amd64.tar.gz
Resolving cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)... 122.193.130.103
Connecting to cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)|122.193.130.103|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 16425899 (16M) [regular file]
Saving to: ‘mtk_2.7.2_linux_amd64.tar.gz’
100%[=====================================================================================================================================================================================================================================>] 16,425,899 58.1MB/s in 0.3s
2022-12-02 13:29:20 (58.1 MB/s) - ‘mtk_2.7.2_linux_amd64.tar.gz’ saved [16425899/16425899]
[omm@opensource-db ~]$ ll
total 16044
-rw------- 1 omm omm 16425899 Nov 29 13:19 mtk_2.7.2_linux_amd64.tar.gz
[omm@opensource-db ~]$ tar -zxvf mtk_2.7.2_linux_amd64.tar.gz
mtk_2.7.2_linux_amd64/CHANGELOG.md
mtk_2.7.2_linux_amd64/README.md
mtk_2.7.2_linux_amd64/example/db22mogdb.json
mtk_2.7.2_linux_amd64/example/db22mogdb.yaml
mtk_2.7.2_linux_amd64/example/db22mysql.json
mtk_2.7.2_linux_amd64/example/db22mysql.yaml
mtk_2.7.2_linux_amd64/example/informix2mogdb.json
mtk_2.7.2_linux_amd64/example/informix2mogdb.yaml
mtk_2.7.2_linux_amd64/example/mysql2mogdb.json
mtk_2.7.2_linux_amd64/example/mysql2mogdb.yaml
mtk_2.7.2_linux_amd64/example/oracle2mogdb.json
mtk_2.7.2_linux_amd64/example/oracle2mogdb.yaml
mtk_2.7.2_linux_amd64/example/pg2mogdb.json
mtk_2.7.2_linux_amd64/example/pg2mogdb.yaml
mtk_2.7.2_linux_amd64/example/sqlserver2mogdb.json
mtk_2.7.2_linux_amd64/example/sqlserver2mogdb.yaml
mtk_2.7.2_linux_amd64/mtk
[omm@opensource-db ~]$ cd mtk_2.7.2_linux_amd64
-- 填写邮箱申请license
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk license gen
License File Not Found (default license.json)
The License code is invalid, start applying
? Email: xxxx@xxxx.com -- 填写邮箱信息
>> please enter a valid email address
Error: the email is invalid
Usage:
mtk license gen [flags]
Flags:
-e, --email string Specify the user email
-s, --server string Specify the license server.
-h, --help help for gen
Global Flags:
--batchSize int Specify the batch size to be used for bulk/copy insert.
Valid values are 1-50000, default batch size is 1000
for MySQL,Oracle,openGauss,PostgreSQL.
--bufferSize int Specify the Buffer size in MB, to be used inserting or querying.
Valid value is from 1 to 1024, default Buffer size is 8 MB
for postgres,openGauss,mysql.
--caseSensitive int Object case parameters in SQL statements.
1 - lower case
2 - upper case
3 - Keep it the same as the source database.
--channelCacheNum int Specify channelCacheNum.
Valid values are 1-50000, default batch size is 10000
-c, --config string Set mtk config file. Support json,yaml. [env MTK_CONFIG] (default "mtk.json")
--cpBufferSize int Specify the Copy Buffer size in MB, to be used in the Copy Command.
Valid value is from 1 to 1024, default Copy Buffer Size is 8 MB
for PostgreSQL,openGauss.
-d, --debug Set the debug mode.
Not necessary for the normal usage. [env MTK_DEBUG]
--disableCollStatistics disable Collect table statistics.
--disableFKCons Disable table foreign key sync
--disableIgnoreCase Disable ignoring case queries
--disableSelectPart Disable the select by partition
--disableTableDataComp disable table select count compare.
--enableSyncCompTabPro Enable Synchronize table compressed properties
--fetchSize int Specify fetch size in terms of number of rows should be fetched in result set at a time.
Valid values are 1-50000, default fetch size is 1000
for Oracle. (default 1000)
--file export to file
--fileType string Indicates the type of a file when data is to be exported to a file.
support csv,sql
--noTerminalReport The terminal does not print an overview of the migration report
-p, --parallel int Specify the parallelism.
the degree of parallelism is now only useful when migrating table data and parallelism when creating an index. (default 1)
--path string Indicates the directory of a file to which data is to be exported.
Default value is config.target.parameter.path. If not configured, the system default value ./data
Command Value > Config Value > MTK Default Value
--preRun preRun check.
-r, --reportFile string Set mtk report file or dir. If it is a file, use the file directory.
Default value is ./report
report directory format [./report/reportYYYYMMDDHHMISS] ./report/report20210101121314
--schemas string the Migrate schema,Separated by commas.(schema1,schema2,db1)
--seqLastNumAddNum int The last value of the sequence is increased
--tableSkip stringArray Define table split. Can be specified multiple times.
format schema.tableName
--tableSkip MTK.TABLE01
--tableSkip MTK.TABLE02
--tableSplit stringArray Define table split. Can be specified multiple times.
format schema.tableName:where:where:where
--tableSplit 'MTK.TABLE01: "ID">100 AND ID<=200: ID>200 AND ID<=300:ID>300'
--tableSplit "MTK.TABLE02: \"ID\">100 AND ID<=200: ID>200 AND ID<=300:ID>300 AND COL1='1'"
--tables string the Migrate tables,Separated by commas.(tab1,schema1.tab1)
2.3 MTK应用license
-- 根据邮箱里收到的license文件上传到MogDB数据库服务器MTK安装目录下，应用license
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ll
total 46480
-rw------- 1 omm omm 60343 Nov 29 12:56 CHANGELOG.md
drwx------ 2 omm omm 4096 Dec 2 13:29 example
-rw-r--r-- 1 omm omm 3304 Dec 2 13:51 license.json
-rwx------ 1 omm omm 47517936 Nov 29 12:54 mtk
-rw------- 1 omm omm 2052 Nov 28 11:00 README.md
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk license gen
Using license file: /home/omm/mtk_2.7.2_linux_amd64/license.json
Name : xxxx@xxxx.com
Expiry: 2023-01-01 13:48:45.572292597 +0800 CST
Version: 2.7.2
The License code is valid, no need to apply
-- 查看MTK版本信息
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -v
Using license file: /home/omm/mtk_2.7.2_linux_amd64/license.json
Name : xxxx@xxx.com
Expiry: 2023-01-01 13:48:45.572292597 +0800 CST
Version: 2.7.2
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.7.2
Git Commit hash: 2f0398f2
Git Commit Date: 2022-11-29T04:54:39Z
Git Tag : v2.7.2
Build timestamp: 20221129045645
======================================================
-- 查看MTK版本时可能遇到的问题及解决办法
[omm@opensource-db mtk_2.7.2_linux_amd64]$ cd mtk_2.7.2_linux_amd64
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -version
./mtk: /lib64/libc.so.6: version `GLIBC_2.14' not found (required by ./mtk)
解决办法：链接：https://blog.csdn.net/l714417743/article/details/110208415
wget http://ftp.gnu.org/gnu/glibc/glibc-2.17.tar.gz
tar -xvf glibc-2.17.tar.gz
cd glibc-2.17
mkdir build; cd build
../configure --prefix=/usr --disable-profile --enable-add-ons --with-headers=/usr/include --with-binutils=/usr/bin
make -j 8
make install
[omm@opensource-db mtk_2.7.2_linux_amd64]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
[root@local-2f-abiz-dev-test-db ~]# strings /lib64/libc.so.6 |grep GLIBC_
GLIBC_2.2.5
GLIBC_2.2.6
GLIBC_2.3
GLIBC_2.3.2
GLIBC_2.3.3
GLIBC_2.3.4
GLIBC_2.4
GLIBC_2.5
GLIBC_2.6
GLIBC_2.7
GLIBC_2.8
GLIBC_2.9
GLIBC_2.10
GLIBC_2.11
GLIBC_2.12
GLIBC_2.13
GLIBC_2.14
GLIBC_2.15
GLIBC_2.16
GLIBC_2.17
GLIBC_PRIVATE
2.4 目标端创建数据库及用户
[omm@opensource-db ~]$ gsql -d postgres -p 26000 -r
gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:41 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
-- 创建数据库表空间
MogDB=# CREATE TABLESPACE tbs_xxxx location '/db/mogdb/tablespace/tbs_xxxx';
CREATE TABLESPACE
-- 本次选择为数据库指定默认表空间
MogDB=# create database dbxxxx WITH TABLESPACE = tbs_xxxx;
CREATE DATABASE
MogDB=# create user moguser with password "mogdb_123";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=# GRANT CREATE ON TABLESPACE tbs_xxxx TO moguser;
GRANT
MogDB=# grant all on schema public to moguser;
GRANT
MogDB=# alter user moguser sysadmin;
ALTER ROLE
MogDB=# grant create on database dbxxxx to moguser;
GRANT
2.5 初始化项目
[omm@opensource-db ~]$ cd /home/omm/mtk_2.7.2_linux_amd64
[omm@opensource-db ~]$ ./mtk init-project -s oracle -t mogdb -n ora2mg
[omm@opensource-db mtk_2.7.2_linux_amd64]$ tree -f ora2mg/
ora2mg
├── ora2mg/config
│ └── ora2mg/config/mtk.json
├── ora2mg/data
├── ora2mg/report
└── ora2mg/schema
4 directories, 1 file
2.6 编辑并检查配置文件
2.6.1 编辑配置文件
[omm@opensource-db mtk_2.7.2_linux_amd64]$ vim ora2mg/config/mtk.json
-- 编辑后的文件如下
{
"source": {
"type": "oracle",
"connect": {
"version": "",
"host": "xxx.xxx.xxx.xxx",
"user": "system",
"port": 1521,
"password": "oracle",
"dbName": "dbxxxx",
"dsn": "",
"charset": "ZHS16GBK",
"clientCharset": ""
},
"parameter": {
"charAppendEmptyString": false
}
},
"target": {
"type": "mogdb",
"connect": {
"version": "3.0.3",
"host": "192.168.73.21",
"user": "moguser",
"port": 26000,
"password": "mogdb_123",
"dbName": "db_xxx",
"dsn": "",
"charset": "UTF8",
"datCompatibility": "A",
"clientCharset": ""
},
"parameter": {
"parallelInsert": 1,
"dropSchema": false,
"dropExistingObject": false,
"truncTable": false,
"colKeyWords": {
"STREAM": 1,
"TID": 1
},
"objKeyWords": {
"STREAM": 1,
"TID": 1
},
"caseSensitive": 0,
"quoteMark": false,
"path": "ora2mogdb/data",
"schemaPath": "ora2mogdb/schema",
"dataPath": "ora2mogdb/data",
"errDataPath": "",
"fileType": "csv",
"fileSize": "10240MiB",
"csvHeader": false,
"csvNullValue": "",
"csvFieldDelimiter": ",",
"csvOptionallyEnclosed": "\"",
"excludeSysTable": [],
"remapSchema": {
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX"
},
"remapTable": {},
"remapTablespace": {},
"enableSyncTabTbsPro": false,
"enableSyncCompTabPro": false,
"timeFormat": "HH:MI:SS",
"dateFormat": "YYYY-MM-DD",
"dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
"timeStampFormat": "YYYY-MM-DD HH24:MI:SSXFFFFFF",
"timeStampZoneFormat": "YYYY-MM-DD HH24:MI:SSXFFFFFF TZR",
"noSupportPartTabToNormalTab": true,
"ignoreDB2PartInclusive": false,
"igNotSupportIntervalPart": false,
"igErrorData": false,
"enableBatchCommit": false,
"ignoreTabPartition": false,
"autoAddMaxvaluePart": false,
"autoAddMySQLAutoIncr": false,
"autoAddMySQLAutoIncrTabList": [
"TABLE_1",
"SCHEMA1.TABLE_1"
],
"ignoreNotSupportDefault": false,
"replaceZeroDate": "",
"virtualColToNormalCol": false,
"virtualColConv": {
"LEFT(HOST,POSSTR(HOST,':')-1)": "SPLIT_PART(HOST,':',1)",
"TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'"
},
"convertPackageMethod": "package",
"convertOracleIntegerToNumeric": false,
"enableOgBlobClob": false,
"enableConvertSrid": false,
"defaultSrid": "4326",
"seqLastNumAddNum": 0,
"skipColumnType": {
"COL_TYPE_1": 1,
"COL_TYPE_2": 2
},
"skipColumnName": {
"SCHEMA1.TAB_01.COL1": 1,
"SCHEMA1.TAB_01.COL2": 2
},
"templateSeqName": "{{.TabName}}_{{.ColName}}_SEQ",
"charAppendEmptyString": false,
"tableOptions": {},
"indexOptions": {}
}
},
"object": {
"tables": [],
"schemas": [
"ABIZ_READ",
"CRUCIS",
"ABIZDBA",
"ABIZ",
"ABIZBUY"
],
"excludeTable": {
"SCHEMA1": [
"TABLE_SKIP1",
"TABLE_DUTY_LOG*",
"^TABLE_DUTY_LOG*",
"^TABLE_DUTY_LOG.*$"
],
"SCHEMA2": [
"TABLE_SKIP1"
]
},
"tableSplit": {
"SCHEMA1": {
"TAB_1": [
"ID < 10000",
"ID < 90000 AND ID >=10000",
"ID >= 90000"
]
},
"SCHEMA2": {
"TAB_1": [
"ID < 10000",
"ID < 90000 AND ID >=10000",
"ID >= 90000"
]
}
}
},
"limit": {
"parallel": 4,
"fetchSize": 1000,
"batchSize": 1000,
"bufferSize": 8,
"cpBufferSize": 8,
"oracleSelectParallel": 2,
"channelCacheNum": 100000,
"limit": 0
},
"dataOnly": false,
"schemaOnly": false,
"disableTableDataComp": false,
"disableCollStatistics": false,
"reportFile": "ora2mogdb/report",
"debug": false,
"disableIgnoreCase": false,
"disableSelectPart": false,
"disableFKCons": false,
"disableSyncIdxAfterData": false
}
2.6.2 检查配置文件
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk check-config -c ora2mg/config/mtk.json --preRun
use config : ora2mogdb/config/mtk.json
There is no error in the configuration file
# 出现如上信息提示配置文件配置正确
# 注意，如果只迁移schema，在配置object时，不能将table和schema同时写，否则检查时会报如下错误：
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk check-config -c ora2mogdb/config/mtk.json --preRun
use config : ora2mogdb/config/mtk.json
Error : schema and tables cannot exist together
schema and tables cannot exist together
2.6.3 MTK迁移配置文件含义
部分MTK迁移配置文件名称及含义描述如下。
| 名称 | 定义类型 | 含义描述 |
|---|---|---|
| source | Option | 源数据库配置信息. |
| target | Option | 目标数据库配置信息. |
| limit | Limit | 迁移并发配置. |
| object | Object | 迁移对象配置. |
| dataOnly | bool | 是否只迁移数据. |
| schemaOnly | bool | 是否只迁移数据结构. |
| disableTableDataComp | bool | 当数据迁移完成后，MTK会统计两边的行数进行对比. |
| disableCollStatistics | bool | 当数据迁移完成后，MTK会收集目标端统计信息. |
| reportFile | string | 迁移报告目录. |
| debug | bool | 是否开启日志debug模式. |
| preRun | bool | 预运行. |
| test | bool | 试迁移. |
| disableIgnoreCase | bool | 关闭在源库查询忽略大小写功能. |
| disableSelectPart | bool | 关闭分区查询功能. |
| disableFKCons | bool | 禁用外键同步. |
| disableSyncIdxAfterData | bool | 在一次性迁移模式下禁用在同步完单个表数据后立即创建这个表的索引. 改为全部完全部迁移表数据后,在进行同步索引 |
| disablePrintMigDataProgress | bool | 关闭打印迁移表数据进度功能.打印进度影响迁移性能 |
三、执行迁移
迁移可以选择只迁移schema、迁移整个库，或者迁移数据库某些表对象。
3.1 执行迁移
-- 整库迁移
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json
-- 只迁移schema
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json --schemaOnly
-- 迁移数据
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json --file --dataOnly
-- 还可以根据需要对部分对象进行迁移
-- 可参加官方文档：https://docs.mogdb.io/zh/mtk/v2.0/mtk-usage (分步迁移)
3.2 执行过程
-- 执行迁移时，部分执行过程如下
time="2022-12-06 10:25:01.384503" level=info msg="CollTableStatistics XXXX.BUY_XXXX140926" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384619" level=info msg="CollTableStatistics XXXX.BUY_XXXX141008" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384329" level=info msg="CollTableStatistics XXXX.TOAD_XXXX" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384121" level=info msg="CollTableStatistics XXXX.XXXX_TMP" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384343" level=info msg="CollTableStatistics XXXX.BUY_XXXX" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384987" level=info msg="Processing CollStatistics finish" taskID=1599948444963704832 function=doTask line=431 file="mtk/pkg/mtk/mtk.go"
time="2022-12-06 10:25:01.38506" level=info msg="Finish Begin" line=280 file="mtk/pkg/mtk/mtk.go" taskID=1599948444963704832 function=Stop
time="2022-12-06 10:25:01.541302" level=info msg="Finish end" line=286 file="mtk/pkg/mtk/mtk.go" taskID=1599948444963704832 function=Stop
3.3 执行结果
-- 迁移结束后，会生成如下一些执行结果
*********************************
Database Migration Toolkit Report
*********************************
--------------
Report Summary
--------------
+-------------------------------+--------------------+
|Source Database Type | oracle |
|Source Database Version | 11.2.0.4.0 |
|Source Database Charset | ZHS16GBK |
|Source Database DatCompat | |
|Target Database Type | file |
|Target Database Version | 3.0.3 |
|Target Database Charset | ZHS16GBK |
|Target Database DatCompat | A |
|StartTime | 2022-12-06 10:06:49 |
|EndTime | 2022-12-06 10:25:01 |
|Time | 18 m 11 s |
|Status | finish |
|MTK Version | v2.7.2_2f0398f2 |
+-------------------------------+--------------------+
-----------------------
Table Data Summary
-----------------------
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+--------+----------+
| SrcName | TgtName | StartTime | EndTime | Time| Status | Select | Insert |Ignore | Size |
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+--------+----------+
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:11|2022-12-06 10:07:11|2 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:12:07|2022-12-06 10:12:07|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:23|2022-12-06 10:07:23|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:12|2022-12-06 10:07:12|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:13|2022-12-06 10:07:13|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:11|2022-12-06 10:07:11|3 ms |succeed | 0| 0| 0| 0|
-----------------------
ObjectName Type Summary
-----------------------
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|Schema |2022-12-06 10:06:49|2022-12-06 10:06:49|finish |5 |5 |0 |0 |0 |5 ms ｜
|Sequence |2022-12-06 10:06:49|2022-12-06 10:06:50|finish |457 |457 |0 |0 |0 |252 ms ｜
|ObjectType |2022-12-06 10:06:50|2022-12-06 10:06:51|finish |5 |5 |0 |0 |0 |1 s 126 ms ｜
|Queue |2022-12-06 10:06:51|2022-12-06 10:06:51|finish |0 |0 |0 |0 |0 |8 ms ｜
|Table |2022-12-06 10:06:51|2022-12-06 10:06:55|finish |639 |624 |15 |0 |0 |4 s 13 ms ｜
|TableData |2022-12-06 10:06:55|2022-12-06 10:24:51|finish |639 |630 |9 |0 |0 |17 m 56 s 397 ms｜
|Index |2022-12-06 10:24:51|2022-12-06 10:24:51|finish |1011 |1011 |0 |0 |0 |296 ms ｜
|Constraint |2022-12-06 10:24:51|2022-12-06 10:24:52|finish |434 |434 |0 |0 |0 |215 ms ｜
|DBLink |2022-12-06 10:24:52|2022-12-06 10:24:52|finish |12 |0 |0 |12 |0 |10 ms ｜
|Synonym |2022-12-06 10:24:52|2022-12-06 10:24:52|finish |61 |61 |0 |0 |0 |31 ms ｜
|View |2022-12-06 10:24:52|2022-12-06 10:24:53|finish |21 |19 |2 |0 |0 |1 s 219 ms ｜
|MaterializedView |2022-12-06 10:24:53|2022-12-06 10:24:53|finish |31 |31 |0 |0 |0 |464 ms ｜
|Function |2022-12-06 10:24:53|2022-12-06 10:24:54|finish |51 |43 |7 |1 |0 |1 s 10 ms ｜
|Procedure |2022-12-06 10:24:54|2022-12-06 10:24:58|finish |34 |24 |10 |0 |0 |3 s 871 ms ｜
|Package |2022-12-06 10:24:58|2022-12-06 10:24:58|finish |0 |0 |0 |0 |0 |11 ms ｜
|Trigger |2022-12-06 10:24:58|2022-12-06 10:25:01|finish |440 |440 |0 |0 |0 |2 s 445 ms ｜
|AlterSequence |2022-12-06 10:25:01|2022-12-06 10:25:01|finish |457 |457 |0 |0 |0 |104 ms ｜
|CollStatistics |2022-12-06 10:25:01|2022-12-06 10:25:01|finish |639 |630 |9 |0 |0 |137 ms ｜
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
[2022-12-06 10:25:06.446002] INFO reportDir : ora2mg/report/report_20221206102506 function=PrintReport line=364 file=mtk/cmd/mtk/cmd/mtk.go
[2022-12-06 10:25:06.704871] INFO the text report : ora2mg/report/report_20221206102506.txt function=HTMLReportToFIle line=134 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.794547] INFO the warring report : ora2mg/report/report_20221206102506.warring function=HTMLReportToFIle line=141 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.813135] INFO the error report : ora2mg/report/report_20221206102506.err function=HTMLReportToFIle line=148 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.827778] INFO the excel report : ora2mg/report/report_20221206102506.xlsx function=HTMLReportToFIle line=155 file=mtk/pkg/report/report.go
-- report 各文件及目录含义
ora2mg/report/report_20221206102506 html 报告
ora2mg/report/report_20221206102506.txt 文本报告
ora2mg/report/report_20221206102506.warring 只包含警告信息的文本报告
ora2mg/report/report_20221206102506.err 只包含错误信息的文本报告
ora2mg/report/report_20221206102506.xlsx 包含导出过程的xlsx文件信息报告
ora2mg/report/report_20221206100649.log 导出过程日志记录
3.4 查看报告
将report 目录report_xxx目录打包下载到本地电脑，然后解压后打开index.html文件，可查看导出的相关报告信息。
还可以根据需要采用如下方式导出指定报告信息：
-- 导出整库迁移报告
./mtk -c ora2mg/config/mtk.json --file
-- 导出仅含schema的信息报告
./mtk -c ora2mg/config/mtk.json --file --schemaOnly
-- 导出仅含数据的信息报告
./mtk -c ora2mg/config/mtk.json --file --dataOnly
四、验证测试
数据迁移仅仅是第一步，在迁移过程中也出现了一些问题，比如部分表未迁移成功，如何跨字符集迁移，迁移后的SQL语句执行结果及效率，这方面还需要花大量时间去解决。
本次测试使用MTK，第一感觉这个迁移工具做的真的挺不错，对源端无侵入性，无需源端部署相关软件。
一、环境概述
本次是进行Oracle到MogDB测试迁移，具体生产迁移，还需考虑更多步骤细节，请查看MogDB官方文档。
| 操作系统版本 | 内核版本 | 数据库类型 | 数据库版本 | 字符集 | 数据库端口 | |
|---|---|---|---|---|---|---|
| 源端 | CentOS release 6.8 (Final) | 2.6.32-642.el6.x86_64 | 单机 | Oracle 11.2.0.4 | GBK | 1521 |
| 目标端 | CentOS Linux release 7.9.2009 (Core) | 3.10.0-1160.80.1.el7.x86_64 | 单机 | MogDB 3.0.3 | GBK | 26000 |
本次迁移，选择采用MTK工具进行迁移，MTK版本 v2.7.2。
MogDB迁移配置文件mtk.json里配置的源端Oracle用户必须能访问
DBA_*/ALL_*视图和查询迁移表权限，该用户必须拥有以下权限。
- connect
- select any dictionary
- select any table
- select_catalog_role
MTK 从 2.3.0 以后访问
DBA_*视图,不在查询ALL_*视图.
二、目标端配置
MogDB目标端，为防止目标端迁移时产生大量归档日志，迁移前建议关闭目标端数据库归档。
– 可通过如下方式查询MogDB是否开启归档
[omm@opensource-db ~]$ cat /db/mogdb/data/postgresql.conf |grep archive_mode #archive_mode = off # allows archiving to be done 或者 [omm@opensource-db ~]$ gsql -d postgres -p 26000 -r gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:41 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. MogDB=# show archive_mode; archive_mode -------------- off (1 row) -- 若archive_mode=on 表示开启了数据库归档
2.1 安装Oracle客户端
-- 本次选择使用rpm包来安装Oracle客户端
-- 目标端下载如下四个 oracle-client rpm包
[root@opensource-db soft]# ls -lrt oracle-instantclient11.2-*.rpm
-rw-r--r-- 1 omm omm 828333 Dec 2 14:31 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 omm omm 59865008 Dec 2 14:36 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 omm omm 610222 Dec 2 14:37 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 1564169 Dec 2 17:39 oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
-- root 用户安装rpm包
[root@opensource-db soft]# rpm -ivh oracle*.rpm
-- rpm 包 安装目录在 /usr/lib/oracle 目录下
-- 配置环境变量
-- omm 用户
[omm@opensource-db ~]$ cat >>/home/omm/.bash_profile<<EOF
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/db/mogdb/app/lib
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
EOF
source /home/omm/.bash_profile
-- 此时执行sqlplus会报错
[omm@opensource-db ~]$ sqlplus
-bash: sqlplus: command not found
# 问题原因，因为安装sqlplus客户端后，会链接生成/bin/sqlplus64
[omm@opensource-db ~]$ sqlplus64
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 2 14:51:34 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name:
[omm@opensource-db ~]$ which sqlplus64
/bin/sqlplus64
[omm@opensource-db ~]$ ls -lrt /bin/sqlplus64
lrwxrwxrwx 1 root root 41 Dec 2 14:40 /bin/sqlplus64 -> /usr/lib/oracle/11.2/client64/bin/sqlplus
-- 解决办法，使用root用户执行如下操作
[root@opensource-db ~]# ln -s /usr/lib/oracle/11.2/client64/bin/sqlplus /bin/sqlplus
[root@opensource-db ~]# su - omm
Last login: Fri Dec 2 14:50:48 CST 2022 on pts/2
[omm@opensource-db ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 2 14:52:50 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name:
-- 此时可通过sqlplus使用tnsnames.ora字符串连接远程源端Oracle数据库。
-- 关于linux安装客户端并配置tnsnames.ora信息可参照我墨天轮文章 https://www.modb.pro/db/572611[Linux 服务器安装Oracle instance client 客户端 rpm 包并远程连接数据库故障与解决办法【亲测有效】]
2.2 下载解压MTK
-- 本次选择使用MTK最新版本2.7.2,选择
[omm@opensource-db ~]$ wget https://cdn-mogdb.enmotech.com/mtk/v2.7.2/mtk_2.7.2_linux_amd64.tar.gz
--2022-12-02 13:29:19-- https://cdn-mogdb.enmotech.com/mtk/v2.7.2/mtk_2.7.2_linux_amd64.tar.gz
Resolving cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)... 122.193.130.103
Connecting to cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)|122.193.130.103|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 16425899 (16M) [regular file]
Saving to: ‘mtk_2.7.2_linux_amd64.tar.gz’
100%[=====================================================================================================================================================================================================================================>] 16,425,899 58.1MB/s in 0.3s
2022-12-02 13:29:20 (58.1 MB/s) - ‘mtk_2.7.2_linux_amd64.tar.gz’ saved [16425899/16425899]
[omm@opensource-db ~]$ ll
total 16044
-rw------- 1 omm omm 16425899 Nov 29 13:19 mtk_2.7.2_linux_amd64.tar.gz
[omm@opensource-db ~]$ tar -zxvf mtk_2.7.2_linux_amd64.tar.gz
mtk_2.7.2_linux_amd64/CHANGELOG.md
mtk_2.7.2_linux_amd64/README.md
mtk_2.7.2_linux_amd64/example/db22mogdb.json
mtk_2.7.2_linux_amd64/example/db22mogdb.yaml
mtk_2.7.2_linux_amd64/example/db22mysql.json
mtk_2.7.2_linux_amd64/example/db22mysql.yaml
mtk_2.7.2_linux_amd64/example/informix2mogdb.json
mtk_2.7.2_linux_amd64/example/informix2mogdb.yaml
mtk_2.7.2_linux_amd64/example/mysql2mogdb.json
mtk_2.7.2_linux_amd64/example/mysql2mogdb.yaml
mtk_2.7.2_linux_amd64/example/oracle2mogdb.json
mtk_2.7.2_linux_amd64/example/oracle2mogdb.yaml
mtk_2.7.2_linux_amd64/example/pg2mogdb.json
mtk_2.7.2_linux_amd64/example/pg2mogdb.yaml
mtk_2.7.2_linux_amd64/example/sqlserver2mogdb.json
mtk_2.7.2_linux_amd64/example/sqlserver2mogdb.yaml
mtk_2.7.2_linux_amd64/mtk
[omm@opensource-db ~]$ cd mtk_2.7.2_linux_amd64
-- 填写邮箱申请license
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk license gen
License File Not Found (default license.json)
The License code is invalid, start applying
? Email: xxxx@xxxx.com -- 填写邮箱信息
>> please enter a valid email address
Error: the email is invalid
Usage:
mtk license gen [flags]
Flags:
-e, --email string Specify the user email
-s, --server string Specify the license server.
-h, --help help for gen
Global Flags:
--batchSize int Specify the batch size to be used for bulk/copy insert.
Valid values are 1-50000, default batch size is 1000
for MySQL,Oracle,openGauss,PostgreSQL.
--bufferSize int Specify the Buffer size in MB, to be used inserting or querying.
Valid value is from 1 to 1024, default Buffer size is 8 MB
for postgres,openGauss,mysql.
--caseSensitive int Object case parameters in SQL statements.
1 - lower case
2 - upper case
3 - Keep it the same as the source database.
--channelCacheNum int Specify channelCacheNum.
Valid values are 1-50000, default batch size is 10000
-c, --config string Set mtk config file. Support json,yaml. [env MTK_CONFIG] (default "mtk.json")
--cpBufferSize int Specify the Copy Buffer size in MB, to be used in the Copy Command.
Valid value is from 1 to 1024, default Copy Buffer Size is 8 MB
for PostgreSQL,openGauss.
-d, --debug Set the debug mode.
Not necessary for the normal usage. [env MTK_DEBUG]
--disableCollStatistics disable Collect table statistics.
--disableFKCons Disable table foreign key sync
--disableIgnoreCase Disable ignoring case queries
--disableSelectPart Disable the select by partition
--disableTableDataComp disable table select count compare.
--enableSyncCompTabPro Enable Synchronize table compressed properties
--fetchSize int Specify fetch size in terms of number of rows should be fetched in result set at a time.
Valid values are 1-50000, default fetch size is 1000
for Oracle. (default 1000)
--file export to file
--fileType string Indicates the type of a file when data is to be exported to a file.
support csv,sql
--noTerminalReport The terminal does not print an overview of the migration report
-p, --parallel int Specify the parallelism.
the degree of parallelism is now only useful when migrating table data and parallelism when creating an index. (default 1)
--path string Indicates the directory of a file to which data is to be exported.
Default value is config.target.parameter.path. If not configured, the system default value ./data
Command Value > Config Value > MTK Default Value
--preRun preRun check.
-r, --reportFile string Set mtk report file or dir. If it is a file, use the file directory.
Default value is ./report
report directory format [./report/reportYYYYMMDDHHMISS] ./report/report20210101121314
--schemas string the Migrate schema,Separated by commas.(schema1,schema2,db1)
--seqLastNumAddNum int The last value of the sequence is increased
--tableSkip stringArray Define table split. Can be specified multiple times.
format schema.tableName
--tableSkip MTK.TABLE01
--tableSkip MTK.TABLE02
--tableSplit stringArray Define table split. Can be specified multiple times.
format schema.tableName:where:where:where
--tableSplit 'MTK.TABLE01: "ID">100 AND ID<=200: ID>200 AND ID<=300:ID>300'
--tableSplit "MTK.TABLE02: \"ID\">100 AND ID<=200: ID>200 AND ID<=300:ID>300 AND COL1='1'"
--tables string the Migrate tables,Separated by commas.(tab1,schema1.tab1)
2.3 MTK应用license
-- 根据邮箱里收到的license文件上传到MogDB数据库服务器MTK安装目录下，应用license
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ll
total 46480
-rw------- 1 omm omm 60343 Nov 29 12:56 CHANGELOG.md
drwx------ 2 omm omm 4096 Dec 2 13:29 example
-rw-r--r-- 1 omm omm 3304 Dec 2 13:51 license.json
-rwx------ 1 omm omm 47517936 Nov 29 12:54 mtk
-rw------- 1 omm omm 2052 Nov 28 11:00 README.md
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk license gen
Using license file: /home/omm/mtk_2.7.2_linux_amd64/license.json
Name : xxxx@xxxx.com
Expiry: 2023-01-01 13:48:45.572292597 +0800 CST
Version: 2.7.2
The License code is valid, no need to apply
-- 查看MTK版本信息
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -v
Using license file: /home/omm/mtk_2.7.2_linux_amd64/license.json
Name : xxxx@xxx.com
Expiry: 2023-01-01 13:48:45.572292597 +0800 CST
Version: 2.7.2
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.7.2
Git Commit hash: 2f0398f2
Git Commit Date: 2022-11-29T04:54:39Z
Git Tag : v2.7.2
Build timestamp: 20221129045645
======================================================
-- 查看MTK版本时可能遇到的问题及解决办法
[omm@opensource-db mtk_2.7.2_linux_amd64]$ cd mtk_2.7.2_linux_amd64
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -version
./mtk: /lib64/libc.so.6: version `GLIBC_2.14' not found (required by ./mtk)
解决办法：链接：https://blog.csdn.net/l714417743/article/details/110208415
wget http://ftp.gnu.org/gnu/glibc/glibc-2.17.tar.gz
tar -xvf glibc-2.17.tar.gz
cd glibc-2.17
mkdir build; cd build
../configure --prefix=/usr --disable-profile --enable-add-ons --with-headers=/usr/include --with-binutils=/usr/bin
make -j 8
make install
[omm@opensource-db mtk_2.7.2_linux_amd64]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
[root@local-2f-abiz-dev-test-db ~]# strings /lib64/libc.so.6 |grep GLIBC_
GLIBC_2.2.5
GLIBC_2.2.6
GLIBC_2.3
GLIBC_2.3.2
GLIBC_2.3.3
GLIBC_2.3.4
GLIBC_2.4
GLIBC_2.5
GLIBC_2.6
GLIBC_2.7
GLIBC_2.8
GLIBC_2.9
GLIBC_2.10
GLIBC_2.11
GLIBC_2.12
GLIBC_2.13
GLIBC_2.14
GLIBC_2.15
GLIBC_2.16
GLIBC_2.17
GLIBC_PRIVATE
2.4 目标端创建数据库及用户
[omm@opensource-db ~]$ gsql -d postgres -p 26000 -r
gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:41 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
-- 创建数据库表空间
MogDB=# CREATE TABLESPACE tbs_xxxx location '/db/mogdb/tablespace/tbs_xxxx';
CREATE TABLESPACE
-- 本次选择为数据库指定默认表空间
MogDB=# create database dbxxxx WITH TABLESPACE = tbs_xxxx;
CREATE DATABASE
MogDB=# create user moguser with password "mogdb_123";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=# GRANT CREATE ON TABLESPACE tbs_xxxx TO moguser;
GRANT
MogDB=# grant all on schema public to moguser;
GRANT
MogDB=# alter user moguser sysadmin;
ALTER ROLE
MogDB=# grant create on database dbxxxx to moguser;
GRANT
2.5 初始化项目
[omm@opensource-db ~]$ cd /home/omm/mtk_2.7.2_linux_amd64
[omm@opensource-db ~]$ ./mtk init-project -s oracle -t mogdb -n ora2mg
[omm@opensource-db mtk_2.7.2_linux_amd64]$ tree -f ora2mg/
ora2mg
├── ora2mg/config
│ └── ora2mg/config/mtk.json
├── ora2mg/data
├── ora2mg/report
└── ora2mg/schema
4 directories, 1 file
2.6 编辑并检查配置文件
2.6.1 编辑配置文件
[omm@opensource-db mtk_2.7.2_linux_amd64]$ vim ora2mg/config/mtk.json
-- 编辑后的文件如下
{
"source": {
"type": "oracle",
"connect": {
"version": "",
"host": "xxx.xxx.xxx.xxx",
"user": "system",
"port": 1521,
"password": "oracle",
"dbName": "dbxxxx",
"dsn": "",
"charset": "ZHS16GBK",
"clientCharset": ""
},
"parameter": {
"charAppendEmptyString": false
}
},
"target": {
"type": "mogdb",
"connect": {
"version": "3.0.3",
"host": "192.168.73.21",
"user": "moguser",
"port": 26000,
"password": "mogdb_123",
"dbName": "db_xxx",
"dsn": "",
"charset": "UTF8",
"datCompatibility": "A",
"clientCharset": ""
},
"parameter": {
"parallelInsert": 1,
"dropSchema": false,
"dropExistingObject": false,
"truncTable": false,
"colKeyWords": {
"STREAM": 1,
"TID": 1
},
"objKeyWords": {
"STREAM": 1,
"TID": 1
},
"caseSensitive": 0,
"quoteMark": false,
"path": "ora2mogdb/data",
"schemaPath": "ora2mogdb/schema",
"dataPath": "ora2mogdb/data",
"errDataPath": "",
"fileType": "csv",
"fileSize": "10240MiB",
"csvHeader": false,
"csvNullValue": "",
"csvFieldDelimiter": ",",
"csvOptionallyEnclosed": "\"",
"excludeSysTable": [],
"remapSchema": {
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX",
"XXXX": "XXXX"
},
"remapTable": {},
"remapTablespace": {},
"enableSyncTabTbsPro": false,
"enableSyncCompTabPro": false,
"timeFormat": "HH:MI:SS",
"dateFormat": "YYYY-MM-DD",
"dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
"timeStampFormat": "YYYY-MM-DD HH24:MI:SSXFFFFFF",
"timeStampZoneFormat": "YYYY-MM-DD HH24:MI:SSXFFFFFF TZR",
"noSupportPartTabToNormalTab": true,
"ignoreDB2PartInclusive": false,
"igNotSupportIntervalPart": false,
"igErrorData": false,
"enableBatchCommit": false,
"ignoreTabPartition": false,
"autoAddMaxvaluePart": false,
"autoAddMySQLAutoIncr": false,
"autoAddMySQLAutoIncrTabList": [
"TABLE_1",
"SCHEMA1.TABLE_1"
],
"ignoreNotSupportDefault": false,
"replaceZeroDate": "",
"virtualColToNormalCol": false,
"virtualColConv": {
"LEFT(HOST,POSSTR(HOST,':')-1)": "SPLIT_PART(HOST,':',1)",
"TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'"
},
"convertPackageMethod": "package",
"convertOracleIntegerToNumeric": false,
"enableOgBlobClob": false,
"enableConvertSrid": false,
"defaultSrid": "4326",
"seqLastNumAddNum": 0,
"skipColumnType": {
"COL_TYPE_1": 1,
"COL_TYPE_2": 2
},
"skipColumnName": {
"SCHEMA1.TAB_01.COL1": 1,
"SCHEMA1.TAB_01.COL2": 2
},
"templateSeqName": "{{.TabName}}_{{.ColName}}_SEQ",
"charAppendEmptyString": false,
"tableOptions": {},
"indexOptions": {}
}
},
"object": {
"tables": [],
"schemas": [
"ABIZ_READ",
"CRUCIS",
"ABIZDBA",
"ABIZ",
"ABIZBUY"
],
"excludeTable": {
"SCHEMA1": [
"TABLE_SKIP1",
"TABLE_DUTY_LOG*",
"^TABLE_DUTY_LOG*",
"^TABLE_DUTY_LOG.*$"
],
"SCHEMA2": [
"TABLE_SKIP1"
]
},
"tableSplit": {
"SCHEMA1": {
"TAB_1": [
"ID < 10000",
"ID < 90000 AND ID >=10000",
"ID >= 90000"
]
},
"SCHEMA2": {
"TAB_1": [
"ID < 10000",
"ID < 90000 AND ID >=10000",
"ID >= 90000"
]
}
}
},
"limit": {
"parallel": 4,
"fetchSize": 1000,
"batchSize": 1000,
"bufferSize": 8,
"cpBufferSize": 8,
"oracleSelectParallel": 2,
"channelCacheNum": 100000,
"limit": 0
},
"dataOnly": false,
"schemaOnly": false,
"disableTableDataComp": false,
"disableCollStatistics": false,
"reportFile": "ora2mogdb/report",
"debug": false,
"disableIgnoreCase": false,
"disableSelectPart": false,
"disableFKCons": false,
"disableSyncIdxAfterData": false
}
2.6.2 检查配置文件
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk check-config -c ora2mg/config/mtk.json --preRun
use config : ora2mogdb/config/mtk.json
There is no error in the configuration file
# 出现如上信息提示配置文件配置正确
# 注意，如果只迁移schema，在配置object时，不能将table和schema同时写，否则检查时会报如下错误：
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk check-config -c ora2mogdb/config/mtk.json --preRun
use config : ora2mogdb/config/mtk.json
Error : schema and tables cannot exist together
schema and tables cannot exist together
2.6.3 MTK迁移配置文件含义
部分MTK迁移配置文件名称及含义描述如下。
| 名称 | 定义类型 | 含义描述 |
|---|---|---|
| source | Option | 源数据库配置信息. |
| target | Option | 目标数据库配置信息. |
| limit | Limit | 迁移并发配置. |
| object | Object | 迁移对象配置. |
| dataOnly | bool | 是否只迁移数据. |
| schemaOnly | bool | 是否只迁移数据结构. |
| disableTableDataComp | bool | 当数据迁移完成后，MTK会统计两边的行数进行对比. |
| disableCollStatistics | bool | 当数据迁移完成后，MTK会收集目标端统计信息. |
| reportFile | string | 迁移报告目录. |
| debug | bool | 是否开启日志debug模式. |
| preRun | bool | 预运行. |
| test | bool | 试迁移. |
| disableIgnoreCase | bool | 关闭在源库查询忽略大小写功能. |
| disableSelectPart | bool | 关闭分区查询功能. |
| disableFKCons | bool | 禁用外键同步. |
| disableSyncIdxAfterData | bool | 在一次性迁移模式下禁用在同步完单个表数据后立即创建这个表的索引. 改为全部完全部迁移表数据后,在进行同步索引 |
| disablePrintMigDataProgress | bool | 关闭打印迁移表数据进度功能.打印进度影响迁移性能 |
三、执行迁移
迁移可以选择只迁移schema、迁移整个库，或者迁移数据库某些表对象。
3.1 执行迁移
-- 整库迁移
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json
-- 只迁移schema
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json --schemaOnly
-- 迁移数据
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ./mtk -c ora2mg/config/mtk.json --file --dataOnly
-- 还可以根据需要对部分对象进行迁移
-- 可参加官方文档：https://docs.mogdb.io/zh/mtk/v2.0/mtk-usage (分步迁移)
3.2 执行过程
-- 执行迁移时，部分执行过程如下
time="2022-12-06 10:25:01.384503" level=info msg="CollTableStatistics XXXX.BUY_XXXX140926" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384619" level=info msg="CollTableStatistics XXXX.BUY_XXXX141008" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384329" level=info msg="CollTableStatistics XXXX.TOAD_XXXX" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384121" level=info msg="CollTableStatistics XXXX.XXXX_TMP" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384343" level=info msg="CollTableStatistics XXXX.BUY_XXXX" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1599948444963704832
time="2022-12-06 10:25:01.384987" level=info msg="Processing CollStatistics finish" taskID=1599948444963704832 function=doTask line=431 file="mtk/pkg/mtk/mtk.go"
time="2022-12-06 10:25:01.38506" level=info msg="Finish Begin" line=280 file="mtk/pkg/mtk/mtk.go" taskID=1599948444963704832 function=Stop
time="2022-12-06 10:25:01.541302" level=info msg="Finish end" line=286 file="mtk/pkg/mtk/mtk.go" taskID=1599948444963704832 function=Stop
3.3 执行结果
-- 迁移结束后，会生成如下一些执行结果
*********************************
Database Migration Toolkit Report
*********************************
--------------
Report Summary
--------------
+-------------------------------+--------------------+
|Source Database Type | oracle |
|Source Database Version | 11.2.0.4.0 |
|Source Database Charset | ZHS16GBK |
|Source Database DatCompat | |
|Target Database Type | file |
|Target Database Version | 3.0.3 |
|Target Database Charset | ZHS16GBK |
|Target Database DatCompat | A |
|StartTime | 2022-12-06 10:06:49 |
|EndTime | 2022-12-06 10:25:01 |
|Time | 18 m 11 s |
|Status | finish |
|MTK Version | v2.7.2_2f0398f2 |
+-------------------------------+--------------------+
-----------------------
Table Data Summary
-----------------------
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+--------+----------+
| SrcName | TgtName | StartTime | EndTime | Time| Status | Select | Insert |Ignore | Size |
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+--------+----------+
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:11|2022-12-06 10:07:11|2 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:12:07|2022-12-06 10:12:07|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:23|2022-12-06 10:07:23|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:12|2022-12-06 10:07:12|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:13|2022-12-06 10:07:13|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2022-12-06 10:07:11|2022-12-06 10:07:11|3 ms |succeed | 0| 0| 0| 0|
-----------------------
ObjectName Type Summary
-----------------------
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|Schema |2022-12-06 10:06:49|2022-12-06 10:06:49|finish |5 |5 |0 |0 |0 |5 ms ｜
|Sequence |2022-12-06 10:06:49|2022-12-06 10:06:50|finish |457 |457 |0 |0 |0 |252 ms ｜
|ObjectType |2022-12-06 10:06:50|2022-12-06 10:06:51|finish |5 |5 |0 |0 |0 |1 s 126 ms ｜
|Queue |2022-12-06 10:06:51|2022-12-06 10:06:51|finish |0 |0 |0 |0 |0 |8 ms ｜
|Table |2022-12-06 10:06:51|2022-12-06 10:06:55|finish |639 |624 |15 |0 |0 |4 s 13 ms ｜
|TableData |2022-12-06 10:06:55|2022-12-06 10:24:51|finish |639 |630 |9 |0 |0 |17 m 56 s 397 ms｜
|Index |2022-12-06 10:24:51|2022-12-06 10:24:51|finish |1011 |1011 |0 |0 |0 |296 ms ｜
|Constraint |2022-12-06 10:24:51|2022-12-06 10:24:52|finish |434 |434 |0 |0 |0 |215 ms ｜
|DBLink |2022-12-06 10:24:52|2022-12-06 10:24:52|finish |12 |0 |0 |12 |0 |10 ms ｜
|Synonym |2022-12-06 10:24:52|2022-12-06 10:24:52|finish |61 |61 |0 |0 |0 |31 ms ｜
|View |2022-12-06 10:24:52|2022-12-06 10:24:53|finish |21 |19 |2 |0 |0 |1 s 219 ms ｜
|MaterializedView |2022-12-06 10:24:53|2022-12-06 10:24:53|finish |31 |31 |0 |0 |0 |464 ms ｜
|Function |2022-12-06 10:24:53|2022-12-06 10:24:54|finish |51 |43 |7 |1 |0 |1 s 10 ms ｜
|Procedure |2022-12-06 10:24:54|2022-12-06 10:24:58|finish |34 |24 |10 |0 |0 |3 s 871 ms ｜
|Package |2022-12-06 10:24:58|2022-12-06 10:24:58|finish |0 |0 |0 |0 |0 |11 ms ｜
|Trigger |2022-12-06 10:24:58|2022-12-06 10:25:01|finish |440 |440 |0 |0 |0 |2 s 445 ms ｜
|AlterSequence |2022-12-06 10:25:01|2022-12-06 10:25:01|finish |457 |457 |0 |0 |0 |104 ms ｜
|CollStatistics |2022-12-06 10:25:01|2022-12-06 10:25:01|finish |639 |630 |9 |0 |0 |137 ms ｜
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
[2022-12-06 10:25:06.446002] INFO reportDir : ora2mg/report/report_20221206102506 function=PrintReport line=364 file=mtk/cmd/mtk/cmd/mtk.go
[2022-12-06 10:25:06.704871] INFO the text report : ora2mg/report/report_20221206102506.txt function=HTMLReportToFIle line=134 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.794547] INFO the warring report : ora2mg/report/report_20221206102506.warring function=HTMLReportToFIle line=141 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.813135] INFO the error report : ora2mg/report/report_20221206102506.err function=HTMLReportToFIle line=148 file=mtk/pkg/report/report.go
[2022-12-06 10:25:07.827778] INFO the excel report : ora2mg/report/report_20221206102506.xlsx function=HTMLReportToFIle line=155 file=mtk/pkg/report/report.go
-- report 各文件及目录含义
ora2mg/report/report_20221206102506 html 报告
ora2mg/report/report_20221206102506.txt 文本报告
ora2mg/report/report_20221206102506.warring 只包含警告信息的文本报告
ora2mg/report/report_20221206102506.err 只包含错误信息的文本报告
ora2mg/report/report_20221206102506.xlsx 包含导出过程的xlsx文件信息报告
ora2mg/report/report_20221206100649.log 导出过程日志记录
3.4 查看报告
将report 目录report_xxx目录打包下载到本地电脑，然后解压后打开index.html文件，可查看导出的相关报告信息。
还可以根据需要采用如下方式导出指定报告信息：
-- 导出整库迁移报告
./mtk -c ora2mg/config/mtk.json --file
-- 导出仅含schema的信息报告
./mtk -c ora2mg/config/mtk.json --file --schemaOnly
-- 导出仅含数据的信息报告
./mtk -c ora2mg/config/mtk.json --file --dataOnly
四、验证测试
数据迁移仅仅是第一步，在迁移过程中也出现了一些问题，比如部分表未迁移成功，如何跨字符集迁移，迁移后的SQL语句执行结果及效率，这方面还需要花大量时间去解决。
本次测试使用MTK，第一感觉这个迁移工具做的真的挺不错，对源端无侵入性，无需源端部署相关软件。
欢迎大家关注我视频公众号和微信公众号
![[C++]哈希应用之位图布隆过滤器](https://img-blog.csdnimg.cn/direct/e3ff0dedf2ee4b4c89ba24e961db3cf4.gif)
