EDB Postgres Migration Portal

Migration Portal is a web-based service for migrating Oracle database schemas to the EDB Postgres platform. The Migration Portal assesses and analyzes Oracle database schemas and converts types, tables, sequences, constraints, triggers, views, stored procedures, packages, database links (dblinks), materialized views, and indexes, producing DDLs compatible with EDB Postgres Advanced Server.

The user-friendly portal interface simplifies the assessment and migration process. Log in to the portal and start with the migration process.

The Migration Portal guide provides a high-level description of the steps involved in the migration process. The guide also includes solutions to common migration problems and details of unsupported features and their potential workarounds.

EnterpriseDB has helped companies migrate their existing database systems to Postgres for years. For more information, visit the EnterpriseDB website at: https://www.edbpostgres.com/

What’s New

The following enhancements are added to the Migration Portal for the release 3.1.0:

• Asynchronous/Parallel assessment

  You can now create multiple projects and run the assessments in parallel, getting notified once the assessment completes. Also, use multiple browser tabs to work on projects simultaneously.

• Restructured schema assessment report for enhanced readability along with consolidated information about the incompatible function/package

• Refined object search and filter experience along with other UI enhancements

• Starting this release, Migration Portal detects function/procedure names from the packages which are not supported in EPAS, for example, 'Incompatible function/package found : UTL_FILE.GET_RAW', giving a better understanding of error and the migration efforts required.

Updated Repair Handler

Repair handlers convert Oracle syntax to Postgres-compatible syntax so that you don’t have to do it manually.

• ERH-2055 - UNSUPPORTED_USING_INDEX_CLAUSE

  Updated the repair handler such that it does not remove GLOBAL indexes from the table definition

• ERH-1012 - INSERT_STATEMENT_ALIAS

  Modified the repair handler as it was not firing for scenarios where INSERT statements contained the SELECT statement

• ERH-2090 - CONVERT_FUNCTION

  Enhanced the performance for large DDLs

• ERH-2027 - SESSION_GLOBAL_SEQUENCE

  Enhanced the accuracy so that it functions correctly for edge case DDLs

• ERH-2079 - GLOBAL_PARTITIONED_INDEX

  Removed this so that Global Partitioned Indexes will not be removed

Knowledge Base

Knowledge base entries provide workarounds or solutions for the objects that fail during an assessment. You can view and apply the knowledge base solutions to reassess the fail objects.

New Knowledge Base entries

Following are the new knowledge base entries added:

• Workaround for Oracle SQLERRM function

  The SQLERRM() function returns the error message associated with the most recently raised error exception. You may use Advanced Server supported SQLCODE and SQLERRM variables in the exception block to get similar results.

• Workaround for Oracle SQLCODE function

  In PL/SQL blocks, the SQLCODE can be used for getting numeric code of exception, in Advanced Server you can create custom function which can return numeric value of error.

• Value assignment with %type operator for TYPE definition inside package

  This syntax is supported starting with Advanced Server v12, hence we recommend upgrading to Advanced Server v12 or above to resolve this error.

• Global Indexes

  Global Indexes map one index to multiple partitioned tables. There can be multiple workarounds, for example, aligning partitioning keys across all referencing and referenced tables.

• Workaround for NESTED Table

  In Advanced Server, ARRAY can be used to achieve the same kind of functionality.

• Method Overloading

  In Oracle, users can overload the method inside the package by changing the name of the arguments. In Advanced Server, users should change the argument type to overload a method.

Supported Platforms

The Migration Portal supports assessment and migration from Oracle 11g and 12c to EDB Postgres Advanced Server 10, 11, 12, or 13. Migration Portal is supported on the following browsers and operating systems:

Supported Browsers

For the best user experience, we recommend using the Google Chrome browser. Migration Portal is also supported on the following browsers:

Browser	Supported Version
Apple Safari on Macintosh OS	11 and above
Google Chrome	68 and above
Microsoft Edge	42 and above
Mozilla Firefox	60 and above

