Cloud Premigration Advisor Tool

19 Cloud Premigration Advisor Tool

To evaluate the compatibility of the source database before you migrate to an Oracle Cloud database, use the Cloud Premigration Advisor Tool (CPAT).

What is the Cloud Premigration Advisor Tool
The Cloud Premigration Advisor Tool (CPAT) is a migration assistant that analyzes database metadata in an Oracle Database, and provides information to assist you to move data to Oracle Autonomous Database in Oracle Cloud.
Prerequisites for Using the Cloud Premigration Advisor Tool
Ensure that you have the required Java environment, user permissions and security set up to run the Cloud Premigration Advisor Tool (CPAT).
Downloading and Configuring Cloud Premigration Advisor Tool
Download the most recent update to the Cloud Premigration Advisor Tool (CPAT), extract it to a directory, and set up environment variables.
Getting Started with the Cloud Premigration Advisor Tool (CPAT)
After you download Oracle SQLcl or CPAT, ensure that your source database has the required Java home, set up environment variables, and decide what kinds of checks you want to perform.
Connection Strings for Cloud Premigration Advisor Tool
The Cloud Premigration Advisor Tool (CPAT) accepts standard Oracle JDBC format connection strings.
Required Command-Line Strings for Cloud Premigration Advisor Tool
Depending on your use case, some strings are required to run the Cloud Premigration Advisor Tool (CPAT).
FULL Mode and SCHEMA Mode
The Cloud Premigration Advisor Tool (CPAT) can run against the entire instance, or against a schema.
Interpreting Cloud Premigration Advisor Tool (CPAT) Report Data
Reports generated by CPAT contain summary information, and details for each check that is performed successfully.
Command-Line Syntax and Properties
Use the Cloud Premigration Advisor Tool (CPAT) properties to specify the checks and other operations you want to perform in CPAT command-line syntax.
Premigration Advisor Tool Log File Structure
The Premigration Advisor Tool produces a log file structure that includes job status and configuration files.
List of Checks Performed By the Premigration Advisor Tool
Review information about the checks you find in a Premigration Advisor Tool report.
Best Practices for Using the Premigration Advisor Tool
These Cloud Premigration Advisor Tool (CPAT) tips can help you use CPAT more effectively.

Parent topic: Other Utilities

19.1 Prerequisites for Using the Cloud Premigration Advisor Tool

Ensure that you have the required Java environment, user permissions and security set up to run the Cloud Premigration Advisor Tool (CPAT).

Java Runtime Environment (JRE) Requirement

You must have Java 7 or later installed on the server or client where you run CPAT. Oracle recommends that you use Java 8 Java Runtime Environment (JRE).

CPAT looks for a JRE using the environment variables JAVA_HOME and ORACLE_HOME. If your source Oracle Database is later than Oracle 12c Release 1 (12.1.0.2), then a version of the Java JRE that can run CPAT is available in the Oracle home. If you are migrating from an earlier release of Oracle Database, or if you want to specify to use a later Java release Oracle home, then ensure that the environment variable is set to an appropriate Java home for CPAT.

If you use a thick Oracle Call Interface-based JDBC connect string, then CPAT currently expects the following environment variables to be set: ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH.

Note:

Oracle recommends that you set ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH by using the oraenv script available within the Oracle Database home.

More details on connect strings and associated environment variables can be found in the Advanced Usage Notes section titled Connection Strings.

User Privileges on the Source Database

When you specify a user to connect to the source database for checks, and provide that user with the CPAT --username property, the user name that you specify must be granted the SELECT ANY DICTIONARY privilege, and be 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.

Note:

Installing and running CPAT does not modify the Oracle Database. CPAT creates no users or packages, and CPAT does not grant any roles or privileges. The CPAT access to the database is

READ
                    ONLY

. It only checks database metadata; no application or business data is checked.

Security Configuration

Use the --outdir property to set the output location of CPAT logs and uses a secure location on your server or client.
Set the user file creation mode mask (umask) on Linux and Unix systems so that the default values for the r|w|x privileges on CPAT scripts are restricted to authorized users.

