This will be my third post on using Zero Downtime Migration (ZDM) for your migration, previously I’ve written about physical offline and online migration. Since Oracle talks a lot about Autonomous Database, let’s look this time what options you have on migrating to Autonomous.
For Autonomous we have two options, logical offline and logical online migration. To put that into technical terms, first one is with datapump and the second one utilizes Golden Gate for the migration. In this post, I will just focus on the offline migration piece.
CPAT for migration
This time we want to make sure we won’t have issues when we migrate to Autonomous Database, you might remember Oracle had a tool called Schema Advisor which you could run on the source database to see if there would be any issues with migration. Now there’s an updated version of that tool which is called Cloud Premigration Analyser Tool, more information on note 2758371.1.
This tool is now integrated with ZDM! You have option to run it through zdmcli and it’ll give you access to results.
First, I will create new response file from the logical template, zdm_logical_template.rsp. Compared to physical template, this one has way more options and configuration variables.
Let’s assume my response file is configured, I can now run zdmcli migrate with flag -advisor. This will create the report which can be found then from the source node. Don’t go looking for it from the zdm server!
ZDM log file still has some output from the report itself which you can review first and then drill down on the CPAT report.
bastion: 2022-03-22T15:05:14.405Z : Running CPAT (Cloud Premigration Advisor Tool) on the source node prod ...
bastion: 2022-03-22T15:05:28.994Z : Premigration advisor output:
Cloud Premigration Advisor Tool Version 22.5.2
Cloud Premigration Advisor Tool completed with overall result: WARNING
Cloud Premigration Advisor Tool generated report location: /u01/app/oracle/zdm/zdm_PROD_6/out/premigration_advisor_rep
ort.json
Cloud Premigration Advisor Tool generated report location: /u01/app/oracle/zdm/zdm_PROD_6/out/premigration_advisor_rep
ort.txt
CPAT exit code: 2
RESULT: WARNING
Schemas Analyzed (2): PDBUSER,TFG
A total of 47 checks were performed
There were 0 checks with FATAL results
There were 0 checks with BLOCKER results
There were 2 checks with WARNING results: xdb_resource_view_has_entries (78 relevant objects), has_directories (3 rele
vant objects)
There were 5 checks with INFORMATIONAL results: modified_db_parameters_serverless (32 relevant objects), has_enabled_s
cheduler_jobs (14 relevant objects), has_default_tablespace_not_data (2 relevant objects), options_in_use_not_availabl
e_serverless (1 relevant objects), has_trusted_server_entries (1 relevant objects) xdb_resource_view_has_entries
RESULT: WARNING
Schemas Analyzed (2): PDBUSER,TFG
A total of 47 checks were performed
There were 0 checks with FATAL results
There were 0 checks with BLOCKER results
There were 2 checks with WARNING results: xdb_resource_view_has_entries (78 relevant objects), has_directories (3 rele
vant objects)
There were 5 checks with INFORMATIONAL results: modified_db_parameters_serverless (32 relevant objects), has_enabled_s
cheduler_jobs (14 relevant objects), has_default_tablespace_not_data (2 relevant objects), options_in_use_not_availabl
e_serverless (1 relevant objects), has_trusted_server_entries (1 relevant objects) xdb_resource_view_has_entries
RESULT: WARNING
DESCRIPTION: XDB Repository is not supported in ADB. Entries in RESOURCE_VIEW will not migrate.
ACTION: Applications must be updated to remove their dependencies on XDB Repository.
has_directories
RESULT: WARNING
DESCRIPTION: There are directories objects in the source database.
ACTION: Recreate the directories on the Autonomous database instance.
bastion: 2022-03-22T15:05:28.996Z : Execution of phase ZDM_PRE_MIGRATION_ADVISOR completed
My source database is just a dummy PDB which has one schema which I created, so the actual report doesn’t have many items it would raise. I can download the report from the database server to take detailed look.
CPAT will raise any warnings or showstoppers for your migration, for example you might have ROWID columns which wouldn’t get migrated etc. So always take a good look on the report before migrating!
Example portion of CPAT, in reality is way longer and goes into details on each phase.
Number of checks run: A total of 47 checks were performed
Number of schemas analyzed: 2
Number of schemas in source DB: 37
List of schemas analyzed: PDBUSER, TFG
Fatal Checks: There were 0 checks with FATAL results
Blocking Checks: There were 0 checks with BLOCKER results
Warning Checks: There were 2 checks with WARNING results: xdb_resource_view_has_entries (78 relevant
objects),
has_directories (3 relevant objects)
Informational Checks: There were 5 checks with INFORMATIONAL results: modified_db_parameters_serverless (3
2 relevant
objects), has_enabled_scheduler_jobs (14 relevant objects), has_default_tablespace_n
ot_data (2
relevant objects), options_in_use_not_available_serverless (1 relevant objects),
has_trusted_server_entries (1 relevant objects)
Passing Checks: There were 40 checks with PASS results: max_data_files_allowed (4 relevant objects),
has_low_streams_pool_size (2 relevant objects), timezone_table_compatibility_higher
(1 relevant
objects), has_data_in_other_tablespaces_serverless (1 relevant objects),
has_user_defined_objects_in_sys (0 relevant objects), lcm_super_admin_user_exists (0
relevant
objects), has_fmw_registry_in_system (0 relevant objects), has_users_with_10g_passwo
rd_version (0
relevant objects), nls_national_character_set (0 relevant objects), has_java_objects
(0 relevant
ZDM Template file for Logical Offline migration
Looking template file, there are more options now compared to physical migration, it’s good to go it through and make sure you’ve configured everything you need to. This time for DB connection, you will supply Autonomous OCID and other details so ZDM gets the values it needs automatically.
Some examples:
SOURCEDATABASE_ENVIRONMENT_DBTYPE can be RDS_ORACLE, so if you’re migrating from Amazon RDS, this can be set accordingly.
DATAPUMPSETTINGS_JOBMODE can be FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE so it gives you options on what datapump mode you are running.
DATAPUMPSETTINGS_FIXINVALIDOBJECTS this let’s you compile invalid objects after migration.
Basically there’s huge chunk of options on datapump settings which is great! You want to have possibility to customize your migration as much as you can. If that’s excluding or including objects or remapping metadata. Golden Gate options are also included in the template file if you’d be doing online migration.
Other settings I configured for this migration in my template:
TARGETDATABASE_OCID=ocid1.autonomousdatabase.oc1.phx.anyhqljt6iltiayabgknkdp7sk7ekluxtbmuuuv3reu4ge5z62eg6sq3453d
TARGETDATABASE_ADMINUSERNAME=admin
SOURCEDATABASE_ADMINUSERNAME=system
SOURCEDATABASE_CONNECTIONDETAILS_HOST=prod-scan.subnetdns.vcndns.oraclevcn.com
SOURCEDATABASE_CONNECTIONDETAILS_PORT=1521
SOURCEDATABASE_CONNECTIONDETAILS_SERVICENAME=PDB2.subnetdns.vcndns.oraclevcn.com
OCIAUTHENTICATIONDETAILS_REGIONID=us-phoenix-1
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_TENANTID=ocid1.ten
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_USERID=ocid1.user.o
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_FINGERPRINT=b
I didn’t touch other variables for this migration, mainly wanted to do simple migration and look on CPAT.
Migrating to Autonomous Database
I’m running zdmcli again, now just without the advisor flag. This will be logical offline (datapump) migration as said earlier.
[zdmuser@bastion bin]$ ./zdmcli migrate database -sourcedb PROD_yyz1c4 -sourcenode prod -rsp /home/zdmuser/zdm_offline_logical2.rsp -srcauth zdmauth -srcarg1 user:opc -srcarg2 identity_file:/home/zdmuser/.ssh/id_rsa -srcarg3 sudo_location:/usr/bin/sudo
bastion.subnetpublic.vcndns.oraclevcn.com: Audit ID: 24
Enter source database administrative user "system" password:
Enter target database administrative user "admin" password:
As mentioned in the earlier post, I can just query my job status to see how it’s progressing.
Job execution elapsed time: 1 hours 2 minutes 44 seconds
ZDM_VALIDATE_TGT ...................... COMPLETED
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_SETUP_SRC ......................... COMPLETED
ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED
ZDM_PREPARE_DATAPUMP_SRC .............. COMPLETED
ZDM_DATAPUMP_ESTIMATE_SRC ............. COMPLETED
ZDM_PREPARE_DATAPUMP_TGT .............. COMPLETED
ZDM_DATAPUMP_EXPORT_SRC ............... COMPLETED
ZDM_UPLOAD_DUMPS_SRC .................. COMPLETED
ZDM_DATAPUMP_IMPORT_TGT ............... COMPLETED
ZDM_POST_DATAPUMP_SRC ................. COMPLETED
ZDM_POST_DATAPUMP_TGT ................. COMPLETED
ZDM_POST_ACTIONS ...................... COMPLETED
ZDM_CLEANUP_SRC ....................... COMPLETED
After everything is completed, I can do necessary validations and also go through the ZDM log file to see any import issues or failures there would be. If it would fail (happened to me earlier!), it would stop on specific step and you could go on debugging the issue.
Summary
Migrating to Autonomous is another almost fully automated step with ZDM, you need to pay attention to datapump settings which probably takes few turns to get them right. Adding integration with CPAT is welcomed addition, makes the whole process more streamlined when you can have that as part of your migration steps inside ZDM and not running it separately.
Be clever, use ZDM! 🙂