安装oracle客户端

在gp数据库的master节点安装oracle客户端，安装过程如下。

linux（CentOS7.6）安装ORACLE客户端

安装依赖

[root@mdw admin]# yum install perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder gcc

在root下配置环境变量

[root@mdw opt]# vim ~/.bashrc
#增加
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[root@mdw ~]# source ~/.bashrc

安装 DBD-Oracle

DBD-Oracle-1.74.tar.gz 提取码：vz8r

[root@mdw admin]# cd /opt
[root@mdw opt]# rz
[root@mdw opt]# tar -zxvf DBD-Oracle-1.74.tar.gz
[root@mdw opt]# cd DBD-Oracle-1.74
[root@mdw DBD-Oracle-1.74]# perl Makefile.PL -l
Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/

Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi)

Remember to actually *READ* the README file! Especially if you have any problems.

Installing on a linux, Ver#3.10
Using Oracle in /usr/lib/oracle/11.2/client64
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
Oracle version 11.2.0.4 (11.2)
Found direct-link candidates: libclntsh.so
Oracle sysliblist: 
Found header files in /usr/include/oracle/11.2/client64.
Your LD_LIBRARY_PATH env var is set to '/usr/lib/oracle/11.2/client64/lib:'

client_version=11.2


DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.4\" -DORA_OCI_102 -DORA_OCI_112


Checking for functioning wait.ph


System: perl5.016003 linux x86-02.bsys.centos.org 3.10.0-957.1.3.el7.x86_64 #1 smp thu nov 29 14:49:43 utc 2018 x86_64 x86_64 x86_64 gnulinux 
Compiler:   gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
Linker:     /usr/bin/ld
Sysliblist: 
Linking with -lclntsh.

Checking if your kit is complete...
Looks good
LD_RUN_PATH=/usr/lib/oracle/11.2/client64/lib
Using DBD::Oracle 1.74.
Using DBD::Oracle 1.74.
Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Writing Makefile for DBD::Oracle

***  If you have problems...
     read all the log printed above, and the README and README.help.txt files.
     (Of course, you have read README by now anyway, haven't you?)

[root@mdw DBD-Oracle-1.74]# make
[root@mdw DBD-Oracle-1.74]# make install

ora2pg安装

官网：

https://sourceforge.net/projects/ora2pg/

百度云 ：

ora2pg-21.0.tar.bz2 提取码：z1c7

[root@mdw DBD-Oracle-1.74]# cd /opt
[root@mdw opt]# rz
[root@mdw opt]# tar -jxvf ora2pg-21.0.tar.bz2
[root@mdw opt]# cd ora2pg-21.0
[root@mdw ora2pg-21.0]# perl Makefile.PL
[root@mdw ora2pg-21.0]# make
[root@mdw ora2pg-21.0]# make install

检查是否安装成功

[root@mdw ora2pg-21.0]# cd ~
[root@mdw ~]# vim check.pl
#!/usr/bin/perl

use strict;
use ExtUtils::Installed;
my $inst= ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules)
{
        my $ver = $inst->version($_) || "???";
        printf("%-12s --  %s\n", $_, $ver);  
}
exit;
[root@mdw ~]# perl check.pl
DBD::Oracle  --  1.74
Ora2Pg       --  21.0
Perl         --  5.16.3

配置配置文件

[root@mdw ~]# su - gpadmin
[gpadmin@mdw ~]$ vim ora2pg.conf 
# oracle client 安装地址
ORACLE_HOME /usr/lib/oracle/11.2/client64
# oracle connent info
ORACLE_DSN dbi:Oracle:host=ipaddress;sid=orcl;port=1521
ORACLE_USER tool
ORACLE_PWD  tool
# oracle schema
SCHEMA tool
# oracle type
TYPE TABLE COPY DATA
# 输出的文件名字
OUTPUT output.sql
# 输出的路径(本地路径)
OUTPUT_DIR ./
#Ipaddress ：链接oracle的IP地址
#username : 链接oracle的用户名
#password : 链接oracle的密码
#schemaname : 链接oracle的schema信息

或者

