源库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]#