回复
天龙八部之MTK 2.9.2 迁移Oracle 11g 至 openGauss 5.0.0操作指南
老老老JR老北
发布于 2023-9-12 11:09
浏览
0收藏
前言: 最近在进行一些去O的验证测试，之前测试过MTK迁移Oracle到MogDB，正好测试下Oracle到openGauss的迁移，于是做了如下测试，并整理记录成文，还望对此熟悉的朋友多多指正。
一、简介
MTK全称(Database Migration Toolkit)，可进行Oracle/DB2/MySQL/openGauss/SqlServer/Informix等数据库异构迁移，支持Oracle/MySQL/DB2->openGauss的存储过程，函数，触发器，包迁移。MTK支持多种数据类型，并可在迁移时对性能进行调整，还支持结构和数据的分离。
本次采用恩墨公司MTK迁移工具，进行Oracle 11g到openGauss 5.0数据迁移测试。
本次迁移采用MTK最新版 2.9.2(2023年7月30日)。
MTK安装比较灵活，其可以安装在源端和目标端甚至可单独部署在一台可远程访问源端及目标端的单独服务器上。
MTK迁移Oracle数据库时，需在部署MTK的服务器上安装Oracle客户端 Oracle Instant Client。
二、环境概述
本次进行Oracle 11g到openGauss 5.0.0 迁移测试，如进行生产迁移，还需考虑更多步骤细节，详情请查看恩墨MTK官方文档[https://docs.mogdb.io/zh/mtk/v2.0/overview]。
操作系统版本 | 内核版本 | 数据库类型 | 工具版本 | 字符集 | 数据库端口 | |
源端 | CentOS release 6.10 (Final) | 2.6.32-754.el6.x86_64 | 单机 | Oracle 11.2.0.4 | AMERICAN_AMERICA.AL32UTF8 | 1521 |
目标端 | CentOS Linux release 7.9.2009 (Core) | 3.10.0-1160.83.1.el7.x86_64 | 集群 | openGauss 5.0.0 | UTF8 | 26000 |
MTK服务器 | CentOS Linux release 7.9.2009 (Core) | 3.10.0-1160.83.1.el7.x86_64 | 单机 | 2.9.2 |
三、安装部署
3.1 安装Oracle客户端
MTK迁移Oracle数据库，需要在部署MTK的服务器上安装Oracle 11g客户端，本次选择采用rpm包来安装客户端。
登录https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html选择下载Version 11.2.0.4.0版本客户端。
-- 1) 安装oracle-client rpm包
-- MTK服务器下载如下四个 oracle-client rpm包
[root@opensource-db soft]# ll
total 61400
-rw-r--r-- 1 root root 59865008 Jul 28 16:46 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 610222 Jul 28 16:47 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 1564169 Jul 28 16:47 oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 828333 Jul 28 16:48 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
-- root 用户安装rpm包
[root@opensource-db soft]# rpm -ivh oracle*.rpm
-- oracle-instantclient11.2*.rpm 包安装目录在 /usr/lib/oracle 目录下
[root@opensource-db ~]# cd /usr/lib
[root@opensource-db lib]# tree oracle/
oracle/
└── 11.2
└── client64
├── bin
│ ├── adrci
│ ├── genezi
│ └── sqlplus
├── lib
│ ├── glogin.sql
│ ├── libclntsh.so -> libclntsh.so.11.1
│ ├── libclntsh.so.11.1
│ ├── libheteroxa11.so
│ ├── libnnz11.so
│ ├── libocci.so -> libocci.so.11.1
│ ├── libocci.so.11.1
│ ├── libociei.so
│ ├── libocijdbc11.so
│ ├── libsqlplusic.so
│ ├── libsqlplus.so
│ ├── ojdbc5.jar
│ ├── ojdbc6.jar
│ ├── orai18n.jar
│ ├── orai18n-mapping.jar
│ ├── ottclasses.zip
│ └── xstreams.jar
└── network
└── admin
└── tnsnames.ora
6 directories, 21 files
-- 2) 配置环境变量
-- omm 用户
[omm@opensource-db ~]$ cat >>/home/omm/.bash_profile<<EOF
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
EOF
[omm@opensource-db ~]$ source /home/omm/.bash_profile
-- 3) 创建sqlplus链接
-- 此时执行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 Sun Jul 30 23:22:22 2023
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 bin]# su - omm
Last login: Sun Jul 30 23:20:56 CST 2023 on pts/8
[omm@opensource-db ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 31 10:09:30 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name:
-- 此时可通过sqlplus使用tnsnames.ora字符串连接远程源端Oracle数据库。3.2 部署MTK
3.2.1 下载MTK
登录 https://docs.mogdb.io/zh/mtk/v2.0/release-2.9网站，根据部署MTK服务器类型选择合适的MTK版本，本次选择最新版MTK2.9.2。
将下载的mtk_2.9.2_linux_amd64.tar.gz软件上传到MTK服务器 omm用户。
3.2.2 校验MTK
使用sha256sum命令对下载的安装包进行校验，并和官网cheksum值进行比对。
[omm@opensource-db ~]$ sha256sum mtk_2.9.2_linux_amd64.tar.gz
cc0cf328f0d4484f569e8520481dc4a3fd4d4acd46ac4a643c0e8e5ccdcfe551 mtk_2.9.2_linux_amd64.tar.gz如下是官网mtk_2.9.2_linux_amd64.tar.gz chechsum值。
3.2.3 解压MTK
MTK无需安装，只需解压即可。
[omm@opensource-db ~]$ tar -zxvf mtk_2.9.2_linux_amd64.tar.gz
mtk_2.9.2_linux_amd64/CHANGELOG.md
mtk_2.9.2_linux_amd64/README.md
mtk_2.9.2_linux_amd64/example/db22mogdb.json
mtk_2.9.2_linux_amd64/example/db22mogdb.yaml
mtk_2.9.2_linux_amd64/example/db22mysql.json
mtk_2.9.2_linux_amd64/example/db22mysql.yaml
mtk_2.9.2_linux_amd64/example/informix2mogdb.json
mtk_2.9.2_linux_amd64/example/informix2mogdb.yaml
mtk_2.9.2_linux_amd64/example/mysql2mogdb.json
mtk_2.9.2_linux_amd64/example/mysql2mogdb.yaml
mtk_2.9.2_linux_amd64/example/oracle2mogdb.json
mtk_2.9.2_linux_amd64/example/oracle2mogdb.yaml
mtk_2.9.2_linux_amd64/example/pg2mogdb.json
mtk_2.9.2_linux_amd64/example/pg2mogdb.yaml
mtk_2.9.2_linux_amd64/example/sqlserver2mogdb.json
mtk_2.9.2_linux_amd64/example/sqlserver2mogdb.yaml
mtk_2.9.2_linux_amd64/mtk
[omm@opensource-db ~]$ ls -lrt
total 22188
drwx------ 2 omm omm 4096 Dec 2 2022 soft
-rw------- 1 omm omm 21524 Dec 6 2022 ab.sql
-rw------- 1 omm omm 22689582 Jul 17 12:48 mtk_2.9.2_linux_amd64.tar.gz
drwx------ 5 omm omm 84 Jul 28 16:58 bak
drwx------ 3 omm omm 18 Jul 30 00:37 oradiag_omm
drwx------ 5 omm omm 122 Jul 30 14:58 mtk_2.9.2_linux_amd643.2.4 申请license
MTK首次使用，需要申请license，可采用如下方式申请和应用license
-- 1） 申请license
-- 填写邮箱申请license
[omm@opensource-db ~]$ cd mtk_2.9.2_linux_amd64/
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk license gen
License File Not Found (default license.json)
The License code is invalid, start applying
Email: xxxx@xxx.com -- 填写申请邮箱
Email: xxxx@xxx.com
Start applying for email xxxx@xxx.com authorization.
Start parsing the interface to return data.
Successful application for authorization. Please check the mail and save it as license.json.
-- 2）下载并上传license
-- 登录申请邮箱会接收到license.json文件，将该文件上传到 /home/omm/mtk_2.9.2_linux_amd64 目录下
-- 3） 应用license
[omm@opensource-db mtk_2.7.2_linux_amd64]$ ll
total 65280
-rw-r--r-- 1 omm omm 77321 Jul 17 12:38 CHANGELOG.md
drwxr-xr-x 2 omm omm 4096 Jul 31 10:19 example
-rw-r--r-- 1 omm omm 3241 Jul 30 16:39 license.json
-rwxr-xr-x 1 omm omm 66754240 Jul 17 12:38 mtk
-rw-r--r-- 1 omm omm 2052 Apr 28 10:33 README.md
-- 根据邮箱里收到的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/mysql/mtk_2.9.2_linux_amd64/license.json
Name : xxxx@xxxx.com
Expiry: 2023-08-29 16:39:17.148823801 +0800 CST
Version:
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.9.2
Git Commit hash: 26deaa81
Git Commit Date: 2023-07-17T04:38:19Z
Git Tag : v2.9.2
Build timestamp: 202307170440233.3 目标端创建数据库及用户
[omm@xsky-node1 ~]$ gsql -d postgres -p 26000
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# create tablespace t_xxxx RELATIVE LOCATION 'my_tablespace/t_xxxx';
CREATE TABLESPACE
-- 本次选择为数据库指定默认表空间
openGauss=# create database gaussdb WITH TABLESPACE = t_xxxx;
CREATE DATABASE
MogDB=# create user opuser with password "Opuser123";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
openGauss=# GRANT CREATE ON TABLESPACE t_xxxx to opuser;
GRANT
openGauss=# grant all on schema public to opuser;
GRANT
openGauss=# alter user opuser sysadmin;
ALTER ROLE
openGauss=# grant create on database gaussdb to opuser;
GRANT3.4 MTK初始化项目
使用MTK迁移，需要首先在MTK服务器进行项目的初始化，初始化方式如下
-- 通过mtk init-project --help命令可以查看mtk所支持的数据库
[omm@opensource-db ~]$ cd /home/omm/mtk_2.9.2_linux_amd64
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk init-project --help
Initialise a typical mtk project tree. Top directory will be created under project base dir.
Usage:
mtk init-project [project_name|--name project_name] [flags]
Examples:
mtk init-project ora2og --sourceDBType oracle --targetDBType mogdb
Flags:
--detail Whether to output full parameters, default output simple parameters
-h, --help help for init-project
-n, --name string specify project name
-s, --sourceDBType string specify source database type
oracle, mysql, db2, sqlserver, dm, informix, opengauss, mogdb, postgresql (default "oracle")
-t, --targetDBType string specify target database type
opengauss, mogdb, mysql, postgresql, db2 (default "mogdb")
Global Flags:
--caseSensitive int Object case parameters in SQL statements.
1 - lower case
2 - upper case
3 - Keep it the same as the source database.
-c, --config string Set mtk config file. Support json,yaml. [env MTK_CONFIG] (default "mtk.json")
-d, --debug Set the debug mode.
Not necessary for the normal usage. [env MTK_DEBUG]
--disableIgnoreCase Disable ignoring case queries
--enableSyncCompTabPro Enable Synchronize table compressed properties
--file export to file
--fileType string Indicates the type of a file when data is to be exported to a file.
support csv,sql
--license string Specify the license file
--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)
--tableSkip stringArray Define table split. Can be specified multiple times.
format schema.tableName --tableSkip MTK.TABLE01 --tableSkip MTK.TABLE02
--tables string the Migrate tables,Separated by commas.(tab1,schema1.tab1)
-- -s 代表源端所支持的数据库类型
-- -t 代表目标端所支持的数据库类型
-- 通过以上内容可以看到mtk支持源端Oracle数据库和目标端openGauss数据库的迁移
-- 使用如下命令初始化项目
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk init-project -s oracle -t opengauss -n ora2gaussdb
[omm@opensource-db mtk_2.9.2_linux_amd64]$ tree -f ora2gaussdb/
ora2gaussdb
├── ora2gaussdb/config
│ └── ora2gaussdb/config/mtk.json
├── ora2gaussdb/data
├── ora2gaussdb/report
└── ora2gaussdb/schema
4 directories, 1 file3.5 编辑并检查配置文件
3.5.1 编辑配置文件
-- 可参照初始化后所提供的mtk.json模版来编辑配置文件
[omm@opensource-db ~]$ cd /home/omm/mtk_2.9.2_linux_amd64/ora2gaussdb/config
[omm@opensource-db config]$ ll
total 4
-rw------- 1 omm omm 1854 Jul 31 11:30 mtk.json
-- 编辑mtk.json配置文件
[omm@opensource-db mtk_2.9.2_linux_amd64]$ vim ora2mg/config/mtk.json
-- 编辑后的文件如下
{
"source": {
"type": "oracle", -- 源端数据类型
"connect": {
"version": "11.2.0.4", -- 源端数据库版本
"host": "10.110.8.204", -- 源端数据库地址
"user": "system", -- 源端用户，需要有相应权限
"port": 1521, -- 源端监听端口
"password": "xxxx", -- 源端用户密码
"dbName": "silk", -- 源端数据库名称
"dsn": "",
"charset": "UTF8", -- 源端字符集
"clientCharset": ""
},
"parameter": {
"charAppendEmptyString": false
}
},
"target": {
"type": "opengauss", -- 目标端数据库类型
"connect": {
"version": "5.0.0", -- 目标端数据库版本
"host": "10.110.7.39", -- 目标端数据库地址
"user": "opuser", -- 目标端用户
"port": 26000, -- 目标段监听端口
"password": "Opuser123", -- 目标端用户密码
"dbName": "gaussdb", -- 目标端数据库名称
"dsn": "",
"charset": "UTF8", -- 目标端数据库字符集
"datCompatibility": "A",
"clientCharset": "UTF8"
},
"parameter": {
"parallelInsert": 4, -- 数据插入的并行度
"dropSchema": false,
"dropExistingObject": false,
"truncTable": false,
"colKeyWords": {
"STREAM": 1,
"TID": 1
},
"objKeyWords": {
"STREAM": 1,
"TID": 1
},
"caseSensitive": 0,
"quoteMark": false,
"path": "ora2gaussdb/data", -- 存放迁移过程中源端及目标端的相关信息
"schemaPath": "ora2gaussdb/schema", -- 存放迁移过程中的一些视图、存储过程、包等信息
"dataPath": "ora2gaussdb/data",
"errDataPath": "",
"fileType": "sql",
"fileSize": "10240MiB",
"csvHeader": false,
"csvNullValue": "",
"csvFieldDelimiter": ",",
"csvOptionallyEnclosed": "\"",
"excludeSysTable": [],
"remapSchema": {
"XXXXX": "XXXXX"
},
"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": [
"XXXXX"
],
"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": "ora2gaussdb/report", -- 迁移生成报告的目录
"debug": false,
"disableIgnoreCase": false,
"disableSelectPart": false,
"disableFKCons": false,
"disableSyncIdxAfterData": false
}
-- mtk.json配置文件具体内容可参照官方文档 https://docs.mogdb.io/zh/mtk/v2.0/mtk-config以下是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 | 关闭打印迁移表数据进度功能.打印进度影响迁移性能 |
3.5.2 检查配置文件
-- 通过预运行检查配置文件有无错误
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk check-config -c ora2gaussdb/config/mtk.json --preRun
use config : ora2gaussdb/config/mtk.json
There is no error in the configuration file
# 出现如上信息提示配置文件配置正确
# 注意，如果只迁移schema，在配置object时，不能将table和schema同时写，否则检查时会报如下错误：
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk check-config -c ora2gaussdb/config/mtk.json --preRun
use config : ora2gaussdb/config/mtk.json
Error : schema and tables cannot exist together
schema and tables cannot exist together四、执行迁移
4.1 迁移命令
迁移可以选择只迁移schema、迁移整个库，或者迁移数据库某些表对象。
-- 通常有如下迁移方式
-- 整库迁移
mtk -c ora2gaussdb/config/mtk.json
-- 只迁移schema
mtk -c ora2gaussdb/config/mtk.json --schemaOnly
-- 只迁移数据
mtk -c ora2gaussdb/config/mtk.json --file --dataOnly
-- 还可以根据需要对部分对象进行迁移
-- 可参加官方文档：https://docs.mogdb.io/zh/mtk/v2.0/mtk-usage (分步迁移)
-- 执行迁移后，还可以通过如下方式生成相应迁移报告
-- 导出整库迁移报告
mtk -c ora2gaussdb/config/mtk.json --file
-- 导出仅含schema的信息报告
mtk -c ora2gaussdb/config/mtk.json --file --schemaOnly
-- 导出仅含数据的信息报告
mtk -c ora2gaussdb/config/mtk.json --file --dataOnly4.2 迁移schema
[omm@opensource-db ~]$ cd mtk_2.9.2_linux_amd64/
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk -c ora2gaussdb/config/mtk.json --schemaOnly
-- 迁移过程部分信息如下
CREATE TRIGGER REP_BUSI_FOLLOW_UP_INFO BEFORE INSERT OR UPDATE ON XXXXX.BUSI_FOLLOW_UP_INFO FOR EACH ROW EXECUTE PROCEDURE XXXXX.TRIG_FCT_REP_BUSI_FOLLOW_UP_INFO();
error code: 42601 msg:syntax error at or near "IS"" function=doTask line=558 file="mtk/pkg/mtk/mtk_task.go" taskID=1685880346911969280
......
time="2023-07-31 13:09:49.511669" level=info msg="CreateTrigger XXXXX.T_BUSI_INFO_RECORD" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1685880346911969280
time="2023-07-31 13:09:54.864476" level=info msg="AlterSequence XXXXX.S_Z_GRILLS_DEALER_INFO" function=doTask line=538 file="mtk/pkg/mtk/mtk_task.go" taskID=1685880346911969280
time="2023-07-31 13:09:54.868065" level=info msg="Processing AlterSequence finish" function=doTask line=450 file="mtk/pkg/mtk/mtk.go" taskID=1685880346911969280
time="2023-07-31 13:09:54.873779" level=info msg="Finish end" function=Stop line=250 file="mtk/pkg/mtk/mtk.go" taskID=1685880346911969280
......4.3 执行结果
迁移执行后，会在末尾生成执行结果，如下所示
*********************************
Database Migration Toolkit Report
*********************************
--------------
Report Summary
--------------
| Name | Value |
|--------------------------------|---------------------|
| Source Database Type | oracle |
| Source Database Version | 11.2.0.4.0 |
| Source Database Charset | AL32UTF8 |
| Source Database DatCompat | |
| Target Database Type | opengauss |
| Target Database Version | 5.0.0 |
| Target Database Charset | UTF8 |
| Target Database DatCompat | A |
| StartTime | 2023-07-31 13:09:31 |
| EndTime | 2023-07-31 13:09:54 |
| Time | 22 s |
| Status | finish |
| MTK Version | v2.9.2_26deaa81 |
-----------------------
ObjectName Type Summary
-----------------------
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|Schema |2023-07-31 13:09:31|2023-07-31 13:09:31|finish |1 |1 |0 |0 |0 |14 ms ｜
|Sequence |2023-07-31 13:09:31|2023-07-31 13:09:32|finish |477 |477 |0 |0 |0 |276 ms ｜
|ObjectType |2023-07-31 13:09:32|2023-07-31 13:09:32|finish |0 |0 |0 |0 |0 |72 ms ｜
|Queue |2023-07-31 13:09:32|2023-07-31 13:09:32|finish |0 |0 |0 |0 |0 |30 ms ｜
|Table |2023-07-31 13:09:32|2023-07-31 13:09:43|finish |558 |67 |491 |0 |0 |11 s 424 ms ｜
|Index |2023-07-31 13:09:43|2023-07-31 13:09:44|finish |1059 |1056 |1 |2 |0 |693 ms ｜
|Constraint |2023-07-31 13:09:44|2023-07-31 13:09:44|finish |485 |482 |2 |1 |0 |511 ms ｜
|DBLink |2023-07-31 13:09:44|2023-07-31 13:09:44|finish |9 |0 |0 |9 |0 |5 ms ｜
|Synonym |2023-07-31 13:09:44|2023-07-31 13:09:44|finish |10 |9 |0 |1 |0 |38 ms ｜
|View |2023-07-31 13:09:44|2023-07-31 13:09:44|finish |0 |0 |0 |0 |0 |38 ms ｜
|MaterializedView |2023-07-31 13:09:44|2023-07-31 13:09:45|finish |18 |0 |0 |18 |0 |225 ms ｜
|Function |2023-07-31 13:09:45|2023-07-31 13:09:46|finish |3 |1 |0 |2 |0 |1 s 725 ms ｜
|Procedure |2023-07-31 13:09:46|2023-07-31 13:09:48|finish |20 |3 |0 |14 |3 |1 s 337 ms ｜
|Package |2023-07-31 13:09:48|2023-07-31 13:09:48|finish |0 |0 |0 |0 |0 |21 ms ｜
|Trigger |2023-07-31 13:09:48|2023-07-31 13:09:54|finish |900 |875 |0 |25 |0 |6 s 436 ms ｜
|AlterSequence |2023-07-31 13:09:54|2023-07-31 13:09:54|finish |477 |477 |0 |0 |0 |134 ms ｜
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
[2023-07-31 13:09:54.877143] INFO reportDir : ora2gaussdb/report/report_20230731130954 function=PrintReport line=364 file=mtk/cmd/mtk/cmd/mtk.go
[2023-07-31 13:09:55.154613] INFO the text report : ora2gaussdb/report/report_20230731130954.txt function=HTMLReportToFIle line=135 file=mtk/pkg/report/report.go
[2023-07-31 13:09:55.990968] INFO the warring report : ora2gaussdb/report/report_20230731130954.warring file=mtk/pkg/report/report.go function=HTMLReportToFIle line=142
[2023-07-31 13:09:56.06896] INFO the error report : ora2gaussdb/report/report_20230731130954.err function=HTMLReportToFIle line=149 file=mtk/pkg/report/report.go
[2023-07-31 13:09:56.092667] INFO the excel report : ora2gaussdb/report/report_20230731130954.xlsx function=HTMLReportToFIle line=156 file=mtk/pkg/report/report.go4.4 查看报告
-- 迁移结束后，会生成如下一些执行结果
-- report 各文件及目录含义
ora2mg/report/report_20230731130954/index.html html报告
ora2mg/report/report_20230731130954.txt 文本报告
ora2mg/report/report_20230731130954.warring 只包含警告信息的文本报告
ora2mg/report/report_20230731130954.err 只包含错误信息的文本报告
ora2mg/report/report_20230731130954.xlsx 包含导出过程的xlsx文件信息报告
ora2mg/report/report_20230731130842.log 导出过程日志记录将report 目录report_xxx目录打包下载到本地电脑，然后解压后打开index.html文件，可查看导出的相关报告信息。
4.5 迁移数据
-- 采用如下方式迁移数据
[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk -c ora2gaussdb/config/mtk.json --file --dataOnly
-- 部分执行结果如下
time="2023-07-31 16:40:20.053455" level=info msg="Processing TableData XXXX.USER_xxxx_xxx_RECORD Reader Finish" function=func1 line=650 file="mtk/pkg/mtk/mtk_table_data.go" taskID=1685932911041187840
time="2023-07-31 16:40:20.055646" level=info msg="Processing TableData XXXX.USER_xxx_VERIFY_RECORD Writer Finish" function=func1 line=676 file="mtk/pkg/mtk/mtk_table_data.go" taskID=1685932911041187840
....
*********************************
Database Migration Toolkit Report
*********************************
--------------
Report Summary
--------------
| Name | Value |
|--------------------------------|---------------------|
| Source Database Type | oracle |
| Source Database Version | 11.2.0.4.0 |
| Source Database Charset | AL32UTF8 |
| Source Database DatCompat | |
| Target Database Type | file |
| Target Database Version | 5.0.0 |
| Target Database Charset | UTF8 |
| Target Database DatCompat | A |
| StartTime | 2023-07-31 16:38:24 |
| EndTime | 2023-07-31 16:40:42 |
| Time | 2 m 18 s |
| Status | finish |
| MTK Version | v2.9.2_26deaa81 |
-----------------------
Table Data Summary
-----------------------
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+----------+----------+
| SrcName | TgtName | StartTime | EndTime | Time| Status | Select | Insert |Ignore | Size |
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+----------+----------+
|XXXX.MLOG$xxxxx |XXXX.MLOG$xxxxx |2023-07-31 16:39:38|2023-07-31 16:39:38|3 ms |succeed | 0| 0| 0| 0|
|XXXX.XXXX |XXXX.XXXX |2023-07-31 16:40:32|2023-07-31 16:40:32|3 ms |succeed | 0| 0| 0| 0|
......
|XXXX.SYS_TEMP_FBT |XXXX.SYS_TEMP_FBT |2023-07-31 16:38:40|2023-07-31 16:38:40|0 ms |warring | 0| 0| 0| 0|
|XXXX.RUPD$_WT_OSS_ADMIN |XXXX.RUPD$_WT_OSS_ADMIN |2023-07-31 16:38:40|2023-07-31 16:38:40|0 ms |warring | 0| 0| 0| 0|
|XXXX.RUPD$_WT_OSS_ADMIN_ROLE |XXXX.RUPD$_WT_OSS_ADMIN_ROLE |2023-07-31 16:38:40|2023-07-31 16:38:40|0 ms |warring | 0| 0| 0| 0|
+----------------------------------------+----------------------------------------+-------------------+-------------------+------+--------+----------+----------+--------+----------+
-----------------------
ObjectName Type Summary
-----------------------
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|TableData |2023-07-31 16:38:24|2023-07-31 16:40:42|finish |558 |548 |10 |0 |0 |2 m 17 s 921 ms｜
|AlterSequence |2023-07-31 16:40:42|2023-07-31 16:40:42|finish |477 |477 |0 |0 |0 |189 ms ｜
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
五、验证测试
数据迁移仅仅是第一步，在迁移过程中也出现了一些问题，比如部分表未迁移成功，表字段类型、存储过程、函数不兼容，如何跨字符集迁移，迁移后的SQL语句执行结果及效率，这方面还需要花大量时间去解决。
可通过一些比对工具，比如恩墨的 MVD 工具可用于异构数据迁移或同步完成之后，校验源库与目标库之间的差异。
生产环境迁移，更要慎重，可能会涉及业务代码改造。
文章转载自公众号：openGauss
分类
标签
已于2023-9-12 11:09:22修改
赞
收藏
回复
相关推荐
