ORACLE迁移到PostgreSQL迁移（二）——EDB 离线方法迁移

ORACLE迁移到PostgreSQL迁移（二）——EDB 离线方法迁移

------如有排版问题，请横屏观看

我们在上一篇说了说EDB的在线迁移方法，本篇我来讲讲EDB离线方法迁移。

环境介绍：

首先我们先看下EDB Migration 的参数控制：

o-Dprop:数据库配置文件的绝对路径（这个我们隐藏在了runMTK.sh 的脚本当中了）

o-sourcedbtype:源数据库类型（oracle/mysql）

o-targetdbtype:目标数据库类型

o-alltables：以下是其他选项

-schemaOnly 只导入架构对象定义。

-dataOnly 只导入表数据。若指定了 -tables，则只导入所选表的数据。注意: 如果对目标表定义了任何外键约束，则此选项需与 -truncLoad 选项一起使用。



-sourcedbtype db_type The -sourcedbtype option specifies the source database type. db_type may be one of the following values: mysql, oracle, sqlserver, sybase, postgresql, uxdb. db_type is case-insensitive. By default, db_type is oracle.

-targetdbtype db_type The -targetdbtype option specifies the target database type. db_type may be one of the following values: oracle, sqlserver, postgresql, uxdb. db_type is case-insensitive. By default, db_type is uxdb.



-allTables 导入所有表。

-tables LIST 导入以逗号分隔的表列表。

-constraints 导入表约束。

-indexes 导入表索引。

-triggers 导入表触发器。

-allViews 导入所有视图。

-views LIST 导入以逗号分隔的视图列表。

-allProcs 导入所有存储过程。

-procs LIST 导入以逗号分隔的存储过程列表。

-allFuncs 导入所有函数。

-funcs LIST 导入以逗号分隔的函数列表。

-allPackages 导入所有包。

-packages LIST 导入以逗号分隔的包列表。

-allSequences 导入所有序列。

-sequences LIST 导入以逗号分隔的序列列表。

-targetSchema NAME 目标架构的名称 (默认: 目标架构以源架构命名)。

-allDBLinks 导入所有数据库链接。

-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] 若架构已存在于目标数据库中，则删除此架构 (默认值: false)。

-truncLoad 此选项对目标表禁用任何约束，并且在导入新数据之前先截断表中的数据。此选项只能与 -dataOnly 一起使用。

-safeMode 使用纯 SQL 语句，以安全模式传输数据。

-copyDelimiter 在加载表数据时，指定一个字符作为复制命令中的分隔符。默认值为 \t

-batchSize 指定“批量插入”要使用的“批次大小”。有效值为 1-1000，默认批次大小为 1000，如果出现“内存不足”异常，则可以降低此值

-cpBatchSize 指定复制命令要使用的“批次大小”，以 MB 为单位。有效值大于 0，默认批次大小为 8 MB

-fetchSize 指定提取大小 (每次应从结果集中提取的行数)。当数据表含有数百万个行，而您想避免发生内存不足错误时，可以使用此选项。

-filterProp 包含表 where 子句的属性文件。

-skipFKConst 跳过外键约束的迁移。

-skipCKConst 跳过检查约束条件的迁移。

-ignoreCheckConstFilter 在缺省的情况下MTK不从Sybase中迁移检查约束和缺省子句，使用这个选项可以关闭这个过滤功能。

-fastCopy 略过 WAL 日志记录，以优化方式执行 COPY 操作，默认情况下禁用。

-customColTypeMapping LIST 使用以分号分隔的列表表示的自定义类型映射，其中每个条目都使用 COL_NAME_REG_EXPR=TYPE 对来指定，例如 .*ID=INTEGER

-customColTypeMappingFile PROP_FILE 由属性文件表示的自定义类型映射，其中每个条目都使用 COL_NAME_REG_EXPR=TYPE 对来指定，例如 .*ID=INTEGER

-offlineMigration [DDL_PATH] 这将执行脱机迁移并将 DDL 脚本保存在文件中供以后执行。默认情况下，如果要求后跟 -offlineMigration 选项以及自定义路径，则脚本文件将保存在用户主文件夹下。