[root@mdw ~]# su - gpadmin
[gpadmin@mdw ~]$ vim ora2pg_table.conf 
# oracle client 安装地址
ORACLE_HOME /usr/lib/oracle/11.2/client64
# oracle connent info
ORACLE_DSN dbi:Oracle:host=ipaddress;sid=orcl;port=1521
ORACLE_USER tool
ORACLE_PWD  tool
# oracle schema
SCHEMA tool
# oracle type
TYPE TABLE
# 输出的文件名字
OUTPUT table.sql
# 输出的路径(本地路径)
OUTPUT_DIR ./
[gpadmin@mdw ~]$ vim ora2pg_data.conf 
ORACLE_HOME /usr/lib/oracle/11.2/client64
ORACLE_DSN  dbi:Oracle:host=ipaddress;sid=orcl;port=1521
ORACLE_USER tool
ORACLE_PWD  tool
SCHEMA  tool
TYPE COPY
PG_NUMERIC_TYPE    0
PG_INTEGER_TYPE    1
DEFAULT_NUMERIC float
SKIP    fkeys pkeys ukeys indexes checks
NLS_LANG    AMERICAN_AMERICA.AL32UTF8
OUTPUT     data.sql

oracle导表gp

创建测试表

[gpadmin@mdw ~]$ sqlplus64
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 18:32:11 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: tool/tool@app

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test as select 1 prod_id,'宽带' prod_name from dual union all select 2,'移动' from dual;
Table created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

导出ORACLE表结构和数据

[gpadmin@mdw ~]$ time ora2pg -c ora2pg.conf -a test
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 1/1 tables (100.0%) end of scanning.   
[>                        ] 0/1 tables (0.0%) end of scanning.     
[>                        ] 0/1 tables (0.0%) end of scanning.     
[========================>] 1/1 tables (100.0%) end of table export.
[========================>] 2/1 rows (200.0%) Table TEST (2 recs/sec)
[========================>] 2/1 total rows (200.0%) - (1 sec., avg: 2 recs/sec).
[========================>] 1/1 rows (100.0%) on total estimated data (1 sec., avg: 1 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(HOST=81.70.208.91)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=app))) at /usr/local/share/perl5/Ora2Pg.pm line 7422.
[========================>] 2/1 rows (200.0%) Table TEST (2 recs/sec)                          
[========================>] 4/1 total rows (400.0%) - (0 sec., avg: 4 recs/sec).
[========================>] 1/1 rows (100.0%) on total estimated data (1 sec., avg: 1 recs/sec)
Fixing function calls in output files...

real    0m47.849s
user    0m0.157s
sys     0m0.448s

[gpadmin@mdw ~]$ cat TABLE_output.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 21.0
-- Copyright 2000-2020 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=81.70.208.91;sid=app;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

SET check_function_bodies = false;


CREATE TABLE test (
        prod_id bigint,
        prod_name char(6)
) ;

[gpadmin@mdw ~]$ cat COPY_output.sql
BEGIN;

COPY test (prod_id,prod_name) FROM STDIN;
1       宽带
2       移动
\.

COMMIT;

或者

[gpadmin@mdw ~]$ time ora2pg -c ora2pg_table.conf -a test
[gpadmin@mdw ~]$ cat table.sql
[gpadmin@mdw ~]$ time ora2pg -c ora2pg_data.conf -a test
[gpadmin@mdw ~]$ cat data.sql

将导出的表结构和数据导入GP

[gpadmin@mdw ~]$ psql -U tool -d etl -W -f table.sql 
Password for user tool: 
SET
SET
psql:table.sql:15: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'prod_id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE

[gpadmin@mdw ~]$ psql -U tool -d etl -W -f data.sql      
Password for user tool: 
BEGIN
COPY 2
COMMIT

[gpadmin@mdw ~]$ psql -U tool -d etl
Password for user tool: 
psql (9.4.24)
Type "help" for help.

etl=> select * from test;
 prod_id | prod_name 
---------+-----------
       1 | 宽带    
       2 | 移动    
(2 rows)

参考文章

Ora2pg 把oracle数据导入到postgres

ora2pg 之一 setup

Oracle迁移至PostgreSQL工具之Ora2Pg

数据大挪移之ora2pg使用指南