Parent topic: Cloud Premigration Advisor Tool

19.2 Downloading and Configuring Cloud Premigration Advisor Tool

Download the most recent update to the Cloud Premigration Advisor Tool (CPAT), extract it to a directory, and set up environment variables.

To run CPAT, Oracle recommends using Oracle SQLcl and the SQLcl command - MIGRATEADVSOR. You can download SQLcl from the following URL:

https://www.oracle.com/database/sqldeveloper/.

Read the My Oracle Support note about CPAT, and download and extract the CPAT patch from the following URL:

Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1).

You require an Oracle account to log in to My Oracle Support.
Ensure that you have Java installed, and the JAVA_HOME user environment variable and other environment variables are set.

After you download and unzip CPAT, ensure that you have an appropriate Java Runtime Environment (JRE) installed on the machine where CPAT is run. The minimum JRE version required for CPAT is Java 7.

CPAT searches for a JRE home using the environment variables JAVA_HOME and ORACLE_HOME. If the version of Java in ORACLE_HOME is Java 6 or an earlier release, which should only be the case with an Oracle Database 12g Release 1 or earlier home, then set JAVA_HOME to point to a Java 7 (or higher) JRE. To upgrade Java in an ORACLE_HOME, visit https://support.oracle.com and search for Document 2366614.1 (patch id 25803774) for Oracle Database 11g databases, or Document 2495017.1 (patch id 27301652) for Oracle Database 12.1 databases.

To set JAVA_HOME on a Microsoft Windows system:
1. Right click My Computer and select Properties.
2. On the Advanced tab, select Environment Variables, and then edit JAVA_HOME to point to the location of the of the Java Runtime Environment (JRE).
  For example:
```
C:\Program Files\Java\jdk1.8\jre
```
  JRE is part of the Java Development Kit (JDK), but you can download it separately.
To set JAVA_HOME on a Linux or Unix system (Korn or Bash shell):
```
export JAVA_HOME=jdk-install-dir
export PATH=$JAVA_HOME/bin:$PATH
```
Note:
On Linux and Unix, systems, Oracle recommends that you set the ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH variables using the oraenv script that comes with Oracle Database.

If you want to use CPAT without defining ORACLE_HOME, and you don't need to use the Oracle Call interface JDBC connection string, then ensure that JAVA_HOME is set to a Java 7 (or higher) JRE. When possible, Oracle recommends that you use a Java 8 or higher JRE. Among other benefits, the functionality included in OJDBC8 jars simplifies wallet-based connections such as those used when connecting to Oracle Cloud instances.

Related Topics

Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1)

Parent topic: Cloud Premigration Advisor Tool

19.3 Getting Started with the Cloud Premigration Advisor Tool (CPAT)

After you download Oracle SQLcl or CPAT, ensure that your source database has the required Java home, set up environment variables, and decide what kinds of checks you want to perform.

The workflow for using the Cloud Premigration Advisor tool (CPAT) is as follows:

Determine the type of Cloud database to which you want to migrate.
Run CPAT to generate a CPAT properties file using the gettargetprops. This switch gathers the properties of the target database, if one has been created. The target properties are used when analyzing the source database to focus, and limits the checks that are run to those required for the target database.
Run CPAT with the options required for your migration scenario. You can run CPAT to test different migration scenarios. If you do run CPAT repeatedly, then to distinguish between the tests, Oracle recommends using the --outfileprefix and --outdir switches to keep the outputs organized, and to keep reports from being overwritten.

The CPAT patch distribution kit contains premigration.sh for running CPAT on Linux and Unix platforms, and premigration.cmd for running CPAT on Microsoft Windows platforms. CPAT can be run from any host with network access to the database instance that you want to analyze.

Note:

Running the premigration script on the server doesn't modify Oracle Database. CPAT itself creates no users or packages, and requires granting no roles or privileges. CPAT treats the database as READ ONLY. It only checks database metadata; no application or business data is checked.

