Using the Cloud Premigration Advisor Tool

6 Using the Cloud Premigration Advisor Tool

The Cloud Premigration Advisor Tool (CPAT) helps you evaluate an existing Oracle database for compatibility with Oracle Autonomous Database before migration. CPAT identifies potential user actions, prioritizes their importance, and suggests resolutions. Oracle recommends using CPAT to accelerate your migration to Oracle Cloud.

For more information about CPAT, see the document CPAT Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1) in My Oracle Support.

You can use SQLcl to run CPAT by using the MIGRATEADVISOR command.

Topics:

6.1 Overview

Using the MIGRATEADVISOR command in SQLcl, you can:

Analyze an Oracle database to a generic autonomous type or specific instance, and create reports identifying actions recommended for successful migration to Oracle Cloud. A report can be generated in HTML, text or JSON format.
Optionally, generate a target properties file that will provide more details specific to an autonomous instance.

The following image shows a CPAT report in HTML format.

Description of the illustration cpatreport.png

CPAT Concepts

The source analysis in CPAT is done generically for different cloud types:

ATPS (Autonomous Transaction Processing on Shared Infrastructure)
ATPD (Autonomous Transaction Processing on Dedicated Infrastructure)
ADWS (Autonomous Data Warehouse on Shared Infrastructure)
ADWD (Autonomous Data Warehouse on Dedicated Infrastructure)
Default (Oracle Database)

The source analysis consists of a series of tests that are executed as SQL Statements. The scope of the tests fall into three categories:

SCHEMA: Checks that are only applicable to data within schemas. For example, tables are checked for usage of deprecated and desupported features.
INSTANCE: Checks that are only applicable to the database instance as a whole. For example, certain parameters and values in the V$PARAMETER table are checked.
UNIVERSAL: Checks that are always run.

The source analysis can run in FULL mode, SCHEMA MODE or FULL SCHEMA mode.

FULL: (Default) Executes all applicable checks in all appropriate schemas. SCHEMA, INSTANCE, and UNIVERSAL scope checks are executed.
SCHEMA: Executes all applicable checks for schemas specified on the command line and UNIVERSAL checks. INSTANCE scope checks are not executed.
SCHEMA FULL: Executes all applicable checks for schemas specified on the command line and UNIVERSAL and INSTANCE checks.

6.2 Prerequisites

The prerequisites for using the migrateadvisor command are:

Oracle Database 11g Release 2 and later versions are supported.
The PROPERTIES command is available only if the connected user is granted the SELECT ANY DICTIONARY privilege.
The ADVISE command is available only if the connected user is granted:
- the SELECT ANY DICTIONARY privilege
- the SELECT privilege to access SYSTEM.DUM$COLUMNS and SYSTEM.DUM$DATABASE
  
  Note:
  You need access to the DUM$ tables only if the source and target character sets indicate that Oracle Database Migration Assistant for Unicode (DMU) is needed.

6.3 About the MIGRATEADVISOR Command

You can run the CPAT command in SQLcl using ma or migrateadvisor.

This command inclues the following sub-commands:

Properties: Generates a target properties file for Oracle or Autonomous Database that provides instance-specific analysis.
Advise: Analyzes the database and produce reports containing information to assist in a successful migration.
Info: Shows version information for CPAT.

Syntax

migrateadvisor help [examples|syntax] | 
advise {{-targettype,-t} {ATPS|ATPD|ADWS|ADWD|DEFAULT} } [<-optional-argument>,...] | 
properties [<optional-argument>,...] |
info

The following table lists the required and optional arguments for advise.

Argument	Description	Default
Required
-targettype,-t	`(ATPS\|ATPD\|ADWS\|ADWD\|DEFAULT}` The type of target database to analyze for migration.	None
Optional
-analysisprops,-a	<propertyFile> The path and name of a properties file. Note: The full path is optional. If the full path is not specified, `cd <path>` is used as the root. If `cd <path>` is not specified, then the launch path is used as the root. A properties file that was created using the properties sub-command while connected to the target database.	None
-excludeschemas,-x	<schema>[,...] The schema or comma-separated-value list of schemas to exclude from analysis.	None
-full, -f	`[TRUE:FALSE]` Execute the full set of tests.	FALSE when -schemas is not specified, otherwise the default is TRUE
-maxrelevantobjects,-m	<maxRelevantObjects> The maximum number of "relevant objects" to be included in all reports. This option overrides -maxtextdatarows for TEXT reports.	Include all objects
-maxtextdatarows,-n	<maxTextDataRows> Relevant object rows to be included in text reports (does not apply to JSON reports).	-maxrelevantobjects if specified, otherwise all relevant objects are included.
-migrationmethod,-mm	`{DATAPUMP\|DATAPUMP_DBLINK\|GOLDENGATE}[,...]` A comma-separated-value list of methods or tooling that will be used to do the migration. Example: DATAPUMP, GOLDENGATE	DATAPUMP
-outdir,-o	<directory> Identifies location for log and report files.	Current SQLcl directory
-outfileprefix,-p	<outFilePrefix> A prefix to the standard base file name of "premigration_advisor_report".	No prefix
-reportformat,-r	`{HTML\|JSON\|TEXT}[,...]` Specify one or more <reportFormats> where <reportFormat> is HTML, JSON, or TEXT.	HTML
-schemas,-s	<schema>[,...] The schema or comma separated value list of schemas to analyze. Example: schema1, schema2	All schemas
-sqltext,-q	Include SQL query used for checks in TEXT reports.	Do not include sql text in TEXT reports. Ignored for JSON reports.
-verbose,-ve	`[TRUE\|FALSE]` TRUE shows additional diagnostic output.	FALSE

