运维首页 > 数据运维 > 开始去O，从Oracle 迁移到 PG

开始去O，从Oracle 迁移到 PG

2024年 4月 12日 9.8k 0

开始去O，从Oracle 迁移到 PG

》 文章均为本人实践后进行撰写，参考文章在文章最下方

ora2pg概述

​ Ora2Pg是一个免费的工具，用于将Oracle数据库迁移到PostgreSQL。它连接Oracle数据库，自动扫描并提取它的结构或数据，然后生成可以装载到PostgreSQL数据库的SQL脚本。这些脚本可以用于将数据库结构和数据导入 PostgreSQL 之中。Ora2Pg可以从逆向工程Oracle数据库到大型企业数据库迁移，或者简单地将一些Oracle数据复制到PostgreSQL数据库中。它非常容易使用，并且不需要任何Oracle数据库知识，不需要提供连接到Oracle数据库所需的参数。

​ Ora2Pg 由一个 Perl 脚本（ora2pg）以及一个 Perl 模块（Ora2Pg.pm）组成，唯一需要做的事情就是修改它的配置文件 ora2pg.conf，设置连接 Oracle 数据库的 DSN 和一个可选的模式名称。完成之后，只需要设置导出的类型：TABLE（包括约束）、VIEW、MVIEW、TABLESPACE、SEQUENCE、INDEXES、TRIGGER、GRANT、FUNCTION、PROCEDURE、PACKAGE、PARTITION、TYPE、INSERT 或 FDW、COPY、QUERY、KETTLE 以及 SYNONYM。

​ 默认情况下，Ora2Pg 导出一个文件，可通过 psql 客户端将文件加载到 PostgreSQL 数据库；但是也可以在配置文件中设置一个数据库的 DSN，直接导入 PostgreSQL 数据库。通过 ora2pg.conf 中的配置选项，可以控制导出的内容和方式。

对象 ora2pg是否支持
view 是
trigger 是，某些情况需要手工修改脚本
package 是，某些情况需要手工修改脚本
sequence 是
function 是
procedure 是，某些情况需要手工修改脚本
type 是，某些情况需要手工修改脚本
materialized view 是，某些情况需要手工修改脚本

下来实验一下ora2pg工具迁移

当前环境

内容 源端 目标端
IP 10.6.132.243 10.6.132.243
数据库 Oracle 11.2.0.4 postgresql 16
操作系统 centos 7.6 centos 7.6
迁移数据 SCOTT SCOTT

软件包版本

perl 5.16
ora2pg 24.3

安装准备环境

安装perl

要求：perl版本5.16以上

yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

root@obproxy01:/home/ora2pg-24.3#perl -v
This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
(with 44 registered patches, see perl -V for more detail)

安装Oracle Instant Client

下载地址：https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-tools-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

更新链路路径

echo /usr/lib/oracle/11.2/client64/lib/ > /etc/ld.so.conf.d/oracle-instantclient.conf
ldconfig

配置环境变量

echo "
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_RUN_PATH=/usr/lib/oracle/11.2/client64/lib/
export POSTGRES_HOME=/home/postgre/pg-16.0/
export POSTGRES_INCLUDE=/home/postgre/pg-16.0/include
export POSTGRES_LIB=/home/postgre/pg-16.0/lib" > /etc/profile
. /etc/profile

要迁移 Oracle 数据库，需要安装 DBD Oracle Perl 模块

安装DBI

DBI(Database Interface)是perl连接数据库的接口。其是perl连接数据库的最优秀方法

下载地址：https://cpan.metacpan.org/authors/id/T/TI/TIMB/

wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.643.tar.gz
tar -xvf DBI-1.643.tar.gz
cd DBI-1.643/
perl Makefile.PL PREFIX=
make
make install

安装DBD Oracle驱动模块

DBI只是个抽象层，要实现支持不同的数据库，则需要在DBI之下，编写针对不同数据库的驱动，对MySql来说，有DBD::Mysql, 而对Oracle来说，则是DBD::Oracle。其中的DBD这是DataBase Driver之简写

下载地址：https://cpan.metacpan.org/authors/id/P/PY/PYTHIAN/

本文使用DBD-Oracle-1.74版本
wget https://cpan.metacpan.org/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz
tar -xf DBD-Oracle-1.74.tar.gz
cd DBD-Oracle-1.74
--安装DBD时必须指定Oracle HOME，由于该环境Oracle 和pg在一台服务器，直接使用oracle用户下的ORACLE HOME来运行，正常情况下Oracle和pg库是分开的，因此需要安装Oracle 客户端并切指定ORACLE HOME
perl Makefile.PL
make
make install

安装DBD PG驱动模块

下载地址：https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/

root操作，需要配置好POSTGRES_HOME变量
wget https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.16.3.tar.gz
tar -xvf DBD-Pg-3.16.3.tar.gz
cd DBD-Pg-3.16.3/
perl Makefile.PL
make
make install

安装ora2pg

下载地址：https://github.com/darold/ora2pg/releases

使用root运行
本文使用的是：ora2pg 24.3版本
cd /opt
wget https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz
tar -xf ora2pg-24.3.tar.gz
cd ora2pg-22.1
perl Makefile.PL PREFIX=
make
make install