Supported Operating Systems

Operating Systems	Supported Version
Macintosh	OS X Sierra
Windows	10
Linux	CentOS 7

Using Migration Portal

Migration Portal allows you to easily migrate your database schema from Oracle to EDB Postgres Advanced Server. You can upload schemas for assessment and get immediate feedback and suggestions. The portal allows you to download assessed DDLs for all objects and create your EDB Postgres database on-premises or in the cloud.

Figure 2-1: The assessment and migration process

Note Migration Portal currently does not have team collaboration features. We recommend collaborative meetings, screen sharing, and using the downloadable assessment report to share results.

To access the Migration Portal:

1. Open a browser and navigate to https://www.edbpostgres.com/.

2. On the EnterpriseDB home page, click Products > Migration Portal.

Figure 2-2: The assessment and migration process

3. Click Open Migration Portal.

4. Log in using your credentials.

Overview of the Migration Portal Home Page

The Migration Portal home page provides quick access to migration tools.

Figure 2-3: The Migration Portal home

The Migration Portal home page allows access to the following Migration Portal features:

1. Projects: The Projects panel displays a list of assessed projects.

2. Create project: Click + New (the button located to the right of the Projects label) to create a new project.

Note Uploading large DDL files might freeze or crash the browser (depending on the browser and the system resources in use). In this scenario, try running Migration Portal in a different browser or extracting fewer schemas and uploading that file on Migration Portal.

3. Search projects: Use the Search box to search for projects.

4. Overview: The Overview panel provides details about the selected project and displays the compatibility percentage after schema assessment.

5. Migrate to: Use the Migrate to button to either download an EDB Postgres Advanced Server compatible .sql file or migrate a schema to an EDB Postgres Advanced Server on-premise or on the cloud. For more information, see Schema Migration.

6. Report: Use the Report button to view and download the schema assessment report.

7. Delete: Use the Delete button to delete a selected project.

8. Warning sign: A warning message is displayed if a project or a schema is less than 70% compatible or any DDL doesn’t succeed after multiple attempts.

9. Upload DDL file: Use the Upload new schema button to upload a new or additional DDL file.

10. Schemas: The Schemas panel displays the assessment result from an uploaded DDL file.

11. Quick help: The Quick help panel contains quick handles to a few required resources.

12. Portal Wiki: The Portal Wiki has links to product information and different help guides.

Overview of the Migration Portal Projects Page

The Migration Portal Projects page provides detailed information about your migration.

Figure 2-4: The Migration Portal Projects Page overview

Use the following resources to gather information about your migration projects:

1. Compatible: The Compatible gauge displays the color on the basis of the compatibility percentage of the assessed schema.

2. Schema Count: Displays the number of schemas in a project.

3. Migrate to: Use the Migrate to to migrate the schema to an EDB Postgres Advanced Server on-prmise or on cloud.

4. Search objects: Use the Search box to search for objects.

5. Filters: You can filter the system repaired and manual repaired objects from the left panel of the Projects page.In addition, you can select one or more filter combinations to refine the information.

6. Objects: Displays the objects for the selected schemas.

7. Common Failures: Displays the reason for the failed objects for the selected schemas.

   

   Figure 2-5: Common Failures tab

Note You can download a CSV file for the common failures for the project.

8. Schema: The Schema panel displays the result of the assessment.

9. Tooltip: Hover over a result set to display a tooltip with the number of passed, failed, and repaired objects.

10. Quick help: The Quick help panel displays links to Knowledge base articles and repair handler documentation.

11. Search: Use the Search box to search the Knowledge base entries or repair handler documentation for specific information.

Figure 2-6: Searching the Knowledge Base entry

Overview of the Migration Portal Wiki Page

The Portal Wiki page provides quick access to information:

• What’s New information
• Quick Start guide
• Migrating Schema
• Migrating Data
• DDL Extractor guide
• Knowledge Base
• Repair handlers
• Migration Portal User’s Guide
• FAQs

