Migrate Data Using Transportable Tablespaces

Topics

Example

The procedures for transporting a tablespace are demonstrated in the following example. This example assumes the existence of the data files and tablespaces shown below:

Task 1: Verify that the tablespaces are self contained

To determine whether a set of tablespaces is self-contained, run invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. For example:

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

The package verifies that all dependent objects (such as indexes, constraints, or partitions) exist entirely within the set of tablespaces being transported. No cross-tablespace dependencies exist that would prevent successful transport.

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Task 2: Create the backup

This task assumes that all required prerequisites for this step have already been completed. See Prepare the Source Database Host Environment for more information.

• Navigate to the Project directory and open the tts-backup-env.txt parameter file. Provide the input as necessary in the parameter file. For example:

  [DEFAULT]
  ...
  
  PROJECT_NAME=tts_project
  DATABASE_NAME=orclpdb1
  TABLESPACES=sales_1,sales_2
  SCHEMAS=SALES
  ...
  
  HOSTNAME=<host>
  LSNR_PORT=1521
  DB_SVC_NAME=tts_db
  ORAHOME=/opt/oracle/product/19c/dbhome_1
  DBUSER=USER
  DB_VERSION=19c
  
  ...
  
  TTS_BACKUP_URL=https://objectstorage.region.oraclecloud.com/n/namespace/b/tts_data_bucket
  TTS_BUNDLE_URL=https://objectstorage.region.oraclecloud.com/n/namespace/b/tts_bucket
  OCI_INSTALLER_PATH=/home/oracle/opc_installer/oci_installer/oci_install.jar
  CONFIG_FILE=/home/oracle/OCI_CONFIG/config
  COMPARTMENT_OCID=ocid1.compartment.oc1..xxxxx
  OCI_PROXY_HOST=proxy.company.com
  OCI_PROXY_PORT=80
  
  ...

• After you have configured the parameter file, you can optionally perform a dry run of the tts-backup.py script. Performing a dry run allows you to validate that the source database host is properly configured and ready for the transportable tablespace operation. This process checks for any missing prerequisites, verifies connectivity to the designated storage destination, and ensures that all required parameters are set correctly. Running a dry run before starting the actual backup helps identify and resolve potential issues in advance, reducing the risk of errors during the transportable tablespace operation.

  Run the following command to perform the dry run of the script:

  $ python3 /u01/tts_project/tts-backup.py --DBPASSWORD=xxx --DRY_RUN=TRUE

• After completing the dry run and resolving any errors encountered, run the tts-backup.py script to initiate the source database backup.

  $ python3 /u01/tts_project/tts-backup.py --DBPASSWORD=xxx

  This example runs the Transportable Tablespaces Backup Utility Python script to back up the source database. Depending on the value of the FINAL_BACKUP parameter, the backup can be either incremental or non-incremental.

  The Project_Directory parameter specifies the full path to the directory where the Transportable Tablespaces Backup Utility (tts-backup.py) and parameter file (tts-backup-env.txt) are stored.

  The --DBPASSWORD password for the database user that performs the backup.

  The --TDE_WALLET_STORE_PASSWORD specifies the password for the Transparent Data Encryption (TDE) wallet, required only if any tablespaces are encrypted.

  Note
  
  Ensure that the tts-backup-env.txt file is configured with the correct inputs (such as OCI Object Storage details, bucket URLs, and database parameters) before running the command. The tts-backup.py script validates inputs, performs checks on the source database, and then starts the backup process. If FINAL_BACKUP is set to TRUE, the script will export tablespace and schema metadata using Data Pump export utility.