The following table lists the optional arguments for properties.

Argument	Description	Default
-outdir,-o	<directory> Identifies location for log and report files.	Current SQLcl directory
-outfileprefix,-p	<outFilePrefix> A prefix to the standard base file name of "premigration_advisor_report".	No prefix

Argument

Description

Default

-outdir,-o

Identifies location for log and report files.

Current SQLcl directory

-outfileprefix,-p

A prefix to the standard base file name of "premigration_advisor_report".

No prefix

6.4 Examples

The following examples illustrate how to use the MIGRATEADVISOR command.

Example 1

Generate the target properties file for Autonomous Database.

-- Set default directory
SQL> cd <directory>
-- Connect to target
SQL> set cloudconfig <config-file>
SQL> connect <autonomous-connect-string>
-- Generate the properties file
SQL> migrateadvisor properties -outfileprefix cloud

Example 2

Create the HTML Advisor report for a single schema using target properties.

-- Set default directory
SQL> cd <directory>
-- Connect to source
SQL> connect <database-connect-string>
-- Generate the report
SQL> migrateadvisor advise -schemas hr -outfileprefix hr -targettype adws -analysisprops <propertyFile>

Example 3

Create the HTML Advisor report for the full database using target properties.

-- Set default directory
SQL> cd <directory>
-- Connect to source
SQL> connect <database-connect-string>
-- Generate the report
SQL> migrateadvisor advise -outfileprefix fulldb -targettype adws -analysisprops <propertyFile>

Example 4

Create the JSON Advisor report excluding schemas.

-- Set default directory
SQL> cd <directory>
-- Connect to source
SQL> connect <database-connect-string>
-- Generate the report
SQL> migrateadvisor advise -excludeschemas hr,sales -outfileprefix xschemas -targettype adws -reportformat json

Example 5

Create the HTML Advisor report for multiple schemas including instance tests.

-- Set default directory
SQL> cd <directory>
-- Connect to source
SQL> connect <database-connect-string>
-- Generate the report
SQL> migrateadvisor advise -schemas hr,sales -full -outfileprefix fullmulti -targettype adws -analysisprops <propertyFile>

Example 6

Create HTML Advisor report for a single schema for migration to an Oracle database with target properties.

-- Set default directory
SQL> cd <directory>
-- Connect to target
SQL> connect <database-connect-string>
-- Generate the properties file
SQL> migrateadvisor -gettargetproperties -outfileprefix cloud
-- Connect to source
SQL> connect <database-connect-string>
-- Generate the report
SQL> migrateadvisor advise -schemas hr -outfileprefix ora -targettype adws -analysisprops <propertyFile>

6.5 Tips and Troubleshooting

Some points to note are:

If the source is an Autonomous Database, running the ADVISE command is not a supported use of CPAT and the result may be unpredictable.

The PROPERTIES and ADVISE commands require that the connected user has the SELECT ANY DICTIONARY privilege.

If you do not have the SELECT ANY DICTIONARY privilege, you see the following error:

SQL> grant connect, resource, unlimited tablespace to jdoe identified by jdoe;
Grant succeeded.

SQL> connect jdoe/jdoe
Connected.

SQL> migrateadvisor advise -schemas hr -outfileprefix pre -targettype adws -reportformat json

CPAT-1004: SQL error while initializing premigration application.
ORA-00942: table or view does not exist

–-Ensure the JDBC connection information is correct. See the log file for more details.

Additional Information: 255: Internal error - please contact support.
**Exception** : /Users/xyz/pre_premigration_advisor_report.json

Log file contains:
Caused by: Error : 942, Position : 20, Sql = SELECT VERSION FROM V$INSTANCE, 
OriginalSql = SELECT VERSION FROM V$INSTANCE, 
Error Msg = ORA-00942: table or view does not exist