使用ora2pg将oracle数据迁移到postgresql库

源库linux6.9+oracle11.2.0.4，目标pg12.2准备编译安装环境:yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes准备oracle环境变量:(如果本机有oracle数据库按下面在root下配置即可，如果没有则需要安装oracle客户端软件)[root@db pg]

好记忆不如烂笔头abc

2279人浏览 · 2020-09-08 13:30:01

好记忆不如烂笔头abc · 2020-09-08 13:30:01 发布

源库linux6.9+oracle11.2.0.4，目标pg12.2

准备编译安装环境:
yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes
准备oracle环境变量:(如果本机有oracle数据库按下面在root下配置即可，如果没有则需要安装oracle客户端软件)
[root@db pg]# source /home/oracle/.bash_profile
[root@db pg]# echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0/dbhome_1
[root@db pg]# echo $LD_LIBRARY_PATH
/oracle/app/oracle/product/11.2.0/dbhome_1/lib:/oracle/app/oracle/product/11.2.0/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/lib
[root@db pg]# echo $ORACLE_BASE
/oracle/app/oracle

安装DBI，DBD::Oracle
DBI只是个抽象层，要实现支持不同的数据库，则需要在DBI之下，编写针对不同数据库的驱动。对MySql来说，有DBD::Mysql, 而对ORACLE来说，则是DBD::Oracle。其中的DBD这是DataBase Driver的简写。安装顺序是先装DBI，再装DBD::Oracle
介质下载路径：
DBI:
http://www.cpan.org/modules/by-module/DBI/
DBD:
http://www.cpan.org/modules/by-module/DBD/

安装DBI
tar -zxvf DBI-1.643.tar.gz
cd DBI-1.643/
perl Makefile.PL
make
make install

安装DBD:Oracle
tar zxvf DBD-Oracle-1.80.tar.gz
perl Makefile.PL
make
make install

检查安装

编辑：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 check.pl
DBD::Oracle -- 1.80
DBI -- 1.643
Ora2Pg -- 20.0
Perl -- 5.10.1

安装ora2pg
wget https://github.com/darold/ora2pg/releases
tar -zxvf ora2pg-20.0.tar.gz
cd ora2pg-20.0/
perl Makefile.PL
make && make install
whereis ora2pgora2pg: /etc/ora2pg /usr/local/bin/ora2pg