默认安装在/usr/local/bin/目录下

环境检查脚本

root@obproxy01:/home/shell#cat 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);
}

root@obproxy01:/home/shell#perl check.pl
DBD::Oracle -- 1.74
DBD::Pg -- 3.16.3
DBI -- 1.643
Ora2Pg -- 24.3
Perl -- 5.16.3

设置环境变量

export PERL5LIB=/usr/bin

迁移操作

源端准备

查看源端对象类型

SYS@fls >select distinct OBJECT_TYPE from dba_objects where OWNER='SCOTT' ;
OBJECT_TYPE
-------------------
INDEX
TABLE

ora2pg导出表结构

默认情况下，ora2pg会查找/etc/ora2pg/ora2pg.conf配置文件，如果存在，则直接执行：/usr/local/bin/ora2pg

root@obproxy01:/home/DBI/ora2pg-24.3#cat ora2pg.conf.dist | grep -v '^#' | grep -v '^$' > ora2pg.conf
#手动创建配置文件，如下配置中没有添加并行，可根据说明添加
[root@obproxy01:/home/ora2pg-24.3]$vim ora2pg.conf
ORACLE_HOME /usr/lib/oracle/11.2/client64
ORACLE_DSN dbi:Oracle:host=10.6.132.243;sid=fls;port=1521
ORACLE_USER system
ORACLE_PWD Adamkuo#l23
EXPORT_SCHEMA 1
TYPE TABLE,INDEX
OUTPUT output.sql
PG_VERSION 16
# Oracle schema/owner to use
SCHEMA scott
PG_SCHEMA scott
# Export Oracle schema to PostgreSQL schema

测试链接

root@obproxy01:/home/DBI/ora2pg-24.3#ora2pg -t SHOW_VERSION -c ora2pg.conf
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

迁移成本评估

root@obproxy01:/home/DBI/ora2pg-24.3#ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf
[2024-04-11 11:21:19] [========================>] 7/7 tables (100.0%) end of scanning.
[2024-04-11 11:21:20] [========================>] 6/6 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v24.3 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Schema SCOTT
Size 689.31 MB

-------------------------------------------------------------------------------
Object Number Invalid Estimated cost Comments Details
-------------------------------------------------------------------------------
DATABASE LINK 0 0 0.00 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE 1 0 10.00 Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior. sys_temp_fbt.
INDEX 4 0 1.10 1 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. 1 b-tree index(es).
JOB 0 0 0.00 Job are not exported. You may set external cron job with them.
SYNONYM 0 0 0.00 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.
TABLE 7 0 1.00 Total number of rows: 6027466. Top 10 of tables sorted by number of rows:. t1 has 5877312 rows. t_stats has 91963 rows. test2 has 58168 rows. emp has 14 rows. salgrade has 5 rows. dept has 4 rows. Top 10 of largest tables:.
-------------------------------------------------------------------------------
Total 12 0 12.10 12.10 cost migration units means approximatively 1 person-day(s). The migration unit was set to 5 minute(s)

-------------------------------------------------------------------------------
Migration level : A-1
-------------------------------------------------------------------------------

Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 5 days
C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------

如果评估时有FATAL: ORA-00604: error occurred at recursive SQL level 1、ORA-08177报错，还没有找到原因，但是目前并不影响实验后续的导出操作，所以先忽略了

导出结构

root@obproxy01:/home/DBI/ora2pg-24.3#ora2pg -c ora2pg.conf
[2024-04-11 11:57:03] [========================>] 7/7 tables (100.0%) end of scanning.
type option must be (TABLE, VIEW, GRANT, SEQUENCE, SEQUENCE_VALUES, TRIGGER, PACKAGE, FUNCTION, PROCEDURE, PARTITION, TYPE, INSERT, COPY, TABLESPACE, SHOW_REPORT, SHOW_VERSION, SHOW_SCHEMA, SHOW_TABLE, SHOW_COLUMN, SHOW_ENCODING, FDW, MVIEW, QUERY, KETTLE, DBLINK, SYNONYM, DIRECTORY, LOAD, TEST, TEST_COUNT, TEST_VIEW, TEST_DATA), unknown INDEX
[2024-04-11 11:57:07] [========================>] 7/7 tables (100.0%) end of table export.
Fixing function calls in output files...
root@obproxy01:/home/DBI/ora2pg-24.3#cat TABLE_output.sql

导出数据

导出数据，将TYPE改为COPY后再次执行执行

root@obproxy01:/home/DBI/ora2pg-24.3#vim ora2pg.conf
root@obproxy01:/home/DBI/ora2pg-24.3#cat ora2pg.conf | grep TYPE
TYPE COPY

