3 Database Migration Workbench

About Migration Workbench

The decision to move workloads starts at the application level. As an organization decides, a particular application is chosen to move with its entire stack to a different location or service. In these cases, the challenge for administrators is how to find the right set of schema and/or databases to move. With years of investment into building and fine tuning applications, dependencies within applications and database, it becomes nearly impossible to maintain a stable relationship among all dependent components using traditional methods.

Oracle Enterprise Manager Database Migration Workbench provides an accurate approach to migration and consolidation by eliminating human errors allowing you to easily move your on-premises databases to Oracle Cloud, Multitenant architecture or upgrade your infrastructure. Some advantages of using Database Migration Workbech are: near zero downtime, assured zero data loss, seamless on-premises or Cloud migrations and, MAA and Cloud Security compliant.

For information on Database Migration Workbench licensing, see: Oracle Real Application Testing.

Migration Workbench Steps

Migration Workbench can be separated into 3 main steps that help make migrations easier: Plan, Migrate and Analyze.
  1. Plan: Identify databases to migrate. As part of the Plan activities using the Sizing and Consolidation Advisor, databases are consolidated into a single destination database optimized for size and performance.
  2. Migrate: The actual migration of the databases, moving data to autonomous and non-autonomous databases. The methods available are Datapump (Logical Data Migration), and Transportable Tablespace (TTS with RMAN).
  3. Analyze: Monitors and tracks the migration process, after migration users can see log reports that show what migrated successfully and help diagnose any errors that may have occurred. Comparison reports are also available to assess database performance before of after migration.

Figure 3-1 Complete Migration Process with Database Migrations Workbench

Complete Migration Process with Database Migrations Workbench

Plan a Database Migration with Database Migration Workbench

Review the following information in order to plan a migration using Database Migration Workbench.

Migration Workbench Support Matrix

Oracle Enterprise Manager Database Migration Workbench provides an accurate approach to migration and consolidation by eliminating human errors allowing you to easily move your on-premises databases to different services like cloud, physical or virtual server, on-premises Exadata.

The Migration Support Matrix shows the different types of migrations you can perform with Enterprise Manager, review the matrix to determine the Migration method most desirable for migrating your database. This will help you choose the specific topics and migration path in this chapter.

Highlights of this matrix are as follows:
  • Migrations can be performed with all Enterprise Manager Migration Methods (Migration Workbench, EM CLI and REST API)
  • Source destination database version for all migration types can be from 12.2.0.1 to 21c
  • Source operating systems can be Linux, Sun Solaris, AIX or Windows
  • All migration types offer Cross Platform Support, allowing you to choose the best combination of database type, database version and operating system for your environment going forward.

Figure 3-2 Migration Workbench Support Matrix

Migration Workbench Support Matrix

Note:

* Transportable Tablespace Migrations are currently not supported for Windows Destination Databases.

Migration Methods

There are two main types of migration methods available in Migration Workbench: Data Pump and Transportable Tablespaces. Review the information on each so you can choose the correct migration method for your needs.

Guideline for Migration Workbench Method Selection

The following table is a guideline for selecting the proper Database Migration Workbench Migration Method for your particular scenario:
Migration Method Size of Source Database Move to New Server Change in Endianness Operating System Change Character Set Change Architecture Layout Change Selected Object (Table/User) Migration Tablespace Migration Downtime Required
Data Pump < 500 GB Yes Yes Yes Yes Yes Yes No Yes
Transportable Tablespace > 500 GB (or any size) Yes Yes Yes No No No Yes Minimal

Data Pump

Data Pump provides an Export and Import way of moving data and metadata from one database to another. It begins the Export by unloading table data, database object metadata, and control information into a file dump. During the Import portion, the file dump is loaded into the destination database. Some of its main benefits include metadata filtering based upon object and object types, you can specify how partitioned tables are handled during import operations, as well as a full range of data types that can be exported and imported.

Data Pump supports the following migration modes:
  • Full Database: In this mode, the entire content of the source database is moved to the File Dump and loaded into the target database.
  • Schema: In this mode, only objects owned by the specified schema are moved to the File Dump and later loaded.

Note:

When performing Data Pump migrations do not use SYSDBA as the named credentials for Export and Import.
For more information on Data Pump see: Oracle Data Pump in Oracle Database Utilities 21c.

Transportable Tablespace (TTS)

Transportable Tablespace provides a Near Zero Downtime (NZDT) way to create a duplicate database from incremental backups of the source database, while retaining the original target database. The duplicate database can be identical to the source database or contain only a subset of the tablespaces in the source database. A duplicate database created in RMAN, is not intended for failover scenarios and does not support the various standby recovery and failover options.

Transportable Tablespace supports the following migration modes:
  • Full Database: In this mode, the entire content of the source database is moved via a backup and loaded into the target database.
  • Tablespace: In this mode, only selected tablespace objects are moved to the target database.
  • Split DP: In this mode, the entire content of the source database is moved via incremental backups and loaded into the target database.

Note:

When performing Transportable Tablespace migrations you will need to use SYSDBA as the named credentials.
For more information on Transportable Tablespace see: Creating Transportable Tablespace Sets in Oracle Database Backup and Recovery User's Guide

Migration Workbench Architecture

The following is an architecture diagram showing the differences between a TTS migration and a Data Pump migration, in which the TTS migration is taken over a period of time with incremental backups incurring in Near Zero Downtime. While the Data Pump migration is done in a single import/export session with minimal downtime.
A diagram showing the differences between TTS and Data Pump migrations.

