文档章节

Oracle数据库迁移到PostgreSQL/EDB初探

Rocky-Wang
 Rocky-Wang
发布于 2015/04/17 16:29
字数 3237
阅读 663
收藏 37
点赞 0
评论 1

由于某些非技术方面的原因,我们也在搞一些开源数据库引入,替换商业数据库,于是瞄上了PostgreSQL。


PostgreSQL本身的技术不在这里做介绍,虽然国内PostgreSQL没有Mysql那么流行,但是搜索一下,还是能够找到不少PostgreSQL中文的一些技术资料, 其中PG的官方文档是最详尽且最有助于学习PG的:

最新的9.4版本的online doc:http://www.postgresql.org/docs/9.4/static/index.html 

山东瀚高科技提供的中文翻译文档:http://www.highgo.com.cn/docs/docs90cn/


Oracle迁移到PostgreSQL:

比较流行的迁移工具 ora2pg:  http://ora2pg.darold.net/config.html#testing

大致原理:

该工具使用perl写的, 主要迁移功能都由ora2pg.conf 这个参数文件控制,该参数文件定义源库Oracle和目标库的DSN,通过DBD::Oracle Perl 、 DBD::Pg Perl 的module连接到源库和目标库, 按照用户制定的参数,导出源库Oracle的相关对象结构(也可以连数据一起导),导入到目标库PG。 导入可以是在线的(导出脚本不落地直接导入PG),也可以先生成导出脚本到本地,手动修改复核后,再手动使用psql导入PG。 

ora2pg命令行各参数使用也比较清晰明了, 支持各种导出导入选项,比如你可以只导某些schema或者对象,或者排除某些schema或者对象不导, 也可以什么都不做,只对源库做一个迁移PG的分析,出具一份报告等等。

具体使用方法,还是自己去实际尝试会比较好。 


使用Ora2pg的方案将Oracle迁移至PG, 遇到的问题多少与源Oracle数据库有多少与PG不兼容的东西成正比。下面是我们遇到的问题简单总结:

应用程序里面sqlmap.xml 人工REVIEW时的问题发现:

Oracle PostgreSQL
dual表 没有dual表,可以直接select 1、select user、select xxx
时间函数(sysdate)

current_date

current_time

current_timestamp

trunc trunc/date trunc(时间截断函数)
sys_guid() 有类似sys_guid的函数uuid_generate_v4,但需要安装,create extension "uuid-oosp".
nvl PG可以使用COALESCE
where rownum < ... select row_number() over() , * from XXXX或者limit



PG导入ora2pg产生的迁移脚本时发现的问题:

tables      

DEFAULT Sys_guid();

session# serial# 字段

DEFAULT uuid_generate_v4;

去掉#

partitions   父表在tables中已创建,创建子表时,由于大小写问题提示找不到父表。 表名称区分大小写,继承父表时,大小写改为与父表相同。增加或减少分区要修改触发器函数。
synonyms  

转换过来的语句类似如下:

CREATE VIEW public.tablename AS SELECT * FROM owner.tablename SECURITY DEFINER;

PG中没有同义词,自动创建为视图,转换过来的视图名称与存在的表名相同,需要修改视图名称。

SECURITY DEFINER不能加到创建语句的后面,可通过授权来控制权限。

packages    

1、PG中没有v$session.(oracle原来的packages里面大多有这个)

select sid, serial# into v_sid, v_serial# from v$session

2、有些转换过来的语句顺序不正确,需要重构。

3、packages自动转换为function,且function会创建到以原来packages命名的schema下

ora2pg会把Oracle里面的package header转换为同名的schema
procedures  

1、dbms_output.put_line('成功插入数据!');-->RAISE NOTICE '%',('成功插入数据!');

2、 dbms_output.put_line(sqlerrm) --- RAISE NOTICE '%', sqlerrm;

3、表的子查询必须包围在圆括弧里并且必须赋予一个别名

