Migration guide

1. Migration overview

The so-called database migration is any form of data movement between this database and another database, and the databases at both ends may be PostgreSql, mySQL, oracle, Sql Server, Highgo DB, etc. The migration process is a challenging, complex process that requires a thorough understanding of how databases work and their characteristics. If the application has been deployed to the production environment and is in a normal operating state, a smooth application migration is required after database migration to maintain uninterrupted business operation and no data loss.

After migration, databases and systems should meet the following requirements:

The migrated database system should fully host the data of the original database system. Avoid data loss during migration that causes incomplete data to the new database system.
The migrated database system should fully adapt to the functions of the original database. Avoid the inability to run or throw errors of the entire business system due to data types, syntax, and functions that are not supported after migration, and there is no alternative.
The migrated database should be adapted to the upstream and downstream of the entire business system to ensure the stable and reliable operation of the entire business system.
The comprehensive performance of the migrated database cannot be weaker than that of the original database, providing performance guarantee for the entire business system.

2. Migration tool——Ora2Pg

Ora2Pg is a free tool for migrating Oracle databases to an IvorySQL-compatible schema. It connects to your Oracle database, automatically scans and extracts its structure or data, and then generates SQL scripts that can be loaded into an IvorySQL database. Ora2Pg can migrate from a reverse-engineered Oracle database to a large enterprise database, or simply copy some Oracle data into an IvorySQL database. It is very easy to use and does not require any Oracle database knowledge without providing the parameters required to connect to Oracle Database.

Ora2Pg consists of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm), the only thing that needs to be done is to modify its configuration file, ora2pg.conf, set the DSN to connect to the Oracle database, and an optional SCHEMA name. ONCE THAT’S DONE, YOU ONLY NEED TO SET THE EXPORTED TYPE: TABLE (INCLUDING CONSTRAINTS AND INDEXES), VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT OR COPY, FDW, QUERY, KETTLE, AND SYNONYM.

By default, Ora2Pg exports an SQL file, which can be executed through the IvorySQL client tool psql. When performing data migration, you can set the DSN of the target database in the configuration file to import data directly from Oracle into the IvorySQL database.

Object

Whether ora2pg is supported

view

yes

trigger

yes，In some cases, you need to modify the script manually

sequence

yes

function

yes

procedure

yes，In some cases, you need to modify the script manually

type

yes，In some cases, you need to modify the script manually

materialized view

yes，In some cases, you need to modify the script manually

3. Migrate Oracle Database to IvorySQL

3.1. Environment preparation

linux

Oracle Version

IvorySQL Version

Centos 7.X

11.2.0.3.0

2.1

3.2. Environment-dependent installation

3.2.1. Install Perl

[root@localhost /]# yum 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. Install the DBI module

DBI，Database Independent Interface，is the interface of the Perl language to connect to the database

Download address: https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.643.tar.gz

[root@localhost oracle2postgresql]# tar zxvf DBI-1.643.tar.gz
[root@localhost oracle2postgresql]# cd DBI-1.643/
[root@localhost DBI-1.643]# perl Makefile.PL
[root@localhost DBI-1.643]# make
[root@localhost DBI-1.643]# make install

3.2.3. Install DBD-Oracle

Download address：https://sourceforge.net/projects/ora2pg/

Set environment variables; Load environment variables; Because ORACLE must be defined_ HOME environment variable; This example configures environment variables under the ivorysql user

export LD_LIBRARY_PATH=/usr/lib/oracle/18.3/client64/lib:$LD_LIBRARY_PATH
export ORACLE_HOME=/usr/lib/oracle/18.3/client64
# tar -zxvf DBD-Oracle-1.76.tar.gz # source /home/postgres/.bashrc
# cd DBD-Oracle-1.76
# perl Makefile.PL
# make
# make install

3.2.4. Install DBD-PG (optional)

Download address：https://metacpan.org/release/DBD-Pg/

Set environment variables：

export POSTGRES_HOME=/opt/ivorysql/2.1
# tar -zxvf DBD-Pg-3.80.tar.gz
# source /home/ivorysql/.bashrc
# cd DBD-Pg-3.8.0
# perl Makefile.PL
# make
# make install

3.3. Install Ora2pg

Download address：https://sourceforge.net/projects/ora2pg/

[root@Test01 ~]# tar -xjf  ora2pg-20.0.tar.bz2
[root@Test01 ~]# cd ora2pg-xx/
[root@Test01 ~]# perl Makefile.PL  PREFIX=<your_install_dir>
[root@Test01 ora2pg-18.2]# make && make install

Installed in/usr/local/bin/directory by default Check the software environment:

[root@Test01 ~]# 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;
[root@test01 bin]# perl check.pl
DBD::Oracle  --  1.76
DBD::Pg      --  3.8.0
DBI          --  1.642
Ora2Pg       --  20.0
Perl         --  5.16.3

Set environment variables

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

3.4. Source side preparation

Update oracle statistics to improve performance

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;/

Query the source end object pair type

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.5. Ora2pg export table structure

Configure ora2pg.conf

By default, Ora2Pg will find the/etc/ora2pg/ora2pg.conf configuration file. If the file exists, you only need to execute:/usr/local/bin/ora2pg