-logDir LOG_PATH 指定用于保存日志文件的自定义路径。默认情况下，日志文件保存在工作目录中的“logs”文件夹下。

-copyViaDBLinkOra 此选项可用来通过使用 dblink_ora COPY 命令复制数据。此选项仅限用在从 Oracle 到 uxDB 迁移模式中。

-singleDataFile Use single SQL file for offline data storage for all tables. This option cannot be used in COPY format.

-allUsers 从源数据库导入所有用户和角色。

-users LIST 从源数据库导入选定用户/角色。LIST 是一个用逗号分隔的用户/角色名称列表，如 -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 从源数据库导入所有规则。

-rules LIST 从源数据库导入选定规则。 LIST 是一个用逗号分隔的名称列表，如 -rules high_sal_emp,low_sal_emp

-allGroups 从源数据库导入所有组。

-groups LIST 从源数据库导入选定组。 LIST 是一个用逗号分隔的组名称列表，如 -groups acct_emp,mkt_emp

-allDomains 从源数据库导入所有域、枚举和复合类型。

-domains LIST 从源数据库导入所选域、枚举和复合类型。 LIST 是一个用逗号分隔的域名称列表，如 -domains d_email,d_dob, mood

-objecttypes 导入用户定义的对象类型。

-replaceNullChar 如果空字符是列值得一部分，那么通过JDBC协议迁移数据就会失败.这个选项可以使用用户指定的字符来替代空字符串。

-importPartitionAsTable [LIST] 通过使用这个选项能够将Oracle中的分区表以常规表的形式导入到uxDB中。为了在所选择表集合上的应用规则，在选项后面应跟随以逗号分隔的表名列表。

-enableConstBeforeDataLoad 通过使用这个选项可以在数据导入前重新启用约束(和触发器).当要迁移的表在uxDB中对应的是一张分区表时，使用这个选项是非常有用的。

-checkFunctionBodies [true|false] 设置为 false 时，将禁用创建函数过程中的函数体验证，从而避免在函数包含向前参考时发生错误。 目标数据库为 Postgres/uxDB 时适用，默认值为 true。

-retryCount VALUE 指定 MTK 迁移由于跨架构相关性而失败的对象的重试次数。 VALUE 参数应该大于 0，默认值为 2。

-analyze 它将对目标 Postgres 或 Postgres Plus Advanced Server 数据库调用 ANALYZE 操作。 ANALYZE 收集用于有效查询计划的迁移表的统计信息。

-vacuumAnalyze 它将对目标 Postgres 或 Postgres Plus Advanced Server 数据库调用 VACUUM 和 ANALYZE 操作。 VACUUM 回收非活动元组存储，ANALYZE 收集用于有效查询计划的迁移表的统计信息。

-loaderCount VALUE 指定并行执行数据加载的作业(线程)数目。 VALUE 参数应该大于 0，默认值为 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 10.0 for uxdb database].

离线迁移是受一个参数控制 --offlineMigration，这里的离线指的就是现将数据库源端的数据导成一堆脚本，然后dba可以将这些文件进行更改配置，再导到PostgreSQL里，我们来看下具体的操作。

前期的配置和之前的配置一样，如果不会安装，看我上篇文章

在此之前我生成了一些数据在我的ORACLE的CUI用户下，其中包括触发器，视图，包，表，索引，存储过程，同义词，序列等。

SQL> select object_name,object_type,owner from dba_objects where owner='CUI';

OBJECT_NAME OBJECT_TYPE OWNER

------------------------------ ------------------- ------------------------------

TBDEVICETYPE_ID_SEQ SEQUENCE CUI

TBDEVICE_ID_SEQ SEQUENCE CUI

TBLOGINLOG_ID_SEQ SEQUENCE CUI

TBMENU_ID_SEQ SEQUENCE CUI

TBPROJECTCOMMENT_ID_SEQ SEQUENCE CUI

TBPROJECT_ID_SEQ SEQUENCE CUI

TBROLE_ID_SEQ SEQUENCE CUI

TBTAG_ID_SEQ SEQUENCE CUI

TBUSEROPERATELOG_ID_SEQ SEQUENCE CUI

TBUSER_ID_SEQ SEQUENCE CUI