4、start with connect by 递归查询在PG中WITH RECURSIVE

views   

1、字符类型问题

2、递归查询没有转换成功

3、外连接中的“+”号没有转换

4、DECODE函数需要重构

5、COALESCE 函数返回值类型不匹配

1、字符类型需要调整。

2、start with connect by 递归查询在PG中WITH RECURSIVE

3、 (+)这样的外连接写法需要调整为SQL标准的 table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON (...);

4、DECODE函数需要重构成(case when some_column = 'some_value' then 'some_other_value' when ... then ... else 'some_default_value' end ) as some_column;

5、COALESCE 函数返回值类型不匹配、需要类型转换。

等等。

可以看到直接将Oracle迁移到PostgreSQL,无论是应用层,还是数据库层面, 都还是有很多改写工作要做的。 

由于有上述诸多问题,我们研究了一下号称为替换Oracle而生的EnterpriseDB(PostgreSQL的商业版):

http://www.enterprisedb.com/products-services-training/products/documentation/enterpriseedition

EnterpriseDB的安装指引:

http://www.enterprisedb.com/docs/en/9.4/instguide/toc.html

EnterpriseDB的官方文档:

http://www.enterprisedb.com/docs/en/9.4/eeguide/toc.html


EnterpriseDB的Oracle迁移PG的工具Migration Toolkit使用指引:

http://www.enterprisedb.com/docs/en/9.4/migrate/toc.html

工具使用比较简单,简单配置一些参数接口, 迁移过程发现的问题相比ora2pg少了很多,但还是有一些:

例如EDB中没有function sys_connect_by_path函数、 oracle trigger语法里面的referencing new as new old as old等。


结论:

虽然EDB的Migration Toolkit这个迁移工具本身没有像ora2pg那么多灵活的迁移选项组合, 但是其功能基本满足迁移需求,而且重要的是由于EDB在PostgreSQL的基础上,外面包了一层,实现了很多Oracle的概念,例如前面提到的dual表、同义词、pkg、内建函数,甚至Oracle的分区表语法可以直接在EDB运行。所以,同样的源Oracle数据库,我们发现90%左右的都可以直接在目标EnterpriseDB运行了,只需要修改一少部分。 


具体EnterpriseDB的兼容Oracle的清单,参考

http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server?quicktabs_advanceservertab=3#quicktabs-advanceservertab


所以,从节省成本与运维稳定两方面平衡考虑, 对于新建系统直接采用PostgreSQL,对于已有系统迁移到PostgreSQL,使用EnterpriseDB也不失为一个过渡期的较好选择。 


附录:

ora2pg --help


Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]


    -a | --allow str  : coma separated list of objects to allow from export.

Can be used with SHOW_COLUMN too.

    -b | --basedir dir: Used to set the default output directory, where files

resulting from exports will be stored.

    -c | --conf file  : Used to set an alternate configuration file than the

default /etc/ora2pg/ora2pg.conf.

    -d | --debug      : Enable verbose output.

    -e | --exclude str: coma separated list of objects to exclude from export.

Can be used with SHOW_COLUMN too.

    -h | --help       : Print this short help.

    -i | --input file : File containing Oracle PL/SQL code to convert with

no Oracle database connection initiated.

    -j | --jobs num   : number of parallel process to send data to PostgreSQL.

    -J | --copies num : number of parallel connection to extract data from Oracle.

    -l | --log file   : Used to set a log file. Default is stdout.

    -L | --limit num  : number of tuples extracted from Oracle and stored in

memory before writing, default: 10000.

    -n | --namespace schema : Used to set the Oracle schema to extract from.

    -o | --out file   : Used to set the path to the output file where SQL will

be written. Default: output.sql in running directory.

    -p | --plsql      : Enable PLSQL to PLPSQL code conversion.

    -P | --parallel num: Number of parallel tables to extract at the same time.

    -q | --quiet      : disable progress bar.

    -s | --source DSN : Allow to set the Oracle DBI datasource.

    -t | --type export: Used to set the export type. It will override the one

