ora2pg使用教程

原创 necessary 2025-01-03

1586

简介

公众号 BigDataTechnolog 后台回复： ora2pg 赠送ora2pg参数解释中文版以及安装集成包
在现在开源工具中、去O化最好的工具就是ora2pg了，在当下的去O环境中，使用面也是比较广泛。
其功能性也很全面。支持oracle多种object的迁移如：TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE,INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE,INSERT or COPY, FDW, QUERY, KETTLE, SYNONYM。

安装

本文模拟oracle 12c迁移到postgresql-16，以下对应的包都通用，除了oracle客户端工具需要悬着和oracle版本号对应的，其他均可以保持一致，ora2pg的安装节点不需要依赖oracle或pg的server
下载ora2pg

wget https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz

安装依赖


yum install -y  perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder  perl-CPAN   perl-ExtUtils-eBuilder
yum install perl-CPAN -y 
yum install perl perl-devel make gcc gcc-c++ -y
yum install perl-Test-Tester -y
yum install   perl-Time-HiRes perl-tests  cpan perl-Time-HiRes    -y
yum install -y libaio libaio-devel glibc libnsl

安装oracle 客户端

rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.2-basiclite-12.2.0.1.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

如果该节点有安装过相应的rpm包，安装无法进行覆盖，可以尝试重建安装该包或者升级

rpm --rebuilddb oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm
rpm -Uvh oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm

编辑环境变量
ORACLE_HOME使用find 指令查找对应的client安装的路径，按照以下规格进行填写

export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export PATH=$ORACLE_HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_USERID=system/system@vm111:1521/orcl

source /etc/profile

安装DBD包
DBI-1.643\DBD-Oracle-1.90\DBD-Pg-3.18.0\ora2pg-24.3
讲对应的包上传到同一路径下
版本号对应，直接copy批量执行以下脚本

for file in *.tar.gz; do
  tar -zxvf "$file"
done

cd DBI-1.643/
perl Makefile.PL  
make && make test && make install 

cd ../DBD-Oracle-1.90
perl Makefile.PL -l
make
make install
cd ../DBD-Pg-3.18.0
perl Makefile.PL
make && TEST_SIGNATURE=1 make test   && make install 

cd ../ora2pg-24.3/
perl Makefile.PL
make && make install

topic：在安装DBD-Oracle-1.90，可能会出现找不到对应的demo.mk文件，主要是环境变量配置有误，检查ORACLE_HOME环境变量是否准确。也可以使用以下指令用于指定

	perl Makefile.PL -m /usr/share/oracle/12.2/client64/demo/demo.mk
	make && make test && make install

检验安装是否完成

ora2pg --help

使用以下脚本检验DBD包安装是否完成。

perl 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;

配置前oracle需要知道的信息

查看Oracle字符集
SELECT value
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

查看sid 
SELECT name
FROM v$database;

ora2pg安装之后会在 /etc/ora2pg/路径下给出配置参数列表ora2pg.conf.dist。
公众号 BigDataTechnolog 后台回复： ora2pg 赠送ora2pg参数解释中文版以及安装集成包

ora2pg需要使用
需要配置导出DDL的ddl.conf文本和导出data的data.conf配置参数
ora2pg.ddl 配置如下,