In this example, premigration.sh is used (use premigration.cmd on Microsoft Windows systems)

Example 19-1 Generating a CPAT Properties File

This example checks whether your source database is ready to migrate to an Oracle Autonomous Database Shared for Transaction Processing and Mixed Workloads (ATP-S), you generate a properties file for the requirements:

premigration.sh --connectstring \
'jdbc:oracle:thin:@db_tp_tunnel?TNS_ADMIN=/path/to/wallets/Wallet1' --username ADMIN \
--gettargetprops --outdir migration

The output of that command is as follows:

Enter password for ADMIN user: Cloud Premigration Advisor Tool Version 22.10.0 Cloud Premigration Advisor Tool generated properties file location: /home/oracle/migration/configprops/atps_premigration_advisor_analysis.properties

Note:

When CPAT is run with the --username switch, the Oracle user name you specify must have the SELECT ANY DICTIONARY privilege, and must be 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.

Parent topic: Cloud Premigration Advisor Tool

19.4 Connection Strings for Cloud Premigration Advisor Tool

The Cloud Premigration Advisor Tool (CPAT) accepts standard Oracle JDBC format connection strings.

Using standard Oracle JDBC format connection strings means that you can use either the thick" or the "thin" Oracle JDBC driver for connections.

Table 19-1 Example JDBC Connection Strings

Connection Description	Connection String	Notes
Thin client	`jdbc:oracle:thin:@host:port:sid`	Replace the variables `host`, `port` and `sid` with the host the connection port, and the system identifier for your source.
Thin client with PDB Service	jdbc:oracle:thin:@host:port/pdb-service-name	Replace the variables `host`, `port` and `pdb-service-name` with the host the connection port, and the PDB service name for your source.
Thin with AWS RDS	`jdbc:oracle:thin:@database-1.xxx.us-east-1.rds.amazonaws.com:port:sid`	Consult the Amazon Web Services Relational Database (AWS RDS) documentation for instructions on finding your database's endpoint and port details.
Operating system authentication	`jdbc:oracle:oci:@`	The CPAT command line must also include the property `--sysdba`
Operating system authentication with PDB	`jdbc:oracle:oci:@`	The CPAT command line must also include the properties `--sysdba` and `--pdbname pdb-name`, where `pdb-name` is the name of the PDB.
Wallet-based with Java 8 JRE	`jdbc:oracle:thin:@service-name?TNS_ADMIN=path-to-wallet`	The `TNS_ADMIN` connection property specifies the following, represented by `path-to-wallet`: The location of `tnsnames.ora`. The location of Oracle Wallet (`ewallet.sso`, `ewallet.p12`) or Java KeyStore (JKS) files (`truststore.jks`, `keystore.jks`). The location of `ojdbc.properties`. This file contains the connection properties required to use Oracle Wallets or Java KeyStore (JKS). For more information about using a keystore, see the Oracle Autonomous Database documentation.

Additional Connection String Information

Using the --pdbname property is only required when the connection string is for CDB$ROOT.

If you use keystore connection strings such as jdbc:oracle:thin:@service-name?TNS_ADMIN=path-to-wallet, then JDBC requires that one of the following is true:

An ojdbc.properties file is located in the Wallet directory, and it contains oracle.net.wallet_location property with a value such as oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
The JAVA_TOOL_OPTIONS environment variable is set with the appropriate values, such as the following:

export JAVA_TOOLS_OPTIONS='-Doracle.net.tns_admin=path-to-wallet-dir -Doracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=path-to-wallet-dir)))'

Related Topics

Parent topic: Cloud Premigration Advisor Tool

19.5 Required Command-Line Strings for Cloud Premigration Advisor Tool

Depending on your use case, some strings are required to run the Cloud Premigration Advisor Tool (CPAT).

When using CPAT to connect to a database for source analysis, there are three required properties in the command string: One that specifies the cloud target (targetcloud), one that specifies the connection string (connectstring), and a user authentication string, provided either with the sysdba or username property.