given in the configuration file (TYPE).

    -u | --user name  : Used to set the Oracle database connection user.

    -v | --version    : Show Ora2Pg Version and exit.

    -w | --password pwd : Used to set the password of the Oracle database user.

    --forceowner: if set to 1 force ora2pg to set tables and sequences owner

 like in Oracle database. If the value is set to a username this

 one will be used as the objects owner. By default it's the user

 used to connect to the Pg database that will be the owner.

    --nls_lang code: use this to set the Oracle NLS_LANG client encoding.

    --client_encoding code: Use this to set the PostgreSQL client encoding.

    --view_as_table str: coma separated list of view to export as table.

    --estimate_cost   : activate the migration cost evalution with SHOW_REPORT

    --cost_unit_value minutes: number of minutes for a cost evalution unit.

 default: 5 minutes, correspond to a migration conducted by a

 PostgreSQL expert. Set it to 10 if this is your first migration.

   --dump_as_html     : force ora2pg to dump report in HTML, used only with

                        SHOW_REPORT. Default is to dump report as simple text.

   --init_project NAME: initialise a typical ora2pg project tree. Top directory

                        will be created under project base dir.

   --project_base DIR : define the base dir for ora2pg project trees. Default

                        is current directory.


See full documentation at http://ora2pg.darold.net/ for more help or see manpage with 'man ora2pg'.



EDB的Migration Toolkit:


runMTK.sh -help

Running EnterpriseDB Migration Toolkit (Build 48.0.1) ...


EnterpriseDB Migration Toolkit (Build 48.0.1)


Usage: runMTK [-options] SCHEMA


If no option is specified, the complete schema will be imported.


where options include:

-helpDisplay the application command-line usage.

-versionDisplay the application version information.

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


-schemaOnlyImport the schema object definitions only.

-dataOnlyImport the table data only. When -tables is in place, it imports data only for the selected tables. Note: If there are any FK constraints defined on target tables, use -truncLoad option along with this option.


-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, enterprisedb. 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, enterprisedb. db_type is case-insensitive. By default, db_type is enterprisedb.


-allTablesImport all tables.

-tables LISTImport comma-separated list of tables.

-constraintsImport the table constraints.

-indexesImport the table indexes.

-triggersImport the table triggers.

-allViewsImport all Views.

-views LISTImport comma-separated list of Views.

-allProcsImport all stored procedures.

-procs LISTImport comma-separated list of stored procedures.

-allFuncsImport all functions.

-funcs LISTImport comma-separated list of functions.

-allPackagesImport all packages.

-packages LIST Import comma-separated list of packages.

-allSequencesImport 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).

-allDBLinksImport all Database Links.

-allSynonymsIt 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.

-allPublicSynonymsIt 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.

-allPrivateSynonymsIt 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.


-dropSchema [true|false] Drop the schema if it already exists in the target database (default: false).

-truncLoadIt 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.

-safeModeTransfer data in safe mode using plain SQL statements.

-copyDelimiterSpecify a single character to be used as delimiter in copy command when loading table data. Default is \t

-batchSizeSpecify the Batch Size to be used by the bulk inserts. Valid values are  1-1000, 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 > 0, 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.

-filterPropThe properties file that contains table where clause.

-skipFKConstSkip migration of FK constraints.

-skipCKConstSkip migration of Check constraints.

-ignoreCheckConstFilterBy default MTK does not migrate Check constraints and Default clauses from Sybase, use this option to turn off this filter.

-fastCopyBypass WAL logging to perform the COPY operation in an optimized way, default disabled.

-customColTypeMapping LISTUse 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_FILEThe 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 commad. This option can only be used in Oracle to EnterpriseDB migration mode.

-singleDataFileUse 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

-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