Figure 2-7: The Migration Portal Wiki page

Migrating a Database

To migrate a database, you must complete the following steps:

1. Perform a Schema Extraction.
2. Perform a Schema Assessment.
3. Perform a Schema Migration.
4. Perform a Data Migration.

The following sections provide detailed information about each step in the migration process.

Performing a Schema Extraction

Prerequisites

Before extracting a schema, you must download the latest EDB DDL Extractor script from the Migration Portal Projects page or from the link provided in the DDL Extractor guide in the Portal Wiki. The script can be run in SQL Developer or SQL*Plus. It uses Oracle’s DBMS_METADATA built-in package to extract DDLs for different objects under schemas (specified while running the script). The EDB DDL extractor creates the DDL file uploaded to the portal and analyzed for EDB Postgres compatibility.

Note You must have CONNECT and SELECT_CATALOG_ROLE roles and CREATE TABLE privilege.

For SQL*Plus

1. Connect to SQL*Plus and run the command:

   SQL>@edb_ddl_extractor.sql

2. Provide the schema name and the path or directory in which the extractor will store the extracted DDL. When extracting multiple schemas, use a comma (‘,’) as a delimiter.

Note If you want to extract all the user schemas from the current database, do not mention any schema names while extracting. However, it is recommended to mention the schema names that you would like to extract.

3. If you want to extract dependent objects from other schemas, enter yes or no.

   For example, on Linux:

Enter a comma separated list of schemas to be extracted (Default all schemas): HR, SCOTT, FINANCE

Location for output file (Default current location) : /home/oracle/extracted_ddls/

WARNING:

Given schema(s) list may contain objects which are dependent on objects from other schema(s), not mentioned in the list.` `Assessment may fail for such objects. It is suggested to extract all dependent objects together.

Extract dependent object from other schemas?(yes/no) (Default no / Ignored for all schemas option): yes

On Windows:

Enter comma separated list of schemas to be extracted (Default all schemas): HR, SCOTT, FINANCE

Location for output file (Default current location) : c:\Users\Example\Desktop\

WARNING:

Given schema(s) list may contain objects which are dependent on objects from other schema(s), not mentioned in the list.` `Assessment may fail for such objects. It is suggested to extract all dependent objects together.

Extract dependent object from other schemas?(yes/no) (Default no / Ignored for all schemas option): yes

For SQL Developer

1. Connect to the SQL server and run the following command:

Figure 3-1: Enter the path for Linux or Windows

2. Enter a comma-separated list of schemas:

Figure 3-2: Provide a list of schemas

3. Enter the path for the output file:

Figure 3-3: Specify the output file path

4. Enter (yes/no) to extract dependant objects:

Figure 3-4: Extracting dependent objects

Note You can also enter single schema name in both SQL*Plus and SQL Developer.

The script then iterates through the object types in the source database and once the task is completed, the .SQL output is stored at the entered location, i.e., c:\Users\Example\Desktop\.

Additional Notes

• The EDB DDL Extractor script does not extract objects restored using Flashback and still have names like BIN$b54+4XlEYwPgUAB/AQBWwA==$0. If you want to extract these objects, you must change the name of the objects and re-run the extraction process.
• DDL Extractor extracts nologging tables as normal tables. Once these tables are migrated to EDB Postgres Advanced Server, WAL log files will be created.
• DDL Extractor creates Global Temporary tables to store the schema names and their dependency information. These tables are dropped at the end of successful extraction.
• DDL Extractor script does not extract schemas whose name starts with PG_ because PostgreSQL does not support it. If you want to extract these schemas, you must change name of schema before extraction.

Supported Object Types

The Migration Portal supports the migration of the following object types:

• Synonyms
• DB Links
• Types and Type Body
• Sequences
• Tables
• Constraints
• Indexes (Except LOB indexes and indexes on materialized views)
• Views
• Materialized Views
• Triggers
• Functions
• Procedures
• Packages

