Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

Oracle migration to Greenplum - (含 Ora2pg)

4 minute read

背景

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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...