Cloud Premigration Advisor Tool aka CPAT has been an integral part of any Oracle Database logical migrations.
Running CPAT across an entire database estate was not easy until the recent CPAT release 25.6.
With the latest feature you can run CPAT across all the databases in an estate with single command.
You have the option to go either completely hands free or get into an interactive mode and both of them looks really cool.
Those who love the AutoUpgrade command line , I am sure you will love this one as well since they look really similar.
Let’s explore how you can do this as simple as in three steps.
Prerequisites
Step 1 : Prepare parameter file
Step 2 : Load the password
Step 3 : Generate CPAT report
Let’s explore each step one at a time.
Prerequisites
We should have sever with SQL*Net connectivity to all databases for which we need to generate CPAT.
You can typically use OEM server for this purpose.
Also download latest CPAT from MOS note : 2758371.1 and install it on the server with SQL*Net connectivity.
Step 1 : Prepare parameter file
We have to prepare a parameter file which will have the details about what kind of CPAT report has to be generated . These are same details that you would have entered previously when using CPAT with out parameter file . Please note that the ability to use parameter file existed before , however it has been enhanced to support multiple databases now with the latest release.
Below is the parameter file that I have used.
global.targetcloud=ATPS
global.reportformat=html
global.migrationmethod=DATAPUMP
global.username=SYSTEM
global.outdir=/home/oracle/cpat/output
global.keystore=/home/oracle/cpat/wallet
# Parameters specific to first database
prd1.outfileprefix=prd1
prd1.connectstring=jdbc:oracle:thin:@xa.xb.xc.xd:1521/PDB1
prd1.pdbname=PDB1
prd1.schemas=HR01
# Parameters specific to second database
prd2.outfileprefix=prd2
prd2.connectstring=jdbc:oracle:thin:@ya.yb.yc.yd:1521/PDB2
prd2.pdbname=PDB2
prd2.schemas=HR02
# Parameters specific to third database
prd3.outfileprefix=prd3
prd3.connectstring=jdbc:oracle:thin:@za.zb.zc.zd:1521/PDB3
prd3.pdbname=PDB3
prd3.schemas=HR03I have specified some parameters with prefix global which are global parameters applicable for all databases .
You can also see that I have used some prefixes (They are called namespaces) like prd1 , prd2 etc. These are used when you would like to have specific values for a database . You can also use this feature to overwrite any global parameters specified using global parameters.
For e.g . If the target database for prd1 is ADWS , you can specify something like below which will overwrite the global parameter for targetcloud(which is ATPS).
prd1.targetcloud=ADWS
You can use any keyword for namespace other than the word all.
Step 2 : Load Password (Optional)
As you know CPAT requires a database user with certain privileges for generating the report.
We have the option to specify the password of the user interactively while running CPAT , however this may not be a preferrable option when you have multiple databases and also in cases where you would like to completely automate the CPAT generation.
For this use case ,we can load the password of the database user to an Oracle wallet which requires a parameter like below in the parameter file.
global.keystore=/home/oracle/cpat/wallet
Let’s see how you can load password for SYSTEM user for each database.
Execute below command.
./premigration.sh --parfile cpatParams.cfg --loadpasswordWhen you run the command first time , it will create a wallet for you.
Below is sample output.
You can now load password for SYSTEM user for each database using a command similar to below.
add prd1 -user SYSTEM.
Below is sample output.
Similarly you can add password for all databases in parameter file and once finished you can save them with save command.
You have an option to create the wallet as auto-login and the decision depends on your requirement.
Please note that auto-login wallets will not ask for wallet password while running the CPAT which is good for automation. However keep in mind that same wallet can be used outside of CPAT and the situation can be tricky if you are using SYSTEM user. I would recommend to use a less privileged user (you can create a new user with privileges required for CPAT) .
I have chosen auto-login wallet for this article.
Below is sample output.
Step 3 : Generate CPAT Report
We have two options which you can use to generate CPAT report when using parameter file.
a. Non-Interactive Mode
b. Interactive Mode
a. Non-Interactive Mode
This options allows for more automation.
We can use a command similar to below which will trigger CPAT report generation of all databases which are specified in the parameter file.
./premigration.sh --parfile cpatParams.cfg -noconsolePlease note that above command will not ask for any input including the wallet password since we had created an auto-login wallet.
Below is sample output.
I like this option since it offers complete automation .
b. Interactive Mode
This option allows you to interact with CPAT CLI.
We can use a command similar to below to trigger CPAT generation.
./premigration.sh --parfile cpatParams.cfgThis will take us to the CPAT CLI where we have the option to see the current jobs , initiate CPAT report generation for all databases or couple of them.
You can use lsj command to see the current running jobs.
As you can see currently nothing is running since we have just connected to CLI.
We can initiate CPAT report generation by below command.
run allBelow is sample output
You can execute lsj command to track the progress . You will see a scree similar to below once all databases have been analyzed.
You will be able to reports for every database in the output folder.
Alternatively you can execute CPAT against a specific namespace like prd1 using below command.
run prd1Above command will only initiate CPAT generation for database specified in namespace prd1.
That’s all .
I hope you will find this feature useful when you have to deal with CPAT generation for multiple databases.
Leave a Reply