Note COMMENTS on Columns, Tables, and Materialized Views are also supported.

Unsupported Object Types

• Editions
• Operators
• Schedulers
• LOB indexes and Indexes on Materialized Views
• XML Schemas
• Profiles
• Role and Object Grants
• Tablespaces
• Directories
• Users
• RLS Policy
• Queues

Oracle System Schemas

EDB DDL Extractor script will ignore the following system schemas while extracting from Oracle:

ANONYMOUS	APEX_PUBLIC_USER	APEX_030200
APEX_040000	APEX_040000	APPQOSSYS
AUDSYS	BI	CTXSYS
DMSYS	DBSNMP	DIP
DVF	DVSYS	EXFSYS
FLOWS_FILES	FLOWS_020100	GSMADMIN_INTERNAL
GSMCATUSER	GSMUSER	IX
LBACSYS	MDDATA	MDSYS
MGMT_VIEW	OE	OJVMSYS
OLAPSYS	ORDPLUGINS	ORDSYS
ORDDATA	OUTLN	ORACLE_OCM
OWBSYS	OWBYSS_AUDIT	PM
RMAN	SH	SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR	SPATIAL_WFS_ADMIN_USR	SYS
SYSBACKUP	SYSDG	SYSKM
SYSTEM SYSMAN	TSMSYS WKPROXY	WKSYS
WK_TEST XS$NULL	WMSYS	XDB

Performing a Schema Assessment

To assess an Oracle database schema for compatibility with EDB Postgres Advanced Server, you must:

1. Navigate to the Migration Portal.

2. Enter your EDB credentials.

3. Click New to create a new project.

Figure 3-5: The Migration Portal New project dialog box

4. On the New project dialog box, enter the project name in the Project name field.

5. Specify project details:

   • Select the radio button next to the Application interface.
   • Select a Source DB and Version for the source.
   • Select the Target DB and Version for the target.

6. Click Choose file to upload the .SQL file generated by the latest EDB DDL Extractor for Oracle Database.

Note - You should not modify the .SQL file. - Only the .SQL file generated by the latest EDB DDL Extractor can be uploaded.

For more information, refer to the Schema Extraction section.

7. Check the box next to Add Index Prefix to specify an index prefix (idx) when creating a project to ensure better assessment results, as EDB Postgres Advanced Server does not support the same name for tables and indexes.

8. Click Create & assess to create a new project and to assess the schemas.

   

   Figure 3-6: The Schema analysis result

   The analysis tool will review every construct and execute repair actions to improve compatibility with EDB Postgres Advanced Server and flag any remaining errors that require manual intervention.

9. Verify the DDL objects (e.g., TABLES) that do not show a 100% success ratio.

Figure 3-7: Verifying the DDL objects

10. Click the objects that are not compatible with EDB Postgres Advanced Server and view the details. At the bottom of the middle panel, you can view repair action details.

Figure 3-8: Incompatible objects are identified

11. Refer to the Knowledge Base information in the right panel to locate the possible workarounds for the objects that are not immediately compatible with EDB Postgres Advanced Server. You can also view the Knowledge Base information on the Portal Wiki page.

Figure 3-9: Assessment result with errors

12. On the Knowledge Base tab, you can enter the error message for the incompatible objects with EDB Postgres Advanced Server and click Search.

    The object detail panel displays the workaround or the resolution for the failed object. You can manually make the changes on the Assessment tab for that object and click Reassess.

Note If any failed object passes while reassessing, the dependent objects for that object are also reassessed.

Figure 3-10: Workaround or resolution for incompatible objects

Similarly, you can make all incompatible objects compatible.

Note If the information or workaround for incompatible objects is not available in the Knowledge Base, please contact the support team for assistance.

When you have finished working with the DDL, you can either download the modified EDB compatible DDL as a .sql file or migrate the schemas see, Schema Migration.

Generating an Assessment Report

Migration Portal’s report functionality provides a high-level assessment summary of the schemas assessed for your project. In addition, the report provides details about the failed objects and the cause of failure.