默认安装完后的配置文件参考:
[root@db ora2pg]# cd /etc/ora2pg/
[root@db ora2pg]# cat ora2pg.conf.dist|grep -v "#"|grep -v "^$"
ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_DSN dbi:Oracle:host=mydb.mydom.fr;sid=SIDNAME;port=1521
ORACLE_USER system
ORACLE_PWD manager
USER_GRANTS 0
DEBUG 0
EXPORT_SCHEMA 0
CREATE_SCHEMA 1
COMPILE_SCHEMA 0
TYPE TABLE
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 0
PRESERVE_CASE 0
INDEXES_RENAMING 0
USE_INDEX_OPCLASS 0
PREFIX_PARTITION 0
PREFIX_SUB_PARTITION 1
DISABLE_PARTITION 0
WITH_OID 0
ORA_RESERVED_WORDS audit,comment,references
USE_RESERVED_WORDS 0
DISABLE_UNLOGGED 0
OUTPUT output.sql
BZIP2
FILE_PER_CONSTRAINT 0
FILE_PER_INDEX 0
FILE_PER_FKEYS 0
FILE_PER_TABLE 0
FILE_PER_FUNCTION 0
STOP_ON_ERROR 1
COPY_FREEZE 0
CREATE_OR_REPLACE 1
PG_NUMERIC_TYPE 1
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC bigint
ENABLE_MICROSECOND 1
TO_NUMBER_CONVERSION numeric
GEN_USER_PWD 0
FORCE_OWNER 0
FORCE_SECURITY_INVOKER 0
DATA_LIMIT 10000
NOESCAPE 0
TRANSACTION serializable
STANDARD_CONFORMING_STRINGS 1
NO_LOB_LOCATOR 1
XML_PRETTY 0
LOG_ON_ERROR 0
TRIM_TYPE BOTH
INTERNAL_DATE_MAX 49
FUNCTION_CHECK 1
NO_BLOB_EXPORT 0
DATA_EXPORT_ORDER name
JOBS 1
ORACLE_COPIES 1
PARALLEL_TABLES 1
DEFAULT_PARALLELISM_DEGREE 0
PARALLEL_MIN_ROWS 100000
DROP_INDEXES 0
SYNCHRONOUS_COMMIT 0
EXPORT_INVALID 0
PLSQL_PGSQL 1
NULL_EQUAL_EMPTY 0
EMPTY_LOB_NULL 0
PACKAGE_AS_SCHEMA 1
REWRITE_OUTER_JOIN 1
FUNCTION_STABLE 1
COMMENT_COMMIT_ROLLBACK 0
COMMENT_SAVEPOINT 0
USE_ORAFCE 0
AUTONOMOUS_TRANSACTION 1
ESTIMATE_COST 0
COST_UNIT_VALUE 5
DUMP_AS_HTML 0
TOP_MAX 10
HUMAN_DAYS_LIMIT 5
PG_VERSION 11
BITMAP_AS_GIN 1
PG_BACKGROUND 0
PG_SUPPORTS_SUBSTR 1
AUTODETECT_SPATIAL_TYPE 1
CONVERT_SRID 1
DEFAULT_SRID 4326
GEOMETRY_EXTRACT_TYPE INTERNAL
FDW_SERVER orcl
MYSQL_PIPES_AS_CONCAT 0
MYSQL_INTERNAL_EXTRACT_FORMAT 0
参考模板在空间充足的路径下编辑导出配置文件，然后做导出：
[root@db oradata]# cat ora2pg_table.conf
PG_VERSION 12
ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_DSN dbi:Oracle:host=IP地址;sid=数据库名称;port=1521
ORACLE_USER 导出用户名称
ORACLE_PWD 导出用户密码
SCHEMA 导出用户名称
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 table.sql

[root@db oradata]# cat ora2pg_data.conf
PG_VERSION 12
ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_DSN dbi:Oracle:host=IP地址;sid=数据库名称;port=1521
ORACLE_USER 导出用户名称
ORACLE_PWD 导出用户密码
SCHEMA 导出用户名称
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 data.sql

[root@gddbtest oradata]# ora2pg -c ora2pg_table.conf
install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.11.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.
at (eval 14) line 3
Compilation failed in require at (eval 14) line 3.
Perhaps a required shared library or dll isn't installed where expected
at /usr/local/share/perl5/Ora2Pg.pm line 1619
[root@gddbtest oradata]# source /home/oracle/.bash_profile
[root@gddbtest oradata]# ora2pg -c ora2pg_table.conf
^CReceived terminating signal (INT).
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(HOST=10.62.233.214)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=gddbdev))) at /usr/local/bin/ora2pg line 255.
[root@gddbtest oradata]# cd pg
[root@gddbtest pg]# ll
20384
drwxr-xr-x 7 oracle oinstall 4096 915 15:01 20200915
drwxr-xr-x 8 oracle oinstall 12288 918 10:40 bjsm
-rw-r--r-- 1 oracle oinstall 430450778 918 09:23 bjsm.tar.gz
drwxr-xr-x 7 oracle oinstall 4096 915 15:29 test_project
[root@gddbtest pg]# cd test_project/
[root@gddbtest test_project]# ora2pg -p -c comm.conf -t COPY -a 'TABLE[T_FIX_COMMUNICATION_GZLTJINFO]' -o T_FIX_COMMUNICATION_GZLTJINFO.sql
[========================>] 1/1 tables (100.0%) end of scanning.
date[========> ] 630000/1772461 rows (35.5%) Table T_FIX_COMMUNICATION_GZLTJINFO (633 recs/sec)

导出表结构:
ora2pg -c ora2pg_table.conf
导出数据：
ora2pg -c ora2pg_data.conf

导入参考：

psql --host=192.168.208.40 --port=5432 --username=xxx --echo-errors db -f data.sql -1

导入脚本参考：psql --host=192.168.208.40 --port=5432 --username=sde --echo-errors jzshdb -f AE_RANGE_PLAN_SCORE_84.sql -v ON_ERROR_STOP=1

