MogDB
Ecological Tools

Migrating Data from Oracle to openGauss/MogDB Using MTK

Supported Versions

  • 10.2+
  • 11.2+
  • 12c+

Comparison Between the MTK Type and the Oracle Type

The object type is from OBJECT_TYPE of DBA_OBJECT.

MTK Oracle
Schema User
ObjectType TYPE/TYPE BODY
Domain
CustomType
Sequence SEQUENCE
Queue
Table TABLE OBJECT TYPE TABLE not included
Constraint
Index INDEX
View VIEW
Trigger TRIGGER
Procedure PROCEDURE
Function FUNCTION
Package PACKAGE/PACKAGE BODY
Synonym
DBLink
Rule

Conversion of Column Types Between Oracle and openGauss/MogDB

Oracle openGauss
SMALLINT numeric
INTEGER numeric
DECIMAL(8,5) numeric(8,5)
NUMERIC(8,5) numeric(8,5)
REAL numeric
FLOAT(20) numeric
NUMBER(8,5) numeric(8,5)
BINARY_FLOAT numeric
BINARY_DOUBLE numeric
CHAR(8) character(8)
NCHAR(8) character(8)
VARCHAR(8) character varying(8)
VARCHAR2(8) character varying(8)
NVARCHAR2(8) character varying(8)
DATE timestamp without time zone
TIMESTAMP timestamp without time zone
INTERVAL YEAR TO MONTH interval
IDTS INTERVAL DAY TO SECOND interval
LONG text
RAW(111) bytea
LONG RAW bytea
CLOB text
BLOB bytea
NCLOB text
BFILE not support

Test Environment Preparation

Oracle Database

git clone git@github.com:oracle/docker-images.git
cd docker-images/OracleDatabase/SingleInstance/dockerfiles
cp <xx>/LINUX.X64_193000_db_home.zip 19.3.0/LINUX.X64_193000_db_home.zip
./buildDockerImage.sh -v 19.3.0 -e
docker run --name mtk_oracle_19 \
-p 1519:1521 -p 5500:5500 \
-e ORACLE_SID=orcl \
-e ORACLE_PDB=mtk \
-e ORACLE_PWD=OracleMTK#2020 \
-e ORACLE_EDITION=enterprise \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v /oradata:/opt/oracle/oradata \
-d \
oracle/database:19.3.0-ee

Data preparation

docker exec -it mtk_oracle_19 bash
sqlplus / as sysdba
CREATE TABLE t1 AS SELECT * FROM dba_objects;
CREATE TABLE t2 AS SELECT * FROM dba_tables;
CREATE TABLE t3 AS SELECT * FROM dba_segments;
CREATE TABLE t4 AS SELECT * FROM dba_tablespaces;
CREATE TABLE t5 AS SELECT * FROM dba_users;
CREATE TABLE t6 AS SELECT * FROM dba_indexes;

Oracle Client

  1. Visit Oracle Instant Client to choose the corresponding platform.

  2. Choose the corresponding version, such as Version 19.10.0.0.0.
  3. Download theBasic Package (ZIP) package and upload it to the server, and run the following command to decompress it:

    # This package applies to the arm64 platform. 
    unzip instantclient-basic-linux.arm64-19.10.0.0.0dbru.zip

    After the package is decompressed, the directory like instantclient_19_10 will be obtained.

  4. Configure environment variables.

    Linux:
    export LD_LIBRARY_PATH=< target directory>/instantclient_19_10:$LD_LIBRARY_PATH
    Mac:
    export DYLD_LIBRARY_PATH=< target directory>/instantclient_19_10$DYLD_LIBRARY_PATH

openGauss/MogDB Database

Step 1 Install MogDB in a docker container for local testing.

docker run --name mogdb --privileged=true -d -e GS_PASSWORD=secretpassword@123 \
    -v /enmotech/mogdb:/var/lib/mogdb \
    -p 5432:5432 \
    enmotech/mogdb:latest

Step 2 Verify that MogDB in the docker container can be accessed from the host.

