EDB Postgres MTK 使用小记

EDB Postgres Sever Migration Toolkit 是PPAS自带的一款数据迁移工具。使用MTK可以方便的把目标数据库（ORACLE/MySQL/Sybase/MS SQL Sever）的数据对象和数据内容迁移到 PPAS or PostgreSQL 。

安装 MTK 软件

安装方式： 图像化 or command-line
测试案例为 text mode

[root@Opython mtk]# ./edb-migrationtoolkit-51.0.0-1-linux-x64.run
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] :
----------------------------------------------------------------------------
Welcome to the EDB Postgres Migration Toolkit Setup Wizard.

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.

Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9

IMPORTANT - READ CAREFULLY

...............................................

Please specify the directory where Migration Toolkit will be installed.

Installation Directory [/opt/edb/mtk]: 

----------------------------------------------------------------------------
Setup is now ready to begin installing Migration Toolkit on your computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------
Please wait while Setup installs Migration Toolkit on your computer.

 Installing EDB Postgres Migration Toolkit
 0% __ 50% __ 100%
 #########################################

----------------------------------------------------------------------------
EnterpriseDB is the leading provider of value-added products and services for
the Postgres community.

Please visit our website at www.enterprisedb.com

安装 ORACLE client oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

下载地址：http://www.oracle.com/technetwork/cn/database/features/instant-client/index-097480.html

[root@Opython mtk]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]

[root@Opython mtk]# cp /usr/lib/oracle/11.2/client64/lib/ojdbc6.jar  /usr/lib/jvm/jre-1.7.0/lib/ext/

配置 toolkit.properties 配置文件（位置在 MTK 主目录 /etc 下）

SRC_DB_URL=jdbc:oracle:thin:@1.2.3.4:1528:wprod11g   #Ensure that the oracle instance client is avalible
SRC_DB_USER=system
SRC_DB_PASSWORD=oracle

TARGET_DB_URL=jdbc:edb://5.6.7.8:5444/cww
TARGET_DB_USER=pgmg
TARGET_DB_PASSWORD=oracle

迁移测试

/opt/edb/mtk/bin/runMTK.sh -dropSchema true SCOTT
Running EnterpriseDB Migration Toolkit (Build 51.0.0) ...
Source database connectivity info...
conn =jdbc:oracle:thin:@
user =system
password=
Target database connectivity info...
conn =jdbc:edb://
user =pgmg
password=
Connecting with source Oracle database server...
Connected to Oracle, version 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '9.3.1.3'
Importing redwood schema SCOTT...
Dropping Schema: scott
Creating Schema...scott
Creating Tables...
Creating Table: BONUS
Creating Table: DEPT
Creating Table: EMP
Creating Table: SALGRADE
Created 4 tables.
Loading Table Data in 8 MB batches...
Loading Table: BONUS ...
[BONUS] Table Data Load Summary: Total Time(s): 0.089 Total Rows: 0
Loading Table: DEPT ...
[DEPT] Migrated 4 rows.
[DEPT] Table Data Load Summary: Total Time(s): 0.051 Total Rows: 4
Loading Table: EMP ...
[EMP] Migrated 14 rows.
[EMP] Table Data Load Summary: Total Time(s): 0.066 Total Rows: 14
Loading Table: SALGRADE ...
[SALGRADE] Migrated 5 rows.
[SALGRADE] Table Data Load Summary: Total Time(s): 0.049 Total Rows: 5
Data Load Summary: Total Time (sec): 0.563 Total Rows: 23 Total Size(MB): 0.0
Creating Constraint: PK_DEPT
Creating Constraint: PK_EMP
Creating Constraint: FK_DEPTNO

Schema SCOTT imported successfully.

MTK-14003: One or more users couldn't be found in the source Oracle database.
With -users mode, the user name should be in uppercase unless it is case-sensitive.

One or more schema objects could not be imported during the migration process. Please review the migration output for more details.

Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs

 Migration Summary **
Tables: 4 out of 4
Constraints: 3 out of 3

Total objects: 7
Successful count: 7
Failed count: 0
Invalid count: 0

*

数据检测

cww=# \d scott.*
            Table "scott.bonus"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 ename  | character varying(10) |
 job    | character varying(9)  |
 sal    | numeric               |
 comm   | numeric               | 

             Table "scott.dept"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 deptno | numeric(2,0)          | not null
 dname  | character varying(14) |
 loc    | character varying(13) |
