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.

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.

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.

The following table lists the optional arguments for properties.

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>

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