$ gsql -d postgres -U mogdb -W'mypass@123' -h 127.0.0.1

gsql ((MogDB 2.0.0 build 5be05d82) compiled at 2020-08-11 10:39:49 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=>

Step 3 Log in to MogDB in the container and create a target database.

$ docker exec -it mogdb /bin/bash
su - omm
$ gsql
omm=# create database mydb;

Step 4 Grant the user the permission for schema creation.

MTK supports concurrent migration of multiple schemas which can be automatically created in MogDB. Therefore, the user needs to be granted the permission for schema creation.

omm=# grant create on database mydb to mogdb;
GRANT

Migration

  1. Edit the mtk_config.json file.

    The following is an example. For more about parameter configuration, see Configuration File.

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "",
      "user": "",
      "port": 1522,
      "password": "",
      "dbName": "",
      "dsn": "connectString=db202009151510_high user=ADMIN password=your_password"
    },
    "parameter": {
      "debugTest": false
    }
  },
  "target": {
    "type": "MogDB",
    "connect": {
      "version": "",
      "host": "168.138.44.190",
      "user": "mogdb",
      "port": 5432,
      "password": "mypass@123",
      "dbName": "mydb",
      "dsn": ""
    },
    "parameter": {
      "dropExistingObject": false,
      "truncTable": false,
      "parallelInsert": 1,
      "path": "./data",
      "fileType": "sql"
    }
  },
  "limit": {
    "parallel": 2,
    "fetchSize": 0,
    "batchSize": 0,
    "bufferSize": 0
  },
  "object": {
    "schemas": [
      "ADMIN"
    ]
  },
  "dataOnly": false,
  "schemaOnly": false,
  "reportFile": "./report_Oracle2MogDB_schemaOnly.html"
}
  1. Run MTK.
$ ./mtk -c mtk_config.json --reportFile mtk_report.html --logfile mtk_report.log --debug

