文章目录
官方文档
http://ora2pg.darold.net/
http://ora2pg.darold.net/documentation.html
https://github.com/darold/ora2pg
特性
MySQL和Oracle数据迁移到PostgreSQL使用
- Export full database schema (tables, views, sequences, indexes), with
unique, primary, foreign key and check constraints.
- Export grants/privileges for users and groups.
- Export range/list partitions and sub partitions.
- Export a table selection (by specifying the table names).
- Export Oracle schema to a PostgreSQL 8.4+ schema.
- Export predefined functions, triggers, procedures, packages and
package bodies.
- Export full data or following a WHERE clause.
- Full support of Oracle BLOB object as PG BYTEA.
- Export Oracle views as PG tables.
- Export Oracle user defined types.
- Provide some basic automatic conversion of PLSQL code to PLPGSQL.
- Works on any platform.
- Export Oracle tables as foreign data wrapper tables.
- Export materialized view.
- Show a report of an Oracle database content.
- Migration cost assessment of an Oracle database.
- Migration difficulty level assessment of an Oracle database.
- Migration cost assessment of PL/SQL code from a file.
- Migration cost assessment of Oracle SQL queries stored in a file.
- Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
- Export Oracle locator and spatial geometries into PostGis.
- Export DBLINK as Oracle FDW.
- Export SYNONYMS as views.
- Export DIRECTORY as external table or directory for external_file extension.
- Full MySQL export just like Oracle database.
- Dispatch a list of SQL orders over multiple PostgreSQL connections
- Perform a diff between Oracle and PostgreSQL database for test purpose.
安装(ubuntu18.04/Redhat7.5)
链接:https://pan.baidu.com/s/1LlRilUM8ygrHFjAI4DjMww
提取码:1qaz
安装说明
- perl要求必须在5.8以上
- 依赖安装
- oracle-instantclient
- DBI
- DBD::Oracle
- DBD::mysql
- DBD::Pg
- Compress::Zlib
- 安装ora2pg
说明:perl包安装有多种方式(cpan,apt,源码编译,下面安装三种方式都有用到。
依赖安装
前提
gcc、make等工具都已经安装。此外下面的包编译安装时,部分包会提示不存在对应的lib库,只要安装对应的包的devel包即可。
安装perl
系统默认已经安装了perl,版本号为5.26
安装DBI
cpan
install DBI
安装oracle-instantclient
#下载 InstantClient(Basic、SDK和Sqlplus的zip包)
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
#解压后放到 /opt/user/lib 目录下
unzip instantclient-basic-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-19.9.0.0.0dbru.zip
mkdir /opt/user/lib -p
mv instantclient_19_9 /opt/user/lib
#修改权限并配置环境变量
cd /opt/user/lib
chmod 755 instantclient_19_9/
vi /etc/profile
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH
source /etc/profile
安装DBD::Oracle
#下载软件包
https://metacpan.org/pod/DBD::Oracle
#安装
tar -xzvf DBD-Oracle-1.80.tar.gz
cd DBD-Oracle-1.80
#编译
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH
perl Makefile.PL -l
make
make install
##另一种方法
perl -MCPAN -e shell
install DBD::Oracle
安装DBD::Pg
#类似DBD:Oracle
#前提:已经安装了PostgreSQL
#下载软件包 https://metacpan.org/
#编译安装
tar -xzvf DBD-Pg-3.14.2.tar.gz
cd DBD-Pg-3.14.2
perl Makefile.PL -l
make
make install
##另一种方法
perl -MCPAN -e shell
install DBD::Pg
安装DBD::Mysql
#https://metacpan.org/pod/distribution/DBD-mysql/lib/DBD/mysql/INSTALL.pod
sudo apt-get install libdbd-mysql-perl
#centos
yum install perl-DBD-MySQL
安装Compress::Zlib(可选)
perl -MCPAN -e shell
install Compress::Zlib
安装Ora2pg
#下载软件包
https://github.com/darold/ora2pg/releases
#编译安装
tar -xzvf ora2pg-21.0.tar.gz
cd ora2pg-21.0
perl Makefile.PL -l
make
make install
#简单检查
ora2pg --help
Oracle迁移
导出SQL文件
说明
- ora2pg已经在pro环境安装
- oracle数据库已经在pro环境配置(只安装ORACLE客户端也可以)
- 在root用户下操作
- 如果是用navicat导入的,先删除表 project_struct
- 库中没有存储过程、触发器等,所以配置文件中也没有设置要导出这些内容
执行
mkdir -p /opt/tmp/jtqy
cd /opt/tmp/jtqy
cp /etc/ora2pg/ora2pg.conf.dist ora2pg_oracle.conf
sed -i 's/^ORACLE_HOME/# ORACLE_HOME/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_DSN/# ORACLE_DSN/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_USER/# ORACLE_USER/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_PWD/# ORACLE_PWD/g' ora2pg_oracle.conf
sed -i 's/^TYPE/# TYPE/g' ora2pg_oracle.conf
sed -i 's/^INDEXES_RENAMING/# INDEXES_RENAMING/g' ora2pg_oracle.conf
sed -i 's/^NLS_LANG/# NLS_LANG/g' ora2pg_oracle.conf
sed -i 's/^SCHEMA/# SCHEMA/g' ora2pg_oracle.conf
sed -i 's/^DEFAULT_NUMERIC/# DEFAULT_NUMERIC/g' ora2pg_oracle.conf
sed -i 's/^PG_NUMERIC_TYPE/# PG_NUMERIC_TYPE/g' ora2pg_oracle.conf
sed -i 's/^PG_INTEGER_TYPE/# PG_INTEGER_TYPE/g' ora2pg_oracle.conf
echo "ORACLE_HOME /opt/user/lib/instantclient_19_9" >> ora2pg_oracle.conf
echo "ORACLE_DSN dbi:Oracle:host=localhost;sid=ORCL;port=1521" >> ora2pg_oracle.conf
echo "ORACLE_USER PROJECT" >> ora2pg_oracle.conf
echo "ORACLE_PWD PROJECT" >> ora2pg_oracle.conf
echo "INDEXES_RENAMING 1" >> ora2pg_oracle.conf
echo "TYPE TABLE,INSERT,VIEW