Lessons from my journey with databases

Let’s explore how we can perform Logical offline migration to Oracle Autonomous Database on Dedicated Infrastructure using ZDM (Zero Downtime Migration) with NFS as the data transfer medium.

Oracle Zero Downtime Migration(ZDM) is a widely used automated solution for migrating Oracle Databases into any Oracle-owned infrastructure, including Exadata Database Machine on-premises, Exadata Cloud@Customer, and Oracle Cloud Infrastructure (OCI), including Oracle Database@Azure.

Please visit ZDM Product page to know more information about the product.

Here is my demo environment.

Source Database : Oracle Base Database (19.22)

Target Database : Autonomous Database on Dedicated Infrastructure

ZDM Service Host : Compute in OCI (Oracle Linux 7)

Preparation

I have divided the preparation into multiple sections as mentioned below.

Prepare ZDM Service Host

Create NFS using OCI File Storage.

Configure ADB-D to use NFS

Configure Source DB to use NFS

Prepare Source Database

Prepare Target Database

Network Connectivity

Create API Key pair

Prepare response file

Let’s deep dive now.

Prepare ZDM Service Host

Spin up an OCI compute host with Oracle Linux 7 (you can also choose Oracle Linux 8 or RHEL 8) and follow the instructions to set up a ZDM service host.

You can also refer to ZDM Live Lab for detailed instruction.

Please ensure that you download the latest ZDM software from Oracle Support Portal using Patch ID : 33509650 (Use Linux x86-64 for Zero Downtime Migration 21.4.1.0.0).

Create NFS using OCI File Storage:

Navigate to File Systems under Storage from OCI console to bring up File System page.

Click on Create File System on below screen.

Update the relevant information on the screen as shown below and click on Create to proceed with File System creation .

I have given Backup as the name of File System.

Navigate to the Exports section (as seen below) once the File System has been created.

Click on the Export path(/Backup in this case) to bring up the detailed information about the export as below.

Click on the Mount commands to get the below details.

Please ensure to allow the above mentioned ingress and egress rules for the VCN or subnet.

Below details are useful for coming sections.

Configuring ADB-D to use NFS

Connect to ADB-D using SQL*Plus.

a. Create a database directory.

CREATE DIRECTORY BKP_DIR AS ‘Backup’;

b. Attach the FS to database using below SQL.

BEGIN
DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
file_system_name => ‘Backup’,
file_system_location => ‘<IP_Address>:/Backup’,
directory_name => ‘BKP_DIR’,
description => ‘NFS for ZDM Migration’
);END;
/

You can get IP address from the mount target information that we collected earlier.

Below is sample output.

Configuring Source DB to use NFS

Let’s mount the NFS to our Source DB system.

Execute below commands to mount the NFS as /mnt/Backup.

sudo mkdir -p /mnt/Backup

sudo mount x.x.x.x:/Backup /mnt/Backup

sudo chown oracle:oinstall /mnt/Backup

sudo chmod 775 /mnt/Backup

note : x.x.x.x is the IP address of the mount target which we collected earlier.

That’s it , we can now see the FS (/mnt/Backup) in source DB system as below.

Source Database Preparation

All the prerequisites for source database in terms of logical offline migration are clearly defined in ZDM documentation.

Please refer documentation for all the detailed prerequisites.

For this Blog , I had to just set streams_pool_size to 256MB.

I have also created a sample schema and table using below SQL.

Target Database Preparation

Again for the target database preparation , everything is well documented .

Few important points are highlighted below.

Target database should have same characterset as the source database.

Target database timezone version should be equal to or greater than the source timezone version.

Network Connectivity

I have deployed source database , target ADB-D and ZDM in to the same VCN to make the connectivity easier.

However for an actual use case , your source database might be an on-premise database , in that case we either need VPN or Fast Connect to OCI so that you can reach ADB-D.

Please note that for this demo, source and target hostnames were easily resolvable from ZDM service host since it is using DNS in OCI.

If you have deployed ZDM outside of OCI or your source database is an on-premise one , you should add the relevant hostname and IP details to /etc/hosts file of ZDM service host.

I have made necessary changes to allow the below required communication.

