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 <CHAR> 如果空字符是列值得一部分，那么通过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

SYS_IL0000091311C00035$$       INDEX           CUI

SYS_LOB0000091311C00035$$      LOB           CUI

SYS_IL0000091311C00034$$       INDEX           CUI

SYS_LOB0000091311C00034$$      LOB           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

SYS_IL0000091327C00009$$       INDEX           CUI

SYS_LOB0000091327C00009$$      LOB           CUI

TBBUG                   TABLE           CUI

SYS_IL0000091330C00005$$       INDEX           CUI

SYS_LOB0000091330C00005$$      LOB           CUI

SYS_IL0000091330C00004$$       INDEX           CUI

SYS_LOB0000091330C00004$$      LOB           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

SYS_IL0000091368C00005$$       INDEX           CUI

SYS_LOB0000091368C00005$$      LOB           CUI

PK_TBUSEROPERATEINFO           INDEX           CUI

TBUSERROLE               TABLE           CUI

TB_POSITION_APPLICATIONSTATE   TABLE           CUI

TB_POSITION_AUTHORITY           TABLE           CUI

SYS_IL0000091374C00005$$       INDEX           CUI

SYS_LOB0000091374C00005$$      LOB           CUI

SYS_IL0000091374C00004$$       INDEX           CUI

SYS_LOB0000091374C00004$$      LOB           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!!!