Problem Description

  • Empty string problem In Oracle, an empty string is null. In openGauss, If dbcompatibility is set to A, an empty string is null. If dbcompatibility is set to PG, an empty string is not null.

    DBCOMPATIBILITY [ = ] compatibility_type

    Specifies the type of the database to be compatible with.

    Value range: A, B, C, and PG

    A indicates that MogDB is compatible with Oracle. B indicates that MogDB is compatible with MySQL. C indicates that MogDB is compatible with Teradata. PG indicates that MogDB is compatible with PostgreSQL. However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

    Note:

    • For the compatibility type of A, the database considers an empty string as null and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
    • For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
    • For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.

    To be supplemented. This document lists only part problems.

  • Sequence

    If the current value is greater than 9223372036854775807, an error will occur. For other situations, the log will be printed.

  • Type

    • Due to syntax restriction, part of them can be converted.

      • CREATE OR REPLACE TYPE T_VARCHAR2 as object(c_key VARCHAR2(200), c_value VARCHAR2(200)) -- not support object keyword.
      • CREATE OR REPLACE TYPE TY_STR_SPLIT IS TABLE OF VARCHAR2 (4000)
  • Table

    • IOT_OVERFLOW table failed to be migrated.

      dba_tables.IOT_TYPE='IOT_OVERFLOW'

    • NESTED TABLE not supported

      dba_tables.NESTED='YES'

    • OBJECT TYPE TABLE not migrated

      This type of table does not exist in DBA/ALL_TABLE. Additionally, openGauss does not support this table. If the table exists, the quantity of dba_object.object_type='TABLE' is inconsistent with that counted by DBA_TABLE.

      CREATE OR REPLACE TYPE address_obj as OBJECT(
            street VARCHAR2(20),
            city VARCHAR2(20),
            state CHAR(2),
            zip CHAR(5));
      /
      CREATE TABLE address_table OF ADDRESS_OBJ;
    • tid cannot be used as a column name because it is a reserved field.

      create table test.test1
      (
          tid VARCHAR(20),  --> Converted to "TID"
          eid VARCHAR(160),
          sync_type VARCHAR(2),
          sync_date DATE,
          id VARCHAR(20)
      )
    • Partition table

      • Hash Partition Table
      • List Partition Table
      • Range Partition Table
      • Sub Partition Table
      • System Partition table
      • Due to limitations of openGauss syntax, only part of interval partitions are supported.

        • Convert interval(NUMTOYMINTERVAL(1,'year')) --> interval(1 year)

          Oracle OpenGauss
          NUMTOYMINTERVAL(1,'YEAR') 1 year
          NUMTOYMINTERVAL ( 1, 'MONTH' ) 1 month
          numtodsinterval ( 1, 'day' ) 1 day
        • [] number interval -- invalid input syntax for type interval
      • openGauss does not support subpartitions.
  • Column

    • Default value of the column

      • [] sys_guid not supported

        SQL> SELECT sys_guid() FROM dual;
        
        SYS_GUID()
        --------------------------------
        B2CBE5EE3AB4032BE053030011AC6BD3
        CREATE or replace FUNCTION pg_catalog.sys_guid() RETURNS varchar AS $$
        BEGIN
        RETURN md5(random()::text || clock_timestamp()::text);
        END;
        $$ LANGUAGE plpgsql;   
      • TO_CHAR(SYSTIMESTAMP, 'TZR') from dual;
    • Customizing columns is not supported.
    • bfile is not supported.
    • The column tid is converted to "TID". tid is a hidden column in openGauss.
    • number(38,127) --> numeric(127, 127) When p (precision) is less than s (scale), numeric(s, s) applies. In this way, the number of digit 0 behind decimal point can be defined in Oracle.

      create table o_test3(value number(38,127));
      INSERT INTO o_test3 VALUES (0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789012345678901234567890123456789012345678901234567890123456789);
      SELECT * FROM o_test3;
    • number(38,-64) --> numeric(102)

      create table o_test4(value number(38,-64));
      INSERT INTO o_test4 VALUES (12345678901234567890123456789012345678901234567890123456789012345678900000000000000000000000000000000);
      INSERT INTO o_test4 VALUES (123456789012345678901234567890123456789012345678901234567890123456789000000000000000000000000000000000);
      INSERT INTO o_test4 VALUES (123456789012345678901234567890123456789012345678901234567890123456789000000000000000000000000000000001);
      SELECT * FROM o_test4;
  • Constraint

    • If the Oracle primary key constraint is consistent with the table name, an error will occur in openGauss. The program will automatically add _PK behind the name.

      • C - Check constraint on a table
      • P - Primary key
      • U - Unique key
      • R - Referential integrity
      • V - With check option, on a view -- Not supported
      • O - With read only, on a view -- Not supported
      • H - Hash expression
      • F - Constraint that involves a REF column
      • S - Supplemental logging -- Not supported
  • Index

    • The bitmap index is not supported, which cannot be resolved currently.
  • View

    • [] Problem of alias of some columns in the SELECT statement
    select name name from dual      -- rewrite name as name
    select name as name from dual.  --- correct
    • [] Oracle TRANSLATE(xx USING NCHAR_CS) function (This problem cannot be resolved.)
    select TRANSLATE(col1 USING NCHAR_CS) from (
    select 1 as col1 from dual;
    )

Number Description

https://developer.aliyun.com/article/57142

create table test_nubmer(number_min number(38),number_max number(38), number1 number(38,19),number2 number(38,127),number3 number(38,-84));
insert into test_nubmer values (99999999999999999999999999999999999999,99999999999999999999999999999999999999,
    999999999999999999.99999999999999999981,
    0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789012345678901234567890123456789012345678901234567890123456789,
    12345678901234567890123456789012345678901234567890123456789012345678900000000000000000000000000000000000000000000000000001
    );

Syntax Problem Summary

  • CREATE OR REPLACE TYPE T_VARCHAR2 as object
  • sys_guid()
  • select name name from dual -> select name as name from dual.
  • TRANSLATE(col1 USING NCHAR_CS)
  • TO_CHAR(SYSTIMESTAMP, 'TZR')