UTILS SYNONYM CUI

V_FLOWSTARTER VIEW CUI

V_FLOWSTARTERBPM VIEW CUI

V_TOTALCH VIEW CUI

V_TOTALCHWEEK VIEW CUI

V_TOTALCHYF VIEW CUI

WF_EMPWORKS VIEW CUI

P_PAGE PACKAGE CUI

SP_CHECKLOGIN PROCEDURE CUI

SP_CHECKONLINE PROCEDURE CUI

SP_GETAUTHORITYBYUSERID PROCEDURE CUI

SP_PAGER PROCEDURE CUI

P_PAGE PACKAGE BODY CUI

BOOKMARKS_MOBILE_TRIGGER TRIGGER CUI

BOOKMARKS_TRIGGER TRIGGER CUI

LAYERID_TRIGGER TRIGGER CUI

LAYERTYPE_TRIGGER TRIGGER CUI

SY_APPROVAL_ID_TRIGGER TRIGGER CUI

SY_DATAPROBLEMTYPE_ID_TRG TRIGGER CUI

SY_PLANPIPELINE_ID_TRIGGER TRIGGER CUI

SY_PLAN_ID_TRIGGER TRIGGER CUI

SY_PROBLEMDATAAREA_ID_TRG TRIGGER CUI

TBBUG_ID_TRG TRIGGER CUI

TBBUTTON_ID_TRG TRIGGER CUI

TBDEPARTMENT_ID_TRG TRIGGER CUI

TBDEVICETYPE_ID_TRG TRIGGER CUI

TBDEVICE_ID_TRG TRIGGER CUI

TBLOGINLOG_ID_TRG TRIGGER CUI

TBMENU_ID_TRG TRIGGER CUI

TBPROJECTCOMMENT_ID_TRG TRIGGER CUI

TBPROJECT_ID_TRG TRIGGER CUI

TBROLE_ID_TRG TRIGGER CUI

TBTAGDEVICEREALDATA_UPDATE_TRG TRIGGER CUI

TBTAG_ID_TRG TRIGGER CUI

TBUSEROPERATELOG_ID_TRG TRIGGER CUI

TBUSER_ID_TRG TRIGGER CUI

AIDEDPLANPIPEINFO TABLE CUI

BOOKMARKS TABLE CUI

BOOKMARKS_MOBILE TABLE CUI

ID INDEX CUI

LAYER TABLE CUI

LAYEYKEY INDEX CUI

LAYERTYPE TABLE CUI

LAYERKEY INDEX CUI

SY_APPROVAL TABLE CUI

SY_DATAPROBLEMTYPE TABLE CUI

SY_PLAN TABLE CUI

SY_PLANPIPELINE TABLE CUI

SY_PROBLEMDATAAREA TABLE CUI

TBBUG TABLE CUI

PK_TBBUG INDEX CUI

TBBUTTON TABLE CUI

PK_TBBUTTON INDEX CUI

TBDEPARTMENT TABLE CUI

PK_TBDEPARTMENT INDEX CUI

TBDEVICE TABLE CUI

PK_TBDEVICE INDEX CUI

TBDEVICETYPE TABLE CUI

PK_TBDEVICETYPE INDEX CUI

TBLOGINLOG TABLE CUI

PK_TBLOGININFO INDEX CUI

TBMENU TABLE CUI

PK_TBMENU INDEX CUI

TBMENUBUTTON TABLE CUI

TBPROJECT TABLE CUI

PK_TBPROJECT INDEX CUI

TBPROJECTCOMMENT TABLE CUI

TBROLE TABLE CUI

PK_TBROLES INDEX CUI

TBROLEDEVICE TABLE CUI

TBROLELAYER TABLE CUI

TBROLEMENUBUTTON TABLE CUI

TBTAG TABLE CUI

PK_TBTAG INDEX CUI

TBTAGDEVICEHISTORYDATA TABLE PARTITION CUI

TBTAGDEVICEHISTORYDATA TABLE PARTITION CUI

TBTAGDEVICEHISTORYDATA TABLE PARTITION CUI

TBTAGDEVICEHISTORYDATA TABLE CUI

