开始去O，从Oracle 迁移到 PG-每日运维

开始去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 ;

导入表结构

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

开始去O，从Oracle 迁移到 PG

开始去O，从Oracle 迁移到 PG

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

ora2pg概述

当前环境

安装准备环境

安装perl

安装Oracle Instant Client

安装DBI

安装DBD Oracle驱动模块

安装DBD PG驱动模块

安装ora2pg

迁移操作

源端准备

ora2pg导出表结构

测试链接

迁移成本评估

导出结构

导出数据

查看导出文件

导入到PG环境中

创建scott库

导入表结构

导入索引

导入数据

数据验证

相关文章

发布评论取消回复

大树

社区版oceanbase安装

oracle 查询改写

ORA-00600 internal error code, arguments [25026]

开始去O，从Oracle 迁移到 PG

开始去O，从Oracle 迁移到 PG 》 文章均为本人实践后进行撰写，参考文章在文章最下方

ora2pg概述

当前环境

安装准备环境

安装perl

安装Oracle Instant Client

安装DBI

安装DBD Oracle驱动模块

安装DBD PG驱动模块

安装ora2pg

迁移操作

源端准备

ora2pg导出表结构

测试链接

迁移成本评估

导出结构

导出数据

查看导出文件

导入到PG环境中

创建scott库

导入表结构

导入索引

导入数据

数据验证

相关文章

发布评论 取消回复

大树

社区版oceanbase安装

oracle 查询改写

ORA-00600 internal error code, arguments [25026]

开始去O，从Oracle 迁移到 PG

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

发布评论取消回复