Oracle migration to Greenplum - (含 Ora2pg)

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , Oracle , Greenplum , PL/SQL , Ora2pg


背景

Oracle在OLTP领域毫无疑问是非常不错的数据库,但是OLAP领域,可以有更好的选择,特别是在数据量大到一定程度的时候,Oracle用来做数据分析会显得有点力不从心。

Greenplum是一个很好的替代Oracle来处理OLAP业务场景的数据库,支持多机并行、列存储、丰富的OLAP SQL语法,存储过程(plpgsql, plR, plpython,等),MADlib机器学习库。。。。

如何将Oracle迁移到Greenplum?

Ora2pg是一个不错的工具,目前支持Oracle, mysql, 迁移到PostgreSQL。由于Greenplum与PostgreSQL兼容,所以也可以用来迁移到Greenplum。

FEATURES INCLUDED

  • Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
  • Export grants/privileges for users and groups.
  • Export range/list partitions and sub partitions.
  • Export a table selection (by specifying the table names).
  • Export Oracle schema to a PostgreSQL 8.4+ schema.
  • Export predefined functions, triggers, procedures, packages and package bodies.
  • Export full data or following a WHERE clause.
  • Full support of Oracle BLOB object as PG BYTEA.
  • Export Oracle views as PG tables.
  • Export Oracle user defined types.
  • Provide some basic automatic conversion of PLSQL code to PLPGSQL.
  • Works on any plateform.
  • Export Oracle tables as foreign data wrapper tables.
  • Export materialized view.
  • Show a detailled report of an Oracle database content.
  • Migration cost assessment of an Oracle database.
  • Migration difficulty level assessment of an Oracle database.
  • Migration cost assessment of PL/SQL code from a file.
  • Migration cost assessment of Oracle SQL queries stored in a file.
  • Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
  • Export Oracle locator and spatial geometries into PostGis.
  • Export DBLINK as Oracle FDW.
  • Export SYNONYMS as views.
  • Export DIRECTORY as external table or directory for external_file extension.
  • Full MySQL export just like Oracle database.

(支持DDL,PL/SQL的转换)

demo

一个简单的DEMO,测试一下Ora2pg导出。

install Ora2pg

1、

yum install -y perf cpan  

2、安装oracle-instantclient安装包,oracle官网下载basic、devel、sqlplus三个rpm包。

http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

3、rpm -ivh oracle-instantclient*.rpm

4、

echo  "/usr/lib/oracle/12.2/client64/lib" > /etc/ld.so.conf.d/oracle_client.conf  
  
ldconfig  
  
ldconfig -p|grep oracle  
        libsqlplusic.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libsqlplusic.so  
        libsqlplus.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libsqlplus.so  
        liboramysql12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/liboramysql12.so  
        libons.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libons.so  
        libocijdbc12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocijdbc12.so  
        libociei.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libociei.so  
        libocci.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so.12.1  
        libocci.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so  
        libnnz12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libnnz12.so  
        libmql1.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libmql1.so  
        libipc1.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libipc1.so  
        libclntshcore.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1  
        libclntshcore.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntshcore.so  
        libclntsh.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1  
        libclntsh.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntsh.so  

5、测试 sqlplus username/password@ip:port/sid

sqlplus64 test/test@xxx.xxx.xxx.xxx:1521/ora11g  

6、安装 DBI

cpan install DBI  

7、安装 DBD-Oracle

export ORACLE_HOME=/usr/lib/oracle/12.2/client64  
export PATH=$ORACLE_HOME/bin:$PATH  
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH  
wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz  
tar -zxvf DBD-Oracle-1.74.tar.gz  
  
cd DBD-Oracle-1.74  
  
perl Makefile.PL -l  
make && make test  
make install  

8、Ora2pg install

wget https://github.com/darold/ora2pg/archive/v18.2.tar.gz  
  
tar -zxvf v18.2.tar.gz   
  
cd ora2pg-18.2/  
  
perl Makefile.PL  
make && make install  

9、导出测试

编辑导出配置文件

vi ora.conf  
  
#---------------------------------#  
  
#---------------------------------#  
  
# Set the Oracle home directory  
ORACLE_HOME /usr/lib/oracle/12.2/client64  
  
# Set Oracle database connection (data source, user, password)  
ORACLE_DSN dbi:Oracle:host=xxx.xxx.xxx.xxx;sid=ora11g;port=1521  
ORACLE_USER test  
ORACLE_PWD test  
# Oracle schema/owner to use  
#SCHEMA SCHEMA_NAME  
SCHEMA  test  
#--------------------------  
# EXPORT SECTION (Export type and filters)  
#--------------------------  
  