Other Problem

  • ORA-01805 msg:possible error in date/time operation

    1. Query oracle database server timezone_file version
    sqlplus "/ as sysdba"
    SQL> select * from v$timezone_file;
    
    FILENAME    VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_34.dat        34    0
    1. Query oracle client timezone_file version
    genezi -v
    Client Shared Library 64-bit - 19.3.0.0.0
    
    System name:  Darwin
    Release:  19.6.0
    Version:  Darwin Kernel Version 19.6.0: Thu Jun 18 20:49:00 PDT 2020; root:xnu-6153.141.1~1/RELEASE_X86_64
    Machine:  x86_64
    
    TIMEZONE INFORMATION
    --------------------
    Operating in Instant Client mode.
    
    Small timezone file = /opt/client_19/oracore/zoneinfo/timezone_32.dat
    Large timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_32.dat

    If the client version and server version are inconsistent, upgrade timezone_file.

    Server version: timezlrg_34.dat

    Client version: timezlrg_32.dat

    Solution:

    1. Install the Oracle client.
    2. Upgrade timezone_file.

      [Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)]()
      for oracle Instant client
      
      # Set environment variable ORA_TZFILE.
      export ORA_TZFILE=timezlrg_34.dat
      # Copy from other machine that has the patch installed. 
      cp timezlrg_34.dat $ORACLE_HOME/oracore/zoneinfo/
      genezi -v
      Client Shared Library 64-bit - 19.3.0.0.0
      
      System name: Darwin
      Release:  19.6.0
      Version:  Darwin Kernel Version 19.6.0: Thu Jun 18 20:49:00 PDT 2020; root:xnu-6153.141.1~1/RELEASE_X86_64
      Machine:  x86_64
      
      TIMEZONE INFORMATION
      --------------------
      Operating in Instant Client mode.
      
      Time zone file timezlrg_34.dat set in ORA_TZFILE environment variable will be used.
      Small timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_34.dat
      Large timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_34.dat
  • Segmentation fault on some requests handling ROWID

    https://github.com/oracle/python-cx_Oracle/issues/315

    Try setting the environment variable ORA_OCI_NO_OPTIMIZED_FETCH to the value 1 before you run the query to avoid the issue


Oracle2MogDB Example File

  • Migrate only the data structure.

    {
      "source": {
        "type": "oracle",
        "connect": {
          "version": "",
          "host": "192.168.56.2",
          "user": "system",
          "port": 1521,
          "password": "oracle",
          "dbName": "orcl",
          "dsn": ""
        },
      },
      "target": {
        "type": "MogDB",
        "connect": {
          "version": "",
          "host": "127.0.0.1",
          "user": "mogdb",
          "port": 5432,
          "password": "Test@123",
          "dbName": "mtk",
          "dsn": ""
        },
        "parameter": {
          "dropExistingObject": false,
          "truncTable": false,
          "parallelInsert": 1
        }
      },
      "limit": {
        "parallel": 2
      },
      "object": {
        "schemas": [
          "BT_PROD"
        ]
      },
      "dataOnly": true ,
      "schemaOnly": false,
      "reportFile": "./report_Oracle2MogDB_schemaOnly.html"
    }
  • Migrate all, including the table structure and data.

    {
      "source": {
        "type": "oracle",
        "connect": {
          "version": "",
          "host": "192.168.56.2",
          "user": "system",
          "port": 1521,
          "password": "oracle",
          "dbName": "orcl",
          "dsn": ""
        },
      },
      "target": {
        "type": "MogDB",
        "connect": {
          "version": "",
          "host": "127.0.0.1",
          "user": "mogdb",
          "port": 5432,
          "password": "Test@123",
          "dbName": "mtk",
          "dsn": ""
        },
        "parameter": {
          "dropExistingObject": false,
          "truncTable": false,
          "parallelInsert": 1
        }
      },
      "limit": {
        "parallel": 2
      },
      "object": {
        "schemas": [
          "BT_PROD"
        ]
      },
      "dataOnly": false ,
      "schemaOnly": false,
      "reportFile": "./report_Oracle2MogDB_all.html"
    }