-objecttypesImport 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 VALUESpecify 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 VALUESpecify the number of jobs (threads) to perform data load in parallel. The VALUE parameter should be greater than 0, default is 1.

-logFileSize VALUEIt represents the maximum file size limit (in MB) before rotating to a new log file, defaults to 50MB.

-logFileCount VALUEIt 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.

-useOraCaseIt preserves the identifier case while migrating from Oracle, except for functions, procedures and packages unless identifier names are given in quotes. 

-logBadSQLIt saves the DDL scripts for the objects that fail to migrate, in a .sql file in log folder.

-targetDBVersionIt 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.4 for EnterpriseDB database].


Database Connection Information:

The application will read the connectivity information for the source and target database servers from toolkit.properties file.

Refer to MTK readme document for more information.





© 著作权归作者所有

共有 人打赏支持
Rocky-Wang
粉丝 4
博文 34
码字总数 43517
作品 0
深圳
数据库管理员
加载中

评论(1)

白豆腐徐长卿
白豆腐徐长卿
写的不错。
EDB PPAS(Oracle 兼容版) Oracle与PostgreSQL 兼容模式的参数配置切换

标签 PostgreSQL , EDB , PPAS , 参数 , Oracle模式 , PostgreSQL模式 背景 EDB PPAS是EDB推出的一款同时兼容Oracle和PostgreSQL协议的数据库,在去O的场景中,使用非常广泛,价格便宜,同时...

德哥
05/06
0
0
2014PostgreSQL用户大会PPT下载

由国内PostgreSQL的志愿者发起的“2014PostgreSQL用户大会”于2014年12月12日如期在深圳召开(本站召集帖),“PostgreSQL用户大会”是一个旨在促进PostgreSQL在中国发展的非营利性的会议。感...

永和
2014/12/17
0
17
多平台向Postgresql 迁移

最近有个工作要把 一个SqlServer 迁移到 Postgresql 平台上,上网搜了搜,EnterpriseDB在这块做了很多工作, EnterpriseDB 的 Postgres Plus Advanced Server管理工具中有一个 mogiration t...

从前
2013/01/30
0
0
阿里云如何打破Oracle 迁移上云的壁垒

  【IT168 评论】摘要:2018第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指“数据库管理系统”,面对Oracle迁移上...

it168网站
05/23
0
0
阿里云如何打破Oracle迁移上云的壁垒

2018第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指“数据库管理系统”,面对Oracle迁移上云的壁垒,阿里云如何能够...

云迹九州
05/22
0
0
DevOps 资讯 | PostgreSQL 的时代到来了吗 ?

PostgreSQL是对象-关系型数据库,BSD 许可证。拼读为"post-gress-Q-L"。 作者: Tony Baer 原文: Has the time finally come for PostgreSQL?(有删节) 近30年来 PostgreSQL 无疑是您从未听...

RiboseYim
06/23
0
0
Oracle migration to Greenplum - (含 Ora2pg)

标签 PostgreSQL , Oracle , Greenplum , PL/SQL , Ora2pg 背景 Oracle在OLTP领域毫无疑问是非常不错的数据库,但是OLAP领域,可以有更好的选择,特别是在数据量大到一定程度的时候,Oracle用...

德哥
05/06
0
0
PostgreSQL 11 preview - Faster partition pruning

标签 PostgreSQL , 分区 , 继承 , constraint_exclusion 背景 目前PG社区版本内置分区表的性能受制于一些BIND、LOCK所有子表等问题,当分区过多时,性能会受到极大的影响。 《分区表锁粒度差...

德哥
05/06
0
0
Oracle DBA 增值 PostgreSQL,Greenplum 学习计划

标签 PostgreSQL , Oracle , Greenplum 背景 去O很大程度上是国家层面的战略考虑,比如斯诺登事件,最近贸易战的“中兴”事件,使得去O成为一个不可不做的事情。 但是去O喊了若干年,并没有真...