Customization Support for Migration Workbench

Migration Workbench allows customization of both Data Pump and Transportable Tablespace migration methods by allowing the user to upload and use Pre and Post migration scripts. For more information on Pre and Post scripts see: Pre/Post Scripts for Provisioning in Oracle Enterprise Manager Cloud Control Database Lifecycle Management Administrator's Guide and Pre and Post Request Creation / Deletion Scripts in Oracle Enterprise Manager Cloud Administration Guide.

Database Migration Prerequisites

Before you can migrate on-premises Oracle Databases to Oracle Cloud, certain general prerequisites need to be met whether you are migrating via Database Migration Workbench, EM CLI or REST API.

Common Prerequistites for Migration Workbench

  • Source Database and Host credentials. Host credentials need to be supplied for any operation which needs to log into the host and are mandatory for any job. Database credentials are used to make database management easier. For more information on setting up credentials see: Setting Up Credentials in Oracle Enterprise Manager Cloud Control Database Lifecycle Management Administrator's Guide.
  • VPN connectivity or an existing connectivity between on-premises data center and destination service.

    Note:

    Destination will be the OCI data center for Autonomous Databases (required for calls to APIS and Autonomous Database connections)
  • Enterprise Manager Migration Workbench requires open connections (direct or via proxy) to the following URLs:
    • https://download.oracle.com/*
    • https://objectstorage.us-phoenix-1.oraclecloud.com/*
  • In Enterprise Manager assign the following privileges to each corresponding target:
    Required Privilege: Target to Apply Privilege to:
    Connect to Target Source and Destination Database
    Execute Command Source Database Host
    Configure Target Destination Database
    Execute Command Monitoring Agent host of Destination Database
    Create New or Existing Views Named Credential
    Create Any Software Library Entity Software Library Entity
    View Any Software Library Entity Software Library Entity
    Create Deployment Procedure and Job System
    For more information on how to grant privileges see: About Deployment Procedures and Granting Roles and Privileges to Administrators in Oracle Enterprise Manager Cloud Control Database Lifecycle Management Administrator's Guide.
Data Pump Migration Prerequisites

The following is a list of prerequisites needed for a Data Pump type migration.

Prerequisite Description
Source Database Database discovered and visible in Enterprise Manager. For more information see: Discovery in Oracle Enterprise Manager Cloud Control Administrator's Guide.
Export Working Directory
  • Create a local directory with sufficient space to host the export dump file.
  • Write privilege to the directory where the export will be stored for the import database user.
Grant Source Database Permissions Grant the DATAPUMP_EXP_FULL_DATABASE role to the export database user. This role grants the user performing the export additional privileges that aid Data Pump migrations:
  • Perform Export operations outside the scope of their assigned schema.
  • Monitor jobs that were initiated by another user.
  • Export objects such as table space definitions that unprivileged users cannot reference.
Example of Datapump Export Full Database role grant:
SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO exp_user;
Destination Database Destination Database is discovered and visible in Enterprise Manager. For more information see: Discover Autonomous Databases in Oracle Enterprise Manager Cloud Control Administrator's Guide for Oracle Autonomous Databases.
Grant Destination Database Permission Grant the DATAPUMP_IMP_FULL_DATABASE role to the import database user. This role grants the user performing the import additional privileges that aid Data Pump migrations:
  • Perform Import operations outside the scope of their assigned schema.
  • Monitor jobs that were initiated by another user.
  • Import objects such as directory definitions that unprivileged users cannot reference.
Example of Datapump Import Full Database role grant:
SQL> GRANT DATAPUMP_IMP_FULL_DATABASE TO imp_user;
Import Working Directory Write privilege to the directory where the import will be stored for the import database user.
Autonomous Database Destination Prerequisites
  • An Oracle Cloud Account is required for the destination Autonomous Database and storage of the uploaded dump files. For more information see: About Oracle Cloud Accounts in Oracle Cloud Getting Started with Oracle Cloud Applications.
  • Wallet file to connect to Autonomous Database (automated from Enterprise Manager). For more information see: Discover Autonomous Databases in Oracle Enterprise Manager Cloud Control Administrator's Guide for Oracle Autonomous Databases.
  • Configure cloud credentials in the Autonomous Database for data import from the Object Store. For more information see: CREATE_CREDENTIAL Procedure in Oracle Cloud Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure.

Additional Prerequisite Information for REST and EM CLI:

Upload Migration Tools

Migration Workbench uses Instant Client and the Cloud Premigration Advisor Tool (CPAT) as part of its migration toolkit. Make sure these utilities are uploaded into the Enterprise Manager software library for Database Migration to work successfully on all Data Pump migrations.

Note:

Enterprise Manager automatically downloads the latest version of the tools when setup with either a MOS Proxy or direct internet connection. If these utilities are not available from the Software Library for your operating system you will need to download them manually. This usually occurs when the latest version of the Migration Tools are not compatible with your current version of the Operating System.

Upload Migration Tools is required for Data Pump database migration, Migration tools are not required for TTS migration.

Instant Client

