ora2pg 之一 setup
阳文轩
os: centos 7.4
db: postgresql 11.5
db: oracle 11.2.0.4
ora2pg 是 postgresql 的一个 tool,用于 oracle 迁移到 postgresql。
192.168.56.110 nodepg11
192.168.56.111 nodeora11g
版本
# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
#
# su - postgres
Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0
$
$ psql -c "select version();"
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
#
# su - oracle
Last login: Tue Jan 21 03:40:05 CST 2020 on pts/0
$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 10:29:09 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set lines 300;
SQL> set pages 300;
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
安装 oracle 11g instantclient 客户端
nodepg11 节点需要安装 oracle 11g instantclient，下载地址
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sdk-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
# mkdir -p /usr/oracle11g_instantclient
# cd /opt
# rz
# unzip ./instantclient-basic-linux.x64-11.2.0.4.0.zip -d /usr/oracle11g_instantclient/
# unzip ./instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /usr/oracle11g_instantclient/
# unzip ./instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /usr/oracle11g_instantclient/
# cd /usr/oracle11g_instantclient/instantclient_11_2
# ln -s libclntsh.so.11.1 libclntsh.so
# chown -R postgres:postgres /usr/oracle11g_instantclient
# vi /etc/profile
export ORACLE_HOME=/usr/oracle11g_instantclient/instantclient_11_2
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export TEMP=/tmp
export TMP=/tmp
export TMPDIR=/tmp
# vi /etc/ld.so.conf.d/oracle.conf
/usr/oracle11g_instantclient/instantclient_11_2
/usr/oracle11g_instantclient/instantclient_11_2/lib
# ldconfig
安装依赖包
nodepg11 节点安装
先安装依赖包
# yum install perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder gcc
安装 DBD-Oracle
# cd /opt
# 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
Checking if your kit is complete...
Looks good
LD_RUN_PATH=/usr/oracle11g_instantclient/instantclient_11_2
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?)
# make
# make install
ora2pg 下载，安装
nodepg11 节点安装
https://sourceforge.net/projects/ora2pg/
# cd /opt
# tar -jxvf ora2pg-20.0.tar.bz2
# cd ora2pg-20.0
# perl Makefile.PL
# make
# make install
Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg
Appending installation info to /usr/lib64/perl5/perllocal.pod
检查一下
vi /tmp/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;
# perl /tmp/check.pl
DBD::Oracle -- 1.74
Ora2Pg -- 20.0
Perl -- 5.16.3
配置
# cd /etc/ora2pg/
# cp ora2pg.conf.dist ora2pg.conf.dist.bak
# mv ora2pg.conf.dist ora2pg.conf
# vi ora2pg.conf
ORACLE_HOME /usr/oracle11g_instantclient/instantclient_11_2
ORACLE_DSN dbi:Oracle:host=192.168.56.111;service_name=orcl;port=1521
ORACLE_USER SCOTT
ORACLE_PWD oracle
USER_GRANTS 1
SCHEMA SCOTT
EXPORT_SCHEMA 0
CREATE_SCHEMA 0
TYPE TABLE
ALLOW DEPT EMP
DISABLE_COMMENT 0
OUTPUT table.sql
启动 ora2pg
# which ora2pg
/usr/local/bin/ora2pg
# ora2pg -c /etc/ora2pg/ora2pg.conf
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 2/2 tables (100.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
Fixing function calls in output files...
# pwd
/etc/ora2pg
# ls -l
total 68
-rw-r--r-- 1 root root 286 Apr 9 18:56 ora2pg.conf
-rw-r--r-- 1 root root 60583 Apr 9 18:23 ora2pg.conf.dist.bak
-rw-r--r-- 1 root root 794 Apr 9 18:56 table.sql
# cat table.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=192.168.56.220;service_name=orcl;port=1521
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
SET check_function_bodies = false;
CREATE TABLE dept (
deptno smallint NOT NULL,
dname varchar(14),
loc varchar(13)
) ;
ALTER TABLE dept ADD PRIMARY KEY (deptno);
CREATE TABLE emp (
empno smallint NOT NULL,
ename varchar(10),
job varchar(9),
mgr smallint,
hiredate timestamp,
sal decimal(7,2),
comm decimal(7,2),
deptno smallint
) ;
ALTER TABLE emp ADD PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
导入pg数据库
$ psql -U peiyb peiybdb
peiybdb=> \i /tmp/table.sql
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
peiybdb=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | dept | table | peiyb
public | emp | table | peiyb
(2 rows)
参考：
http://ora2pg.darold.net/documentation.html
https://sourceforge.net/projects/ora2pg/
类似资料：