德哥
05/06
0
0
PostgreSQL 分区表的逻辑复制(逻辑订阅)

标签 PostgreSQL , 分区表 , 逻辑复制 , 逻辑订阅 , 分区 背景 PostgreSQL 10 引入了内置分区语法,同时引入了逻辑订阅的功能。 《PostgreSQL 10.0 preview 逻辑订阅 - 原理与最佳实践》 逻辑...

德哥
05/06
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

微信小程序Java登录流程(ssm实现具体功能和加解密隐私信息问题解决方案)

文章有不当之处,欢迎指正,如果喜欢微信阅读,你也可以关注我的微信公众号:好好学java,获取优质学习资源。 一、登录流程图 二、小程序客户端 doLogin:function(callback = () =>{}){let ...

公众号_好好学java
37分钟前
0
1
流利阅读笔记28-20180717待学习

“我不干了!” 英国脱欧大臣递交辞呈 雪梨 2018-07-17 1.今日导读 7 月 6 日,英国政府高官齐聚英国首相的官方乡间别墅——契克斯庄园,讨论起草了一份关于英国政府脱欧立场的白皮书。可是没...

aibinxiao
今天
7
0
OSChina 周二乱弹 —— 理解超算排名这个事,竟然超出了很多人的智商

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @-冰冰棒- :分享Ed Sheeran/Beyoncé的单曲《Perfect Duet (with Beyoncé)》 《Perfect Duet (with Beyoncé)》- Ed Sheeran/Beyoncé 手机...

小小编辑
今天
180
7
Android 获取各大音乐平台的真实下载地址

废话 电脑使用谷歌浏览器或者QQ浏览器的时候。。。。。。。说不清楚,还是看图吧 大概意思就是,只要网页上需要播放,只要能播放并且开始播放,这个过程就肯定会请求到相关的音乐资源,然后就...

她叫我小渝
今天
0
0
shell中的函数、shell中的数组、告警系统需求分析

shell中的函数 格式: 格式: function f_name() { command } 函数必须要放在最前面 示例1(用来打印参数) 示例2(用于定义加法) 示例3(用于显示IP) shell中的数组 shell中的数组1 定义数...

Zhouliang6
今天
2
0
用 Scikit-Learn 和 Pandas 学习线性回归

      对于想深入了解线性回归的童鞋,这里给出一个完整的例子,详细学完这个例子,对用scikit-learn来运行线性回归,评估模型不会有什么问题了。 1. 获取数据,定义问题     没有...

wangxuwei
今天
1
0
MAC安装MAVEN

一:下载maven压缩包(Zip或tar可选),解压压缩包 二:打开终端输入:vim ~/.bash_profile(如果找不到该文件新建一个:touch ./bash_profile) 三:输入i 四:输入maven环境变量配置 MAVEN_HO...

WALK_MAN
今天
0
0
33.iptables备份与恢复 firewalld的9个zone以及操作 service的操作

10.19 iptables规则备份和恢复 10.20 firewalld的9个zone 10.21 firewalld关于zone的操作 10.22 firewalld关于service的操作 10.19 iptables规则备份和恢复: ~1. 保存和备份iptables规则 ~2...

王鑫linux
今天
2
0
大数据教程(2.11):keeperalived+nginx高可用集群搭建教程

上一章节博主为大家介绍了目前大型互联网项目的系统架构体系,相信大家应该注意到其中很重要的一块知识nginx技术,在本节博主将为大家分享nginx的相关技术以及配置过程。 一、nginx相关概念 ...

em_aaron
今天
1
1
Apache Directory Studio连接Weblogic内置LDAP

OBIEE默认使用Weblogic内置LDAP管理用户及组。 要整理已存在的用户及组,此前办法是导出安全数据,文本编辑器打开认证文件,使用正则表达式获取用户及组的信息。 后来想到直接用Apache Dire...

wffger
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部