Oracle Instant Client provides the necessary Oracle Database client-side command-line utilities: SQL*Plus, SQL*Loader and Oracle Data Pump. Instant Client simplifies the deployment of applications by eliminating the need for an Oracle home on the client machines, leading to a significantly smaller footprint.
Operation Tools Used Comments
Export expdp This will be used from the source Database home as part of the Lift operation.
Upload N/A OCI REST APIS will be used
Import sqlplus/impdp These are required in the destination Database, make sure you have the latest version matching the OCI Database version.
For more information on Instant Client see: Oracle Instant Client. To download SQL*Plus, SQL*Loader and Oracle Data Pump zip files see: Oracle Instant Client Downloads, make sure you download the correct version corresponding to your operating system. To download the zip files, first select your operating system to view the available zip files.

To upload the Migration Tools into Enterprise Manager for a migration see step 3 Migrate Using Database Migration Workbench User Interface in Migrate Using Database Migration Workbench.

Cloud Premigration Advisor Tool (CPAT)

Starting with Oracle Enterprise Manager 13.5 Release 5 Update 4 (13.5.0.4) you can now use Cloud Premigration Advisor Tool (CPAT) instead of Schema Advisor.

CPAT is a modern robust tool that will give you a detailed analysis of your databases before migration ensuring all possible Failures, Action Requirements, Required and Suggested Reviews are addressed beforehand. To use CPAT select Cloud Premigration Advisor Tool (CPAT), and select to upload from a Local Machine (uploaded using browser) or from the Agent Machine (Agent uploads the tool). Starting with Oracle Enterprise Manager 13.5 Release 5 Update 6 (13.5.0.6) you can now use CPAT for on-premises migration.

CPAT Utility has the following features, aiding in your migration:
  • CPAT helps to determine the suitability of migrating an Oracle database instance to on premise or Cloud database.
  • CPAT assess source database instance, checks for potentially problematic content, other factors that could impede a successful migration.
  • CPAT pre-migration advisory report categorize results into Failure, Action Required, Review Required, and Review Suggested categories with pass information including severity wise breakdown.
  • The overall result of the CPAT advisory report will be the most severe result of all checks performed.

For more information on CPAT see: Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1)

Prerequisites for EM CLI Data Pump Database Migration

Before you can begin a Data Pump database migration with the EM CLI verb migrate_db you will need to review, install and configure the following:

Migration Workbench migrations using EM CLI migrate_db verb, use a JSON payload file exclusively.

Validate if Prerequistes are Met

emcli migrate_db 
-file="/home/migrations/prereqs.json"
The JSON file prereqs.json contains the following payload:
{
    "action": "VALIDATE_PREREQS",
    "type": "DATAPUMP", 
    "platform": "linux32|linux_64|windows64|solaris_sparc64|solaris_x64|aix_ppc64" 
}

Upload Migration Tools Using EM CLI

emcli migrate_db 
      -file="/home/migrations/prereqsupload.json"
The JSON file prereqsupload.json contains the following payload:
{
    "action": "UPLOAD_PREREQS",
    "type": "DATAPUMP",      
    "hostTarget": "<host-target>",      
    "hostTargetCred": "<hostCredName>:<hostCredOwner>",      
    "tools": {    
             "instantClient": {
         "version": "<DB Version e.g.: 18.5.0.0.0>",
               "platform": "linux32|linux_64|windows64|solaris_sparc64|solaris_x64|aix_ppc64",
               "files": [
                    "/u01/db/insClients/instantclient-basic-<Operating System>-<DB Version>dbru.zip",
                    "/u01/db/insClients/instantclient-sqlplus-<Operating System>-<DB Version>dbru.zip",
                    "/u01/db/insClients/instantclient-tools-<Operating System>-<DB Version>dbru.zip"               
          ]           
        },          
        "schemaAdvisor":{                
            "files":[                     
                    "/u01/db/tools/install_adb_advisor.sql"         
            ]           
        }     
    }
}

Note:

Schema Advisor is currently available for Oracle Enterprise Manager 13c Release 5 Update 3 (13.5.0.3) and below. For Oracle Enterprise Manager 13c Release 5 Update 4 (13.5.0.4) use the CPAT tool noted below.

Upload CPAT Into the Software Library

Starting with Oracle Enterprise Manager 13c Release 5 Update 4 (13.5.0.4) you can use the Cloud Premigration Advisor Tool (CPAT). CPAT is a modern robust tool that will give you a detailed analysis of your databases before migration ensuring all possible Failures, Action Requirements, Required and Suggested Reviews are addressed beforehand.
To upload CPAT execute the following EM CLI command:
emcli migrate_db 
      -file="/home/migrations/prereqsupload.json"
In the JSON file, add the following additional parameter after removing schemaAdvisor:
{
    "action": "UPLOAD_PREREQS",
    "type": "DATAPUMP",      
    "hostTarget": "<hostname>",      
    "hostTargetCred": "<host-cred-name>:<cred-owner>",      
    "tools": {    
        "cpat":{                
            "files":[                    
                    "<Complete file path with zip file>"         
            ]           
        }     
    }	
}
Run the Cloud Premigration Advisor Tool (CPAT)

The following are examples on how to run CPAT and view a CPAT report on EM CLI

Run the Cloud Premigration Advisor Tool (CPAT)

Before starting a migration it is strongly suggested you run the Cloud Premigration Advisor Tool (CPAT) available from Oracle Enterprise Manager 13c Release 5 Update 4 (13.5.0.4) onward. CPAT is a modern robust tool that will give you a detailed analysis of your databases before migration ensuring all possible Failures, Action Requirements, Required and Suggested Reviews are addressed beforehand.

