ora2pg installation

在做oracle迁移到greenplum的POC过程中,CLOB迁移是一件比较头疼的事情,经过搜索,后来google发现可以采用ora2pg来进行。

在greenplum下安装ora2pg为例,源端oracle是11.2.0.3的数据库。
greenplum使用的是10g的client。

所需要的软件版本如下
instantclient_10_2.tar.gz
ora2pg-12.1.tar.bz2
DBD-Oracle-1.74.tar.gz

Perl版本要求
ora2pg-12.1要求的perl版本是5.6及以上
[root@greenplum ~]# perl –version
This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi

配置oracle环境变量

[root@greenplum ~]# cat .bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_HOME=/root/instantclient_10_2
export PATH=$ORACLE_HOME:$PATH  
export TNS_ADMIN=$ORACLE_HOME/network/admin  
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export NLS_LANG='american_america.AL32UTF8'

配置TNSNAME文件

[root@greenplum admin]# ls -l
total 4
-rw------- 1 root root 175 May 19 22:22 tnsnames.ora
[root@greenplum admin]# cat tnsnames.ora 
ora11g =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ora11g)
    )
  )
[root@greenplum admin]# pwd
/root/instantclient_10_2/network/admin

解压ora2pg安装文件

[root@greenplum ~]# tar -jxvf ora2pg-12.1.tar.bz2 
ora2pg-12.1/
ora2pg-12.1/LICENSE
ora2pg-12.1/MANIFEST
ora2pg-12.1/doc/
ora2pg-12.1/doc/Ora2Pg.pod
ora2pg-12.1/doc/ora2pg.3
ora2pg-12.1/lib/
ora2pg-12.1/lib/Ora2Pg/
ora2pg-12.1/lib/Ora2Pg/PLSQL.pm
ora2pg-12.1/lib/Ora2Pg.pm
ora2pg-12.1/Makefile.PL
ora2pg-12.1/scripts/
ora2pg-12.1/scripts/ora2pg
ora2pg-12.1/packaging/
ora2pg-12.1/packaging/RPM/
ora2pg-12.1/packaging/RPM/ora2pg.spec
ora2pg-12.1/packaging/slackbuild/
ora2pg-12.1/packaging/slackbuild/Ora2Pg.info
ora2pg-12.1/packaging/slackbuild/Ora2Pg.SlackBuild
ora2pg-12.1/packaging/slackbuild/doinst.sh
ora2pg-12.1/packaging/slackbuild/slack-desc
ora2pg-12.1/packaging/slackbuild/README
ora2pg-12.1/packaging/debian/
ora2pg-12.1/packaging/debian/create-deb-tree.sh
ora2pg-12.1/packaging/debian/ora2pg/
ora2pg-12.1/packaging/debian/ora2pg/DEBIAN/
ora2pg-12.1/packaging/debian/ora2pg/DEBIAN/copyright
ora2pg-12.1/packaging/debian/ora2pg/DEBIAN/control
ora2pg-12.1/packaging/README
ora2pg-12.1/INSTALL
ora2pg-12.1/changelog
ora2pg-12.1/README

安装,按照安装包里的README文件

