Options for Running CPAT – Cloud Premigration Advisor Tool – Part 2

If you missed the part 1, you can find it here.

The CPAT can be executed directly on the database server or from another machine using a remote connection. CPAT connects to your database using JDBC and accepts standard Oracle JDBC format connection strings. This means that both the “thick” and “thin” Oracle JDBC drivers can be used.

  • The parameters “--targetcloud” and “--connectstring” are typically required.
  • If you are using Operating System authentication or the SYS user then “--sysdba” would also be required.
  • The “--sysdba” parameter must be used together with the “--connectstring” parameter whenever the connection to the database is made with the SYS user, regardless of whether it is a local or remote connection. For remote connections, the “--username” parameter must be included along with “--connectstring” and “--sysdba” (assuming the connection is with the SYS user).
  • Oracle JDBC connect strings are URL based and always begin with jdbc:oracle:thin:@ or jdbc:oracle:oci:@.
  • You can use whatever JDBC connection string works best for your situation (connectivity/security): thin or thick (OCI).
  • Use whatever authentication/security you’d like:
    • Username/password
    • Wallets
    • Kerberos
    • Anything supported by OJDBC

Example of local connection with SYSDBA (remember to set the ORACLE_HOME):

premigration.sh --connectstring jdbc:oracle:oci:@ --sysdba --targetcloud ATPS

Example of local connection, specifying the name of the PDB that should be evaluated:

premigration.sh --connectstring jdbc:oracle:oci:@ --sysdba --pdbname ORCLPDB1 --targetcloud ATPD
  • Using “--pdbname” is only required when the connection string is for CDB$ROOT and you do not wish to analyze all the PDBs in the CDB.
  • Supplying multiple PDB names with “--pdbname” will instruct CPAT to analyze just those named PDBs in the CDB.

Example of remote connection with SYSDBA specifying the ORACLE_SID:

premigration.sh --connectstring jdbc:oracle:thin:@db01:1521:ORCLCDB --sysdba --username SYS --targetcloud ATPD

Example of remote connection with SYSDBA specifying the SERVICE_NAME:

premigration.sh --connectstring jdbc:oracle:thin:@db01:1521/orclpdb1 --sysdba --username SYS --targetcloud ATPD

Invoking CPAT Using a Parameter File

As an alternative to passing CPAT parameters via the command line, CPAT will also accept parameters via a configuration file.

premigration.sh --parfile cpatParams.cfg

Example configuration file:

connectstring=jdbc:oracle:thin:@example.com:1521/ORCLCDB
pdbname=ORCLPDB1
username=SYSTEM
targetcloud=ATPS
outdir=./cpatOutput
reportformat=TEXT,JSON,HTML
schemas=USER1,USER2,USER3

CPAT Basic Usage

1. Pick the target cloud “flavor” using the “--targetcloud” command line switch:

  • Typically, it is one of ATPD, ADWD, ATPS, or ADWS.
  • May also use DEFAULT if your goal is to migrate to ExaCS, ExaCC or another DB offering that does not have the autonomous restrictions/lockdowns.

2. Specify the migration tooling expected to be employed in the (logical) migration using “--migrationmethod“. The default is DATAPUMP but may also be GOLDENGATE and/or DATAPUMP_DBLINK.

3. Use the parameter “--reportformat“

  • The “--reportformat” switch accepts one or more report formats separated by spaces. Allowed values for the “--reportformat” switch are “text”, “html”, and “json”.

4. For best results, pull information from the target instance using “--gettargetprops“. It allows a more complete analysis (e.g., time zone version or character set on target).

One important point is the Full vs Schema “Mode”. If you are planning to migrate only a few schemas, please use the Schema mode. I will talk more about it in a later post.

You can get the parameter list using the option “--help“:

premigration.sh --help

This list should provide sufficient clarification, but if you have any further questions, you can refer to MOS Doc ID 2758371.1 for additional guidance.

What privileges does CPAT require?

  • Most of the queries executed by CPAT are against the data dictionary therefore CPAT requires the SELECT ANY DICTIONARY privilege.
  • You may also want to connect as a schema that has been granted SELECT on SYSTEM.DUM$COLUMNS and SYSTEM.DUM$DATABASE.
  • Access to the DUM$ tables is needed only if the source and target character sets indicate that Oracle Database Migration Assistant for Unicode (DMU) is required.

Generate “fixup” scripts

The parameter “--genfixups” can generate “fixup” scripts for those checks that do not pass and can be resolved by a fixup.  Fixup scripts are generated in folders below the output directory.

Stay tuned for the next post where I will guide you with some examples of how to run the CPAT.