官方参考:Ora2Pg : Migrates Oracle to PostgreSQL

ora2pg --project_base /app/migration/ --init_project test_project

　修改ora2pg.conf文件添加：

[root@gddbtest test_project]# cd config/
[root@gddbtest config]# ll
total 120
-rw-r--r-- 1 root root 60759 Sep  9 10:03 ora2pg.conf
[root@gddbtest config]# more ora2pg.conf
PG_VERSION 12
ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_DSN  dbi:Oracle:host=192.168.208.40;sid=sgdddb;port=1521
ORACLE_USER sgdd
ORACLE_PWD  123456
SCHEMA  sgdd
NLS_LANG    AMERICAN_AMERICA.UTF8
EXCLUDE         *20*
ALLOW           TPR_.* TSM_.* TWF_.* ZD_.* WZ_.* T_FIX_ZADDRESS T_DEVICE_DELL_APPLY_VIEW ZNZW.*

文件直接到psql导入就行了

gddb=# \i /root/table.SQL

Ora2Pg usage

First of all be sure that libraries and binaries path include the Oracle Instant Client installation:

        export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
        export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"

By default Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration file, if the file exist you can simply execute:

        /usr/local/bin/ora2pg

or under Windows(tm) run ora2pg.bat file, located in your perl bin directory. Windows(tm) users may also find a template configuration file in C:\ora2pg

If you want to call another configuration file, just give the path as command line argument:

        /usr/local/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf

Here are all command line parameters available when using ora2pg:

Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]

    -a | --allow str  : Comma separated list of objects to allow from export.
                        Can be used with SHOW_COLUMN too.
    -b | --basedir dir: Set the default output directory, where files
                        resulting from exports will be stored.
    -c | --conf file  : Set an alternate configuration file other than the
                        default /etc/ora2pg/ora2pg.conf.
    -d | --debug      : Enable verbose output.
    -D | --data_type STR : Allow custom type replacement at command line.
    -e | --exclude str: Comma separated list of objects to exclude from export.
                        Can be used with SHOW_COLUMN too.
    -h | --help       : Print this short help.
    -g | --grant_object type : Extract privilege from the given object type.
                        See possible values with GRANT_OBJECT configuration.
    -i | --input file : File containing Oracle PL/SQL code to convert with
                        no Oracle database connection initiated.
    -j | --jobs num   : Number of parallel process to send data to PostgreSQL.
    -J | --copies num : Number of parallel connections to extract data from Oracle.
    -l | --log file   : Set a log file. Default is stdout.
    -L | --limit num  : Number of tuples extracted from Oracle and stored in
                        memory before writing, default: 10000.
    -m | --mysql      : Export a MySQL database instead of an Oracle schema.
    -n | --namespace schema : Set the Oracle schema to extract from.
    -N | --pg_schema schema : Set PostgreSQL's search_path.
    -o | --out file   : Set the path to the output file where SQL will
                        be written. Default: output.sql in running directory.
    -p | --plsql      : Enable PLSQL to PLPGSQL code conversion.
    -P | --parallel num: Number of parallel tables to extract at the same time.
    -q | --quiet      : Disable progress bar.
    -r | --relative   : use \ir instead of \i in the psql scripts generated.
    -s | --source DSN : Allow to set the Oracle DBI datasource.
    -t | --type export: Set the export type. It will override the one
                        given in the configuration file (TYPE).
    -T | --temp_dir DIR: Set a distinct temporary directory when two
                         or more ora2pg are run in parallel.
    -u | --user name  : Set the Oracle database connection user.
                        ORA2PG_USER environment variable can be used instead.
    -v | --version    : Show Ora2Pg Version and exit.
    -w | --password pwd : Set the password of the Oracle database user.
                        ORA2PG_PASSWD environment variable can be used instead.
    --forceowner      : Force ora2pg to set tables and sequences owner like in
                  Oracle database. If the value is set to a username this one
                  will be used as the objects owner. By default it's the user
                  used to connect to the Pg database that will be the owner.
    --nls_lang code: Set the Oracle NLS_LANG client encoding.
    --client_encoding code: Set the PostgreSQL client encoding.
    --view_as_table str: Comma separated list of views to export as table.
    --estimate_cost   : Activate the migration cost evaluation with SHOW_REPORT
    --cost_unit_value minutes: Number of minutes for a cost evaluation unit.
                  default: 5 minutes, corresponds to a migration conducted by a
                  PostgreSQL expert. Set it to 10 if this is your first migration.
   --dump_as_html     : Force ora2pg to dump report in HTML, used only with
                        SHOW_REPORT. Default is to dump report as simple text.
   --dump_as_csv      : As above but force ora2pg to dump report in CSV.
   --dump_as_sheet    : Report migration assessment with one CSV line per database.
   --init_project NAME: Initialise a typical ora2pg project tree. Top directory
                        will be created under project base dir.
   --project_base DIR : Define the base dir for ora2pg project trees. Default
                        is current directory.
   --print_header     : Used with --dump_as_sheet to print the CSV header
                        especially for the first run of ora2pg.
   --human_days_limit num : Set the number of human-days limit where the migration
                        assessment level switch from B to C. Default is set to
                        5 human-days.
   --audit_user LIST  : Comma separated list of usernames to filter queries in
                        the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
                        and QUERY export type.
   --pg_dsn DSN       : Set the datasource to PostgreSQL for direct import.
   --pg_user name     : Set the PostgreSQL user to use.
   --pg_pwd password  : Set the PostgreSQL password to use.
   --count_rows       : Force ora2pg to perform a real row count in TEST action.
   --no_header        : Do not append Ora2Pg header to output file
   --oracle_speed     : Use to know at which speed Oracle is able to send
                        data. No data will be processed or written.
   --ora2pg_speed     : Use to know at which speed Ora2Pg is able to send
                        transformed data. Nothing will be written.

