Prerequisites

• You need an IBM Cloud account
• You also need an Databases for EnterpriseDB deployment. Provision from the IBM Cloud catalog. Give your deployment a memorable name that appears in your account's Resource List.

Notes

• The Databases for EnterpriseDB Migration Portal (EMP) is only for schema assessment and migration.
• EMP projects are only visible to the user that created the project. Individual users each have their own set of projects in the EMP that are visible only to themselves.
• While EMP is available on public or private endpoints, it can exist only on one endpoint for the same formation. For example, if you provisioned Databases for EnterpriseDB with only public enabled, then EMP exists on a public endpoint. Likewise, if you provisioned Databases for EnterpriseDB with only private enabled then EMP exists only on a private endpoint. However, if you provisioned Databases for EnterpriseDB on both public and private at the same time, then EMP is available only on the public endpoint.

Extract the schema

1. Log in to the EnterpriseDB migration portal (EMP).
2. Click Portal Wiki
3. Follow the steps in DDL Extractor guide section
4. After a successful attempt, a file like _gen_ot_ddls_2005111737123.sql is created in the specified path

Validate the extracted schema

1. Log in to the EnterpriseDB migration portal (EMP)

2. Create a project by clicking New.

3. Enter a project name, and load the DDL Extractor-generated file for assessment. EMP accepts only single string project names without spaces.

   

4. Click Create & Assess.

5. Fix any issue report by the EMP and make sure you are getting 100% coverage successfully

Migrate your exported converted schema into your EnterpriseDB formation

Export and deploy the converted schema from migration portal to your provisioned Databases for EnterpriseDB formation.

1. Select the Migrate to... button to begin the migration process.

   

2. Choose the EDB Postgres Advanced Server on Cloud option to migrate your schemas on EDB Postgres Advanced Server on Cloud, and click "next".

   

3. Select the schemas and click "next".

   

4. Select the schemas and click "next".

   

5. Click next to use an existing cluster (or click Go to IBM Cloud to create a new cluster).

   

6. Connect to an existing cluster. Enter the target deployment's details (hostname and port are available on the deployment's endpoints PostgresSQL tab) and click Test Connection to validate the connection. To deploy, click "next" after a successful connection is confirmed.

   

   You can use the admin user (after changing its password), or you can create a new db user on the database and use those credentials.

7. Upon successful migration completion, download and review the summary log file, and click "done" to exit from the process.

   

Verify schema migration to EnterpriseDB formation

List the available databases by using the CLI command: psql -d ibmclouddb -c "\l"

bash-4.2# psql -d ibmclouddb -c "\l"
                                         List of databases
     Name     |        Owner        | Encoding |  Collate   |   Ctype    | ICU | Access privileges
--------------+---------------------+----------+------------+------------+-----+-------------------
 edb          | ibm                 | UTF8     | en_US.utf8 | en_US.utf8 |     |
 ibmclouddb   | ibm-cloud-base-user | UTF8     | en_US.utf8 | en_US.utf8 |     |
 ot_migration | enterprisedb        | UTF8     | en_US.utf8 | en_US.utf8 |     |
 postgres     | ibm                 | UTF8     | en_US.utf8 | en_US.utf8 |     |
 template0    | ibm                 | UTF8     | en_US.utf8 | en_US.utf8 |     | =c/ibm           +
              |                     |          |            |            |     | ibm=CTc/ibm
 template1    | ibm                 | UTF8     | en_US.utf8 | en_US.utf8 |     | =c/ibm           +
              |                     |          |            |            |     | ibm=CTc/ibm
(6 rows)

Install EnterpriseDB Migration Toolkit

1. Follow the steps on how to install the EnterpriseDB Migration Toolkit here. (Skip the IDENT Authentication section in the installation guide.)
2. Migration Toolkit script is located in the Oracle container at: /usr/edb/migrationtoolkit/bin/runMTK.sh MTK by default includes the edb jdbc driver, but to connect to an Oracle instance you must install the Oracle jdbc.

Run MTK to migrate data from Oracle to edb-migration formation

1. Edit toolkit.properties file to set up source and target connections. The file is available at /usr/edb/migrationtoolkit/etc/toolkit.properties. More about toolkit.properties

2. Following the setup steps, the toolkit.properties file now resembles:

       SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:ORCL
       SRC_DB_USER=ot
       SRC_DB_PASSWORD=password
   
       TARGET_DB_URL=jdbc:edb://$TARGET_HOST:TARGET_PORT/ot_migration
       TARGET_DB_USER=enterprisedb
       TARGET_DB_PASSWORD=password
   

3. Start MTK to begin the data migration process from OT Oracle schema to ot EnterpriseDB schema under ot_migration database. For more about MTK args, see Migration Tookit

       /usr/edb/migrationtoolkit/bin/runMTK.sh -dataOnly -targetSchema ot -truncLoad OT
   

4. A successful migration output sample:

       Enabling FK constraints & triggers on ot.warehouses...
       Enabling indexes on ot.warehouses after data load...
       Data Load Summary: Total Time (sec): 8.764 Total Rows: 2981 Total Size(MB): 0.105
       
       Schema OT imported successfully.
       
       Migration process completed successfully.
       
       Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs
       
       ******************** Migration Summary ********************
       Tables: 12 out of 12
       
       Total objects: 12
       Successful count: 12
       Failed count: 0
       Invalid count: 0
       
       *************************************************************