cat /etc/ora2pg/ora2pg.conf.dist  | grep -v ^# |grep -v ^$ >ora2pg.conf
vi ora2pg.conf
[root@test01 ora2pg]# cat ora2pg.conf
ORACLE_HOME     /usr/lib/oracle/18.3/client64
ORACLE_DSN      dbi:Oracle:host=10.85.10.6 ;sid=PROD1;port=1521
ORACLE_USER     system
ORACLE_PWD      oracle
SCHEMA          SH
EXPORT_SCHEMA  1
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

Only one type of export can be executed at the same time, so the TYPE instruction must be unique. If you have more than one, only the last one will be found in the file. But I can export multiple types at the same time.

Please note that you can link multiple exports by providing a comma-separated list of export types to the TYPE directive, but in this case, you cannot use COPY or INSERT with other export types.

Some export types cannot or should not be directly loaded into the IvorySQL database, and still require little manual editing. This is the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE, QUERY and PACKAGE export types, especially if you have PLSQL code or Oracle specific SQL.

For TABLESPACE, you must ensure that the file path exists on the system. For SYNONYM, you can ensure that the owner and schema of the object correspond to the new PostgreSQL database design.

It is recommended to export the table structure one type at a time to avoid other errors affecting each other.

3.5.1. Test connection

After setting the Oracle database DSN, you can execute ora2pg to check whether it is valid：

[root@test01 ora2pg]#  ora2pg -t SHOW_VERSION -c config/ora2pg.conf

WARNING: target IvorySQL version must be set in PG_VERSION configuration directive. Using default: 11

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

3.5.2. Migration cost assessment

It is not easy to estimate the cost of the migration process from Oracle to PostgreSQL. In order to obtain a good evaluation of the migration cost, Ora2Pg will check all database objects, all functions and stored procedures to detect whether there are still some objects and PL/SQL code that cannot be automatically converted by Ora2Pg. Ora2Pg has a content analysis mode, which checks the Oracle database to generate a text report about the content contained in the Oracle database and the content that cannot be exported.

[root@test01 ora2pg]# ora2pg -t SHOW_REPORT --estimate_cost  -c ora2pg.conf
WARNING: target IvorySQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 11g Enterprise Edition Release 11.2.0.3.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.5.3. Export SH table structure

[root@test01 ora2pg]#  ora2pg  -c ora2pg.conf
WARNING: target IvorySQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 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.5.4. Export SH user data

Configure the type of ora2pg.conf as COPY or INSERT

[root@test01 ora2pg]# cp ora2pg.conf sh_data.conf

[root@test01 ora2pg]# vi sh_data.conf

ORACLE_HOME     /usr/lib/oracle/18.3/client64

ORACLE_DSN      dbi:Oracle:host=10.85.10.6 ;sid=PROD1;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

Export Data

[root@test01 ora2pg]# ora2pg  -c sh_data.conf

WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11

[========================>] 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...

To view the exported file:

[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

Export HR and SCOTT user data in the same way.

3.6. Create orcl library in IvorySQL environment

Create ORCL database

[root@test01 ~]# su - ivorysql

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

[postgres@test01 ~]$ createdb orcl

[postgres@test01 ~]$ psql

psql (11.2)

Type "help" for help.



ivorysql=# \l

                                 List of databases

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges

-----------+----------+----------+------------+------------+-----------------------

 orcl      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

 pgdb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +

           |          |          |            |            | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +

           |          |          |            |            | postgres=CTc/postgres

(5 rows)

ivorysql=#

Create SH, HR, SCOTT users:

[postgres@test01 ~]$ psql orcl

psql (11.2)

Type "help" for help.

orcl=#

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

CREATE ROLE

4. Migration Portal

4.1. Import table structure

Because of the materialized view, in TABLE_ The sh.sql contains the index of the materialized view, which will fail to create. You need to first create a table, then create a materialized view, and finally create an index. Cancel the materialized view index and create it separately later:

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.sql

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

4.2. Authorize objects

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. Import materialized view structure

Materialized views require relevant query permissions, so import permissions. Please keep up with users here

 [ivorysql@test01 ora2pg]$  psql orcl sh -f  MVIEW_sh.sql
SELECT 0
SELECT 0
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

4.4. Import View

[ivorysql@test01 ora2pg]$  psql orcl  -f  VIEW_sh.sql
SET
SET
SET
CREATE VIEW

4.5. Import partition table

[ivorysql@test01 ora2pg]$  psql orcl  -f  PARTITION_sh.sql
SET
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
…………………………

4.6. Import data

[ivorysql@test01 ora2pg]$  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. Data validation

Source database and target side extract part of objects for comparison：

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. Generate migration template

When using, there are two options — project_ Base and — init_ Project indicates to ora2pg that he must create a project template, which contains the work tree, configuration files and scripts for exporting all objects from the Oracle database. Generate a generic configuration file. 1. Create script export_ Schema.sh to automatically perform all exports. 2. Create script import_ All.sh to automatically perform all imports. example：

mkdir -p  /ora2pg/migration

[root@test01 ora2pg-20.0]# 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.