Indexes:
    "pk_dept" PRIMARY KEY, btree (deptno)
Referenced by:
    TABLE "scott.emp" CONSTRAINT "fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno)

                 Table "scott.emp"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 empno    | numeric(4,0)                | not null
 ename    | character varying(10)       |
 job      | character varying(9)        |
 mgr      | numeric(4,0)                |
 hiredate | timestamp without time zone |
 sal      | numeric(7,2)                |
 comm     | numeric(7,2)                |
 deptno   | numeric(2,0)                |
Indexes:
    "pk_emp" PRIMARY KEY, btree (empno)
Foreign-key constraints:
    "fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno)

       Index "scott.pk_dept"
 Column |     Type     | Definition
--------+--------------+------------
 deptno | numeric(2,0) | deptno
primary key, btree, for table "scott.dept"

        Index "scott.pk_emp"
 Column |     Type     | Definition
--------+--------------+------------
 empno  | numeric(4,0) | empno
primary key, btree, for table "scott.emp"

    Table "scott.salgrade"
 Column |  Type   | Modifiers
--------+---------+-----------
 grade  | numeric |
 losal  | numeric |
 hisal  | numeric |

EDB Migration Toolkit 支持数据对象类型

MTK tools常用参数：

MTK 工具可根据需求使用参数灵活使用

-verbose [on|off] Display application log messages on standard output (default: on).

-schemaOnly Import the schema object definitions only.
-dataOnly Import the table data only.
-sourcedbtype db_type The -sourcedbtype option specifies the source database type.
-targetdbtype db_type The -targetdbtype option specifies the target database type.
-allTables Import all tables.
-tables LIST Import comma-separated list of tables.
-constraints Import the table constraints.
-indexes Import the table indexes.
-triggers Import the table triggers.
-allViews Import all Views.
-views LIST Import comma-separated list of Views.
-allProcs Import all stored procedures.
-procs LIST Import comma-separated list of stored procedures.
-allFuncs Import all functions.
-funcs LIST Import comma-separated list of functions.
-allPackages Import all packages.
-packages LIST Import comma-separated list of packages.
-allSequences Import all sequences.
-sequences LIST Import comma-separated list of sequences.
-targetSchema NAME Name of the target schema (default: target schema is named after source schema).
-allDBLinks Import all Database Links.
-allSynonyms It enables the migration of all public and private synonyms from an Oracle database to an Advanced Server database.  If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version.
-allPublicSynonyms It enables the migration of all public synonyms from an Oracle database to an Advanced Server database.  If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version.
-allPrivateSynonyms It enables the migration of all private synonyms from an Oracle database to an Advanced Server database.  If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version.
-allQueues Import all queues from the source database.
-queues LIST Import the selected queues from the source database. LIST is a comma-separated list of queue names.
-skipUserSchemaCreation This option prevents implicit schema creation for a migrated Oracle user. 