See full documentation at http://ora2pg.darold.net/ for more help or see manpage with 'man ora2pg'.

ora2pg will return 0 on success, 1 on error. It will return 2 when a child process has been interrupted and you've gotten the warning message: "WARNING: an error occurs during data export. Please check what's happen." Most of the time this is an OOM issue, first try reducing DATA_LIMIT value.

For developers, it is possible to add your own custom option(s) in the Perl script ora2pg as any configuration directive from ora2pg.conf can be passed in lower case to the new Ora2Pg object instance. See ora2pg code on how to add your own option.

Note that performance might be improved by updating stats on oracle:

        BEGIN
        DBMS_STATS.GATHER_SCHEMA_STATS
        DBMS_STATS.GATHER_DATABASE_STATS 
        DBMS_STATS.GATHER_DICTIONARY_STATS
        END;

Generate a migration template

The two options --project_base and --init_project when used indicate to ora2pg that he has to create a project template with a work tree, a configuration file and a script to export all objects from the Oracle database. Here a sample of the command usage:

        ora2pg --project_base /app/migration/ --init_project test_project

Oracle迁移至PostgreSQL工具之Ora2Pg - lottu - 博客园

ora2pg的使用（一、centos下的安装）_ITPUB博客

ORACLE 迁移到 PG 之 ora2pg_weixin_30404405的博客-CSDN博客

ORA2PG --从oracle迁移数据到postgres - kingle-l - 博客园

升级ora2pg版本：