Figure 3-11: Select schemas for reports

To generate a report:

1. Click the Report button to access the schema selection dialog.

2. Select the schemas that you wish to include in the report.

3. Click Generate to generate the onscreen report.

Figure 3-12: The Schema Assessment Report

You can optionally select Generate PDF to save the report in .pdf format. You can also view the count of distinct repair handlers applied to the DDLs under the selected schemas.

Figure 3-13: The saved pdf report

Schema Migration

After resolving errors in your schemas, you can use the schemas with a client application such as pgAdmin, ToadEdge, or the PSQL client, or migrate the schema to an EDB Postgres Advanced Server.

Note

For more information about using Toad Edge with EDB Postgres Advanced Server, see Toad Edge for Postgres.

You can choose one of the following options for migrating schemas; migrate to an:

• Existing on-premises EDB Postgres Advanced Server
• New on-premises EDB Postgres Advanced Server
• EDB Postgres Advanced Server on Cloud

Figure 3-14: Schema migration home page

Migrating to an Existing On-Premises EDB Postgres Advanced Server Host

You can migrate schemas to an existing on-premises EDB Postgres Advanced Server on Windows or Linux platforms.

Migrating Schemas on Windows

1. Select the Existing on-premises EDB Postgres Postgres Advanced Server option:

Figure 3-15: Existing on-premises EDB Postgres Advanced Server home page

2. Select one or more schemas to migrate to EDB Postgres Advanced Server:

Figure 3-16: Selecting schemas for migration

Note If your schemas are not 100% compatible, a banner will be displayed as shown; complete the Contact Us form for any assistance required.

Figure 3-17: The Contact Us form

3. Download the assessed schemas:

Figure 3-18: Download the assessed schemas

4. Click on Windows:

Figure 3-19: Selecting Windows operating system

5. To import the schemas, run the following command:

• On CLI

\i c:\users\...\<project_name>.sql

• On cmd/shell

edb-psql -f <project_name>.sql

Note You can also use pgAdmin instead.

The converted schemas are migrated to the target server.

Figure 3-20: A successful migration

Migrating Schemas on Linux

To migrate schemas to an existing on-premises EDB Postgres Advanced Server on Linux, complete the following steps:

1. Click the Existing on-premises EDB Postgres Advanced Server option:

Figure 3-21: Existing on-Premises EDB Postgres Advanced Server home page

2. Select one or more schemas to migrate to EDB Postgres Advanced Server:

Figure 3-22: Selecting schemas for migration

3. Download the assessed schemas:

Figure 3-23: Download the assessed schemas

4. Click on Linux:

Figure 3-24: Selecting Linux operating system

5. To import the schemas, invoke the following edb-psql client commands:

sudo su - enterprisedb
edb-psql edb
create database <database_name>;
\\connect <database_name>
\\i <project_name>.sql

Note You can optionally use the pgAdmin client for the import.

The converted schemas are migrated to the target server.

Figure 3-25: A successful schema migration

Migrating to a New On-Premises EDB Postgres Advanced Server Installation

You can install new EDB Postgres Advanced Server on-premises on Windows or Linux platforms and migrate the schemas.

Migrating Schemas on Windows

To migrate schemas to a new on-premises EDB Postgres Advanced Server on Windows, complete the following steps:

1. Click New on-premises EDB Postgres Postgres Advanced Server option.

Figure 3-26: New on-premises EDB Postgres Advanced Server home page

2. Select one or more schemas to migrate on EDB Postgres Advanced Server.

Figure 3-27: Selecting schemas for migration

3. Select the Windows operating system.

Figure 3-28: Selecting Windows operating system

4. Download Windows Installer.

Figure 3-29: Downloading Windows installer

5. For installation steps, click EDB Postgres Advanced Server Installation Guide for Windows.

Figure 3-30: A successful schema migration

6. Download the assessed schemas.

Figure 3-31: Downloading the assessed file

7. You can import schemas by running the following command:

