迁移指南

1. 迁移概述

数据库迁移是指将数据从一个数据库转移到另一个数据库的过程,两端的数据库可能是PostgreSQL, IvorySQL, MySQL, Oracle, SQL Server等。迁移过程是一个具有挑战性的复杂过程,需要对数据库的原理以及各自的特性了如指掌。如果应用已经部署到生产环境并处于正常运行状态,为了保持业务不中断运行,并且不发生数据丢失,数据库迁移后需进行平滑的应用迁移。

迁移后数据库和系统应符合以下要求:

  • 迁移后的数据库系统应完全承载原数据库系统的数据。避免迁移过程中数据丢失导致新的数据库系统数据不完整。

  • 迁移后的数据库系统应完全适配原有数据库的功能。避免迁移后因数据类型或语法、函数的不支持,且无替代方案,导致整个业务系统的无法运行或抛错。

  • 迁移后的数据库应适配整个业务系统的上下游,稳定可靠的保障整个业务系统的运行。

  • 迁移后的数据库各方面综合性能不能弱于原数据库,为整个业务系统提供性能保证。

2. 迁移工具Ora2Pg

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

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

默认情况下,Ora2Pg 导出一个 SQL 文件,可以通过 IvorySQL 客户端工具 psql 执行导出的SQL文件。当进行数据迁移时,可以在配置文件中设置一个目标数据库的 DSN,直接将数据从Oracle导入到 IvorySQL 数据库中。

对象

ora2pg是否支持

view

trigger

是,某些情况下需要手工修改脚本

sequence

function

procedure

是,某些情况下需要手工修改脚本

type

是,某些情况下需要手工修改脚本

materialized view

是,某些情况下需要手工修改脚本

3. 迁移Oracle数据库至IvorySQL

3.1. 环境准备

linux环境

Oracle版本

IvorySQL版本

Centos Stream 9

19.0.0.0

3.4

3.2. 依赖环境安装

3.2.1. 安装Perl

[root@localhost /]# dnf install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
[root@localhost /]# 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)


Copyright 1987-2012, Larry Wall


Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.


Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

3.2.2. 安装DBI模块

DBI,Database Independent Interface,是 Perl 语言连接数据库的接口

# tar zxvf DBI-1.643.tar.gz
# cd DBI-1.643/
# perl Makefile.PL
# make && make install

3.2.3. 安装DBD-Oracle

下载地址:https://sourceforge.net/projects/ora2pg/

设置环境变量; 加载环境变量;因为必须定义ORACLE_HOME环境变量;本例在ivorysql用户下配置环境变量

export LD_LIBRARY_PATH=/opt/oracle/product/19c/dbhome_1/lib:$LD_LIBRARY_PATH
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
# tar -zxvf DBD-Oracle-1.76.tar.gz # source /home/postgres/.bashrc
# cd DBD-Oracle-1.76
# perl Makefile.PL
# make && make install

3.3. 安装Ora2pg

下载地址:https://sourceforge.net/projects/ora2pg/

# tar -xjf ora2pg-24.0.tar.bz2
# cd ora2pg-24.0
# perl Makefile.PL
# make && make install

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

检查软件环境:

# vi 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.76
DBD::Pg      --  3.8.0
DBI          --  1.643
Ora2Pg       --  24.0
Perl         --  5.16.3
设置环境变量

export PERL5LIB=<your_install_dir> #export PERL5LIB=/usr/local/bin/

=== 源端准备工作

更新oracle统计信息 提高性能

BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SH'); DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT'); DBMS_STATS.GATHER_SCHEMA_STATS('HR'); DBMS_STATS.GATHER_DATABASE_STATS; DBMS_STATS.GATHER_DICTIONARY_STATS; END;/

查询源端对象的类型