[root@gddbtest pg]# whereis ora2pg
ora2pg: /etc/ora2pg /usr/local/bin/ora2pg
[root@gddbtest pg]# ll /etc/ora2pg
0
-rw-r--r-- 1 root root 60578 9 8 2020 ora2pg.conf.dist
[root@gddbtest pg]# pwd
/root/pg
[root@gddbtest pg]# mv /etc/ora2pg /etc/ora2pg.bak
[root@gddbtest pg]# mv /usr/local/bin/ora2pg /usr/local/bin/ora2pg.bak
[root@gddbtest pg]# ll
1872
-rw-r--r-- 1 root     root          244 9 8 2020 check.pl
drwxrwxr-x 7     1000 oinstall     4096 9 8 2020 DBD-Oracle-1.80
-rw-rw-r-- 1 hsmt     hsmt       413229 9 8 2020 DBD-Oracle-1.80.tar.gz
drwxr-x--- 6 songyz   games        4096 9 8 2020 DBI-1.643
-rw-r----- 1 root     root       612372 9 8 2020 DBI-1.643.tar.gz
drwxr-xr-x 7 root     root         4096 9 8 2020 ora2pg-20.0
-rw-rw-r-- 1 hsmt     hsmt       453222 9 7 2020 ora2pg-20.0.zip
drwxr-xr-x 6 root     root         4096 211 03:00 ora2pg-23.1
-rw-rw-r-- 1 root     root       522529 418 13:00 ora2pg-23.1.zip
drwxrwxrwx 6     1107     1107     4096 119 2020 postgresql-12.2
-rw-rw-r-- 1 postgres postgres 20363545 818 2020 postgresql-12.2.tar.bz2
[root@gddbtest pg]# cd ora2pg-23.1
[root@gddbtest ora2pg-23.1]# ll
12
-rw-r--r-- 1 root root 330922 211 03:00 changelog
drwxr-xr-x 2 root root   4096 211 03:00 doc
-rw-r--r-- 1 root root     21 211 03:00 INSTALL
drwxr-xr-x 3 root root   4096 211 03:00 lib
-rw-r--r-- 1 root root  32472 211 03:00 LICENSE
-rw-r--r-- 1 root root  70244 211 03:00 Makefile.PL
-rw-r--r-- 1 root root    180 211 03:00 MANIFEST
drwxr-xr-x 5 root root   4096 211 03:00 packaging
-rw-r--r-- 1 root root 160666 211 03:00 README
drwxr-xr-x 2 root root   4096 211 03:00 scripts
[root@gddbtest ora2pg-23.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Ora2Pg

Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: make && make install
[root@gddbtest ora2pg-23.1]# make && make install
cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm
cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm
cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm
cp lib/Ora2Pg/Oracle.pm blib/lib/Ora2Pg/Oracle.pm
cp lib/Ora2Pg/MySQL.pm blib/lib/Ora2Pg/MySQL.pm
cp scripts/ora2pg blib/script/ora2pg
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg
cp scripts/ora2pg_scanner blib/script/ora2pg_scanner
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner
Manifying blib/man3/ora2pg.3
Installing /usr/local/share/perl5/Ora2Pg.pm
Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm
Installing /usr/local/share/perl5/Ora2Pg/MySQL.pm
Installing /usr/local/share/perl5/Ora2Pg/Oracle.pm
Installing /usr/local/share/perl5/Ora2Pg/GEOM.pm
Installing /usr/local/share/man/man3/ora2pg.3
Installing /usr/local/bin/ora2pg_scanner
Installing /usr/local/bin/ora2pg
Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg
Appending installation info to /usr/lib64/perl5/perllocal.pod
[root@gddbtest ora2pg-23.1]# whereis ora2pg
ora2pg: /etc/ora2pg.bak /etc/ora2pg /usr/local/bin/ora2pg.bak /usr/local/bin/ora2pg
[root@gddbtest ora2pg-23.1]# ora2pg -v
Ora2Pg v23.1
[root@gddbtest ora2pg-23.1]#

亚马逊云科技技术品牌专区

更多推荐

STM32节点移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）

STM32移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）前言前言在移植协议之前，先给大家科普一下Lora 和 lorawan 的区别。LoRa 是LPWAN通信技术中的一种，是美国Semtech公司采用和推广的一种基于扩频技术的超远距离无线传输方案。这一方案改变了以往关于传输距离与功耗的折衷考虑方式为用户提供一种简单的能实现远距离、长电池寿命、大容量的系统，进而扩...

亚马逊云科技技术品牌专区

物联网主机E6000引领工业自动化的新篇章

亚马逊云科技技术品牌专区

搞 IoT 物联网，你居然要懂这么多种协议...

物联网协议是指在物联网环境中用于设备间通信和数据传输的协议。根据不同的作用，物联网协议可分为传输协议、通信协议和行业协议。传输协议：一般负责子网内设备间的组网及通信。例如 Wi-Fi、Ethernet、NFC、 Zigbee、Bluetooth、GPRS、3G/4G/5G等。这些协议能够确保在网络上传输的数据的安全性和可靠性。通讯协议：主要是运行在传统互联网TCP/IP协议之上的设备通讯协议，负责