Performing a schema assessment v4

You can assess an Oracle database schema for compatibility with EDB Postgres Advanced Server. You can also generate a report about the assessment.

Perform a schema assessment

  1. Navigate to Migration Portal.

  2. Enter your EDB credentials.

  3. Select New to create a project.

    The Migration Portal New project dialog

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

  5. Specify project details:

    • Select the application interface.
    • Select a source DB and version for the source.
    • Select the target DB and Version for the target.
  6. To upload the .SQL file generated with your schema metadata, select Choose file.

    Note

    Don't modify the .SQL file.

  1. To specify adding a suffix to indexes (_IDX) and constraints (_CON) when creating a project, select the Add suffix to make object names unique check box. This selection ensures better assessment results, as EDB Postgres Advanced Server doesn't support the same name for tables and indexes whereas Oracle does.

  2. Select Create & assess to create a new project and to assess the schemas.

    The analysis tool reviews every construct and executes repair actions to improve compatibility with EDB Postgres Advanced Server. It flags any remaining errors that require manual intervention.

  3. Verify the DDL objects (e.g., TABLES) that don't show a 100% success ratio.

  4. Select the objects that aren't compatible with EDB Postgres Advanced Server and view the details. At the bottom of the middle panel, you can view repair action details.

Incompatible objects are identified

  1. Refer to the Knowledge Base information in the right panel to locate the possible workarounds for the objects that aren't immediately compatible with EDB Postgres Advanced Server. You can also view the Knowledge Base information on the Portal wiki page.

Incompatible objects are identified

  1. On the Knowledge Base tab, you can enter the error message for the incompatible objects with EDB Postgres Advanced Server and select 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 select Reassess.

    Note

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

    Similarly, you can make all incompatible objects compatible.

Workaround or resolution for incompatible objects

Note

If the information or workaround for incompatible objects isn't available in the Knowledge Base, contact the Support team for help.

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.

Generate an assessment report

Migration Portal reports provide 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.

  1. Select Report.

  2. Select the schemas to include in the report.

  3. To generate the onscreen report, select Generate.

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.

Note

Migration Portal doesn't assess sensitive PL/SQL source code hidden in Oracle wrapped objects. These wrapped objects aren't included in the assessed objects count, and therefore the true value of compatibility percentage might be different from the value calculated in the assessment report.

See the note in Known issues and notes for more information about wrapped objects.

Evaluate an assessment report

The assessment report presents data and information about the compatibility of objects and features in your schemas and the overall project.

  • Object: Migration Portal generates an object for all CREATE statements it processes. In addition, it generates an object for each ALTER TABLE statement that adds a constraint in the uploaded DDL.
  • Feature: An Oracle clause or a construct that is used in the creation of an object. A feature can be used multiple times in one or more objects.

The assessment report lists tables for Incompatible Features and Unresolved Objects.

Object compatibility

An object is compatible if it successfully executes on a target database. In the assessment report, you see a summary of object compatibility based on the following categories:

Object compatibility levelDescription
PassedExecutes on a target database without applying any repair handlers.
System repairedExecutes successfully on a target database after applying one or more repair handlers. Repair handlers use transformation logic to automatically make DDLs compatible with EDB Postgres Advanced Server. Ensure that you carefully review the descriptions in the Repair Handler applied section of the report in case of an unexpected impact on the behavior of the object.
User repairedExecutes successfully on a target database after being manually repaired and reassessed by the user.
Failed objectsDoes not execute successfully on the target database.
Note

The object analysis presented is based on the existing state of the objects in the target DDL, which might change if the objects are updated and reassessed. For example, objects that are fixed are removed from the Unresolved Objects table after reassessment.

Feature compatibility

Some features might not be natively available in EDB Postgres Advanced Server. The assessment report presents a summary of feature occurrences and compatibility levels.

Feature compatibility levelWorkaround status
Compatible features-
Features with existing available workaroundExists
Features with reasonable effort new workaroundPossible
Features with no workaround or requiring further investigationNone / Needs Investigation
Note

For features with no workaround, it might be possible to address the feature incompatibility through application-level changes.

Limitations

  • The complexity level and factors reported are based on the Oracle compatibility features available in EDB Postgres Advanced Server (EPAS) version 14. Suppose a previous version of EPAS is targeted for this migration project. In that case, the project may include features not implemented in the selected version, resulting in a different feature assessment. For accurate assessments of schema migrations to EPAS version 13 or earlier, contact EDB to see how the EDB Professional Services team can help.

  • The assessment report is updated after a user manually makes changes to the target DDL and selects Reassess. However, the Incompatible Features table in the report does not get updated and will continue to list the incompatible features in the source DDL. The schema migration complexity assessment score will be updated as incompatible objects with incompatible features are resolved. Also, after objects are fixed and reassessed they are removed from the Unresolved Objects table along with the listing of any incompatible features that were originally found in those objects.