The first two command properties must always be

--targetcloud type (or -t type), where type is the Oracle Cloud target type
--connectstring jdbc-connect-string, or -c jdbc-connect-string, where jdbc-connect-string is the JDBC connection string you use to connect to the migration source Oracle Database.

The other required property provides user credentials, and so it depends on what user credentials you use to start the analysis:

For operating system authentication by user account, or authorization on the local system by using the SYS user, you use--sysdba, or -d. This starts CPAT by connecting to the source database with AS SYSDBA. This authentication option is also required if you connect as a user that has been granted SYSDBA but not the other privileges required by CPAT.
For authentication by user account, where you are not using a wallet or operating system authentication, use --username name, or -u name, where name is the user account name you use to log in to the source system. As it runs, CPAT prompts you for the password for that user. The user name that you provide must be a user account granted SYSDBA and ADMIN privileges.

If you authenticate CPAT with the username property, then the Oracle user name that you specify must have the SELECT ANY DICTIONARY privilege, and must be 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.

Parent topic: Cloud Premigration Advisor Tool

19.6 FULL Mode and SCHEMA Mode

The Cloud Premigration Advisor Tool (CPAT) can run against the entire instance, or against a schema.

FULL Mode

FULL mode is the default mode. In this mode, CPAT runs any check relevant to the migration methods and the Cloud target types you choose, and analyzes data in all schemas that are not maintained by Oracle. In FULL mode, SCHEMA, INSTANCE, and UNIVERSAL scope checks are run.

Note:

Even in FULL mode, CPAT by default excludes checking data in schemas known to be maintained by Oracle. The use of the --excludeschemas property does not change CPAT's default FULL mode.

SCHEMA Mode

SCHEMA mode is set with the --schemas property. When --schemas is set, and --full is not also specified, then CPAT runs in SCHEMA mode. In SCHEMA mode, SCHEMA and UNIVERSAL scope checks are run. INSTANCE scope checks are not run.

Controlling CPAT Modes

The CPAT mode is controlled by the use of two options properties:

