ora2pg使用

大家好，欢迎来到IT知识分享网。

本文介绍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 install

4、安装DBD::Pg模块

 DBD-Pg-3.18.0 – DBI PostgreSQL interface – metacpan.org

The usual steps to install DBD::Pg:

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 install

5、安装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 install

6、安装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 install

6.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.3

6.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.0

7、导出表测试

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...