写点什么

MTK 2.9.2 迁移 Oracle 11g 至 openGauss 5.0.0 操作指南

作者:daydayup
  • 2023-08-08
    北京
  • 本文字数:15500 字

    阅读完需:约 51 分钟

MTK 2.9.2 迁移 Oracle 11g 至 openGauss 5.0.0 操作指南

尚雷 [openGauss](javascript:void(0);) 2023-08-04 18:01 发表于四川


前言: 最近在进行一些去 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]。


三、安装部署

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]# lltotal 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.ora6 directories, 21 files
-- 2) 配置环境变量-- omm 用户[omm@opensource-db ~]$ cat >>/home/omm/.bash_profile<<EOFexport LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/libexport TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/adminEOF[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/sqlplus64lrwxrwxrwx 1 root root 41 Dec 2 14:40 /bin/sqlplus64 -> /usr/lib/oracle/11.2/client64/bin/sqlplus
-- 解决办法,使用root用户执行如下操作[root@opensource-db bin]# su - ommLast 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.mdmtk_2.9.2_linux_amd64/README.mdmtk_2.9.2_linux_amd64/example/db22mogdb.jsonmtk_2.9.2_linux_amd64/example/db22mogdb.yamlmtk_2.9.2_linux_amd64/example/db22mysql.jsonmtk_2.9.2_linux_amd64/example/db22mysql.yamlmtk_2.9.2_linux_amd64/example/informix2mogdb.jsonmtk_2.9.2_linux_amd64/example/informix2mogdb.yamlmtk_2.9.2_linux_amd64/example/mysql2mogdb.jsonmtk_2.9.2_linux_amd64/example/mysql2mogdb.yamlmtk_2.9.2_linux_amd64/example/oracle2mogdb.jsonmtk_2.9.2_linux_amd64/example/oracle2mogdb.yamlmtk_2.9.2_linux_amd64/example/pg2mogdb.jsonmtk_2.9.2_linux_amd64/example/pg2mogdb.yamlmtk_2.9.2_linux_amd64/example/sqlserver2mogdb.jsonmtk_2.9.2_linux_amd64/example/sqlserver2mogdb.yamlmtk_2.9.2_linux_amd64/mtk
[omm@opensource-db ~]$ ls -lrttotal 22188drwx------ 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.gzdrwx------ 5 omm omm 84 Jul 28 16:58 bakdrwx------ 3 omm omm 18 Jul 30 00:37 oradiag_ommdrwx------ 5 omm omm 122 Jul 30 14:58 mtk_2.9.2_linux_amd64
复制代码

3.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 genLicense File Not Found (default license.json) The License code is invalid, start applyingEmail: xxxx@xxx.com     -- 填写申请邮箱Email: xxxx@xxx.comStart 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]$ lltotal 65280-rw-r--r-- 1 omm omm 77321 Jul 17 12:38 CHANGELOG.mddrwxr-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]$ lltotal 46480-rw------- 1 omm omm 60343 Nov 29 12:56 CHANGELOG.mddrwx------ 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 genUsing license file: /home/mysql/mtk_2.9.2_linux_amd64/license.jsonName : xxxx@xxxx.comExpiry: 2023-08-29 16:39:17.148823801 +0800 CSTVersion:
MMMMMMMM MMMMMMMMTTTTTTTTTTTTTTTTTTTTTTTKKKKKKKKK KKKKKKKM:::::::M M:::::::MT:::::::::::::::::::::TK:::::::K K:::::KM::::::::M M::::::::MT:::::::::::::::::::::TK:::::::K K:::::KM:::::::::M M:::::::::MT:::::TT:::::::TT:::::TK:::::::K K::::::KM::::::::::M M::::::::::MTTTTTT T:::::T TTTTTTKK::::::K K:::::KKKM:::::::::::M M:::::::::::M T:::::T K:::::K K:::::KM:::::::M::::M M::::M:::::::M T:::::T K::::::K:::::KM::::::M M::::M M::::M M::::::M T:::::T K:::::::::::KM::::::M M::::M::::M M::::::M T:::::T K:::::::::::KM::::::M M:::::::M M::::::M T:::::T K::::::K:::::KM::::::M M:::::M M::::::M T:::::T K:::::K K:::::KM::::::M MMMMM M::::::M T:::::T KK::::::K K:::::KKKM::::::M M::::::M TT:::::::TT K:::::::K K::::::KM::::::M M::::::M T:::::::::T K:::::::K K:::::KM::::::M M::::::M T:::::::::T K:::::::K K:::::KMMMMMMMM MMMMMMMM TTTTTTTTTTT KKKKKKKKK KKKKKKK
Release version: v2.9.2Git Commit hash: 26deaa81Git Commit Date: 2023-07-17T04:38:19ZGit Tag : v2.9.2Build timestamp: 20230717044023
复制代码

3.3 目标端创建数据库及用户

[omm@xsky-node1 ~]$ gsql -d postgres -p 26000gsql ((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 DATABASEMogDB=# create user opuser with password "Opuser123";NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.CREATE ROLEopenGauss=# GRANT CREATE ON TABLESPACE t_xxxx to opuser;GRANTopenGauss=# grant all on schema public to opuser;GRANTopenGauss=# alter user opuser sysadmin;ALTER ROLEopenGauss=# grant create on database gaussdb to opuser;GRANT
复制代码

3.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 --helpInitialise 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 file
复制代码

3.5 编辑并检查配置文件

3.5.1 编辑配置文件

-- 可参照初始化后所提供的mtk.json模版来编辑配置文件[omm@opensource-db ~]$ cd /home/omm/mtk_2.9.2_linux_amd64/ora2gaussdb/config[omm@opensource-db config]$ lltotal 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 部分配置参数及含义描述:


3.5.2 检查配置文件

-- 通过预运行检查配置文件有无错误[omm@opensource-db mtk_2.9.2_linux_amd64]$ ./mtk check-config -c ora2gaussdb/config/mtk.json --preRunuse config : ora2gaussdb/config/mtk.jsonThere 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 --preRunuse config : ora2gaussdb/config/mtk.jsonError : schema and tables cannot exist together schema and tables cannot exist together
复制代码

四、执行迁移

4.1 迁移命令

迁移可以选择只迁移 schema、迁移整个库,或者迁移数据库某些表对象。


-- 通常有如下迁移方式-- 整库迁移mtk -c ora2gaussdb/config/mtk.json
-- 只迁移schemamtk -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 --dataOnly
复制代码

4.1 迁移 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=1685880346911969280time="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=1685880346911969280time="2023-07-31 13:09:54.868065" level=info msg="Processing AlterSequence finish" function=doTask line=450 file="mtk/pkg/mtk/mtk.go" taskID=1685880346911969280time="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.2 执行结果

迁移执行后,会在末尾生成执行结果,如下所示


*********************************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.go
复制代码

4.3 查看报告

-- 迁移结束后,会生成如下一些执行结果-- 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.4 迁移数据

-- 采用如下方式迁移数据[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=1685932911041187840time="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 |+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
复制代码



用户头像

daydayup

关注

还未添加个人签名 2023-07-18 加入

还未添加个人简介

评论

发布
暂无评论
MTK 2.9.2 迁移Oracle 11g 至 openGauss 5.0.0操作指南_daydayup_InfoQ写作社区