Edit

Database Migration Assessment for Oracle extension

  • Article

The Database Migration Assessment for Oracle extension in Azure Data Studio helps you assess an Oracle workload for migrating to Azure SQL and Azure Database for PostgreSQL. The extension identifies an appropriate Azure SQL or PostgreSQL target with right-sizing recommendations, and how complex the migration can be.

Prerequisites

Install Azure Data Studio extension

Follow these steps to install the Database Migration Assessment for Oracle extension in Azure Data Studio.

  1. Open the extensions manager in Azure Data Studio. You can select the extensions icon or select Extensions in the View menu.

  2. Type Oracle in the search bar.

  3. Select the Database Migration Assessment for Oracle extension and view its details.

  4. Select Install.

    Screenshot of installing the extension.

Configure extension settings

You can configure several extension settings after installing the extension.

  1. Go to extensions and select Database Migration Assessment for Oracle, select the manage settings icon, and then select extension settings.

    Screenshot of extension.

  2. Under extension settings, edit any extension settings to meet the environment's requirements.

    Screenshot of extension properties settings.

    Note

    To perform Oracle to PostgreSQL code assessment, the Oracle client home path and Ora2Pg installation path are mandatory parameters.

Run assessment

Once the assessment extension installs, the next step is to connect to Oracle your database, collect metadata information from your Oracle instance and generate an assessment report.

  1. Go to the connections icon in the menu bar.

    Screenshot of connections menu bar.

  2. In the Connection Details, fill out the fields.

    1. In the Connection type field, select Oracle.
    2. In the Data Source field, type in your Oracle server name and instance.
      1. You can provide the TNS name as well. (Make sure that the ORACLE_HOME environment variable is set and the TNSNAMES.ORA file is located in the <ORACLE_HOME>/network/admin folder.)
      2. For container database, you can either connect the container root database using the CDB service ID, or use the pluggable database service name to connect to pluggable database.
    3. Provide the database username in the User Id field.
    4. Provide the database password in the Password field.

    Screenshot of connection details.

  3. Select Connect.

  4. Now, a new connection appears in the connection details menu.

  5. Right-click on the Oracle connection and select Manage.

    Screenshot showing the Manage database screen.

  6. Select Migration Assessment.

    Screenshot showing the Migration Assessment database screen.

  7. Select Run new Assessment.

    Screenshot showing the Run new Assessment screen.

  8. Provide your assessment details.

    1. In the Assessment name field, enter a title, for example, demo1.
    2. Enter your Assessment setting.

      1. Enter the destination migration database in the Target Platform field. Currently, it supports Azure SQL and PostgreSQL as targets.

        Note

        PostgreSQL target is in preview. This target option will be discontinued. You should export any prior assessments.

      2. In the Performance data collection section, either select Run performance data collection on Oracle database (connected) or Add AWR report.

        The recommendation is to use the connected option if you're running this tool during a peak or realistic load. Otherwise, provide the AWR reports generated in the past for performance and sizing recommendations.

        Note

        The AWR report assessment option isn't enabled for pluggable database.

      3. Enter the multiplier value in the Scale factor field.

        • If the recommended SKU needs to consider other peak loads, the scale factor multiplier should be greater than 1. For example, Burst Load, Seasonal usage, and future capacity planning. When a partial Oracle schema workload is migrated, the multiplier should be less than 1.

      4. In the Percentile utilization field, enter the percentile value for sizing the Azure target.

        • The percentile value of the performance sample set to be considered for sizing the Azure target.

    Screenshot showing the assessment details screen.

  9. There are two types of assessment:

    • Workload Assessment. In this assessment, the Oracle assessment module performs a lightweight discovery of the schema objects and categorizes the schema complexity for migration to various categories with a high-level estimate for conversion hours. This preassessment helps to find the feasibility of the migration to the target platform.

    • Code Assessment. The code assessment performs an in-depth assessment of the Oracle schema objects and then suggests an overall readiness of the code objects with "ready", "need review", and "not ready" object types at a granular level and its associated conversion hours with higher accuracy.

      Note

      The extension uses the open-source Ora2Pg schema converter for code assessments.

      If the Oracle client and/or Ora2Pg isn't installed on the same machine as Azure Data Studio, either manually install them, or use the Ora2Pg installation script from GitHub.

      If the Oracle client and Ora2Pg are installed, ensure that the Oracle client home path, and Ora2Pg installation path are updated in the extension settings.

      • When you select Code assessment for SQL scenarios, select the schemas that you would like to access for Oracle to SQL conversion and migration. By default, all the eligible schemas are selected for the assessment.

  10. Choose Run validation to perform the prerequisites and various validation rules related to security permissions, configuration settings etc. If validation fails, then you need to fix the errors to proceed further.

  11. When Validation is successful, you can select Run assessment.

  12. Now, you see the new assessment in the "Last five assessments" section.

    Screenshot showing the option to name the assessment.

  13. Select the assessment link to view the assessment details page. You can view the latest assessment status.

    Screenshot showing the latest assessment status.

  14. Select Refresh, or wait until the assessment completes. By default, the assessment status page refreshes every 15 seconds. The status shows you one of four statuses: success, failed, in-progress, or canceled.

View the assessment

Once the assessment is complete, a consolidated output using cards is generated for either each Azure SQL target: SQL Server on Azure Virtual Machines, Azure SQL Database, and Azure SQL Managed Instance, or Azure Database for PostgreSQL - Flexible server.

Screenshot of the view assessment option.

