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.

   

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.

7. 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.

8. To specify using Oracle default casing when creating a project, select the Use Oracle’s default case for schema migration check box. Use the Oracle’s default case feature to preserve the Oracle’s default, uppercase naming convention for all database objects when migrating schema from an Oracle database to an EDB Postgres Advanced Server database. See Known issues, limitations, and notes for more information about this feature.

9. To specify assessing the DEFAULT profile, when creating a project, select the Assess DEFAULT profile (if available) check box. The EDB DDL Extractor extracts the ALTER PROFILE statements for DEFAULT profiles that are overridden in the Oracle database. This option results in Migration Portal assessing the DEFAULT profile and attempting to apply these statements in the target EDB Postgres Advanced Server database when the schema is migrated. We recommend that the settings of the ALTER PROFILE DEFAULT statement be verified and updated, if necessary, before migrating the DDL to the target database. See Known issues, limitations, and notes for more information about this feature.

10. 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.

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

12. 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.

13. 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.

14. 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.

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.

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:

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.

Limitations

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 continues to list the incompatible features in the source DDL. The schema migration complexity assessment score is 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.

• On this page
• Perform a schema assessment
• Generate an assessment report
• Evaluate an assessment report