• On CLI

\i c:\users\...\<project_name>.sql

• On cmd/shell

edb-psql -f <project_name>.sql

Figure 3-32: Importing schemas into EDB Postgres Advanced Server

Note You can also use pgAdmin instead.

The schemas are migrated to the target server.

Figure 3-33: Importing schemas into EDB Postgres Advanced Server

Migrating Schemas on Linux

To migrate schemas to an on-premises EDB Postgres Advanced Server on Linux, complete the following steps:

1. Click New On-premises EDB Postgres Advanced Server option.

Figure 3-34: Migrating schemas home page

2. Select one or more schemas to migrate on EDB Postgres Advanced Server.

Figure 3-35: Selecting schemas for migration

3. Select the Linux operating system.

Figure 3-36: Selecting Linux operating system

4. You can select one of the following options to install the EDB Postgres Advanced Server:

• Repository

• More options

  

  Figure 3-37: Selecting Linux repository

5. For information on the installation procedure, click EDB Postgres Advanced Server Installation Guide for Linux:

Figure 3-38: Selecting Linux installation guide

6. Download the assessed schemas:

Figure 3-39: Downloading the assessed schemas

7. To import the schemas, run the following command:

sudo su - enterprisedb
edb-psql edb
create database <database_name>;
\connect <database_name>
\i <project_name>.sql

Figure 3-40: Importing schemas into EDB Postgres Advanced Server

Note You can also use pgAdmin instead.

The converted schemas are migrated to the target server.

Figure 3-41: A successful schema migration

Migrating to the Cloud

To migrate schemas on EDB Postgres Advanced Server to Cloud, complete the following steps:

2. Click EDB Postgres Advanced Server on Cloud option:

Figure 3-42: EDB Postgres Advanced Server on Cloud

3. Select one or more schemas to migrate to EDB Postgres Advanced Server:

Selecting schemas for migration

4. Select the cloud platform. For example, IBM Cloud:

Figure 3-43: Selecting cloud option for migration

5. To launch a new cluster, click Go to Cloud:

Figure 3-44: Launching a cloud cluster

Or, if you have an existing cluster running, click Next.

6. Enter the required connection details on the Connect page:

Figure 3-45: Connecting to the cloud cluster

Note You can click Edit Connection to make changes to the connection details and retest the connection details.

7. Click Test Connection to verify the connection details:

Figure 3-46: Verify the connection details

8. Once the connection is successful, click Next:

Figure 3-47: A successful migration

The converted schemas are migrated to the target server.

Data Migration

After performing the schema migration, complete the following steps to migrate data:

1. Use Migration Toolkit to migrate the data. For detailed information about using Migration Toolkit, see the Migration Toolkit Guide.

2. Configure the Migration Toolkit toolkit.properties file, ensuring that connection information for the source and target databases is available in the property file:

SRC_DB_URL = jdbc: oracle:thin:@localhost:1521:ORCL
SRC_DB_USER = user_name
SRC_DB_PASSWORD = password
TARGET_DB_URL= jdbc:edb://localhost:5444/migration
TARGET_DB_USER = enterprisedb
TARGET_DB-PASSWORD = password

For more information, see Building the toolkit.properties File.

3. Invoke Migration Toolkit in –dataOnly mode. Include the –truncLoad keyword to resolve foreign key dependencies across tables.

   For example, the following command migrates data to a schema named hr:

   runMTK.sh –dataOnly –targetSchema hr –truncLoad HR

   The command migrates the specified source_schema to the target_schema. The data is loaded into the locally installed EDB Postgres instance with a database superuser named enterprisedb and the password of password.

Note The tables are truncated before attempting the data load.

Advanced Data Migration

For larger databases that require a parallel data load, you can use one of the following methods:

• Use the EDB Postgres Advanced Server database link feature (for compatibility with Oracle databases).

  Or

• Use a Dblink or a database link style migration (if your data contains CLOB data).

For more information, see the Migration Toolkit Guide.