Introduction
ZDM version 21c supports logical migrations using Oracle GoldenGate as described in Part 2 or using the classical Data Pump export and import. This blog post provides a step by step instruction how to accordingly configure ZDM for Data Pump export and import with the minimal parameters required.
Using Data Pump export and import is for sure quite straightforward. Letting ZDM do it all for you (export, copy, import, and clean up) makes it possible to test your migration multiple times for no additional effort, and then when you are ready for the real migration, you are sure that exactly the same steps are executed and reduce the risk of making any mistakes.
The Environment
We will use:
- The ZDM host as described in Part 1.
- Oracle database version 19.10 installed on a VM with IP 10.0.2.185 and hostname onpremdb as source database.
- Oracle Autonomous Database as target database.
Migration
Step 1: Prepare the source database host.
Copy the SSH public key for zdmuser from the ZDM host (created in Part 1, post task 1) to the .ssh/authorized_keys file on the source database host for the user you want to use for login, in this case opc:
#on ZDM host as zdmuser
[zdmuser@zdmhost ~]$ cat .ssh/id_rsa.pub
#on the source database host as user opc
[opc@onpremdb ~]$ vi .ssh/authorized_keys
#insert the public key and save the changes
Step 2: Prepare the source database.
As SYS user:
SQL> alter system set streams_pool_size = 64M;
System altered.
SQL> alter user SYSTEM identified by <Your_SYSTEM_PW>;
User altered.
SQL> grant DATAPUMP_EXP_FULL_DATABASE to system;
Grant succeeded.
Step 3: Prepare the DZM host.
Add the servers hostname and IP information into the /etc/hosts file. As root user:
[root@zdmhost ~]# vi /etc/hosts
#add the following entries
10.0.2.247 zdmhost.pubsubnetlb.vcnfra.oraclevcn.com zdmhost
10.0.2.185 onpremdb
Verify that TTY is disabled for the SSH privileged user. If TTY is disabled, the following command returns the date from the remote host without any errors:
[zdmuser@zdmhost ~]$ ssh -i /home/zdmuser/.ssh/id_rsa opc@onpremdb "/usr/bin/sudo /bin/sh -c date"
Thu Oct 7 17:09:19 GMT 2021
Step 4: Create the ZDM response file on the ZDM host.
You’ll find a template at $ZDMHOME/rhp/zdm/template/zdm_logical_template.rsp on the ZDM host that contains a brief description of the parameters and their possible values. Here we will create a new response file with the minimal parameters required. As zdmuser:
[zdmuser@zdmhost ~]$ vi /home/zdmuser/logical_offline.rsp
# migration method
MIGRATION_METHOD=OFFLINE_LOGICAL
DATA_TRANSFER_MEDIUM=OSS
# data pump
DATAPUMPSETTINGS_JOBMODE=SCHEMA
DATAPUMPSETTINGS_METADATAREMAPS-1=type:REMAP_TABLESPACE,oldValue:USERS,newValue:DATA
INCLUDEOBJECTS-1=owner:HR
INCLUDEOBJECTS-2=owner:zdmmig
DATAPUMPSETTINGS_DATABUCKET_NAMESPACENAME=oraseemeadesandbox
DATAPUMPSETTINGS_DATABUCKET_BUCKETNAME=zdmbucket
DATAPUMPSETTINGS_DATAPUMPPARAMETERS_EXPORTPARALLELISMDEGREE=2
DATAPUMPSETTINGS_DATAPUMPPARAMETERS_IMPORTPARALLELISMDEGREE=2
DATAPUMPSETTINGS_EXPORTDIRECTORYOBJECT_NAME=DATA_PUMP_DIR
# on source db: select directory_path from dba_directories where directory_name = 'DATA_PUMP_DIR';
DATAPUMPSETTINGS_EXPORTDIRECTORYOBJECT_PATH=/u01/app/oracle/admin/ORCL/dpdump/BFB29F07DDB2450FE053B902000ADDF9
DATAPUMPSETTINGS_IMPORTDIRECTORYOBJECT_NAME=DATA_PUMP_DIR
DATAPUMPSETTINGS_CREATEAUTHTOKEN=FALSE
DATAPUMPSETTINGS_DELETEDUMPSINOSS=TRUE
# source db
SOURCEDATABASE_CONNECTIONDETAILS_HOST=onpremdb
SOURCEDATABASE_CONNECTIONDETAILS_PORT=1521
SOURCEDATABASE_CONNECTIONDETAILS_SERVICENAME=orclpdb
SOURCEDATABASE_ADMINUSERNAME=SYSTEM
# target db
TARGETDATABASE_OCID=ocid1.autonomousdatabase.oc1...
TARGETDATABASE_ADMINUSERNAME=ADMIN
# oci cli
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_USERID=ocid1.user.oc1...
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_TENANTID=ocid1.tenancy.oc1...
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_FINGERPRINT=9f:3b:55:c1:bd:a4:3e:de:d7:e1:a1:12:eb:93:ba:3a
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_PRIVATEKEYFILE=/home/zdmuser/.oci/oci_api_key.pem
OCIAUTHENTICATIONDETAILS_REGIONID=eu-frankfurt-1
Set DATAPUMPSETTINGS_DELETEDUMPSINOSS=FALSE to keep the dump files on Object Storage after migration.
Step 5: Evaluate the configuration.
On the ZDM host as zdmuser:
[zdmuser@zdmhost ~]$ $ZDMHOME/bin/zdmcli migrate database \
-rsp /home/zdmuser/logical_offline.rsp \
-sourcenode onpremdb \
-sourcesid ORCL \
-srcauth zdmauth \
-srcarg1 user:opc \
-srcarg2 identity_file:/home/zdmuser/.ssh/id_rsa \
-srcarg3 sudo_location:/usr/bin/sudo \
-eval
Enter source database administrative user "SYSTEM" password:
Enter target database administrative user "ADMIN" password:
Enter Authentication Token for OCI user "ocid1.user.oc1...":
Enter Data Pump encryption password:
Operation "zdmcli migrate database" scheduled with the job ID "1".
If the source database is using ASM for storage management, then use -sourcedb <db_unique_name> instead of -sourcesid <SID> in the zdmcli command.
Check the job status. On the ZDM host as zdmuser:
[zdmuser@zdmhost ~]$ $ZDMHOME/bin/zdmcli query job -jobid 1
Job ID: 1
User: zdmuser
Client: zdmhost
Job Type: "EVAL"
...
Current status: EXECUTING
Current Phase: "ZDM_SETUP_SRC"
Result file path: "/home/zdmuser/zdmbase/chkbase/scheduled/job-1-2021-04-11-14:17:49.log"
...
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_VALIDATE_TGT ...................... COMPLETED
ZDM_SETUP_SRC ......................... STARTED
ZDM_PRE_MIGRATION_ADVISOR ............. PENDING
ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... PENDING
ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... PENDING
ZDM_CLEANUP_SRC ....................... PENDING
Wait until all phases are completed. To repeat the check each 10 seconds:
[zdmuser@zdmhost ~]$ while :; do $ZDMHOME/bin/zdmcli query job -jobid 1; sleep 10; done
Job execution elapsed time: 1 minutes 58 seconds
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_VALIDATE_TGT ...................... COMPLETED
ZDM_SETUP_SRC ......................... COMPLETED
ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED
ZDM_CLEANUP_SRC ....................... COMPLETED
Step 6: Initiate the migration.
Execute the same command for evaluation, but this time without the -eval parameter. On the ZDM host as zdmuser:
[zdmuser@zdmhost ~]$ $ZDMHOME/bin/zdmcli migrate database \
-rsp /home/zdmuser/logical_offline.rsp \
-sourcenode onpremdb \
-sourcesid ORCL \
-srcauth zdmauth \
-srcarg1 user:opc \
-srcarg2 identity_file:/home/zdmuser/.ssh/id_rsa \
-srcarg3 sudo_location:/usr/bin/sudo
Enter source database administrative user "SYSTEM" password:
Enter target database administrative user "ADMIN" password:
Enter Authentication Token for OCI user "ocid1.user.oc1...":
Enter Data Pump encryption password:
Operation "zdmcli migrate database" scheduled with the job ID "2".
Check the job status. On the ZDM host as zdmuser:
[zdmuser@zdmhost ~]$ while :; do $ZDMHOME/bin/zdmcli query job -jobid 2; sleep 10; done
Job ID: 2
User: zdmuser
Client: zdmhost
Job Type: "MIGRATE"
...
Current status: SUCCEEDED
Result file path: "/home/zdmuser/zdmbase/chkbase/scheduled/job-2-2021-04-11-14:36:19.log"
...
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_VALIDATE_TGT ...................... 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_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
The migration is now completed. Check your target database. It will contain the schemas and data from the source database.
Log Files
In case of any issue, check the following log files:
#job log file on the zdm host
/home/zdmuser/zdmbase/chkbase/scheduled/job-<job_id>-<data>.log
#ZDM log file on the zdm host
/home/zdmuser/zdmbase/crsdata/zdmhost/rhp/zdmserver.log.0
#data pump export file on the source database host
/u01/app/oracle/admin/ORCL/dpdump/BFB29F07DDB2450FE053B902000ADDF9/ZDM_6_DP_EXPORT_6170.log
#data pump import file on Object Storage
ZDM_6_DP_IMPORT_6170.log
GUI
Do you rather prefer using a graphical interface? Have a look at the Database Migration Service.
Conclusion
After investing some work in the setup, all steps are done for you in one click: export from source, copy to Object Storage, import into the target database, and clean up the dump files.
ZDM offers a wide range of options that you might need for more flexibility and control. Have a look at the documentation for the complete list of available parameters.
Further Reading
- Oracle Zero Downtime Migration
- Doc: Move to Oracle Cloud Using Zero Downtime Migration
- LiveLab: Zero Downtime Migration – Logical Offline Migration to ADB
- Part 1/5: Zero Downtime Migration (ZDM) – Introduction & Installation
- Part 2/5: Zero Downtime Migration (ZDM) – Logical Online Migration using Oracle GoldenGate
- Part 3/5: Zero Downtime Migration (ZDM) – Logical Offline Migration using Data Pump (this blog post)
- Part 4/5: Zero Downtime Migration (ZDM) – Physical Online Migration using Data Guard
- Part 5/5: Zero Downtime Migration (ZDM) – Physical Offline Migration using RMAN
- Use an Existing RMAN Backup with Zero Downtime Migration
- AWS RDS for Oracle to Autonomous Database using Zero Downtime Migration
- Zero Downtime Migration (ZDM) – Logical Offline Migration to Co-Managed Database Services
- Zero Downtime Migration (ZDM) – Logical Online Migration to VM DB Systems
Database Heartbeat 