PostgreSQL插件之ora2pg

1.环境介绍

项目	信息
源端数据库	Oracle 11.2.0.4
目标端数据库	PostgreSQL 14.6
操作系统版本	CentOS 7.6
迁移工具	Ora2PG 22.1
Oracle 连接驱动	DBD-Oracle 1.80
Oracle客户端	Oracle Instance Client 11.2.04
依赖包	perl-DBD-Pg、perl-DBI、perl-ExtUtils-MakeMaker、perl-Params-Validate
迁移Schema	Ora11g:TEST2 àPG::TEST2（数据量60M）

2.源端数据检查

2.1数据对象统计

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
T3
T2
T1_TMP
T1

2.2数据量统计

SQL> select count(*) from t3;
  COUNT(*)
----------
     86361
SQL> select count(*) from t2;
  COUNT(*)
----------
     86358
SQL> select count(*) from t1_tmp;
  COUNT(*)
----------
    172702
SQL> select count(*) from t1;
  COUNT(*)
----------
         1

3.安装工具

3.1安装依赖包

yum install -y perl-DBD-Pg
yum install -y perl-DBI*
yum install -y perl-ExtUtils-MakeMaker
yum install -y perl-Params-Validate.x86_64

3.2安装Oracle客户端

unzip instantclient-basic-linuxx64.zip -d ./
unzip instantclient-sqlplus-linuxx64.zip -d ./
unzip instantclient-sdk-linuxx64.zip -d ./
export ORACLE_HOME=/ora2pg/instantclient_21_9
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:$PATH

3.3安装Oracle驱动

tar -zxf DBD-Oracle-1.80.tar.gz
perl Makefile.PL -l
make
make install 

3.4安装Ora2PG

tar -zxf ora2pg-22.1\ \(1\).tar.gz
cd ora2pg-22.1/
perl Makefile.PL
make && make install

4.迁移流程

4.1编辑配置文件

vi ora2pg.conf
ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
#If you use SID
ORACLE_DSN dbi:Oracle:host=test2;sid=ora11g;port=1521
#If you use service name
ORACLE_DSN dbi:Oracle://localhost:1521/ora11g
ORACLE_USER test2
ORACLE_PWD oracle
TYPE              TABLE VIEW GRANT COPY
OUTPUT                    output.sql
PG_VERSION 14
# Oracle schema/owner to use
SCHEMA    test2
PG_SCHEMA      test2
# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA      1

4.2执行Oracle数据导出

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:$LD_LIBRARY_PATH
[root@test2 ora2pg]# ora2pg -c ora2pg.conf 
[========================>] 4/4 tables (100.0%) end of scanning.    
Retrieving table partitioning information...
[>                        ] 0/4 tables (0.0%) end of scanning.      
[========================>] 4/4 tables (100.0%) end of table export.
[========================>] 0/0 views (100.0%) end of output.       
[========================>] 1/1 rows (100.0%) Table T1 (1 recs/sec)
[======>                  ] 1/4 total rows (25.0%) - (0 sec., avg: 1 recs/sec).
[========================>] 172702/1 rows (17270200.0%) Table T1_TMP (10793 recs/sec)         
[========================>] 172703/4 total rows (4317575.0%) - (16 sec., avg: 10793 recs/sec).
[========================>] 86358/1 rows (8635800.0%) Table T2 (10794 recs/sec)                
[========================>] 259061/4 total rows (6476525.0%) - (24 sec., avg: 10794 recs/sec).
[========================>] 86361/1 rows (8636100.0%) Table T3 (10795 recs/sec)                
[========================>] 345422/4 total rows (8635550.0%) - (32 sec., avg: 10794 recs/sec).
[========================>] 4/4 rows (100.0%) on total estimated data (32 sec., avg: 0 recs/sec)
Fixing function calls in output files...

4.3复制导出文件到目标端

[root@test2 ora2pg]# ls -l
total 43356
-rw-r--r-- 1 root root 44378989 Feb 23 22:54 COPY_output.sql
-rw-r--r-- 1 root root      341 Feb 23 22:53 GRANT_output.sql
-rw-r--r-- 1  777 root      448 Feb 23 22:05 ora2pg.conf
-rw-r--r-- 1 root root     1557 Feb 23 22:53 TABLE_output.sql
-rw-r--r-- 1 root root      338 Feb 23 22:53 VIEW_output.sql
[root@node1 conf]# cp ./*.sql /home/pg14/import/
[root@node1 conf]# chown pg14:pg14 /home/pg14/import/*.sql

4.4目标端创建库和用户

psql
create database test2;
exit
psql -Upostgres -d test2
create user test2 with password '123';

4.5目标端执行数据导入

psql -Upostgres -d test2 <TABLE_output.sql 
psql -Upostgres -d test2 <GRANT_output.sql 
psql -Upostgres -d test2 <VIEW_output.sql
psql -Upostgres -d test2 <COPY_output.sql

5.数据验证

[postgres@test2 ora2pg]$ psql -Utest2 -dtest2
psql (12.2)
Type "help" for help.

test2=> \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 test2  | t1     | table | postgres
 test2  | t1_tmp | table | postgres
 test2  | t2     | table | postgres
 test2  | t3     | table | postgres
(4 rows)

test2=> select count(*) from t3;
 count 
-------
 86361
(1 row)

test2=> select count(*) from t2;
 count 
-------
 86358
(1 row)

test2=> select count(*) from t1_tmp;
 count  
--------
 172702
(1 row)

test2=> select count(*) from t1;
 count 
-------
     1
(1 row)