# Type of export. Values can be the following keyword:  
# TABLE Export tables, constraints, indexes, …  
# PACKAGE Export packages  
# INSERT Export data from table as INSERT statement  
# COPY Export data from table as COPY statement  
# VIEW Export views  
# GRANT Export grants  
# SEQUENCE Export sequences  
# TRIGGER Export triggers  
# FUNCTION Export functions  
# PROCEDURE Export procedures  
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)  
# TYPE Export user-defined Oracle types  
# PARTITION Export range or list partition (PostgreSQL >= v8.4)  
# FDW Export table as foreign data wrapper tables  
# MVIEW Export materialized view as snapshot refresh view  
# QUERY Convert Oracle SQL queries from a file.  
# KETTLE Generate XML ktr template files to be used by Kettle.  
  
TYPE PROCEDURE FUNCTION  
# By default all output is dump to STDOUT if not send directly to PostgreSQL  
# database (see above). Give a filename to save export to it. If you want  
# a Gzip’d compressed file just add the extension .gz to the filename (you  
# need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2  
# compression.  
OUTPUT output.sql  
# Base directory where all dumped files must be written  
#OUTPUT_DIR /var/tmp  
OUTPUT_DIR /tmp  

使用配置文件导出

ora2pg -c ./ora.conf  

检查导出结果

cat /tmp/output.sql  
  
[========================>] 3/3 procedures (100.0%) end of procedures export.  
[========================>] 0/0 functions (100.0%) end of functions export.    
[root@iZbp13nu0s9j3x3op4zpd4Z ~]# cat /tmp/output.sql   
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2  
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.  
-- DATASOURCE: dbi:Oracle:host=xxx.xxx.xxx.xxx;sid=ora11g  
  
SET client_encoding TO 'UTF8';  
  
\set ON_ERROR_STOP ON  
  
SET check_function_bodies = false;  
  
  
  
CREATE OR REPLACE FUNCTION p_cursor () RETURNS VOID AS $body$  
DECLARE  
  
nls_settings CURSOR FOR SELECT parameter,value from nls_session_Parameters;  
  
BEGIN  
RAISE NOTICE 'a';  
end;  
$body$  
LANGUAGE PLPGSQL  
;  
-- REVOKE ALL ON FUNCTION p_cursor () FROM PUBLIC;  
  
  
  
CREATE OR REPLACE FUNCTION p2 () RETURNS VOID AS $body$  
DECLARE  
  
        name varchar(20) := 'oomdb';  
  
BEGIN  
        RAISE NOTICE '%', name;  
end;  
$body$  
LANGUAGE PLPGSQL  
;  
-- REVOKE ALL ON FUNCTION p2 () FROM PUBLIC;  
  
  
  
CREATE OR REPLACE FUNCTION p1 () RETURNS VOID AS $body$  
BEGIN  
        RAISE NOTICE 'aaa';  
end;  
$body$  
LANGUAGE PLPGSQL  
;  
-- REVOKE ALL ON FUNCTION p1 () FROM PUBLIC;  
  
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2  
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.  
-- DATASOURCE: dbi:Oracle:host=xxx.xxx.xxx.xxx;sid=ora11g  
  
SET client_encoding TO 'UTF8';  
  
\set ON_ERROR_STOP ON  
  
SET check_function_bodies = false;  
  
-- Nothing found of type FUNCTION  

output.sql的内容在某个Greenplum或PostgreSQL库执行,完成DDL的迁移。

Ora2pg的详细使用请参考文档:

http://ora2pg.darold.net/documentation.html

参考

http://ora2pg.darold.net/documentation.html#plsql_to_plpsql_convertion

https://metacpan.org/pod/DBD::Oracle

https://ilparle.com/2017/04/25/ora2pg-to-export-plsql-to-plpgsql/

https://www.pgcon.org/2011/schedule/attachments/205_Oracle_to_Postgres_Migration.pdf

http://www.ispirer.com/products/convert-oracle-to-greenplum

https://gpdb.docs.pivotal.io/43240/ref_guide/extensions/pl_sql.html#topic10

https://www.postgresql.org/docs/8.2/static/plpgsql-statements.html

http://g14n.info/2013/07/how-to-install-dbdoracle/

ora2pg conf模板

/etc/ora2pg/ora2pg.conf.dist

ora2pg帮助文档

man ora2pg
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
Oracle 关系型数据库 数据库
|
SQL Oracle 关系型数据库
【学习资料】第1期Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
大家好,这里是Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
|
Oracle 关系型数据库 测试技术
oracle 同步数据 greenplum linux kettle
gh 加油 最近公司在做项目时,使用oracle采集底层数据,使用greenplum分析加工数据,数据交换使用的是kettle。
3048 0
|
23小时前
|
存储 Oracle 关系型数据库
关系型数据库Oracle运行RMAN脚本
【7月更文挑战第23天】
7 4
|
3天前
|
存储 监控 Oracle
关系型数据库Oracle备份策略建议
【7月更文挑战第21天】
12 6

推荐镜像

更多