Migrating an Oracle database to the new schema

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

Before you begin

Take a backup of the database, and its relevant configuration information, that you 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, you should ensure that your file names do not exceed 2000 characters.

About this task

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.