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.
Note
Review the release notes and known issues for an overview of unsupported schema objects.
Perform a schema assessment
Navigate to Migration Portal.
Enter your EDB credentials.
Select New to create a project.
In the dialog box, enter the project name in the Project name field.
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.
To upload the
.SQLfile generated with your schema metadata, select Choose file.Note
Don't modify the
.SQLfile.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.
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.
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.
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.
Verify the DDL objects (e.g., TABLES) that don't show a 100% success ratio.
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.
To locate the possible workarounds for the objects that aren't immediately compatible with EDB Postgres Advanced Server, refer to the Quick Help (Knowledge Base) information or interact with the AI Copilot in the right panel.
When using the Quick Help (Knowledge Base), enter the error message for the incompatible objects with EDB Postgres Advanced Server and select Search.
When using the AI Copilot, use the chat window to interact with the AI-enabled chat feature. You can ask questions about compatibility, enter the error message, request syntax Postgres equivalents for DDL queries, and so on.
Note
If the information or workaround for incompatible objects isn't available in the Knowledge Base or AI Copilot, contact the Support team for help.
Use the information you obtained from the Knowledge Base and AI Copilot to make all incompatible objects compatible. Manually make the changes in the Target pannel for that object and select Reassess.
Important
Ensure that you test all suggested solutions to confirm the converted schemas behave as expected.
Note
If any failed object passes while reassessing, the dependent objects for that object are also reassessed.
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.
Select Report.
Select the schemas to include in the report.
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 level | Description |
|---|---|
| Passed | Executes on a target database without applying any repair handlers. |
| System repaired | Executes 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 repaired | Executes successfully on a target database after being manually repaired and reassessed by the user. |
| Failed objects | Does 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 level | Workaround status |
|---|---|
| Compatible features | - |
| Features with existing available workaround | Exists |
| Features with reasonable effort new workaround | Possible |
| Features with no workaround or requiring further investigation | None / Needs Investigation |
Note
For features with no workaround, it might be possible to address the feature incompatibility through application-level changes.
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.