To run CPAT in EM CLI use the following command:

emcli migrate_db 
-file="/home/migrations/DB_Migration.JSON"
Where the file DB_Migration.JSON contains the following parameters:
{
    "action": "ANALYZE_SCHEMA",
    "srcDatabaseInfo":{
        "targetName":"<source database target name>",
        "targetType":"<source database target type>",
        "dbCredName":"<db-creds-name>:<db-creds-owner>",
        "hostCredName":"<host-creds-name>:<host-creds-owner>"
    },
    "destDatabaseInfo":{
        "targetName":"<destination database target name>",
        "targetType":"<destination database target type>",
        "dbCredName":"<db-creds-name>:<db-creds-owner>",
        "hostCredName":"<host-creds-name>:<host-creds-owner>"
    },
    "schemas":<list of schema names to analyze>,
    "excludeSchemas":<list of schema names to be excluded>,
    "srcStageDir":"<Stage directory on source target>",
    "destStageDir":"<Stage directory on destination target>",
    "targetCloud":"<ATPD|ATPS|ADWD|ADWS|DEFAULT>"
}

Note:

  • schemas and excludeSchemas are mutually exclusive. Use only one or the other, depending on your environment's needs.
  • If neither schemas or excludeSchemas are included in the JSON, CPAT will not run in full mode.
  • targetCloud is an optional parameter. If not provided, information is obtained from destDatabaseInfo if available. If neither are available it will be set to a DEFAULT value.
  • If both destDataaseInfo and targetCloud are available, destDatabaseInfo will be used preferentially by Enterprise Manager.

View a CPAT Report

To view the CPAT report in EM CLI use the following command:

emcli migrate_db 
-file="/home/migrations/DB_Migration.JSON"
Where the file DB_Migration.JSON contains the following parameters:
{    
"action": "CPAT_REPORT",    
"executionId":"<DP_Execution_ID>"
}

For more information on migrate_db and the accepted parameters required for migration, see: migrate_db in Oracle Enterprise Manager Command Line Interface .

Prerequisites for REST API Data Pump Database Migration

If you are migrating via REST API, part of the specific prerequisites for this method involve reviewing and uploading the Migration Tools. For more information on Migration Tools see: Upload Migration Tools

Validate if Prerequistes are Met

This REST request verifies that the Migration Tools are available within Enterprise Manager.
Feature Description
Request Method
POST
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
 
{
    "action": "VALIDATE_PREREQS",
    "type": "DATAPUMP", 
    "platform": "linux32|linux_64|windows64|solaris_sparc64|solaris_x64|aix_ppc64", 
}
Response
{
    "statusCode": 200,
    "platform": "linux_64",
    "instantClient": true,
    "customInstantClients": false,
    "schemaAdvisor": true,
    "tools": {
        "instantClient": [
            {
                "platform": "linux32|linux_64|windows64|solaris_sparc64|solaris_x64|aix_ppc64",
                "version": "<DB Version e.g.: 18.5.0.0.0>",
                "available": true,
                "compUrn": "oracle:defaultService:em:provisioning:1:cmp:COMP_Component:SUB_Generic:AFD8D0B13D4E7A7EE053CB98F70AF63B:0.1",
                "compPath": "Database Migration/Tools/linux_64/Instant Client/Default Instant Client",
                "files": [
                    "instantclient-sqlplus-<Operating System>-<DB Version>dbru.zip",
                    "instantclient-tools-<Operating System>-<DB Version>dbru.zip",
                    "instantclient-basic-<Operating System>-<DB Version>dbru.zip"
                ],
                "lastModifiedUser": "SYSMAN",
                "uploadSource": "USER",
                "lastModifiedDate": "Sep 21, 2020 07:30:31 PM UTC"
            }
        ],
        "schemaAdvisor": [
            {
                "available": true,
                "compUrn": "oracle:defaultService:em:provisioning:1:cmp:COMP_Component:SUB_Generic:AFD8D0B13D547A7EE053CB98F70AF63B:0.1",
                "compPath": "Database Migration/Tools/all_platforms/ADB Schema Advisor/Default ADB Schema Advisor",
                "files": [
                    "install_adb_advisor.sql"
                ],
                "lastModifiedUser": "SYSMAN",
                "uploadSource": "USER",
                "lastModifiedDate": "Sep 21, 2020 07:30:32 PM UTC"
            }
        ]
    },
    "toolsFolders": {
        "instantClient": "Database Migration/Tools/linux_64/Instant Client",
        "schemaAdvisor": "Database Migration/Tools/all_platforms/ADB Schema Advisor"
    },
    "scriptsFolder": "Database Migration/User Defined Scripts",
    "msgs": [
        "Usage: Valid platforms for Instant Client are: [solaris_sparc64, linux32, windows64, solaris_x64, aix_ppc64, linux_64]",
        " Multipart form data file type keys are: basicfile, sqlplusfile, toolsfile, and safile"
    ]
}

Upload Migration Tools Using REST-API

This REST request will upload the Migration Tools and CPAT before starting a migration. To download the correct Migration Tools see: Upload Migration Tools.

Note:

