Data migration tool - Part V: from Oracle Database 21c to IRIS

Introduction

This is the fifth part of the series of articles on migrating from the main databases on the market to InterSystems IRIS. In this part, the procedures for migrating from Oracle Database 21c will be detailed. As described in previous articles, there are currently a few options for how to do the migration, but the two most popular ones include the use of DBeaver (https://openexchange.intersystems.com/package/DBeaver) or SQLGateway. The first one will be demonstrated in this article, and the second one is described in an excellent article by Robert Cemper, DB Migration using SQLgateway (https://community.intersystems.com/post/db-migration-using-sqlgateway).

Get the sample data for the migration process

In GitHub it is possible to download a docker-compose project to build and run 2 databases:

1. Source Database: Oracle Database 21c Express Edition Docker instance with a sample database (SAMPLEDB).
2. Target Database: InterSystems IRIS data platform Docker instance with a ready schema to receive the source database.

To get the sample and run it, follow these steps:

1. Go to https://github.com/yurimarx/migration-oracle-iris to access the Git repository.
2. Clone the project: git clone  https://github.com/yurimarx/migration-oracle-iris.git  
3. Go to the project folder migration-oracle-iris.
4. Do the build: docker-compose build.
5. Execute the containers: docker-compose up -d.
6. Check if everything is ok with the instances in your docker desktop:

About the data to be migrated

In the first two parts we worked with a sales database. In part III we used a DB2 sample database (it is the most complete sales database currently on the market). In part IV we used the AdventureWorks Schema. Now we will work with an inventory database, the SAMPLEDB database. The data to be migrated is represented below:

SAMPLEDB Schema

SAMPLEDB is an inventory database with 12 tables. It maintains data about product inventory and orders from customers. In this table you can see details for each table:

So, the migration process from Oracle Database to IRIS will include 10 tables and the migration destination will be the dc_test schema inside the USER namespace in the InterSystems IRIS database.

About this docker project

This docker-compose project consists of two database images. Check out the definition beneath:

services:
    oracle: #from https://geraldonit.com/2021/08/15/oracle-xe-docker-images/ and https://oralytics.com/2021/10/01/oracle-21c-xe-database-and-docker-setup/
      image: gvenzl/oracle-xe:latest
      container_name: oracle21c
      ports:
      - "15210:1521"
      environment:
      - ORACLE_PASSWORD=Welcome1
      volumes:
      - ./init_scripts:/container-entrypoint-initdb.d
    iris:
      build:
        context: .
        dockerfile: Dockerfile
      restart: always
      command: --check-caps false
      ports:
        - 19721:1972
        - 52773:52773
        - 53773:53773
      volumes:
        - ./:/irisrun/repo

 

The IRIS container definition creates an IRIS database exposing the superport 1972 as 19721 to the host and the other ports as the same port number.

The Oracle container definition creates an Oracle Database 21c Express Edition (an edition employed for the development of environments; it is similar to the IRIS community edition) exposing it on the port 15210. The image used is gvenzl/oracle-xe:latest. The documentation and source code for this image are defined on https://github.com/gvenzl/oci-oracle-xe. It also allows you to work with different Oracle database versions. Check out the available versions:

• latest, 21, 21.3.0, latest-faststart, 21-faststart, 21.3.0-faststart
• slim, 21-slim, 21.3.0-slim, slim-faststart, 21-slim-faststart, 21.3.0-slim-faststart
• full, 21-full, 21.3.0-full, full-faststart, 21-full-faststart, 21.3.0-full-faststart
• 18, 18.4.0, 18-faststart, 18.4.0-faststart
• 18-slim, 18.4.0-slim, 18-slim faststart, 18.4.0-slim-faststart
• 18-full, 18.4.0-full, 18-full-faststart, 18.4.0-full-faststart,
• 11, 11.2.0.2, 11-faststart, 11.2.0.2-faststart
• 11-slim, 11.2.0.2-slim, 11-slim-faststart, 11.2.0.2-slim-faststart
• 11-full, 11.2.0.2-full, 11-full-faststart, 11.2.0.2-full-faststart

The latest version is the 21c version, but is possible to work with legacy versions such as the version 11g too. There are also some flavors available to you, but if you do not choose a flavor, the full flavor will be used. The following table detaisl the flavors:

Loading the sample database on database creation

The gvenzl/oracle-xe docker image allows you to execute sql files on the database creation. To do it, you must copy the SQL files to the /container-entrypoint-initdb.d folder. On this sample the folder init_scripts was mapped to /container-entrypoint-initdb.d folder. So the file /init_scripts/sampledb.sql will be executed on the database creation and will create a sample database that will be used as a source database to be migrated. This script has the user, database and tables creation and the data to be inserted into the tables.

 

An Open-source tool to migrate from SQL Server to IRIS: DBeaver

DBeaver is a database tool used to connect, create, drop, select, update and delete data objects to the main database products in the market. Download it from: https://openexchange.intersystems.com/package/DBeaver. Now follow the installation instructions to get this fantastic product into your laptop or desktop.

DBeaver can be used to migrate data between database connections, even if they come from different manufacturers and versions.

Before Open DBeaver, increase memory settings

Before open Dbeaver, it will be necessary to increase memory allocation, because the memory resources requirements for the migration. Follow these steps:

1. Locate DBeaver folder installation (in my machine is C:\Program Files\DBeaver)
2. Edit the file dbeaver.ini and increase Xms and Xmx values with 128m and 2048m (double from the original values). See an example:

Connecting the Source and Target Databases using the DBeaver

Now we will set the database connections to be migrated.

To set Oracle Database connection to the DBeaver:

1. In the DBeaver Go to File > New.
2. Select Database Connection and click Next:

3. Choose SQL tab > Oracle and click next:

4. Fill the Oracle connection fields as shown in this picture:

• Host: localhost
• Port: 15210
• Database: XE
• Username: sys
• Password: Welcome1
• Role: SYSDBA
• Client: OracleHome1
• Click OK.

To set InterSystems IRIS connection to the DBeaver:

1. In the DBeaver Go to File > New.
2. Select Database Connection and click Next:

3. Choose SQL tab > InterSystems IRIS and click next:

4. If DBeaver requests to download the InterSystems IRIS driver, press Yes or Ok.
5. Set the InterSystems IRIS connection fields as demonstrated in this picture:

• Host: localhost
• Database/Schema: user
• Port: 19721
• Username: _SYSTEM
• Password: SYS
• Click Test Connection and OK.

The connections (localhost and user) are available in the Database Navigator:

 

Do the migration

To do the migration, follow these steps:

1. Expand the Oracle connection > Schemas > SAMPLEDB > Tables and select all tables. Right-click the selected tables and choose Export Data, as illustrated in this image:

2. Select Database, as displayed in this illustration, and and click Next:

3. Click the Choose button:

4. Select dc_test and click Ok.

5. You must change all ID fields from the data type NUMERIC to INTEGER. To do it, first select and expand a table, then choose the field you need and click Configure:

6. Change the Target Type from NUMERIC to INTEGER and click Ok. 

7. Repeat the same process (change the data type from) with the following fields:

   

8. You also need to change the types of the following fields from numeric to double:

9. Now, when the Target Data Types have been changed, click Next.

10. Set the Fetch size to 1000000 and click Next.

11. Accept the default values in the Data load settings and click Next.

12. In the Confirm click Proceed.

13. Now you can see all Oracle Tables inside InterSystems IRIS dc_test schema in the Database Navigator.

 

The migration process is very simple for tables, but for views, functions, triggers, and stored procedures, you will need to rewrite the SQL source code using ObjectScript or SQL.

An alternative

An alternative is to generate an SQL file with table creation and with the data to be inserted using Oracle EM (Enterprise Manager). To execute this file use:

 

DO $SYSTEM.SQL.Schema.ImportDDL($lb("C:\temp\somesql.sql","UTF8"),,"ORACLE")

Review the documentation about this option here: 

https://docs.intersystems.com/iris20221/csp/docbook/Doc.View.cls?KEY=GSQL_import  

This is the last article from the series, but other databases have a similar approach. Enjoy!