TBTAGDEVICEREALDATA TABLE CUI

PK_DTV INDEX CUI

TBUSER TABLE CUI

PK_TBUSER INDEX CUI

TBUSERDEPARTMENT TABLE CUI

TBUSEROPERATELOG TABLE CUI

PK_TBUSEROPERATEINFO INDEX CUI

TBUSERROLE TABLE CUI

TB_POSITION_APPLICATIONSTATE TABLE CUI

TB_POSITION_AUTHORITY TABLE CUI

TB_POSITION_AUTHORITYTATE TABLE CUI

UESER_LAYER TABLE CUI

BOOKMARKS_MOBIEL_SEQ SEQUENCE CUI

BOOKMARK_ID_SEQ SEQUENCE CUI

LAYERTYPE_ID_SEQ SEQUENCE CUI

LAYER_ID_SEQ SEQUENCE CUI

SY_APPROVAL_ID_SEQ SEQUENCE CUI

SY_DATAPROBLEMTYPE_ID_SEQ SEQUENCE CUI

SY_PLANPIPELINE_ID_SEQ SEQUENCE CUI

SY_PLAN_ID_SEQ SEQUENCE CUI

SY_PROBLEMDATAAREA_ID_SEQ SEQUENCE CUI

TBBUG_ID_SEQ SEQUENCE CUI

TBBUTTON_ID_SEQ SEQUENCE CUI

TBDEPARTMENT_ID_SEQ SEQUENCE CUI

128 rows selected.

创建脚本存放目录

[oracle@ora15 bin]$mkdir /opt/edb/mtk/dumpfiles/

迁移命令如下

[oracle@ora15 bin]$ ./runMTK.sh -targetdbtype postgres -schemaOnly -offlineMigration /opt/edb/mtk/dumpfile/ CUI

Running EnterpriseDB Migration Toolkit (Build 51.0.1) ...

Source database connectivity info...

conn =jdbc:oracle:thin:@ip:1521:uxdb

user =cui

password=******

Connecting with source Oracle database server...

Connected to Oracle, version 'Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options'

Importing redwood schema CUI...

Creating Sequence: BOOKMARKS_MOBIEL_SEQ

Creating Sequence: BOOKMARK_ID_SEQ

Creating Sequence: LAYERTYPE_ID_SEQ

Creating Sequence: LAYER_ID_SEQ

Creating Sequence: SY_APPROVAL_ID_SEQ

Creating Sequence: SY_DATAPROBLEMTYPE_ID_SEQ

Creating Sequence: SY_PLANPIPELINE_ID_SEQ

Creating Sequence: SY_PLAN_ID_SEQ

Creating Sequence: SY_PROBLEMDATAAREA_ID_SEQ

Creating Sequence: TBBUG_ID_SEQ

Creating Sequence: TBBUTTON_ID_SEQ

Creating Sequence: TBDEPARTMENT_ID_SEQ

Creating Sequence: TBDEVICETYPE_ID_SEQ

Creating Sequence: TBDEVICE_ID_SEQ

Creating Sequence: TBLOGINLOG_ID_SEQ

Creating Sequence: TBMENU_ID_SEQ

Creating Sequence: TBPROJECTCOMMENT_ID_SEQ

Creating Sequence: TBPROJECT_ID_SEQ

Creating Sequence: TBROLE_ID_SEQ

Creating Sequence: TBTAG_ID_SEQ

Creating Sequence: TBUSEROPERATELOG_ID_SEQ

Creating Sequence: TBUSER_ID_SEQ

Creating Tables...

Creating Table: AIDEDPLANPIPEINFO

Creating Table: BOOKMARKS

Creating Table: BOOKMARKS_MOBILE

Creating Table: LAYER

Creating Table: LAYERTYPE

Creating Table: SY_APPROVAL

Creating Table: SY_DATAPROBLEMTYPE

Creating Table: SY_PLAN

Creating Table: SY_PLANPIPELINE

Creating Table: SY_PROBLEMDATAAREA

Creating Table: TBBUG

Creating Table: TBBUTTON

Creating Table: TBDEPARTMENT

Creating Table: TBDEVICE

Creating Table: TBDEVICETYPE