This API requires the files to be available at a host that is managed by Enterprise Manager. It will pull the files from that host and upload the Enterprise Manager Software Library.
Feature Description
Request Method
POST
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic 
Connection: keep-alive
Payload
{
    "action": "UPLOAD_PREREQS",
    "type": "DATAPUMP",      
    "hostTarget": "<host-target>",      
    "hostTargetCred": "<hostCredName>:<hostCredOwner>",      
    "tools": {    
            "instantClient": {
         "version": "<DB Version e.g.: 18.5.0.0.0>",
               "platform": "linux32|linux_64|windows64|solaris_sparc64|solaris_x64|aix_ppc64",
               "files": [
                    "/u01/db/insClients/instantclient-basic-<Operating System>-<DB Version>dbru.zip",
                    "/u01/db/insClients/instantclient-sqlplus-<Operating System>-<DB Version>dbru.zip",
                    "/u01/db/insClients/instantclient-tools-<Operating System>-<DB Version>dbru.zip"
          ]           
        },          
        "cpat":{                
            "files":[                    
                    "<Complete file path with zip file>"         
            ]           
        }     
    }
}
Response
{
    "statusCode": 200,
    "instantClient": true,
    "schemaAdvisor": false,
    "cpat": true,
    "msgs": [
        "Instant Client files uploaded to URN: oracle:defaultService:em:provisioning:1:cmp:COMP_Component:SUB_Generic:<GUID>:0.1",
        "URN Version: 1",
        "CPAT files uploaded to URN: oracle:defaultService:em:provisioning:1:cmp:COMP_Component:SUB_Generic:<GUID>:0.1",
        "URN Version: 1"
    ]
}

Note:

If your Oracle Enterprise Manager version is 13c Release 5 Update 3 (13.5.0.3) or below, CPAT is not available. You will need to use Schema Advisor instead.
Perform a CPAT Analysis with REST API

The following is sample code to perform a CPAT analysis with REST API. To run the CPAT advisor Enterprise Manager 13c Release 5 update 5 (13.5.0.5) and above is required.

Run the Cloud Premigration Advisor Tool (CPAT)

Before starting a migration it is strongly suggested you run the Cloud Premigration Advisor Tool (CPAT) available from Oracle Enterprise Manager 13c Release 5 Update 4 (13.5.0.4) onward. CPAT is a modern robust tool that will give you a detailed analysis of your databases before migration ensuring all possible Failures, Action Requirements, Required and Suggested Reviews are addressed beforehand. Use this REST call to enter the migration parameters:
Feature Description
Request Method POST
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{
    "action": "ANALYZE_SCHEMA",
    "srcDatabaseInfo":{
        "targetName":"<source database target name>",
        "targetType":"<source database target type>",
        "dbCredName":"<db-creds-name>:<db-creds-owner>",
        "hostCredName":"<host-creds-name>:<host-creds-owner>"
    },
    "destDatabaseInfo":{
        "targetName":"<destination database target name>",
        "targetType":"<destination database target type>",
        "dbCredName":"<db-creds-name>:<db-creds-owner>",
        "hostCredName":"<host-creds-name>:<host-creds-owner>"
    },
    "schemas":<list of schema names to analyze>,
    "excludeSchemas":<list of schema names to be excluded>,
    "srcStageDir":"<Stage directory on source target>",
    "destStageDir":"<Stage directory on destination target>",
    "targetCloud":"<ATPD|ATPS|ADWD|ADWS|DEFAULT>"
}
Response
{
    "statusCode": 200,
    "status": "SUCCESS",
    "message": "....",
    "submissionName": ".....",
    "submissionBy": "SYSMAN",
    "submissionId": "<Unique Submission ID>",
    "executionId": "<Unique Execution ID>",
    "uiActivityUrl": "/em/faces/core-jobs-procedureExecutionTracking?executionGUID=<Submission ID>",
    "restActivityUrl": "/em/websvcs/restful/db/migration/activities/<Submission ID>"
}

Note:

  • schemas and excludeSchemas are mutually exclusive. Use only one or the other, depending on your environment's needs.
  • If neither schemas or excludeSchemas are included in the JSON, CPAT will not run in full mode.
  • targetCloud is an optional parameter. If not provided, information is obtained from destDatabaseInfo if available. If neither are available it will be set to a DEFAULT value.
  • If both destDataaseInfo and targetCloud are available, destDatabaseInfo will be used preferentially by Enterprise Manager.

View a CPAT Report

Feature Description
Request Method Post
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{    
"action": "CPAT_REPORT",    
"executionId":"<DP_Execution_ID>"
}
Response
{
    "statusCode": 200,
    "status": "SUCCESS",
    "message": "....",
    "submissionName": ".....",
    "submissionBy": "SYSMAN",
    "submissionId": "<Unique Submission ID>",
    "executionId": "<Unique Execution ID>",
    "uiActivityUrl": "/em/faces/core-jobs-procedureExecutionTracking?executionGUID=<Submission ID>",
    "restActivityUrl": "/em/websvcs/restful/db/migration/activities/<Submission ID>"
}
Transportable Tablespace Migration Prerequistes

The following is a list of prerequisites needed for Transportable Tablespace type migration.

Prerequisite Description
Software Library Configuration The Software Library should be configured in EM and the EM user should have the following privileges aasinged: SWLIB_VIEW_ANY_ENTITY, SWLIB_CREATE_ANY_ENTITY.
Source Database The source database should be open in Read-Write mode and in ARCHIVELOG mode.
OS Authentication OS Authentication must be enabled at both source and destination.
Working Directory The source and destination working directories should be present and have read-write permissions. The destination directory should also have sufficient disk space to stage the backup.
Source Tablespace
  • SYS, SYSAUX and TEMP tablespaces are not allowed.
  • The tablespace type should be permanent.
  • The tablespace should be in Read-Write mode.
  • Tablespace Datafiles should be online.
