Database Migration Assessment for Oracle extension (Preview)

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

Pre-requisites

Install Azure Data Studio extension

Follow the steps below 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.

    install extension

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.

    connections menu bar

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

    1. In 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 ORACLE_HOME environment variable is set and TNSNAMES.ORA file is located in the <ORACLE_HOME>/network/admin folder. )
    3. In the User Id field, provide the database username.
    4. In the Password field, provide the database password.

    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.

    manage database

  6. Select Migration Assessment.

    migration assessment database

  7. Select Run new Assessment.

    run new assessment

  8. Provide your assessment details.

    1. In the Assessment name field, enter a title.
      1. For example, demo1.
    2. Enter your Assessment setting.
      1. In the Target Platform field, enter the destination migration database.

        1. For example, SQL.
      2. In the Scale factor field, enter the multiplier value.

        1. If the recommended SKU needs to consider other peak load, the scale factor multiplier should be greater than 1. Example: Burst Load, Seasonal usage, future capacity planning etc. Whereas, when partial Oracle schema workload is considered migrated, then the multiplier should be less than 1.
      3. In the Percentile utilization field, enter the percentile value for sizing the Azure target.

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

    assessment details

  9. Now, you see the new assessment in the last five assessments section.

    name the assessment demo1

  10. On Click on the assessment link to view assessment details page. You can view the latest assessment status.

    latest assessment status

  11. Click refresh or wait until the Assessment completes. The assessment status page refreshes frequently. The default value is 15 seconds. The status updates to show you 1 of 4 statuses - success, failed , in-progress or canceled.

View assessment

Once the Assessment is complete, a consolidated output is generated for each Azure SQL target. Currently, these targets include SQL Server on Azure Virtual Machines, Azure SQL Database, and Azure SQL Managed Instance.

view assessment

Each card has three sections. The card shows the overall feasibility of the migration to the SQL target. Also drills down on various workloads under various migration complexity categories. The feature compatibility section that provides the feature assessment review result. SKU recommendation provides the proper sizing of the target.

When you click on view detail report, it first shows the summary of the Assessment.

card sections

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

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 SQL Server migration Assistant for Oracle.

Note

The following system or Oracle built-in schemas are excluded in the workload assessment 'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW' 'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS 'SYSMAN','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','DIP','MDDATA','ORACLE_OCM', 'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT','SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF','AUDSYS','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN','DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER','APEX','FLOW_'

The SKU recommendation provides the suitable Azure SQL target, its service tier and the metric thresholds that have been used to provide the recommended SKU.

Sku recommendations

The SKU recommendation evaluates various performance metrics - CPU, memory, IOPS, latency and storage. Based on the usage and the configuration data, the recommender provides the suitable SQL target and the appropriate service tier.

Note

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

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

Mapped assessment

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

download assessment report

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

Change Assessment Directory

  1. Go to extension marketplace and search for Database Migration Assessment for Oracle.

  2. Click on the manage icon and click extension settings.

  3. Provide the new assessment path under Oracle Assessment: Assessment Path.

    Screen capture shows the Change assessment path

Troubleshoot

Delete assessment

  1. Go to the assessment directory. The assessment directory path is shown in the Oracle Assessment:Assessment Path settings under the manage extension. Default Assessment Path

    • Windows - C:\Users\<username>\.dmaoracle
    • Linux - ~/home/<username>/.dmaoracle/
    • Mac - /Users/<username>/.dmaoracle/
  2. For each Oracle server, there will be separate folder inside .dmaoracle

  3. Traverse through the folder and identify the folder matching the assessment name.

  4. Delete the particular folder.

Logging

The extension has the errors, warning and other diagnostic logging written in the default log directory.

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

By default, the extension stores last 7 log files.

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

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>/bin/service/Properties/ConfigSettings/extension-settings.json

Mac - /Users/<username>/.azuredatastudio/extensions/microsoft.azuredatastudio-dma-oracle-/<VersionNumber>/bin/service/Properties/ConfigSettings/extension-settings.json

Known issues

  • Error GE-1002 Path provided does not exist.

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

    Possible Solution:

    a. User has read and write permission on the assessment folder.

    b. If there is 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:

    a. Check if the port Oracle is running on isn't blocked by firewall rules.

    b. Perform tnsping and see if the Service ID gets resolved.

  • Feature Data Collection Warning

    Reason: Few features don't have the latest usage statistics available in the Oracle metadata store.

Next steps