本文介绍ora2pg迁移到pg12的用法
ora2pg官网：Ora2Pg：将 Oracle 迁移到 PostgreSQL (darold.net)
1、安装oracle
1.1、oracle11g在本地
无动作
1.2、oracle非本地
#libsqlplus依赖库
yum install libnsl2-devel libnsl2 -y
ln -s /usr/lib64/libnsl.so.2 /usr/lib64/libnsl.so.1
#下面2个库安装DBD-Oracle时候冲突，我卸载了
yum remove libxcrypt-devel-4.1.1-6.el8.x86_64
yum remove man-pages-5.02-6.ky10.noarch安装Oracle Instant Client。下载地址 Instant Client for Linux x86-64 (64-bit) | Oracle Hong Kong SAR, PRC
找到对应版本，我下载的是ZIP版本。
- instantclient-basic-linux.x64-11.2.0.4.0.zip
- instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
- instantclient-jdbc-linux.x64-11.2.0.4.0.zip
- instantclient-sdk-linux.x64-11.2.0.4.0.zip
useradd oracle
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /home/oracle
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /home/oracle
unzip instantclient-jdbc-linux.x64-11.2.0.4.0.zip -d /home/oracle
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /home/oracle
cd /home/oracle/instantclient_11_2
vi /etc/profile
ORACLE_BASE=/home/oracle/instantclient_11_2
ORACLE_HOME=/home/oracle/instantclient_11_2
LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME
export ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH
source /etc/profile
#创建
mkdir -p /home/oracle/instantclient_11_2/network/admin
cd /home/oracle/instantclient_11_2/network/admin
vi tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.197)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
2、安装pg12
pg12
3、安装perl
官网：DBI-1.643 - Database independent interface for Perl - metacpan.org
DBI，Database Independent Interface，是Perl语言连接数据库的接口
yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
cd /root
wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.643.tar.gz
tar -xvf DBI-1.643.tar.gz
cd DBI-1.643
perl Makefile.PL
make
make install4、安装DBD::Pg模块
DBD-Pg-3.18.0 - DBI PostgreSQL interface - metacpan.org
The usual steps to install DBD::Pg:
1. perl Makefile.PL
2. make
3. make test
4. make install
Do steps 1 to 2 as a normal user, not as root!
cd /opt
wget https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.18.0.tar.gz
tar -xvf DBD-Pg-3.18.0.tar.gz
#授权给非root用户
chown pg127:pg127 DBD-Pg-3.18.0 -R
su - pg127
cd /opt/DBD-Pg-3.18.0
export POSTGRES_INCLUDE="/usr/local/pg127/include"
export POSTGRES_LIB="/usr/local/pg127/lib"
perl Makefile.PL
make
#切换到root
su - root
cd /opt/DBD-Pg-3.18.0
make install5、安装DBD::Oracle模块
DBD::Oracle - Oracle database driver for the DBI module - metacpan.org
cd /opt
wget https://cpan.metacpan.org/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz
tar -xvf DBD-Oracle-1.74.tar.gz
cd DBD-Oracle-1.74/
perl Makefile.PL
make && make install6、安装ora2pg
6.1、安装ora2pg
cd /opt
wget https://sourceforge.net/projects/ora2pg/files/24.3/ora2pg-24.3.tar.gz/download
tar -xvf download
cd ora2pg-24.3/
perl Makefile.PL
make
make install6.2、验证软件
测试依赖库是否安装成功
[root@baidu ora2pg-24.3]#vi check.pl
cat 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@baidu ora2pg-24.3]#perl check.pl
DBD::Oracle -- 1.74
DBD::Pg -- 3.18.0
DBI -- 1.643
Ora2Pg -- 24.3
Perl -- 5.28.36.3、测试oracle连通性
注意:SCHEMA需要填用户，oracle没有模式概念
/opt/ora2pg
[root@baidu ora2pg]# vi ora2pg_table.conf
ORACLE_HOME /home/oracle/instantclient_11_2
ORACLE_DSN dbi:Oracle:host=192.168.10.197;sid=orcl
ORACLE_USER wangdp
ORACLE_PWD aaaaaa
SCHEMA wangdp
TYPE TABLE
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT /opt/ora2pg/table.sql
#执行
[root@baidu ora2pg]# ora2pg -t SHOW_VERSION -c ora2pg_table.conf
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
Oracle Database 11g Enterprise Edition Release 11.2.0.1.07、导出表测试
7.1、创建表5张表
--创建表，GIS统称用SDO_GEOMETRY
CREATE TABLE TEST_PONINT (
id NUMBER PRIMARY KEY,
geometry SDO_GEOMETRY
);
--创建表，GIS统称用SDO_GEOMETRY
CREATE TABLE TEST_LINE (
id NUMBER PRIMARY KEY,
geometry SDO_GEOMETRY
);
--创建表，GIS统称用SDO_GEOMETRY
CREATE TABLE TEST_POLYGON (
id NUMBER PRIMARY KEY,
geometry SDO_GEOMETRY
);
--创建表，GIS统称用SDO_GEOMETRY
CREATE TABLE TEST_COLLECTION (
id NUMBER PRIMARY KEY,
geometry SDO_GEOMETRY
);
CREATE TABLE "WANGDP"."TABLE1" (
COLUMN1 INTEGER NULL
COLUMN2 VARCHAR2(100) NULL
) ;7.2、导出表
[root@baidu ora2pg]# ora2pg -c ora2pg_table.conf
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[2024-07-02 17:39:24] [========================>] 5/5 tables (100.0%) end of scanning.
[2024-07-02 17:39:24] [========================>] 5/5 tables (100.0%) end of table export.
Fixing function calls in output files...查看导出表，是按照postgis的格式导出的，很OK
CREATE TABLE table1 (
column1 numeric(38),
column2 varchar(100)
) ;
CREATE TABLE test_collection (
id float NOT NULL,
geometry geometry(GEOMETRY,4326)
) ;
CREATE TABLE test_line (
id float NOT NULL,
geometry geometry(GEOMETRY,4326)
) ;
CREATE TABLE test_polygon (
id float NOT NULL,
geometry geometry(GEOMETRY,4326)
) ;
CREATE TABLE test_ponint (
id float NOT NULL,
geometry geometry(GEOMETRY,4326)
) ;8、导出数据
8.1、插入数据
--插入点数据
INSERT INTO TEST_PONINT (id, geometry)
VALUES (1, SDO_GEOMETRY(
2001, -- 2D 点
8307, -- SRID (WGS 84)
SDO_POINT_TYPE(10.0, 20.0, NULL),
NULL,
NULL
));
--插入线串
INSERT INTO TEST_LINE (id, geometry)
VALUES (2, SDO_GEOMETRY(
2002, -- 2D 线串
8307, -- SRID (WGS 84)
NULL,
SDO_ELEM_INFO_ARRAY(1, 2, 1),
SDO_ORDINATE_ARRAY(10.0, 20.0, 30.0, 40.0)
));
--插入多边形
INSERT INTO TEST_POLYGON (id, geometry)
VALUES (3, SDO_GEOMETRY(
2003, -- 2D 多边形
8307, -- SRID (WGS 84)
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(10.0, 10.0, 20.0, 10.0, 20.0, 20.0, 10.0, 20.0, 10.0, 10.0)
));
--插入集合数据
INSERT INTO TEST_COLLECTION (id, geometry)
VALUES (4, SDO_GEOMETRY(
2004, -- 2D 集合
8307, -- SRID (WGS 84)
NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1, 1, 2, 1, 7, 1003, 1),
SDO_ORDINATE_ARRAY(
10.0, 20.0, -- 点
15.0, 25.0, 30.0, 35.0, -- 线串
40.0, 40.0, 50.0, 50.0, 50.0, 60.0, 40.0, 60.0, 40.0, 40.0 -- 多边形
)
));
INSERT INTO WANGDP.TABLE1(COLUMN1, COLUMN2) VALUES(0, 'abc');
INSERT INTO WANGDP.TABLE1(COLUMN1, COLUMN2) VALUES(1, '中国');8.2、导出数据
编写导出配置文件
[root@baidu ora2pg]# vi ora2pg_data.conf
ORACLE_HOME /home/oracle/instantclient_11_2
ORACLE_DSN dbi:Oracle:host=192.168.10.197;sid=orcl
ORACLE_USER wangdp
ORACLE_PWD aaaaaa
SCHEMA wangdp
TYPE COPY
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT /opt/ora2pg/data.sql导出
[root@baidu ora2pg]# ora2pg -c ora2pg_data.conf
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[2024-07-02 18:03:35] [========================>] 5/5 tables (100.0%) end of scanning.
[2024-07-02 18:03:50] [========================>] 2/1 rows (200.0%) Table TABLE1 (2 recs/sec)
[2024-07-02 18:03:50] [=========> ] 2/5 total rows (40.0%) - (1 sec., avg: 2 recs/sec).
[2024-07-02 18:03:50] [========================>] 1/1 rows (100.0%) Table TEST_COLLECTION (1 recs/sec)
[2024-07-02 18:03:50] [==============> ] 3/5 total rows (60.0%) - (1 sec., avg: 3 recs/sec).
[2024-07-02 18:03:50] [========================>] 1/1 rows (100.0%) Table TEST_LINE (1 recs/sec)
[2024-07-02 18:03:50] [===================> ] 4/5 total rows (80.0%) - (1 sec., avg: 4 recs/sec).
[2024-07-02 18:03:51] [========================>] 1/1 rows (100.0%) Table TEST_POLYGON (1 recs/sec)
[2024-07-02 18:03:51] [========================>] 5/5 total rows (100.0%) - (2 sec., avg: 2 recs/sec).
[2024-07-02 18:03:51] [========================>] 1/1 rows (100.0%) Table TEST_PONINT (1 recs/sec)
[2024-07-02 18:03:51] [========================>] 6/5 total rows (120.0%) - (2 sec., avg: 3 recs/sec).
[2024-07-02 18:03:51] [========================>] 5/5 rows (100.0%) on total estimated data (2 sec., avg: 2 recs/sec)
Fixing function calls in output files...
1516
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