Encryption The source and destination databases should not be encrypted.
Compatibility
  • The source database version should be lower or equal to that of the destination.
  • The character set of source should be supported by destination.
  • The timezones of source and destination databases should be same.
Source Tablespace Ensure there are no logical or physical dependencies between the database objects in the transportable set and the database objects outside of the transportable set. Only self-contained tablespaces can be transported.
Source Database The Source Database should always be Primary. Standby Database is currently not supported.
Working Directory
  • In case, Source or Destination being RAC then Working directory should be a shared location across all the nodes of RAC

Analyze Migration Activities

Database Migration Workbench offers robust tools for monitoring and troubleshooting your recently complete migrations. Database Migration Workbench also offers clean up tools that aid in recovering important disk space from dump files.

Database Migration Activities Dashboard Screen

In the main Database Migration screen you are presented with the latest Migration Activities information:
  • All: Migrations occurred in the last 24 hours (default), 7 days or all.
  • Completed: Completed migrations in the last 24 hours
  • In Progress: Migrations currently in progress
  • Problem: Migrations that encountered problems in the last 24 hours
  • Scheduled: Migrations scheduled to occur in the next 24 hours
  • Warnings: Migrations that completed with warnings in the last 24 hours

Note:

You can modify the dashboard views by selecting the time range in View Data to 7 Days or All on the top right corner of the dashboard.

The Migration Activity table, lists detailed information for each specific migration including Activity, Status, Source, Destination, Start, End, Elapsed Time and Owner.

Clicking on the right hand menu specific to each Activity, you can perform the following operations:
  • Cleanup: Deletes the dump files created during the migration from the Data Pump directory and Object Storage. For more information on Cleanup see: Clean Up Migration Files.

    Note:

    The Cleanup activity will not remove, cancel, rollback or stop an ongoing migration.
  • View Details: Takes you to a new screen with specific details for the selected Activity. You can see specific steps and results for each step in this view.
  • View Analysis: Takes you to a new screen with Validation, Export and Import dashboards with detailed information allowing you to drill down to specific validations and schema to best analyze performance for your migrated database.
  • Compare Performance: In this screen you can compare performance from before migration (source database) to after migration (destination database). For more information see: Analyze Migrated Database Performance
  • Mark as Complete: Migration activities that encounter warnings are reported with Status Completed with Errors, you can review and can mark the migration as Completed. For more information see: Review Migration Status and Complete Migration

Analyze Migrated Database Performance

Migrating a database can change the execution plans of SQL statements improving performance. Migrating to Oracle Cloud changes the platform on which database workloads are running. Oracle Cloud Infrastructure provides a high performing platform to run a database workload.

SQL Performance Analyzer (SPA): Automates the process of assessing the overall effect of a change on the  SQL workload by identifying performance divergence for each SQL statement. A report that shows the net impact on workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, you can remedy any negative outcome before the end-users are affected. Furthermore, you can validate—with time and cost savings—that migration will result in net improvement.

Note:

When running SPA make sure to export your schema as it is needed to stage the tuning set. Failure to do so will generate an error while performing validations.

An SQL Tuning Set (STS) containing the SQL and relevant execution metadata from the source database can be passed to the migration procedure. A SPA task will be created which can then be used to compare the change in SQL Performance due to migration.

Should you need to use out-of-the-box performance management provided by the Autonomous Databases, Database Migration Workbench provides an option to verify performance by disabling indexes. You can then choose to disable indexes and let the Autonomous Database handle performance tuning. For more information see: Managing Auto Indexes in Oracle Database Administrator’s Guide .

View Migration Analysis

Follow these steps to view an analysis of a specific migration with Database Migration Workbench:
  1. Navigate to the Migration Activities Dashboard.
  2. Locate a recently completed Migration Activity you wish to review within the Migration Activity table.
  3. Click on the Action Menu located at the far left of the specific Migration Activity you want to analyze and click on View Analysis.
  4. In the View Analysis dashboard screen you can analyze the results from the three main steps:
    • Validation: Shows the results of the pre-migration checks.
    • Export: Shows the amount of successfully and unsuccessfully exported database objects.
    • Import: Shows the amount of successfully and unsuccessfully imported database objects.
    Underneath the analysis dashboards you can further drill down and analyze in detail each database object's performance and errors during the migration by selecting clicking on Validation, Export and Import.
View SQL Performance Using REST API

Obtain the SPA Report Using REST API