• After the script completes successfully, it uploads the tablespace data backups from the source database to the OCI Object Storage bucket. The backup can be either incremental or non-incremental, depending on the selected backup type. A non-incremental backup consists of all the tablespace data at a specific point in time, providing a complete backup for the restore operation. An incremental backup consists of a level 0 backup, which serves as the baseline, and one or more subsequent incremental (level 1) backups, each including changes since the previous backup. However, when restoring from incremental backups, you must apply the level 0 backup and all subsequent incremental backups in sequence to ensure data consistency.

  The script also creates a compressed Transportable Tablespaces metadata bundle containing the necessary metadata and, if applicable, wallet files, and uploads it to the OCI Object Storage metadata bucket.

  Example URLs for non-incremental backup:

  https://swiftobjectstorage.region.oraclecloud.com/v1/dwcsdev/tts_bucket/orclpdb1_bkp.tgz

  Example URLs for incremental backup:

  Note that, each URL includes the incremental backup level in its metadata bundle name, for example, level_0, level_1, or final_bkp. This helps you quickly identify and reference each backup set during the restore process. Ensure that you apply each incremental backup in order, starting from the level 0 backup and proceeding through each subsequent backup up to the final backup.

  https://swiftobjectstorage.region.oraclecloud.com/v1/dwcsdev/tts_bucket/orclpdb1_level_0.tgz

  https://swiftobjectstorage.region.oraclecloud.com/v1/dwcsdev/tts_bucket/orclpdb1_level_1.tgz

  …

  https://swiftobjectstorage.region.oraclecloud.com/v1/dwcsdev/tts_bucket/orclpdb1_final_bkp.tgz

The process for restoring Transportable Tablespaces on the target Autonomous AI Database depends on whether the tablespace backup from the source database is incremental or non-incremental.

Restore using a non-incremental tablespace backup of the source database

With a non-incremental (full) source tablespace backup, the metadata bundle includes the complete tablespace backup of the source database as well as a Data Pump export of the tablespace and schema metadata. During the provisioning of the Autonomous AI Database, you provide the metadata bundle URL (in the Object Storage bucket URI of the tablespace zip file text box in the Migration section under Advanced Options) to import the tablespaces from the source tablespace backup. The provisioning process restores the tablespace backup and imports the tablespace and schema metadata into your database.

For example:

Description of the illustration adb_transportable_tablespace_migration.png

For detailed steps to provision an Autonomous AI Database, see Provision an Autonomous AI Database Instance.

Restore using an incremental tablespace backup of the source database

Provision the Autonomous AI Database using the level 0 backup:

Begin by provisioning your Autonomous AI Database with the level 0 tablespace backup the source database. This operation provisions the database and restores the initial level 0 backup on the database. See Provision an Autonomous AI Database Instance for more information.

Apply subsequent incremental (level 1) backups:

After the database is provisioned and the level 0 backup of the source tablespaces is restored, apply any incremental (level 1) backups in sequence.

Steps to update your target Autonomous AI Database with incremental backups:

• Perform the following prerequisite steps as necessary:

  • Open the Oracle Cloud Infrastructure Console by clicking the next to Cloud.

  • From the Oracle Cloud Infrastructure left navigation menu click Oracle Database and then click Autonomous AI Database.

  • On the Autonomous AI Database page, select your Autonomous AI Database from the links under the Display name column.

    To apply subsequent incremental tablespace backup from the source database on the target Autonomous AI Database, follow these steps:

    • On the More actions drop-down list, select Migrate Data.

      This shows the Migrate Data page.

      Note
      
      The Migrate data option is available only for databases that have completed a level 0 import during provisioning and require additional incremental tablespace backup imports to complete the transportable tablespace operation after provisioning.

    • On the Migrate Data page, enable the Import transportable tablespace toggle. When you enable the toggle, an Object Storage Bucket URI of the tablespace zip file field appears. Input the Transportable Tablespace metdata bundle URL in this field.

      Since the Transportable Tablespace metadata bundle URL must be downloaded from OCI Object Storage, you need to set up dynamic groups and policies that allow access to OCI Object Storage using a resource principal. Configuring a resource principal is required to securely download the Transportable Tablespaces metadata bundle on the target Autonomous AI Database. See Prerequisites for more information.

      For example, to apply the level 1 tablespace backup from the source database, specify the appropriate URL for the level 1 tablespace backup from the source database:

      
      
      Description of the illustration adb_transportable_tablespaces_level1_restore.png
      

      ...

      Similarly, when applying the final tablespace backup from the source database, supply the appropriate URL. For example:

      
      
      Description of the illustration adb_transportable_tablespace_restore_final.png
      

    • Click Migrate.

      The database Lifecycle state changes to Transporting. After the restore is complete Lifecycle state changes to Available.