Lessons from my journey with databases

LinkedIn
Youtube
Livelabs
About
, , ,

Migrating to Oracle Autonomous AI Database Serverless using Transportable Tablespace and Object Storage

Published by

Amalraj Puthenchira

on

Transportable Tablespace is one of my favorite migration method and I am glad that finally we have that capability available for migrations to Oracle Autonomous AI Database serverless.

Oracle has published detailed documentation about the overall procedure . I have tested the procedure and I am going to share my observations in this post.

Basically the procedure involves two main parts.

  1. Use a python script to backup the source database tablespaces to Object Storage Bucket or to an NFS mount.
  2. Use OCI console to perform necessary actions to move the data to Autonomous Database.

I have divided the steps in to multiple sections as below so that it is easy to navigate.

Step 0 – Prerequisites

Step 1 – Dry Run

Step 2 – Perform level 0 backup of source database tablespaces

Step 3 – Create an Oracle Autonomous AI Database

Step 4 – Perform incremental backup of source database tablespaces

Step 5 – Restore incremental backups on Autonomous AI Database

Step 6 – Perform final incremental backup of source database tablespaces (Downtime required)

Step 7 – Restore final incremental backup on Autonomous AI Database (Downtime required)

Step 8 – Verify Data

Below is my environment.

Source Platform : OCI Oracle Base Database

Source Database Name: DB

Source DB Version : 19.29

Target Platform : Autonomous Database serverless on OCI

Target Database Name : ATP_TTS

Target DB version : 19.29

Let’s explore how we can achieve this.

Step 0 – Prerequisites

All the required prerequisites are mentioned in the documentation .

Below are some of my observations for the prerequisites.

a. Download and Install the Oracle Database Cloud Backup Module for OCI

You don’t have to download the module if your database version is 19.27 and above since it is part of Oracle binary.

You will have to install it irrespective of your database version.

Ensure that you use Java from Oracle Home (provided it has JDK 1.8 or higher) during installation to avoid below error.

b. Update parameter file for backup.

We need to modify tts-backup-env.txt (Downloaded from GitHub as part of prerequisites) file with our details before initiating the procedure.

Below are the contents of my file.

[DEFAULT]
### Project and Tablespace inputs
PROJECT_NAME=tts_p
DATABASE_NAME=DB_PDB1
TABLESPACES=HR,USERS
SCHEMAS=hr01,test

#### Database connection inputs
HOSTNAME=ada.xxxxxxxxx.xxxxx.xxxxx.com
LSNR_PORT=1521
DB_SVC_NAME=db_pdb1.xxxxxxxx.xxxxx.xxxxx.com
ORAHOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
DBUSER=SYS
DB_VERSION=19c


#### Object Storage Service (OSS) inputs
#### OCI Installer inputs
TTS_BACKUP_URL=https://<storage_namespace>.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/<storage_namespace>/b/tts-bkp
TTS_BUNDLE_URL=https://<storage_namespace>.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/<storage_namespace>/b/tts-bkp-metadata
OCI_INSTALLER_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib/ocimodule/oci_install.jar
CONFIG_FILE=/home/oracle/oci_config/config
COMPARTMENT_OCID=ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx




### Final backup inputs
FINAL_BACKUP=FALSE

PARALLELISM=2

You can refer the documentation for more details on the parameters .

Please note that I have used different format for the Backup and Bundle URL since object storage URI format specified in the documentation is not working and it is a legacy format for accessing object storage.

I also had to use my PDB name(DB_PDB1) for DB_NAME parameter.

c. Create API signing key config file .

We have to create API signing key config file (/home/oracle/oci_config/config) with below contents on the source database host.

[DEFAULT]
user=ocid1.user.oc1..aaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
fingerprint=xx:xx:xx:xx:xx:xx::xx:xx:xx:xx:xx:xx:xx:xx:xx
tenancy=ocid1.tenancy.oc1..aaaaaaaabbbbbbbbbbbbbbbbbbbbbbbb
region=eu-frankfurt-1
key_file=/home/oracle/api_oss.pem

You can see that this config file is being referred in the tts-backup-env.txt file.

You will have similar configuration for your API keys and it can be retrieved from console.

d. Execute CPAT to identify any actions required