The schemas property (--schemas 'schemaname' ['schemaname''schemaname'], runs checks against the schemas that you list, in a space-delimited schema name list of one or more schema names, where the names are specified within single straight quotes. In schema mode, SCHEMA and UNIVERSAL scope checks are run. INSTANCE scope checks are not run.
The Full property (--full) runs checks against the entire source database instance.

If you do not specify a value for the --schemas property, then the default is FULL mode.

If you specify --schemas on the command line, then CPAT runs in SCHEMA mode unless you also specify --full in the command line. If both properties are used, then SCHEMA, INSTANCE, and UNIVERSAL scope checks are run, but only on the list of schemas in the -schemas list.

If a schema name is lowercase, mixed case, or uses special characters, then use double quotation marks as well as single quotation marks to designate the schema name. For example:

premigration.sh --schemas 'PARdUS' '"ComEDIT"' '"faciem.$meam"' --targetcloud ATPS --connectstring jdbc-connect-string"

Parent topic: Cloud Premigration Advisor Tool

19.7 Interpreting Cloud Premigration Advisor Tool (CPAT) Report Data

Reports generated by CPAT contain summary information, and details for each check that is performed successfully.

Each check includes the following information in the Premigration Advisor report:

Description: This field describes what the check is looking for, or why the check is being performed.
Impact: This field describes the consequences of a result other than Passing.
Action: This check describes what, if anything, you should do before migration to correct issues, if the check result is not Passing.

Each check CPAT runs is given a report status of Passing, Review Suggested, Review Required, or Action Required.

The overall result of the CPAT report will be the most severe result of all checks performed. For example, if 30 checks have the status Passing, one check has a Review Required status, then the overall result will be Review Required.

The current definitions of each of the CPAT check results are as follows:

Table 19-2 Premigration Advisor Tool (CPAT) Check Result Definitions

Check	Definition
Passing	Indicates that the migration should succeed, and that there should be no difference in behavior of applications.
Review Suggested	Indicates that migration should succeed, and that applications likely will have no functional difference. However, database administrators should evaluate each check with this status to look for potential issues before migration.
Review Required	Indicates that migration may succeed (at least in part), but that either you cannot expect everything to work exactly as it did in the source database, or that a database administrator must complete additional work after migration to bring the target instance into alignment with the source database.
Action Required	Indicates something that likely would cause the migration to be unsuccessful. Checks with this result typically must be resolved before attempting migration.
Failed	The Cloud Premigration Advisor was unable to complete its analysis. Please contact Oracle Support Services.

Note: A CPAT result of Action Required does not necessarily mean that, for instance, Oracle Data Pump import will terminate prematurely while importing the data. It means that there will likely be errors during import which can indicate not all data has been migrated. It is imperative that an administrator familiar with both the database and the applications supported by the database examine the results of any checks that are not Passing.

Why are Checks sometimes marked as "skipped"

Checks marked in the Premigration Advisor report as Skipped should have completed during the CPAT analysis for properties provided in the CPAT command (for example, --targetcloud --migrationmethod, or other report value), but were not run in this particular Premigration Advisor report.

Either one of these two cases are the cause of a "Skipped" status:

The check should be run but it is impossible to run at the time the report is generated, either due to the current contents or configuration of the source database. In this case, the check result will be Review Suggested or more severe.
The check does not need to be completed at the time of the report, due to the current contents or configuration of the source database. The check result in this case will be Passing.

Parent topic: Cloud Premigration Advisor Tool

19.8 Best Practices for Using the Premigration Advisor Tool

These Cloud Premigration Advisor Tool (CPAT) tips can help you use CPAT more effectively.

Generate Properties File on the Target Database Instance
Oracle recommends that you generate a Premigration Advisor Tool (CPAT) properties file on the target database instance.
Focus the CPAT Analysis
Oracle recommends that you focus the Premigration Advisor Tool (CPAT) analysis to restrict what schemas CPAT will examine.
Reduce the Amount of Data in Reports
Some Cloud Premigration Advisor tool checks can return thousands of objects with the same concern. Here's how you can reduce the report size.
Generate the JSON Report and Save Logs
Even if you only plan to use the text report, Oracle suggests you also generate a JSON output file with the Cloud Premigration Advisor tool (CPAT), and save the log files for diagnosis.
Use Output Prefixes to Record Different Migration Scenarios
To keep track of reports for different migration options, use the --outfileprefix and --outdir properties on the CPAT command line.

Parent topic: Cloud Premigration Advisor Tool

19.8.1 Generate Properties File on the Target Database Instance

Oracle recommends that you generate a Premigration Advisor Tool (CPAT) properties file on the target database instance.

To perform the most complete and targeted analysis of the source database instance, certain properties of the target database instance are required. For this reason, you should generate your CPAT properties file on the database instance that you want to migrate. To perform this function, the --gettargetprops property is intended to be used with the other connection-related properties.

In the following example, the CPAT script is run by the user ADMIN on the target database instance:

./premigration.sh --gettargetprops -username ADMIN --connectstring 'jdbc:oracle:thin:@service-name?TNS_ADMIN=path-to-wallet'

The command generates a properties file, premigration_advisor_analysis.properties, which you can use to analyze a source instance.

If necessary, you can copy the properties file generated on the target to the host where the source database analysis will be performed, and provide the file to CPAT using the --analysisprops property.

For example:

./premigration.sh --connectstring jdbc:oracle:oci:@ --targetcloud ATPD --sysdba --analysisprops premigration_advisor_analysis.properties

If you know that you (or Oracle Zero Downtime Migration (ZDM) or Oracle Database Migration Service (DMS) will be mapping (or precreating) all needed tablespaces, then append the property MigrationMethodProp.ALL_METHODS.TABLESPACE_MAPPING=ALL to the properties file you provide to CPAT. This property setting causes CPAT to PASS most (if not all) of its tablespace-related checks. However, if you choose this option, then be aware that there can still be migration issues related to quotas with tablespace mapping.

Parent topic: Best Practices for Using the Premigration Advisor Tool

19.8.2 Focus the CPAT Analysis

Oracle recommends that you focus the Premigration Advisor Tool (CPAT) analysis to restrict what schemas CPAT will examine.

Consider using the --schema switch property to restrict what schemas you want CPAT to examine during its analysis. When you start CPAT using --schemas list, where list is a space-delimited list of schemas, CPAT performs checks only on those schemas. Without the --schemas switch, CPAT will analyze all schemas in the source instance (excluding Oracle-maintained schemas), which can result in problems being found in schemas that you do not intend to migrate. Using the --schemas property to restrict scope can be particularly useful if the source instance is hosting multiple applications, each of which could potentially be migrated to different Oracle Autonomous Database instances.

In the following example, the CPAT script is run by the user ADMIN on the target database instance to perform analysis on the schemas schema1 and schema2:

./premigration.sh -username SYSTEM --connectstring 'jdbc:oracle:thin:@service-name?TNS_ADMIN=path-to-wallet' --schemas schema1 schema2

The --schemas switch property provides a space-separated list of schemas (schema1 and schema2) to CPAT, so that the checks it performs are restricted only to those two schemas.

Parent topic: Best Practices for Using the Premigration Advisor Tool

19.8.3 Reduce the Amount of Data in Reports

Some Cloud Premigration Advisor tool checks can return thousands of objects with the same concern. Here's how you can reduce the report size.

Depending on the checks you run, some CPAT checks can return results for the same issue in multiple objects in the text report. To reduce the number of results, you can use the --maxtextdatarows n function, where n is an integer that specifies the number of rows that you want to view.

The --maxrelevantobjects n property performs the same function for reports, but limiting the size of JSON reports is typically not necessary.

In the following example, the CPAT script is run by the user SYSTEM on the target database instance, with the output set to return a maximum of 10 rows of text file data:

./premigration.sh --username SYSTEM --connectstring 'jdbc:oracle:thin:@service-name?TNS_ADMIN=path-to-wallet --maxtextdatarows 10"

Parent topic: Best Practices for Using the Premigration Advisor Tool

19.8.4 Generate the JSON Report and Save Logs

Even if you only plan to use the text report, Oracle suggests you also generate a JSON output file with the Cloud Premigration Advisor tool (CPAT), and save the log files for diagnosis.

Oracle recommends generating the JSON report as well as the text report, and always save your log report files. Why? If you encounter an issue while using CPAT, and need to contact Oracle Support, then you can provide all possible information to assist Oracle Support with resolving your issue. You can assist Oracle Support by being prepared to submit both the text and JSON reports, as well as the .log reports generated by CPAT. The --reportformat property accepts one or more space-delimited report formats. The permitted values for the --reportformat switch are json and text.

For example:

./premigration.sh -username SYSTEM --connectstring 'jdbc:oracle:thin:@service-name --reportformat json text

Parent topic: Best Practices for Using the Premigration Advisor Tool

19.8.5 Use Output Prefixes to Record Different Migration Scenarios

To keep track of reports for different migration options, use the --outfileprefix and --outdir properties on the CPAT command line.

To generate reports for different Cloud migration options, you can use the Cloud Premigration Advisor Tool (CPAT) with the --outfileprefix, so that you place a prefix on reports and log files that can organize the report options that you have generated. You can also use the --outdir property to organize reports for different instances, or to organize reports for different scenarios.

Note:

The --outdir property accepts either an absolute or a relative folder path. Using this property specifies a particular location where CPAT creates the log files, report files, and any properties files that you generate. If --outdir is omitted from the command line, then the log file and other generated files are created in the user's current folder, which can lead to files being overwritten when multiple analyses are performed.

For example:

./premigration.sh --outfileprefix ATPS_RUN_01 --outdir /path/CPAT_output --reportformat TEXT JSON ...

Parent topic: Best Practices for Using the Premigration Advisor Tool