[root@greenplum ora2pg-12.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Ora2Pg

Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: make && make install
[root@greenplum ora2pg-12.1]# make && make install
cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm
cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm
cp scripts/ora2pg blib/script/ora2pg
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg
Manifying blib/man3/ora2pg.3
Installing /usr/local/share/perl5/Ora2Pg.pm
Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm
Installing /usr/local/share/man/man3/ora2pg.3
Installing /usr/local/bin/ora2pg
Installing default configuration file (ora2pg.conf.dist) to /etc
Appending installation info to /usr/lib64/perl5/perllocal.pod

解压安装DBD-Oracle-1.74
按照软件包的安装INSTALL说明
[root@greenplum DBD-Oracle-1.74]#perl Makefile.PL
[root@greenplum DBD-Oracle-1.74]#make install

测试DBD安装是否可以工作,编写一个perl的test程序,源码如下,具体环境做相应的修改

[root@greenplum DBD-Oracle-1.74]# more test.pl 
#!/bin/perl
use DBI;
use DBD::Oracle;
$host="192.168.137.111";
$sid="ora11g";
$user="perl";
$passwd="perl";

my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1521", $user, $passwd);

my $sql = "select sysdate from dual";      # the query to execute
my $sth = $dbh->prepare($sql);          # prepare the query
$sth->execute();                        # execute the query
my @row;
while (@row = $sth->fetchrow_array) {  # retrieve one row
    print join(", ", @row), "\n";
}

[root@greenplum DBD-Oracle-1.74]# perl test.pl 
25-JUN-14

有返回值,配置正常。

Ora2pg的配置和测试使用

Ora2pg的配置文件所在的位置为/etc/ora2pg下,初始安装后这个路径下只有个模板文件ora2pg.conf.dist,需要拷贝一份在当前目录下,并命名为ora2pg.conf,需要使用ora2pg进行什么工作都需要来操作这个配置文件。

下面是我测试过程中配置的ora2pg.conf文件,只截取到修改部分,其他默认部分没有黏贴进来。

[root@greenplum ora2pg]# more ora2pg.conf

####################  Ora2Pg Configuration file   #####################
# Support for including common config file that may containt any
# of the following configuration directives.
#IMPORT common.conf

# Set this directive to a file containing PL/SQL Oracle Code like function,
# procedure or a full package body to prevent Ora2Pg from connecting to an
# Oracle database end just apply his convertion tool to the content of the
# file. This can only be used with the following export type: PROCEDURE,
# FUNCTION or PACKAGE. If you don't know what you do don't use this directive.
#INPUT_FILE     ora_plsql_src.sql

# Set the Oracle home directory
ORACLE_HOME     /root/instantclient_10_2

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=192.168.137.111;sid=ora11g
ORACLE_USER     SCOTT
ORACLE_PWD      TIGER

# Set this to 1 if you connect as simple user and can not extract things
# from the DBA_... tables. It will use tables ALL_... This will not works
# with GRANT export, you should use an Oracle DBA username at ORACLE_USER
USER_GRANTS     0

# Trace all to stderr
DEBUG           0

# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA   0

# Oracle schema/owner to use
SCHEMA          SCOTT

# Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
# It is enable by default and concern on TABLE export type.
CREATE_SCHEMA   1

# Enable this directive to force Oracle to compile schema before exporting code.
# This will ask to Oracle to validate the PL/SQL that could have been invalidate
# after a export/import for example. If you set the value to 1 will exec:
# DBMS_UTILITY.compile_schema(schema => sys_context('USERENV', 'SESSION_USER'));
# but if you probvide the name of a particular schema it will use the following
# command: DBMS_UTILITY.compile_schema(schema => 'schemaname');
COMPILE_SCHEMA  0

# If the above configuration directive is not enough to validate your PL/SQL code
# enable this configuration directive to allow export of all PL/SQL code even if
# it is marked as invalid. The 'VALID' or 'INVALID' status applies to functions,
# procedures, packages and user defined types.
EXPORT_INVALID  0

# PostreSQL search path schem to use. Can be a coma delimited list,
# for example: users_schem,public will result in the following PostgreSQL 
# schema path: SET search_path = users_schema,public;
# By default search_path is set to Oracle schema and pg_catalog.
#PG_SCHEMA      pg_catalog

# Type of export. Values can be the following keyword:
#       TABLE           Export tables, constraints, indexex, ...
#       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
TYPE            TABLE COPY

# Set which object to export from. By default Ora2Pg export all objects.
# Value must be a list of object name or regex separated by space.
#ALLOW          TABLE_TEST

# Set which object to exclude from export process. By default none.
# Value must be a list of object name or regexp separated by space.
#EXCLUDE        OTHER_TABLES

# Set which view to export as table. By default none. Value must be a list of
# view name or regexp separated by space. If the object name is a view and the
# export type is TABLE, the view will be exported as a create table statement.
# If export type is COPY or INSERT, the corresponding data will be exported.
#VIEW_AS_TABLE  VIEW_NAME

上述的红色部分是可以灵活进行配置的选项,在于希望导出后是什么样子的格式。

Ora2pg数据导出

[root@greenplum ora2pg]# ora2pg  -u scott -w tiger -o output.sql
[========================>] 5/5 tables (100.0%) end of scanning.      
[>                        ] 0/5 tables (0.0%) end of scanning.        
[========================>] 5/5 tables (100.0%) end of table export.
[>                        ] 0/1 rows (0.0%) Table BONUS (0.0 recs/sec)
[====>                    ] 1/5 rows (20.0%) on total data (avg: 1.0 recs/sec)
[========================>] 4/1 rows (400.0%) Table DEPT (4.0 recs/sec)       
[=========>               ] 2/5 rows (40.0%) on total data (avg: 2.0 recs/sec)
[========================>] 14/1 rows (1400.0%) Table EMP (14.0 recs/sec)     
[==============>          ] 3/5 rows (60.0%) on total data (avg: 3.0 recs/sec)
[========================>] 5/1 rows (500.0%) Table SALGRADE (5.0 recs/sec)   
[===================>     ] 4/5 rows (80.0%) on total data (avg: 4.0 recs/sec)
[========================>] 10000/1 rows (1000000.0%) Table TEST (9999.0 recs/sec)
[========================>] 5/5 rows (100.0%) on total data (avg: 5.0 recs/sec)

加载数据到greenplum

[gpadmin@greenplum ~]$ psql -f output.sql 
psql:output.sql:11: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named '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
psql:output.sql:18: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'deptno' 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
psql:output.sql:19: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "dept_pkey" for table "dept"
ALTER TABLE
psql:output.sql:31: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'empno' 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
psql:output.sql:32: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "emp_pkey" for table "emp"
ALTER TABLE
psql:output.sql:40: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ename' 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
psql:output.sql:47: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'grade' 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
psql:output.sql:49: WARNING:  Referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced.
ALTER TABLE
BEGIN
COMMIT
[gpadmin@greenplum ~]$ 
[gpadmin@greenplum ~]$ 
[gpadmin@greenplum ~]$ 
[gpadmin@greenplum ~]$ psql
psql (8.2.15)
Type "help" for help.

blue=# \dt
               List of relations
 Schema |   Name   | Type  |  Owner  | Storage 
--------+----------+-------+---------+---------
 test   | bonus    | table | gpadmin | heap
 test   | dept     | table | gpadmin | heap
 test   | emp      | table | gpadmin | heap
 test   | foo      | table | gpadmin | heap
 test   | foo1     | table | gpadmin | heap
 test   | salgrade | table | gpadmin | heap
 test   | test     | table | gpadmin | heap
(7 rows)
blue=# select count(*) from test;
count 
-------
10000
(1 row)
blue=#

源库的表和记录

[oracle@ora11g ~]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 25 02:59:22 2014

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


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

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST                           TABLE

SQL> select count(*) from test;

  COUNT(*)
----------
     10000

迁移完成。
ora2pg的错误

HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by “client_encoding”.

上述错误是因为源端的数据库编码不是UTF8所致,出现这类错误首先要使用
ora2pg -t SHOW_ENCODING查看一下字符集,然后在导出的时候使用ora2pg自带的参数–nls_lang AMERICAN_AMERICA.UTF8来处理。
[root@greenplum ~]# ora2pg –nls_lang AMERICAN_AMERICA.UTF8

此条目发表在greenplum, postgresql分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s