I see CPAT will be updated soon to reflect the actions required for this type migration using TTS.

I will update here as soon as CPAT has released those feature.

Step 1 – Dry Run

Documentation says we can perform a dry run of the python script to identify potential issues before the actual steps.

python3 /home/oracle/tts_p/tts-backup.py --DRY_RUN=TRUE

However I am getting below syntax error , I believe the code is not properly updated for this to work.

As you can see there is no argument like –DRY_RUN , however documentaion mentions it.

I will update here as soon as I am able to make it work.

Step 2 – Perform level 0 backup of source database tablespaces.

We are now ready to kick off the backup for source database tablespaces.

Execute below command to initiate level 0 backup

python3 /home/oracle/tts_p/tts-backup.py

We need to provide SYS and TDE wallet store password when prompted.

Below is sample output.

Above command will initiate an RMAN compressed backup of the tablespaces to the bucket that we have specified in tts-backup-env.txt file.

At the end of successful completion of command , we will get an message similar to below.

Please take a note of the URL since it will be required in next step.

Step 3 – Create an Oracle Autonomous AI Database.

Provision an Oracle Autonomous AI Database by navigating to OCI console and making necessary selection based on requirements.

We should provide the level 0 backup bundle URL (from step 1) and also enable Import transportable tablespace option under Advanced options before hitting Create button.

Below is a sample screenshot.

This will create an autonomous database using level 0 backup that we have provided.

Step 4 – Perform incremental backup of source database tablespaces.

Let us initiate incremental backup of source database tablespaces.

We can use the same command that we used for level 0 for incremental backups as well and there is no need to perform any other changes to our configuration files until we decide to take the final incremental backup.

I have initiated two incremental backups and below are names of the metadata bundles.

Step 5 – Restore incremental backups on Autonomous AI Database.

We can apply the incremental backups to target autonomous database once they are available to keep the Oracle Autonomous AI Database in sync with source database.

To restore the incremental backup , navigate to More Actions from Autonomous Database console and select Migrate Data and provide the URL of the incremental backup.

Below are sample screenshots of the steps.

We can monitor the progress by checking the work request.

Below is sample screenshot of successful restore.

We can repeat the process to apply all the incremental backups.

Step 6 – Perform final incremental backup of source database tablespaces (Downtime required)

We can take final backup of source database tablespaces during downtime window.

Perform below steps.

a. Bring down application.

Bring down the application to ensure that no more changes are being done to the database.

b. Place tablespaces in read only mode.

We should manually place all the tablespaces involved in migration to read only.

c. Modify parameter file.

Modify FINAL_BACKUP=TRUE parameter in tts-backup-env.txt file.

d. Execute below command to initiate Final backup.

python3 /home/oracle/tts_p/tts-backup.py

This command will perform three main actions.

i. Perform final backup of tablespaces along with Datapump export of metadata of transportable tablespaces.

ii. Perform DataPump export for schema metadata.

iii. Perform metadata of tablespaces with TRANSPORT_TABLESPACES option.

I haven’t really understood the reason for the Datapump export in point iii and I see it is same as the DataPump export taken by RMAN in point i . I will update as soon as I have more clarity.

Step 7 – Restore final incremental backup on Autonomous AI Database.

Procedure to initiate final restore is same as Step 4 , however there will be additional steps done in the background including metadata import.

As you know Oracle Autonomous AI database only allows one Bigfile tablespaces, however my source databases tablespaces are smallfile tablespaces which means there are steps done in the background for moving the data to bigfile tablespace DATA.

Step 8 – Verify the data.

We can verify our schema and related objects since migration has been completed.

I had created one table under HR and USERS and I could see data in those tables and these tables are now under DATA tablespace.

How to Troubleshoot ?

We can troubleshoot errors during backup since we have access to the logs and the python script.

However what happens on the Autonomous AI Database part is a black box and we don’t have any visibility on the errors and I see only way is to involve Oracle Support for any issues.

How much downtime required ?

It took 45 minutes for me to complete the major step (step 7) with two tables having one row which is not impressive.

Summary:

I believe transportable tablespace is a very promising method for migrating to Autonomous AI Database and this will definitely evolve over the time.

Leave a Reply