Each card has multiple sections. The card shows the overall feasibility of the migration to the target. You can drill down on various workloads under various migration complexity categories:

  • The feature compatibility section provides the feature assessment review result.
  • SKU recommendation provides the proper sizing of the target.
  • The code assessment provides the code complexity of database objects.

When you select View report details, it first shows the assessment summary.

Screenshot showing assessment summary.

If the target selected is Database for Azure PostgreSQL, either the Database details tab or Code assessment tab is visible based on the assessment type selection.

The following Database details tab provides the breakdown per schema basis. It shows the list of schemas discovered, the migration feasibility, and the estimated time to convert the schema in hours.

Screenshot showing the breakdown per schema basis.

This estimation is based on a statistical model that applies to the object count, lines of code, enabled features, and size of the database. For more accurate estimate on the code conversion, use code assessment.

Note

The following system or Oracle built-in schemas are excluded from the workload assessment:

APEX, APPQOSSYS, AUDSYS, CSMIG, CTXSYS, DBMS_PRIVILEGE_CAPTURE, DBSFWUSER, DBSNMP, DIP, DMSYS, DVF, DVSYS, EXFSYS, FLOW_, GSMADMIN_INTERNAL, LBACSYS, MDDATA, MDSYS, MGDSYS, MGMT_VIEW, MTMSYS, ODM, ODM_MTR, OJVMSYS, OLAPSYS, ORACLE_OCM, ORDDATA, ORDPLUGINS, ORDSYS, OSE$HTTP$ADMIN, OUTLN, OWBSYS, OWBSYS_AUDIT, PERFSTAT, SDE, SI_INFORMTN_SCHEMA, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, SQLTXPLAIN, SYS, SYSMAN, SYSTEM, TRACESRV, TSMSYS, WEBSYS, WKPROXY, WKSYS, WK_PROXY, WK_TEST, WMSYS, XDB, XS$NULL

The SKU recommendation provides the following items:

  • The suitable Azure target.

  • The service tier.

  • The metric thresholds are used to provide the recommended SKU.

    Screenshot showing SKU recommendations.

The SKU recommendation evaluates various performance metrics, such as CPU, memory, IOPS, latency, and storage. Based on the usage and the configuration data, the recommender provides the suitable target and the appropriate service tier. The SKU recommendation is based on the container root instance or pluggable database utilization. Schema selection is ignored and not applicable to the SKU sizing.

Note

If the automatic workload repository (AWR) feature is enabled on the Oracle instance, the SKU recommender will use the DBA_HIST_ views to gather the performance metrics metadata. Otherwise, the recommender will use server configuration and other system view information for sizing the Azure SQL target.

The code compatibility (for PostgreSQL targets only) provides a summary of schema objects that can be converted to the Azure target. The report provides the breakup of the objects that can be converted automatically, ready with conditions, and need to be remediated and converted manually.

Screenshot showing the code compatibility report.

The report shows the total number of objects and conversion effort hours required to migrate code to the Azure PostgreSQL database. The graphical image provides the breakup for each schema object type such as tables, views, and clusters. The efforts are categorized as "Ready - automatically converted", "Needs review", and "Not ready - requires manual conversion".

A drill-down for the "Not ready" and "Needs review" sections provides the list of remediation tasks and the effort hours for each task.

Screenshot showing the remediation task.

The feature assessment provides the Oracle to Azure data target mapped features and the effort required for migrating those capabilities to the Azure target.

Screenshot showing the mapped assessment.

You can download the report for offline viewing by selecting Download combined reports or Download individual reports.

Screenshot showing the download assessment report option.

You can also cancel an ongoing assessment, delete an assessment and move assessments to another directory.

Change assessment path

  1. Go to the extension marketplace and search for Database Migration Assessment for Oracle.
  2. Select the Manage icon, and select Extensions settings.
  3. Provide the new assessment path under Oracle Assessment: Assessment Path.

Screenshot showing the Change assessment path option.

Troubleshoot

Logs

The extension stores errors, warnings, and other diagnostic logs in the default log directory:

  • Windows - C:\Users\<username>.dmaoracle\logs\
  • Linux - ~/.dmaoracle/logs
  • macOS - /Users/<username>/.dmaoracle/logs

Note

By default, the extension stores the last seven log files.

To change the log directory, update the LogDirectory property in the extension settings file.

Operating system Path
Windows C:\Users\<username>\.azuredatastudio\extensions\microsoft.azuredatastudio-dma-oracle-<VersionNumber>\bin\service\Properties\ConfigSettings\extension-settings.json
Linux ~/.azuredatastudio/extensions/microsoft.azuredatastudio-dma-oracle-<VersionNumber>/<VersionNumber>/bin/service/Properties/ConfigSettings/extension-settings.json
macOS /Users/<username>/.azuredatastudio/extensions/microsoft.azuredatastudio-dma-oracle-<VersionNumber>/<VersionNumber>/bin/service/Properties/ConfigSettings/extension-settings.json

For more information about troubleshooting issues, visit Troubleshoot Database Migration Assessment for Oracle extension errors.

Known issues and limitations

  • Path provided doesn't exist.

    Reason: Missing files or missing permission on the assessment folder.

    Possible solution:

    • The user has read and write permission on the assessment folder.
    • If there's a missing file or folder, delete the assessment and generate a new assessment.

  • Encountered connection timeout exception while interacting with Oracle.

    Reason: Failed to connect to Oracle instance.

    Possible solution:

    • Check if firewall rules block the Oracle listening port.
    • Run tnsping and see if the Service ID gets resolved.

Contact Microsoft for help

If you need further assistance from Microsoft, contact @dmasupport. For faster turnaround, you can attach the logs from the default log directory.

Related content