Creating Table: TBLOGINLOG

Creating Table: TBMENU

Creating Table: TBMENUBUTTON

Creating Table: TBPROJECT

Creating Table: TBPROJECTCOMMENT

Creating Table: TBROLE

Creating Table: TBROLEDEVICE

Creating Table: TBROLELAYER

Creating Table: TBROLEMENUBUTTON

Creating Table: TBTAG

Creating Table: TBTAGDEVICEHISTORYDATA

Creating Table: TBTAGDEVICEREALDATA

Creating Table: TBUSER

Creating Table: TBUSERDEPARTMENT

Creating Table: TBUSEROPERATELOG

Creating Table: TBUSERROLE

Creating Table: TB_POSITION_APPLICATIONSTATE

Creating Table: TB_POSITION_AUTHORITY

Creating Table: TB_POSITION_AUTHORITYTATE

Creating Table: UESER_LAYER

Created 35 tables.

Creating Constraint: PK_TBUSEROPERATEINFO

Creating Constraint: PK_TBUSER

Creating Constraint: PK_DTV

Creating Constraint: PK_TBTAG

Creating Constraint: PK_TBROLES

Creating Constraint: PK_TBPROJECT

Creating Constraint: PK_TBMENU

Creating Constraint: PK_TBLOGININFO

Creating Constraint: PK_TBDEVICETYPE

Creating Constraint: PK_TBDEVICE

Creating Constraint: PK_TBDEPARTMENT

Creating Constraint: PK_TBBUTTON

Creating Constraint: PK_TBBUG

Creating Constraint: LAYERKEY

Creating Constraint: LAYEYKEY

Creating Constraint: ID

Schema CUI imported successfully.

Migration process completed successfully.

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

通过上面的报告可以看出，导的东西并不全，只导出来序列、表和约束

我们查看生成脚本的路径下产生了5个SQL文件

MTK_cui_ddl.sql是总的脚本文件，其他的文件都是基于各个对象类型分开的脚本。

[oracle@ora15 dumpfile]$ vi mtk_cui_ddl.sql

我们尝试手动指定导出对象类型，看看能不能导出来。

导存储过程：

[oracle@ora15 dumpfile]$ ../bin/runMTK.sh -targetdbtype postgres -offlineMigration /opt/edb/mtk/dumpfile/ -allProcs CUI

Running EnterpriseDB Migration Toolkit (Build 51.0.1) ...

MTK-13010:You can not migrate procedures, packages, synonyms and database links to PostgreSQL database.

MTK-02001:Run runMTK -help to see the usage details.

这里直接报错，提示不能导存储过程，包，同义词，dblink到pg

导视图：

[oracle@ora15 dumpfile]$ ../bin/runMTK.sh -targetdbtype postgres -offlineMigration /opt/edb/mtk/dumpfile/ -allViews CUI

Running EnterpriseDB Migration Toolkit (Build 51.0.1) ...

Source database connectivity info...

conn =jdbc:oracle:thin:@10.1.0.38:1521:uxdb

user =cui

password=******

Connecting with source Oracle database server...

Connected to Oracle, version 'Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options'

Importing redwood schema CUI...

Schema CUI imported successfully.

Migration process completed successfully.

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

这里我们看到没有报错，但是我们发现，实际的文件里面并没有视图的脚本

查看mtk_cui_ddl.sql脚本里面啥也没有：

这里视图没有被迁移出来很有可能是因为视图的某些语法和PostgreSQL的语法不同。这个视图有的时候也可以迁移出来，所以说这里特别要注意，迁移视图等对象不一定能迁移出来。

导入的话我这里就不多说了，进入到pg当中使用\i或copy都可以执行脚本。

总结：

1.EDB Migration 工具有两种方法，可以在线和离线迁移，在线方法也有可能无法保证迁移的完整性，在迁移的过程中一旦有问题他不会报错，就是不给你迁进去，这里一定注意。

2.离线的方法推荐使用，可以修改脚本，达到PostgreSQL的要求。

3.迁移当中不能迁移存储过程，同义词，触发器和DBLINK ，这些ORACLE和PostgreSQL相差较大。

THAT'S ALL

BY CUI PEACE!!!