-dropSchema [true|false] Drop the schema if it already exists in the target database (default: false).
-truncLoad It disables any constraints on target table and truncates the data from the table before importing new data. This option can only be used with -dataOnly.
-safeMode Transfer data in safe mode using plain SQL statements.
-copyDelimiter Specify a single character to be used as delimiter in copy command when loading table data. Default is \t
-batchSize Specify the Batch Size to be used by the bulk inserts. Valid values are  1-50000, default batch size is 1000, reduce if you run into Out of Memory exception
-cpBatchSize  Specify the Batch Size in MB, to be used in the Copy Command. Valid value is from 1 to 1024, default batch size is 8 MB.
-fetchSize  Specify fetch size in terms of number of rows should be fetched in result set at a time. This option can be used when tables contain millions of rows and you want to avoid out of memory errors.
-filterProp The properties file that contains table where clause.
-skipFKConst Skip migration of FK constraints.
-skipCKConst Skip migration of Check constraints.
-ignoreCheckConstFilter By default MTK does not migrate Check constraints and Default clauses from Sybase, use this option to turn off this filter.
-fastCopy Bypass WAL logging to perform the COPY operation in an optimized way, default disabled.
-customColTypeMapping LIST Use custom type mapping represented by a semi-colon separated list, where each entry is specified using COL_NAME_REG_EXPR=TYPE pair. e.g. .*ID=INTEGER
-customColTypeMappingFile PROP_FILE The custom type mapping represented by a properties file, where each entry is specified using COL_NAME_REG_EXPR=TYPE pair. e.g. .*ID=INTEGER
-offlineMigration [PATH] This performs offline migration and saves the DDL/DML scripts in files for a later execution. By default the script files will be saved under user home folder, if required follow -offlineMigration option with a custom path.
-logDir LOG_PATH Specify a custom path to save the log file. By default, on Linux the logs will be saved under folder $HOME/.enterprisedb/migration-toolkit/logs. In case of Windows logs will be saved under folder %HOMEDRIVE%%HOMEPATH%\.enterprisedb\migration-toolkit\logs.
-copyViaDBLinkOra This option can be used to copy data using dblink_ora COPY command. This option can only be used in Oracle to EnterpriseDB migration mode.
-singleDataFile Use single SQL file for offline data storage for all tables. This option cannot be used in COPY format.
-allUsers Import all users and roles from the source database.
-users LIST Import the selected users/roles from the source database. LIST is a comma-separated list of user/role names e.g. -users MTK,SAMPLE
-allProfiles Import all profiles from the source database.
-profiles LIST Import the selected profiles from the source database. LIST is a comma-separated list of profile names e.g. -profiles USER_PROFILE,ADMIN_PROFILE
-allRules Import all rules from the source database.
-rules LIST Import the selected rules from the source database. LIST is a comma-separated list of rule names e.g. -rules high_sal_emp,low_sal_emp
-allGroups Import all groups from the source database.
-groups LIST Import the selected groups from the source database. LIST is a comma-separated list of group names e.g. -groups acct_emp,mkt_emp
-allDomains Import all domain, enumeration and composite types from the source database.
-domains LIST Import the selected domain, enumeration and composite types from the source database. LIST is a comma-separated list of domain names e.g. -domains d_email,d_dob, mood
-objecttypes Import the user-defined object types.
-replaceNullChar <CHAR> If null character is part of a column value, the data migration fails over JDBC protocol. This option can be used to replace null character with a user-specified character.
-importPartitionAsTable [LIST] Use this option to import Oracle Partitioned table as a normal table in EnterpriseDB. To apply the rule on a selected set of tables, follow the option by a comma-separated list of table names.
-enableConstBeforeDataLoad Use this option to re-enable constraints (and triggers) before data load. This is useful in the scenario when the migrated table is mapped to a partition table in EnterpriseDB.
-checkFunctionBodies [true|false] When set to false, it disables validation of the function body during function creation, this is to avoid errors if function contains forward references. Applicable when target database is Postgres/EnterpriseDB, default is true.
-retryCount VALUE Specify the number of re-attempts performed by MTK to migrate objects that failed due to cross-schema dependencies. The VALUE parameter should be greater than 0, default is 2.
-analyze  It invokes ANALYZE operation against a target Postgres or Postgres Plus Advanced Server database. The ANALYZE collects statistics for the migrated tables that are utilized for efficient query plans.
-vacuumAnalyze  It invokes VACUUM and ANALYZE operations against a target Postgres or Postgres Plus Advanced Server database. The VACUUM reclaims dead tuple storage whereas ANALYZE collects statistics for the migrated tables that are utilized for efficient query plans.
-loaderCount VALUE Specify the number of jobs (threads) to perform data load in parallel. The VALUE parameter should be greater than 0, default is 1.
-logFileSize VALUE It represents the maximum file size limit (in MB) before rotating to a new log file, defaults to 50MB.
-logFileCount VALUE It represents the number of files to maintain in log file rotation history, defaults to 20. Specify a value of zero to disable log file rotation.
-useOraCase It preserves the identifier case while migrating from Oracle, except for functions, procedures and packages unless identifier names are given in quotes.
-logBadSQL It saves the DDL scripts for the objects that fail to migrate, in a .sql file in log folder.
-targetDBVersion It represents the major.minor version of the target database. This option is applicable for offline migration mode and is used to validate certain migration options as per target db version [default is 9.6 for EnterpriseDB database]

MTK 可用 ./runMTK.sh -help 查看更详细功能介绍