Guideline for Migration Workbench Method Selection

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.

Transportable Tablespace (TTS)

Migration Workbench Architecture

The following is an architecture diagram example for a TTS multi-phase migration. TTS migration support multi-phase and single-phase migration.

For example: If you want to migrate huge-size database with minimum downtime, then a multi-phase migration with a full and incremental backup combination is performed. For a small database, you can go for single-phase Transportable Tablespace migration which will take a full backup and complete the migration process without taking any incremental backups. During the cutover phase TTS migration puts your tablespaces in a read-only mode, takes the final incremental backup with a metadata backup, completes the migration, and the cutover process.

About Pre and Post Scripts

You can run custom scripts before and after a database has been created or provisioned. These requests are additional commands that can run on the machine on which the database is originating or commands to perform additional activities and operations on the newly migrated database. Scripts can be Shell scripts or Perl scripts (SQL scripts cannot be called directly but can be called internally from Shell or Perl scripts).

Pre and Post scripts need to be uploaded as EM Software Library Entity (Directive) prior to their usage with database migration activities. With the Pre and Post scripts in Software Library, the whole process is centralized and automated with the script staged at a common place within the Software Library, the script is staged and cleaned up on the target host as part of the deployment execution itself.

Pre and Post Script Requirements

• Response File: The response file contains the service template and request specific information. Before the custom script is executed, the request procedure, the request procedure generates a response file (file with name-value pairs) containing the data from the service template as well as the request-specific data such as the SID that is auto computed, the hosts on which the database will be deployed, and so on.
• Driver Script: This is the key driver script that will be invoked by the request procedure. The driver script accepts only one argument which specifies the location of the response file.
• Other Scripts: Apart from the driver script, you can specify other perl or sql scripts. These scripts must be invoked from the driver script.
• Additional Variables: You can include a file containing additional variables that can used by the custom script as applicable.

Creating a Software Library Entity For Uploading The Script

1. Select Enterprise, then Provisioning and Patching and click on Software Library.
2. Select the directory where you want to create the Software Library entity.
3. Select Actions, then select Create Entity and Click Directives.
4. Provide all the basic details on the Directive screen.
5. Click Add under Command Line Arguments on the Configure screen.
6. The INPUT_FILE to be added as mentioned in the list below. Click OK.
   • Argument Prefix="
   • Argument Suffix="
   • Property Name=INPUT_FILE
7. Click Next.
8. Click Add in Specify Source and select the script files to upload the custom script on the Select File screen. Choose the driver script as the main file.
9. Click Next.
10. Click Save and Upload.
11. An entity is created successfully in the Software Library.

How to Get URN for SWLIB Entity

• To get URN for the SWLIB through EMCLI: $OMS_HOME/bin/emcli list_swlib_entities -show_entity_rev_id -name=""
• To get URN for the SWLIB through EM UI:
  1. Select Enterprise, then Provisioning and Patching and click on Software Library.
  2. Click View, select Columns and click on Internal ID.
  3. Go to the uploaded entity and copy the Internal ID for the uploaded entity, this is the URN.

Common Prerequistites for Migration Workbench

• For NEW installations of Enterprise Manager 13.5 Release Update 18 and onward the DOWNLOAD_DB_MIGRATION_PREREQS job that downloads the Database Migration Prerequisites is disabled. For existing Enterprise Manager customers the job will remain enabled for three months after the last Migration Workbench activity. Run the following REST API to enable:

  Feature	Description
  Request Method	Post
  URI	/em/websvcs/restful/db/migration
  Payload	{ "action": "ENABLE_DOWNLOAD_DB_MIGRATION_PREREQS", "jobName": "DOWNLOAD_DB_MIGRATION_PREREQS" }

• 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:	Resource 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

  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.
• For Oracle 11.2.0.4 databases migrating to a higher release version, DBCONTROL should be present at the source database and verify compatibility of the APEX version at the source database matches with that at the destination database.
  • To verify if the DBCONTROL utility is present on the database use the following query:

    select count(*) as object_count from dba_segments where owner i n
    ('SYS','SYSMAN','SYSTEM') and tablespace_name not in
    ('SYSTEM','SYS','SYSMAN', 'UNDO','SYSAUX') and segment_type not in ('INDEX')
    and segment_type not like ('% UNDO%')

  • Migrate any user objects reside in SYS, SYSMAN, SYSTEM schema separately outside of the Migration Workbench. For databases not 11.2.0.4 database, you can skip this step.
  • To check for APEX certifications MyOracle Support’s Certification Tab
    1. Go to https://support.oracle.com.
    2. Click Certifications.
    3. Enter Oracle Application Express as the Product.
    4. Select APEX version from the Release List.

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

View a SPA Report

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 Performance, this will open the SPA Report.
4. In the SPA report you can verify the following:
   • Compare elapsed time by workload impact, allowing you to review the overall time, improved, regressed, missing and new SQL statements.
   • Within the Breakdown you can review elapsed time, SQL statements by performance, SQL statements by plan change, and SQL statements by problems.
   • Additionaly the report shows a listing of SQL statements by workload impacts
   .

View a Migration Analysis

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 (data pump only): Shows the amount of successfully and unsuccessfully exported database objects.
   • Import (data pump only): 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.

Clean Up Using 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.