Feature Description
Request Method POST
URI
https://hostname:port/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{
"action": "SPA_REPORT",
"executionId": "9F26EECBE89B5E9FE053356A580A2CDA",
"reportType": "html",
"reportLevel": "typical",
"reportSection": "ALL"
}
Response
{"report": "<html> <head> <title> SQL Performance Impact Analyzer Report </title> <style type=\"text/css\">
        body, table, input, select, textarea {font:normal normal 8pt
        Verdana,Arial;text-decoration:none;color:#000000;} .s8 {font-size:8pt;color:#006699} .s9
        {font-size:10pt;color:#006699} .s10 {font-size:14pt;color:#006699;} .s16 {border-width :
        1px; border-color : #CCCC99; border-style: solid;color:#006699;font-size:8pt;} .s17
        {border-width : 1px; border-color : #CCCC99; border-style: solid;font-size:8pt;} .s27
        {border-width : 1px; border-color : #CCCC99; border-style: solid;} </style> </head> <body> <a
        name=\"general\"/> <a href=\"#general\" style=\"text-decoration:none\"> <font
        class=\"s10\"> General Information </font> </a>....
}
Run SQL Performance Analyzer Using EM CLI

The following is an example on how to run SQL Performance Analytics (SPA) on EM CLI.

Run SPA Using EM CLI

To run SPA using EM CLI use the following command:
emcli migrate_db 
-file="/home/migrations/DB_SPA.JSON"
Where the file DB_SPA.JSON contains the following parameters:
 "analyzePerformance":
    {
        "useSPA": true,
        "createSTS": true, 
       “usePreferredSTS”:  false —optional
      “stsName”: “stsname01”  —optional
      “stsOwner”: “stsOwnedSchema”  —optional
        "stsOwner": "EXPUSER" --optional
    }

View SPA Report Using EM CLI

To view SPA report using EM CLI use the following command:
emcli migrate_db 
-file="/home/migrations/DB_SPA.JSON"
Where the file DB_SPA.JSON contains the following parameters:
{
"action": "SPA_REPORT",
"executionId": "<ID>",
"reportType": "html",
"reportLevel": "typical",
"reportSection": "ALL"
}

For more information on migrate_db and the accepted parameters required for migration, see: migrate_db in Oracle Enterprise Manager Command Line Interface .

Review Migration Status and Complete Migration

As part of the Analyze section of the Database Migration work cycle, you can review completed migrations to verify any errors or warnings. When satisfied with the migration results you can also mark migrations as Complete.

Follow these steps to review errors and warnings that might have come up during a migration using the Database Migration Workbench:
  1. Navigate to the Migration Activities Dashboard.
  2. Locate a recently completed Migration Activity you wish to review within the Migration Activity table. You can further filter migration activities by clicking on the different Migration Activities statuses:
    • All: Shows all migrations including scheduled and in progress migrations.
    • Completed: Shows all migrations that successfully completed. These will be marked as completed.
    • Problems: Shows migrations that completed with errors.
    • Warnings: Shows migrations that completed with warnings.
  3. On the Migration Activities Dashboard click on the left hand navigation menu specific to the reviewed activity, click View Details to review procedure details, analyze migration details and compare performance. Once you are satisfied that all warnings/errors reported are addressed return to the previous screen.
  4. On the Migration Activities Dashboard click on the left hand navigation menu specific to the reviewed activity, select Mark as Completed enter a comment in the pop up and click Yes. The Status of the Migration Activity will now show a blue check mark.
Review Migration Status Using REST API

As part of the Analyze section of the Database Migration work cycle, you can review completed migrations to verify any errors or warnings. To review migrations with REST API use the following:

Note:

POST can be used if user wants the OMS to pull the files from the Host. The Host needs to be discovered in Enterprise Manager.

Table 3-1 Review all Migration Activites

Feature Description
Request Method Post
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{
    "action": "LIST_ACTIVITIES", 
    "targetName": "<Source target/Destination database>",
    "targetType": "<Target type of the database>"
	"filters": {
		"activityStatus": "ALL | IN_PROGRESS | PROBLEM | SCHEDULED | COMPLETED"
		"timePeriod": "<startTimestamp>", "<endTimestamp>"
	}
}
Response
}  
    "statusCode": 200,
    "activities": [
       {
        "instanceGuid": "B07C1E3035C725F5E0538052F20A290C",
        "executionGuid": "B07C1E3035CA25F5E0538052F20A290C",
        "lastUpdated": 1601418294000,
        "sourceTargetType": "sample_pdb",
        "sourceTargetName": "CDBSample.example.com_DB2",
        "destTargetType": "oracle_cloud_atp",
        "destTargetName": "ATPD_USER",
        "createdBy": "SYSMAN",
        "instanceName": "Database Migration Tue Sep 29 2020 14:34:38 PDT",
        "elapsedTime": "4 minutes, 57 seconds",
        "status": {
           "code": 9,
           "displayName": "Completed (with errors)",
           "name": "COMPLETED_WITH_ERRORS"
        },
        "completed": 1601418294000,
        "sourceDisplayName": "CDBSample.example.com_DB2",
        "destDisplayName": "ATPD_USER",
        "hasChildActivities": true,
        "started": 1601417997000,
        "action": "MIGRATE"
      } ......
    ],
    "summary": {
    "all": 3,
    "in_progress": 0,
    "problem": 0,
    "scheduled": 0,
    "warning": 2,
    "completed": 1
  }
}

Table 3-2 Review a Specific Migration Activity

Feature Description
Request Method Post
URI
/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{
    "action": "VALIDATION_RESULTS", 
    "executionId": "<Execution_ID>"
}
Response The generated Response will be a summary of the specific Migration Activity giving you a status of Failed, Completed with Errors or Completed and the specific details for each step.
Review Migration Status Using EM CLI

As part of the Analyze section of the Database Migration work cycle, you can review completed migrations to verify any errors or warnings. To review migrations with EM CLI use the following:

To view the status of a migration in EMCLI use the following command:
emcli migrate_db 
-file="/home/migrations/Analyze_Migration.json"
Where the Analyze_Migration file contains the following JSON payload format to review a list of activities:
{
    "action": "LIST_ACTIVITIES", 
    "targetName": "<Source target/Destination database>",
    "targetType": "<Target type of the database>"
	"filters": {
		"activityStatus": "ALL | IN_PROGRESS | PROBLEM | SCHEDULED | COMPLETED"
		"timePeriod": "<startTimestamp>", "<endTimestamp>"
	}
}
Or where the Analyze_Migration file contains the following JSON payload format to review a specific activity:
{
    "action": "VALIDATION_RESULTS", 
    "executionId": "<Execution_ID>"
}

Clean Up Migration Files

As part of the Analyze section of the Database Migration work cycle, you can clean up dump files with ease in the Database Migration Workbench. The Cleanup operation will identify the exported dump files for the migration activity and delete them from the Dump Directory where they were exported and the Object Storage where they were uploaded. The cleanup operation can be used to remove dump files for completed or failed database migrations.

Clean Up can be used to clean up files and Data Pump master table after a failure or after success. We recommend you analyse the results of the migration before taking this action.

Note:

Cleanup will not rollback the destination database or drop the PDB (if a new PDB was created) at this time.

Clean Up Using the Database Migration Workbench

Follow these steps to cleanup database dump files after a migration with the Database Migration Workbench:
  1. To begin cleanup, navigate to the Migration Activities Dashboard.
  2. Locate the specific Migration Activity to be cleaned up within the Migration Activity table.
  3. Click on the left hand hamburger menu specific to the activity, select Cleanup and click on Yes.
Cleanup will now run, identifying the exported dump files for the selected Migration Activity and delete them from the export Dump Directory and the upload Object Storage.

Note:

The Cleanup activity will not remove, cancel, rollback or stop an ongoing migration.
Perform Migration Activity Cleanup Using REST API

After a migration has successfully completed it is recommended you run a Cleanup operation to free resources in your environment.

Feature Description
Request Method
POST
URI
https://hostname:port/em/websvcs/restful/db/migration
Header
Content-Type: application/json
Authorization: Basic
Payload
{
"action": "CLEANUP_ACTIVITY",
"executionId": "<Execution Id>"
}
Response
{
"statusCode": 200,
"status": "SUCCESS",
"message": "Successfully submitted the requested activity: Cleanup Database Migration",
"validations": {
"resultsList": [],
"result": false
},
"submissionName": "Cleanup Database Migration Tue Aug 10 2021 06:09:01 UTC",
"submissionBy": "SYSMAN",
"submissionId": "C92F546493DC1569E0538F0B4B6409E9",
"executionId": "C92F546493DF1569E0538F0B4B6409E9",
"restActivityUrl": "/em/websvcs/restful/db/migration/activities/C92F546493DF1569E0538F0B4B6409E9",
"uiActivityUrl": "/em/faces/core-jobs-procedureExecutionTracking?executionGUID=C92F546493DF1569E0538F0B4B6409E9"
}
Perform Migration Activity Cleanup Using EM CLI

After a migration has successfully completed it is recommended you run a Cleanup operation to free resources in your environment.

Cleanp Using EM CLI

To perform a cleanup after a migration with EM CLI use the following command:
emcli migrate_db 
-file="/home/migrations/DB_Migration.JSON"
Where the file DB_Migration.JSON contains the following parameters:
{
"action": "CLEANUP_ACTIVITY",
"executionId": "<Execution Id>"
}

For more information on migrate_db and the accepted parameters required for migration, see: migrate_db in Oracle Enterprise Manager Command Line Interface .

Troubleshoot the Migration Process

Database Migration Workbench reviews and informs at several steps during the process that all prerequisites, requirements, databases, and schema are met, available, and ready. Should an issue or error come up during the Database Migration Workbench process this section can help you troubleshoot your migration.

The following table is a list of the most common migration issues and potential solutions:
Issue Reason Category Workaround Database Version
Upload dump file step generates error because the agent is down. The agent may have run out of memory. Review the log files. EM Agent You can update the degree of parallelism within the failed step to a lower number and retry. Pending files will upload. All
Creation of Table Space fails at destination Autonomous Database Autonomous Databases do not allow Table Space creation with small Data Files ADB Create a tablespace at the destination ADB with a large data file and specify remap_tablespace. All versions
Creation of schema fails at destination ADB The user profile does not get created at the destination ADB, causing schema creation failure.

Data Pump does not export Profiles when not run in Full Mode.

ADB Pre-create profile at the ADB, or use remap_schema.
Example:
remap_schema=<op_source>:<op_target>
      
All versions
Export failed because of error: DETERMINE_FATAL_ERROR with ORA-20005: object statistics are locked (stattype = ALL) Tables used for statistic gathering are blocked. Data Pump Unlock the tables for statistics using the command: SQL> exec dbms_stats.unlock_table_stats('owner', 'tablename');

To get a list of tables that are locked you can execute: SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where STATTYPE_LOCKED = 'ALL'

All versions
Data remapped to the USERS table space might not get imported. USERS table space is missing at the destination database. On Premise Migration Create the USERS table space and retry. Alternative solution is to remap to an existing available table space that can replace USERS. All versions
While moving the data to a new Pluggable Database (PDB), the data remapped to USERS tables pace might not get imported. USERS table space failed to create in the new PDB. Migration into a new PDB. Remap to a SYSTEM table space with some caveats or select an existing PDB. All versions