```bash
SYS@PROD1>set pagesize 200
SYS@PROD1>select distinct OBJECT_TYPE from  dba_objects where OWNER in ('SH','SCOTT','HR') ;
OBJECT_TYPE
-------------------
INDEX PARTITION
TABLE PARTITION
SEQUENCE
PROCEDURE
LOB                                X
TRIGGER
DIMENSION                          X
MATERIALIZED VIEW
TABLE
INDEX
VIEW
11 rows selected.

3.4. ora2pg导出表结构

配置ora2pg.conf:

默认情况下,Ora2Pg会查找/etc/ora2pg/ora2pg.conf配置文件,如果文件存在,您只需执行:/usr/local/bin/ora2pg

cat /etc/ora2pg/ora2pg.conf.dist  | grep -v ^# |grep -v ^$ >ora2pg.conf
vi ora2pg.conf
# cat ora2pg.conf
ORACLE_HOME     /opt/oracle/product/19c/dbhome_1
ORACLE_DSN      dbi:Oracle:host=localhost;sid=ORCLCDB;port=1521
ORACLE_USER     system
ORACLE_PWD      oracle
SCHEMA          SH
EXPORT_SCHEMA  1         # 将用户导入到PostgreSQL数据库中
DISABLE_UNLOGGED  1       #避免将NOLOGGING属性设为UNLOGGED
SKIP  fkeys ukeys checks      #跳过外键 唯一 和检查约束
TYPE            TABLE,VIEW,GRANT,SEQUENCE,TABLESPACE,PROCEDURE,TRIGGER,FUNCTION,PACKAGE,PARTITION,TYPE,MVIEW,QUERY,DBLINK,SYNONYM,DIRECTORY,TEST,TEST_VIEW
NLS_LANG    AMERICAN_AMERICA.UTF8
OUTPUT     sh.sql
  1. 只能同时执行一种类型的导出,因此TYPE指令必须是唯一的。如果您有多个,则只会在文件中找到最后一个。但我测试就可以同时导出多个类型的。

  2. 请注意,您可以通过向TYPE指令提供以逗号分隔的导出类型列表来链接多个导出,但在这种情况下,您不能将COPY或INSERT与其他导出类型一起使用。

  3. 某些导出类型不能或不应该直接加载到 IvorySQL 数据库中,仍然需要很少的手动编辑。GRANT,TABLESPACE,TRIGGER,FUNCTION,PROCEDURE,TYPE,QUERY和PACKAGE导出类型就是这种情况,特别是如果您有PLSQL代码或Oracle特定SQL。

  4. 对于TABLESPACE,您必须确保系统上存在文件路径,对于SYNONYM,您可以确保对象的所有者和模式对应于新的PostgreSQL数据库设计。

  5. 建议导出表结构时,一个类型一个类型的操作,避免其它错误相互影响。

3.4.1. 测试连接

设置Oracle数据库DSN后,您可以执行ora2pg以查看它是否有效:

#  ora2pg -t SHOW_VERSION -c ora2pg.conf

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

3.4.2. 迁移成本评估

估算从Oracle到PostgreSQL的迁移过程的成本并不容易。为了获得对此迁移成本的良好评估,Ora2Pg将检查所有数据库对象,所有函数和存储过程,以检测是否仍有一些对象和PL / SQL代码无法由Ora2Pg自动转换。

Ora2Pg具有内容分析模式,该模式检查Oracle数据库以生成有关Oracle数据库包含的内容和无法导出的内容的文本报告。

# ora2pg -t SHOW_REPORT --estimate_cost  -c ora2pg.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v24.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Schema  SH
Size    287.25 MB
-------------------------------------------------------------------------------
Object  Number  Invalid Estimated cost  Comments        Details
-------------------------------------------------------------------------------
DATABASE LINK   0       0       0       Database links will be exported as SQL/MED IvorySQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
DIMENSION       5       0       0
GLOBAL TEMPORARY TABLE  0       0       0       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.
INDEX   20      0       3.4     14 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) and hash index(es) will be exported as b-tree index(es) if any. 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.      11 bitmap index(es). 1 domain index(es). 2 b-tree index(es).

INDEX PARTITION 196     0       0       Only local indexes partition are exported, they are build on the column used for the partitioning.

JOB     0       0       0       Job are not exported. You may set external cron job with them.

MATERIALIZED VIEW       2       0       6       All materialized view will be exported as snapshot materialized views, they are only updated when fully refreshed.

SYNONYM 0       0       0       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   11      0       1.1     1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration directive to export as file_fdw foreign tables or use COPY in your code if you just want to load data from external files.     Total number of rows: 1063384. Top 10 of tables sorted by number of rows:. sales has 918843 rows. costs has 82112 rows. customers has 55500 rows. supplementary_demographics has 4500 rows. times has 1826 rows. promotions has 503 rows. products has 72 rows. countries has 23 rows. channels has 5 rows. sales_transactions_ext has 0 rows. Top 10 of largest tables:.

TABLE PARTITION 56      0       5.6     Partitions are exported using table inheritance and check constraint. Hash and Key partitions are not supported by PostgreSQL and will not be exported.  56 RANGE partitions..

VIEW    1       0       1       Views are fully supported but can use specific functions.

-------------------------------------------------------------------------------

Total   291     0       17.10   17.10 cost migration units means approximatively 1 man-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

-------------------------------------------------------------------------------

3.4.3. 导出SH表构

#  ora2pg  -c ora2pg.conf
[========================>] 11/11 tables (100.0%) end of scanning.

[========================>] 12/12 tables (100.0%) end of table export.

[========================>] 1/1 views (100.0%) end of output.

[========================>] 0/0 sequences (100.0%) end of output.

[========================>] 0/0 procedures (100.0%) end of procedures export.

[========================>] 0/0 triggers (100.0%) end of output.

[========================>] 0/0 functions (100.0%) end of functions export.

[========================>] 0/0 packages (100.0%) end of output.

[========================>] 56/56 partitions (100.0%) end of output.

[========================>] 0/0 types (100.0%) end of output.

[========================>] 2/2 materialized views (100.0%) end of output.
[========================>] 0/0 dblink (100.0%) end of output.

[========================>] 0/0 synonyms (100.0%) end of output.

[========================>] 2/2 directory (100.0%) end of output.

Fixing function calls in output files....

3.4.4. 导出SH用户数据

配置ora2pg.conf 的TYPE 为COPY 或 INSERT

# cp ora2pg.conf sh_data.conf

# vi sh_data.conf

ORACLE_HOME     /opt/oracle/product/19c/dbhome_1

ORACLE_DSN      dbi:Oracle:host=localhost;sid=ORCLCDB;port=1521

ORACLE_USER     system

ORACLE_PWD      oracle

SCHEMA          SH

EXPORT_SCHEMA  1

DISABLE_UNLOGGED  1

SKIP  fkeys ukeys checks

TYPE           COPY

NLS_LANG    AMERICAN_AMERICA.UTF8

OUTPUT     sh_data.sql

导出数据

# ora2pg  -c sh_data.conf

[========================>] 11/11 tables (100.0%) end of scanning.

[========================>] 5/5 rows (100.0%) Table CHANNELS (5 recs/sec)

[>                        ]       5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).

[>                        ]     0/82112 rows (0.0%) Table COSTS_1995 (0 recs/sec)

[>                        ]       5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).

[>                        ]     0/82112 rows (0.0%) Table COSTS_H1_1997 (0 recs/sec)

[>                        ]       5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).

[>                        ]     0/82112 rows (0.0%) Table COSTS_1996 (0 recs/sec)

[>                        ]       5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).

……………………………………………………………

[========================>] 4500/4500 rows (100.0%) Table SUPPLEMENTARY_DEMOGRAPHICS (4500 recs/sec)

[=======================> ] 1061558/1063384 total rows (99.8%) - (45 sec., avg: 23590 recs/sec).

[========================>] 1826/1826 rows (100.0%) Table TIMES (1826 recs/sec)

[========================>] 1063384/1063384 total rows (100.0%) - (45 sec., avg: 23630 recs/sec).

[========================>] 1063384/1063384 rows (100.0%) on total estimated data (45 sec., avg: 23630 recs/sec)

Fixing function calls in output files...

查看导出的文件:

[root@test01 ora2pg]# ls -lrt *.sql

-rw-r--r-- 1 root root 15716 Jul  2 21:21 TABLE_sh.sql

-rw-r--r-- 1 root root   858 Jul  2 21:21 VIEW_sh.sql

-rw-r--r-- 1 root root  2026 Jul  2 21:21 TABLESPACE_sh.sql

-rw-r--r-- 1 root root   345 Jul  2 21:21 SEQUENCE_sh.sql

-rw-r--r-- 1 root root  2382 Jul  2 21:21 GRANT_sh.sql

-rw-r--r-- 1 root root   344 Jul  2 21:21 TRIGGER_sh.sql

-rw-r--r-- 1 root root   346 Jul  2 21:21 PROCEDURE_sh.sql

-rw-r--r-- 1 root root   344 Jul  2 21:21 PACKAGE_sh.sql

-rw-r--r-- 1 root root   345 Jul  2 21:21 FUNCTION_sh.sql

-rw-r--r-- 1 root root  6771 Jul  2 21:21 PARTITION_sh.sql

-rw-r--r-- 1 root root   341 Jul  2 21:21 TYPE_sh.sql

-rw-r--r-- 1 root root   342 Jul  2 21:21 QUERY_sh.sql

-rw-r--r-- 1 root root   950 Jul  2 21:21 MVIEW_sh.sql

-rw-r--r-- 1 root root   344 Jul  2 21:21 SYNONYM_sh.sql

-rw-r--r-- 1 root root   926 Jul  2 21:21 DIRECTORY_sh.sql

-rw-r--r-- 1 root root   343 Jul  2 21:21 DBLINK_sh.sql

-rw-r--r-- 1 root root 55281235 Jul  2 17:11 sh_data.sql

以同样的方法分别导出HR,SCOTT用户数据。

3.5. 在IvorySQL环境中创建orcl库

创建ORCL 数据库

# su - ivorysql

Last login: Tue Jul  2 20:04:30 CST 2019 on pts/3

$ createdb orcl

$ psql

psql (15.6, server 16.2)

Type "help" for help.



ivorysql=# \l

                                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
-----------+----------+-----------+---------+-------+------------+-----------------+-----------------------
 ivorysql  | ivorysql | SQL_ASCII | C       | C     |            | libc            |
 orcl      | ivorysql | SQL_ASCII | C       | C     |            | libc            |
 postgres  | ivorysql | SQL_ASCII | C       | C     |            | libc            |
 template0 | ivorysql | SQL_ASCII | C       | C     |            | libc            | =c/ivorysql          +
           |          |           |         |       |            |                 | ivorysql=CTc/ivorysql
 template1 | ivorysql | SQL_ASCII | C       | C     |            | libc            | =c/ivorysql          +
           |          |           |         |       |            |                 | ivorysql=CTc/ivorysql

(5 rows)

ivorysql=#
创建SH,HR,SCOTT 用户:
$ psql orcl

psql (15.6, server 16.2)

Type "help" for help.

orcl=#

orcl=# create user sh with password 'sh';

CREATE ROLE

4. 迁移门户

4.1. 导入表结构

由于有物化视图,在TABLE_sh.sql 里包含了物化视图的索引,会创建失败。需先创建表,在创建物化视图,最后创建索引。

取消物化视图索引,后面单独创建:

CREATE INDEX fw_psc_s_mv_chan_bix ON fweek_pscat_sales_mv (channel_id);

CREATE INDEX fw_psc_s_mv_promo_bix ON fweek_pscat_sales_mv (promo_id);

CREATE INDEX fw_psc_s_mv_subcat_bix ON fweek_pscat_sales_mv (prod_subcategory);

CREATE INDEX fw_psc_s_mv_wd_bix ON fweek_pscat_sales_mv (week_ending_day);

CREATE TEXT SEARCH CONFIGURATION en (COPY = pg_catalog.english);
ALTER TEXT SEARCH CONFIGURATION en ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;
psql orcl  -f  tab.sql

ALTER TABLE PARTITION sh.sales OWNER TO sh;
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
ALTER TABLE
ALTER TABLE
ALTER TABLE
………………………………

4.2. 给对象授权

cat psql orcl  -f  GRANT_sh.sql
CREATE USER SH WITH PASSWORD 'change_my_secret' LOGIN;
ALTER TABLE sh.fweek_pscat_sales_mv OWNER TO sh;
GRANT ALL ON  sh.fweek_pscat_sales_mv TO sh;

4.3. 导入物化视图结构

物化视图需要相关查询权限,所以导入权限,注意这里要跟上用户

$  psql orcl sh -f  MVIEW_sh.sql
SELECT 0
SELECT 0
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

4.4. 导入视图

$  psql orcl  -f  VIEW_sh.sql
SET
SET
SET
CREATE VIEW

4.5. 导入分区表

$  psql orcl  -f  PARTITION_sh.sql
SET
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
…………………………

4.6. 导入数据

$  psql orcl   -f   sh_data.sql
SET
COPY 0
SET
COPY 0
SET
COPY 0
SET
COPY 0
SET
COPY 0
SET
COPY 0
SET
COPY 0
SET
COPY 4500
SET
COPY 1826
COMMIT

5. 数据验证

源库,目标端抽取部份对象对比:

SYS@PROD1>select count(*) from sh.products;
  COUNT(*)
----------
        72

orcl=#  select count(*) from sh.products;
 count
-------
    72
(1 row)
---------------------------------------------------------------------------

SYS@PROD1>select count(*) from sh.channels;

  COUNT(*)

----------

         5

orcl=#   select count(*) from sh.channels;
 count
-------

     5

(1 row)

--------------------------------------------------------------------------

SYS@PROD1>select count(*) from sh.customers ;

  COUNT(*)
----------

     55500
orcl=# select count(*) from sh.customers ;
 count
-------
 55500
(1 row)

6. 生成迁移模板

使用时,两个选项—​project_base和—​init_project向Ora2Pg表明他必须创建一个项目模板,其中包含工作树,配置文件和从Oracle数据库导出所有对象的脚本。 生成通用配置文件。 1.创建脚本export_schema.sh以自动执行所有导出。2.创建脚本import_all.sh以自动执行所有导入。例:

mkdir -p  /ora2pg/migration

# ora2pg --project_base /ora2pg/migration/ --init_project test_project
Creating project test_project.
/ora2pg/migration//test_project/
        schema/
                dblinks/
                directories/
                functions/
                grants/
                mviews/
                packages/
                partitions/
                procedures/
                sequences/
                synonyms/
                tables/
                tablespaces/
                triggers/
                types/
                views/
        sources/
                functions/
                mviews/
                packages/
                partitions/
                procedures/
                triggers/
                types/
                views/
        data/
        config/
        reports/
Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.