导出数据时文件和表结构文件不同，表结构文件名前缀启动加TABLE
root@obproxy01:/home/DBI/ora2pg-24.3#ora2pg -c ora2pg.conf
[2024-04-11 12:01:31] [========================>] 7/7 tables (100.0%) end of scanning.
[2024-04-11 12:02:09] [========================>] 0/0 rows (100.0%) Table BONUS (0 recs/sec)
[2024-04-11 12:02:09] [> ] 0/6027466 total rows (0.0%) - (0 sec., avg: 0 recs/sec).
[2024-04-11 12:02:09] [========================>] 4/4 rows (100.0%) Table DEPT (4 recs/sec)
[2024-04-11 12:02:09] [> ] 4/6027466 total rows (0.0%) - (0 sec., avg: 4 recs/sec).
[2024-04-11 12:02:09] [========================>] 14/14 rows (100.0%) Table EMP (14 recs/sec)
[2024-04-11 12:02:09] [> ] 18/6027466 total rows (0.0%) - (0 sec., avg: 18 recs/sec).
[2024-04-11 12:02:09] [========================>] 5/5 rows (100.0%) Table SALGRADE (5 recs/sec)
[2024-04-11 12:02:09] [> ] 23/6027466 total rows (0.0%) - (0 sec., avg: 23 recs/sec).
[2024-04-11 12:12:14] [========================>] 5877312/5877312 rows (100.0%) Table T1 (9714 recs/sec)
[2024-04-11 12:12:14] [=======================> ] 5877335/6027466 total rows (97.5%) - (605 sec., avg: 9714 recs/sec).
[2024-04-11 12:12:18] [========================>] 58168/58168 rows (100.0%) Table TEST2 (14542 recs/sec)
[2024-04-11 12:12:18] [=======================> ] 5935503/6027466 total rows (98.5%) - (609 sec., avg: 9746 recs/sec).
[2024-04-11 12:12:26] [========================>] 91963/91963 rows (100.0%) Table T_STATS (13137 recs/sec)
[2024-04-11 12:12:26] [========================>] 6027466/6027466 total rows (100.0%) - (617 sec., avg: 9768 recs/sec).
[2024-04-11 12:12:26] [========================>] 6027466/6027466 rows (100.0%) on total estimated data (617 sec., avg: 9768 recs/sec)

查看导出文件

--导出一个表、一个索引结构和一个表数据文件
root@obproxy01:/home/DBI/ora2pg-24.3#ls -alrth
-rw-r--r-- 1 root root 2.5K 4月 11 11:57 TABLE_output.sql
-rw-r--r-- 1 root root 0 4月 11 11:57 INDEX_output.sql
-rw-r--r-- 1 root root 806M 4月 11 12:12 output.sql
...

导入到PG环境中

创建scott库

postgre@obproxy01:/home/postgre/pg-16.0/bin$./psql postgres
postgres=# create database scott;
postgres=# create user scott with encrypted password '123456';
#postgres=# alter role scott createrole superuser;
#postgres=# grant all privileges on database scott to scott;
postgres=# alter database scott owner to scott ;

postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+---------+----------+-----------------+------------+------------+------------+-----------+---------------------
postgres | postgre | UTF8 | libc | zh_CN.utf8 | zh_CN.utf8 | | |
scott | scott | UTF8 | libc | zh_CN.utf8 | zh_CN.utf8 | | | =Tc/scott +
| | | | | | | | scott=CTc/scott
template0 | postgre | UTF8 | libc | zh_CN.utf8 | zh_CN.utf8 | | | =c/postgre +
| | | | | | | | postgre=CTc/postgre
template1 | postgre | UTF8 | libc | zh_CN.utf8 | zh_CN.utf8 | | | =c/postgre +
| | | | | | | | postgre=CTc/postgre
(4 rows)

导入表结构

postgre@obproxy01:/home/postgre/pg-16.0/bin$./psql scott -U scott -f /home/postgre/pg-16.0/impsql/TABLE_output.sql
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
ALTER TABLE

导入索引

postgre@obproxy01:/home/postgre/pg-16.0/bin$./psql scott -U scott -f /home/postgre/pg-16.0/impsql/INDEX_output.sql
由于该环境没有，则没有输出

导入数据

postgre@obproxy01:/home/postgre/pg-16.0/bin$./psql scott -U scott -f /home/postgre/pg-16.0/impsql/output.sql
...一堆输出，导出速度比较快，比导出快很多，毕竟导出要进行转换

数据验证

postgre@obproxy01:/home/postgre/pg-16.0/bin$./psql scott
scott=# select * from pg_tables where schemaname='scott';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
scott | bonus | scott | | f | f | f | f
scott | salgrade | scott | | f | f | f | f
scott | t1 | scott | | f | f | f | f
scott | test2 | scott | | t | f | f | f
scott | t_stats | scott | | t | f | f | f
scott | dept | scott | | t | f | t | f
scott | emp | scott | | t | f | t | f
(7 rows)

scott=# select count(*) from scott.t1;
count
---------
5877312
(1 row)
...

参考文档：https://www.pgfans.cn/a/2231

的头像 大树
数据运维
0 0

相关文章

pt-kill工具的使用
pt-ioprofile工具包的使用
数据库管理-第216期 Oracle的高可用-01（20240703）
DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION
数据库事务的四大特性： ACID 
使用BBED修复损坏的SYSTEM文件头

发布评论

大树的头像

大树

这个人很懒，什么都没有留下～

文章7999 评论0