RACLE_HOME /usr/lib/oracle/12.2/client64
ORACLE_DSN dbi:Oracle:host=10.0.0.112;sid=orcl;port=1521
ORACLE_USER system
ORACLE_PWD 123456
NLS_NCHAR AL32UTF8
SCHEMA DIP
USER_GRANTS 1 
DEBUG 1
ORA_INITIAL_COMMAND commit 
EXPORT_SCHEMA 1 
CREATE_SCHEMA 1
COMPILE_SCHEMA 0 
TYPE copy
SKIP fkeys ukeys checks 
DISABLE_COMMENT 0 
EXTERNAL_TO_FDW 1 
TRUNCATE_TABLE 0 
USE_TABLESPACE 0 
REORDERING_COLUMNS 0 
CONTEXT_AS_TRGM 0 
FTS_INDEX_ONLY 1 
USE_UNACCENT 0 
USE_LOWER_UNACCENT 0 
DATADIFF 0 
DATADIFF_UPDATE_BY_PKEY 0 
DATADIFF_DEL_SUFFIX _del 
DATADIFF_UPD_SUFFIX _upd 
DATADIFF_INS_SUFFIX _ins 
DATADIFF_WORK_MEM 256 MB 
DATADIFF_TEMP_BUFFERS 512 MB 
KEEP_PKEY_NAMES 0 
PKEY_IN_CREATE 0 
FKEY_ADD_UPDATE never 
FKEY_DEFERRABLE 0 
DEFER_FKEY 0 
DROP_FKEY 0 
DISABLE_SEQUENCE 0 
DISABLE_TRIGGERS 1 
PRESERVE_CASE 0 
INDEXES_RENAMING 0 
USE_INDEX_OPCLASS 0 
PREFIX_PARTITION 0 
PREFIX_SUB_PARTITION 1 
RENAME_PARTITION 1
PARTITION_EXPORT 1
DISABLE_PARTITION 0 
WITH_OID 0 
ORA_RESERVED_WORDS audit,comment,references 
USE_RESERVED_WORDS 0 
DISABLE_UNLOGGED 1 
PG_DSN dbi:Pg:dbname=postgres;host=10.0.0.141;port=5432
PG_USER postgres
PG_PWD postgres
PG_VERSION 16.2 
OUTPUT ddl.sql 
OUTPUT_DIR /home/postgres/output/ 
FILE_PER_CONSTRAINT 0 
FILE_PER_INDEX 0 
FILE_PER_FKEYS 0 
PG_VERSION 16

ora2pg.data配置将TYPE 的值改为copy，虽然其支持insert 导出但是copy的效率会更高

RACLE_HOME /usr/lib/oracle/12.2/client64
ORACLE_DSN dbi:Oracle:host=10.0.0.112;sid=orcl;port=1521
ORACLE_USER system
ORACLE_PWD 123456
NLS_NCHAR AL32UTF8
SCHEMA DIP
USER_GRANTS 1 
DEBUG 1
ORA_INITIAL_COMMAND commit 
EXPORT_SCHEMA 1 
CREATE_SCHEMA 1
COMPILE_SCHEMA 0 
TYPE TABLE,PARTITION
SKIP fkeys ukeys checks 
DISABLE_COMMENT 0 
EXTERNAL_TO_FDW 1 
TRUNCATE_TABLE 0 
USE_TABLESPACE 0 
REORDERING_COLUMNS 0 
CONTEXT_AS_TRGM 0 
FTS_INDEX_ONLY 1 
USE_UNACCENT 0 
USE_LOWER_UNACCENT 0 
DATADIFF 0 
DATADIFF_UPDATE_BY_PKEY 0 
DATADIFF_DEL_SUFFIX _del 
DATADIFF_UPD_SUFFIX _upd 
DATADIFF_INS_SUFFIX _ins 
DATADIFF_WORK_MEM 256 MB 
DATADIFF_TEMP_BUFFERS 512 MB 
KEEP_PKEY_NAMES 0 
PKEY_IN_CREATE 0 
FKEY_ADD_UPDATE never 
FKEY_DEFERRABLE 0 
DEFER_FKEY 0 
DROP_FKEY 0 
DISABLE_SEQUENCE 0 
DISABLE_TRIGGERS 1 
PRESERVE_CASE 0 
INDEXES_RENAMING 0 
USE_INDEX_OPCLASS 0 
PREFIX_PARTITION 0 
PREFIX_SUB_PARTITION 1 
RENAME_PARTITION 1
PARTITION_EXPORT 1
DISABLE_PARTITION 0 
WITH_OID 0 
ORA_RESERVED_WORDS audit,comment,references 
USE_RESERVED_WORDS 0 
DISABLE_UNLOGGED 1 
PG_DSN dbi:Pg:dbname=postgres;host=10.0.0.141;port=5432
PG_USER postgres
PG_PWD postgres
PG_VERSION 16.2 
OUTPUT ddl.sql 
OUTPUT_DIR /home/postgres/output/ 
FILE_PER_CONSTRAINT 0 
FILE_PER_INDEX 0 
FILE_PER_FKEYS 0 
PG_VERSION 16

执行ora2pg,一次导出对应的DDL和DATA

ora2pg -c ora2pg.ddl 
ora2pg -c ora2pg.data

公众号 BigDataTechnolog 后台回复： ora2pg 赠送ora2pg参数解释中文版以及安装集成包

最后修改时间：2025-01-03 12:01:15

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ora2pg使用教程

简 介

安装

评论

简介