Initiator	Target	Protocol	Port	Purpose
ZDM host	Source DB Server	TCP	22	SSH
ZDM host	Source DB Server	TCP	1521	SQL*Net
ZDM host	Target ADB-D	TCPS	2484	SQL*Net
ZDM Host	Oracle Cloud Interface REST endpoint	SSL	443	OCI REST endpoint

For detailed information on the network connectivity , refer to ZDM documentation.

Create API Key Pair

ZDM requires API Keys to validate/discover the target database.

Please create API keys following Documentation.

You will get below information at the end of API Key creation , Please keep it handy.

Also save the private and public API keys.

Prepare response file

I have prepared below response file and saved it as /home/zdmuser/logical_offline_adb_d.rsp on the ZDM service host.

MIGRATION_METHOD=OFFLINE_LOGICAL
DATA_TRANSFER_MEDIUM=NFS
SOURCEDATABASE_CONNECTIONDETAILS_HOST=<source_db_hostname>
SOURCEDATABASE_CONNECTIONDETAILS_PORT=1521
SOURCEDATABASE_CONNECTIONDETAILS_SERVICENAME=<service_name_of_pdb>
SOURCEDATABASE_ADMINUSERNAME=system
TARGETDATABASE_OCID=<OCID of Target ADB>
TARGETDATABASE_ADMINUSERNAME=ADMIN
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_TENANTID=<ocid_of_tenancy>
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_USERID=<ocid_of_user>
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_FINGERPRINT=<finger_print_of_api>
OCIAUTHENTICATIONDETAILS_USERPRINCIPAL_PRIVATEKEYFILE=<loc of private api key file>
OCIAUTHENTICATIONDETAILS_REGIONID=<region_id>
DATAPUMPSETTINGS_EXPORTDIRECTORYOBJECT_NAME=BKP_DIR
DATAPUMPSETTINGS_EXPORTDIRECTORYOBJECT_PATH=/mnt/Backup
DATAPUMPSETTINGS_IMPORTDIRECTORYOBJECT_NAME=BKP_DIR

Database Migration

Okay , we are all set to proceed with migration.

Let’s do a migration evaluation (or dry run).

I have started the migration evaluation using below command.

Please provide source and target database administrative user password when prompted.

$ZDM_HOME/bin/zdmcli migrate database -rsp /home/zdmuser/logical_offline_adb_d.rsp -sourcedb src_pdb -sourcenode src001 -srcauth zdmauth -srcarg1 user:opc -srcarg2 identity_file:/home/zdmuser/mykey.key -srcarg3 sudo_location:/bin/sudo –eval

-rsp – specify the response file with absolute path.

-sourcedb – specify db_unique_name of source database.

-sourcenode – specify hostname of source database.

-srcauth – specify authentication plug-in ( I have used zdmauth in this case).

-srcarg1 user:<username> – specify the user having sudo privileges on the source database server ( I have used opc here).

-srcarg2 identitity_file:<file_name> – specify the private ssh key file for user mentioned with -srcarg1.

-srcarg3 – location of sudo command.

Below is sample screenshot of initiating a migration evaluation.

We can monitor the progress of evaluation using below command

$ZDM_HOME/bin/zdmcli query job -jobid 23

23 is the job_id for the evaluation that I have initiated.

Below is the sample output.

You will see a similar output as below at the end of successful migration evaluation.

Optionally you can check the logfile specified in output as shown below.

It is recommended to check the CPAT (Cloud premigration advisor tool) output before proceeding with actual migration .

CPAT output will be located on the source database , location of the CPAT file is specified in result file.

Please note that ZDM won’t allow you to proceed if there were critical errors that needs to be addressed in CPAT unless you skip the CPAT check in ZDM.

Good , finally let’s start the database migration.

We can use the same command as we used for evaluation except that we will remove -eval from the command.

I have initiated the Database migration and below is sample output.

Below is the sample output of the progress.

At the end of successful migration , you can see that all phases completed successfully as shown below.

Let’s now quickly check the data in our sample schema in target database.

Yes! , we can see the sample data in HR01 schema.

That’s how easy it is to perform a logical offline migration to ADB-D.