Migrating an Oracle database to the new schema

How we migrate a database with the existing schema to the new schema, by using the sample SQL script file.


Before starting

Take a backup of the database, and its relevant configuration information, that we are going to migrate and refer to the Oracle entity relationship diagram.

Attention: The NCLOB data type has no limit on the length of the data that can be stored. However, VARCHAR2 has a limit of 4000 bytes, so there could be some data loss while migrating to a new schema if the existing database contains file names that are longer than 4000 bytes (or 32767 bytes for an extended string).

In this situation, only the last 2000 characters of the filename will be migrated, therefore, we should ensure that your file names do not exceed 2000 characters.


The following four sample SQL script files are located in <MQ_Installation_Directory>/mqft/sql:

  • oracle_nvarchar_migration_step_1.sql
  • oracle_nvarchar_migration_step_2.sql
  • oracle_nvarchar_migration_step_3.sql
  • oracle_nvarchar_migration_step_4.sql


Procedure

  1. Carry out the following tasks in order:
    1. Run oracle_nvarchar_migration_step_1.sql
    2. Run oracle_nvarchar_migration_step_2.sql
    3. Run oracle_nvarchar_migration_step_3.sql
    4. Run oracle_nvarchar_migration_step_4.sql

    Important: Before running step 1.c, ensure that steps 1.a and 1.b have run successfully.

  2. Issue the command cd <MQ_Installation_Directory>/mqft/sql
  3. Process the SQL script files, using the following commands in order:
    1. Run sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step1.sql
    2. Run sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step2.sql
    3. Run sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step3.sql
    4. Run sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step4.sql

    where USERNAME/PASSWORD refers to the user Id and password of a particular user.

Parent topic: MFT database logger tables