Migrating using Oracle transportable tablespaces

Log in to your source host as the owner of your Oracle home.

Make sure that your ORACLE_HOME and ORACLE_SID environment variables point to your source database.

Log in to your database as an administrator, and verify that the time zone version, DB character set, and national character set are the same as in your target database.

SELECT * FROM V$TIMEZONE_FILE;
SELECT * FROM NLS_DATABASE_PARAMETERS 
  WHERE PARAMETER IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

Set up the transportable tablespace utility as described in Oracle Support note 2471245.1.

The setup includes editing the xtt.properties file on your source host. The following sample xtt.properties file specifies backups of three tablespaces in the /dsk1/backups directory. These are the tablespaces that you intend to transport to your target DB instance. It also specifies the source platform ID to convert the endianness automatically.

#linux system 
platformid=13
#list of tablespaces to transport
tablespaces=TBS1,TBS2,TBS3
#location where backup will be generated
src_scratch_location=/dsk1/backups
#RMAN command for performing backup
usermantransport=1

If your tablespaces are in read-only mode, log in to your source database as a user with the ALTER TABLESPACE privilege, and place your tablespaces in read/write mode. Otherwise, skip to the next step.

The following example places tbs1, tbs2, and tbs3 in read/write mode.

ALTER TABLESPACE tbs1 READ WRITE;
ALTER TABLESPACE tbs2 READ WRITE;
ALTER TABLESPACE tbs3 READ WRITE;

Back up your tablespaces using the xttdriver.pl script. Optionally, you can specify --debug to run the script in debug mode.

export TMPDIR=location_of_log_files
cd location_of_xttdriver.pl
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

Start SQL*Plus or Oracle SQL Developer and log in to your RDS for Oracle DB instance.

Download the backups from the Amazon S3 bucket to your target DB instance by using the Amazon RDS procedure rdsadmin.rdsadmin_s3_tasks.download_from_s3 to d. The following example downloads all of the files from an Amazon S3 bucket named amzn-s3-demo-bucket to the DATA_PUMP_DIR directory.

EXEC UTL_FILE.FREMOVE ('DATA_PUMP_DIR', 'res.txt');
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
  p_bucket_name    =>  'amzn-s3-demo-bucket',
  p_directory_name =>  'DATA_PUMP_DIR') 
AS TASK_ID FROM DUAL;

The SELECT statement returns the ID of the task in a VARCHAR2 data type. For more information, see Downloading files from an Amazon S3 bucket to an Oracle DB instance.

Start an Oracle SQL client and log in to your target RDS for Oracle DB instance as the master user.

Run the procedure rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces, specifying the tablespaces to import and the directory containing the backups.

The following example imports the tablespaces TBS1, TBS2, and TBS3 from the directory DATA_PUMP_DIR. The source platform is AIX-Based Systems (64-bit), which has the platform ID of 6. You can find the platform IDs by querying V$TRANSPORTABLE_PLATFORM.

VAR task_id CLOB

BEGIN
  :task_id:=rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces(
        'TBS1,TBS2,TBS3',
        'DATA_PUMP_DIR',
        p_platform_id => 6);
END;
/

PRINT task_id

(Optional) Monitor progress by querying the table rdsadmin.rds_xtts_operation_info. The xtts_operation_state column shows the value EXECUTING, COMPLETED, or FAILED.

SELECT * FROM rdsadmin.rds_xtts_operation_info;

View the log of the completed import by using the task ID from the previous step.

SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||'&task_id'||'.log'));

Make sure that the import succeeded before continuing to the next step.

Start an Oracle SQL client and log in to your target RDS for Oracle DB instance as the master user.

Run the procedure rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces, specifying the tablespaces to import and the directory containing the backups.

The following example imports the tablespaces TBS1, TBS2, and TBS3 from the directory DATA_PUMP_DIR.

BEGIN
  :task_id:=rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces('TBS1,TBS2,TBS3','DATA_PUMP_DIR');
END;
/
PRINT task_id

(Optional) Monitor progress by querying the table rdsadmin.rds_xtts_operation_info. The xtts_operation_state column shows the value EXECUTING, COMPLETED, or FAILED.

SELECT * FROM rdsadmin.rds_xtts_operation_info;

View the log of the completed import by using the task ID from the previous step.

SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||'&task_id'||'.log'));

Make sure that the import succeeded before continuing to the next step.

Take a manual DB snapshot by following the instructions in Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS.

Start your Oracle SQL client and log in to your target DB instance as the master user.

Create the users that own schemas in your transported tablespaces, if these users don't already exist.

CREATE USER tbs_owner IDENTIFIED BY password;

Import the metadata, specifying the name of the dump file and its directory location.

BEGIN
  rdsadmin.rdsadmin_transport_util.import_xtts_metadata('xttdump.dmp','DATA_PUMP_DIR');
END;
/

(Optional) Query the transportable tablespace history table to see the status of the metadata import.

SELECT * FROM rdsadmin.rds_xtts_operation_info;

When the operation completes, your tablespaces are in read-only mode.

(Optional) View the log file.

The following example lists the contents of the BDUMP directory and then queries the import log.

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP'));

SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file(
  p_directory => 'BDUMP',
  p_filename => 'rds-xtts-import_xtts_metadata-2023-05-22.01-52-35.560858000.log'));

Start SQL*Plus or SQL Developer and log in to your target DB instance as the master user.

Validate the tablespaces using the procedure rdsadmin.rdsadmin_rman_util.validate_tablespace.

SET SERVEROUTPUT ON
BEGIN
    rdsadmin.rdsadmin_rman_util.validate_tablespace(
        p_tablespace_name     => 'TBS1',
        p_validation_type     => 'PHYSICAL+LOGICAL',
        p_rman_to_dbms_output => TRUE);
    rdsadmin.rdsadmin_rman_util.validate_tablespace(
        p_tablespace_name     => 'TBS2',
        p_validation_type     => 'PHYSICAL+LOGICAL',
        p_rman_to_dbms_output => TRUE);
    rdsadmin.rdsadmin_rman_util.validate_tablespace(
        p_tablespace_name     => 'TBS3',
        p_validation_type     => 'PHYSICAL+LOGICAL',
        p_rman_to_dbms_output => TRUE);
END;
/

Place your tablespaces in read/write mode.

ALTER TABLESPACE TBS1 READ WRITE;
ALTER TABLESPACE TBS2 READ WRITE;
ALTER TABLESPACE TBS3 READ WRITE;

Remove old backups in DATA_PUMP_DIR as follows:

1. List the backup files by running rdsadmin.rdsadmin_file_util.listdir.

   SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'DATA_PUMP_DIR'));

2. Remove the backups one by one by calling UTL_FILE.FREMOVE.

   EXEC UTL_FILE.FREMOVE ('DATA_PUMP_DIR', 'backup_filename');

If you imported tablespaces but didn't import metadata for these tablespaces, you can delete the orphaned data files as follows:

1. List the orphaned data files that you need to delete. The following example runs the procedure rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files.

   SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files);
   
   FILENAME       FILESIZE
   -------------- ---------
   datafile_7.dbf 104865792
   datafile_8.dbf 104865792

2. Delete the orphaned files by running the procedure rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import.

   BEGIN
     rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import('DATA_PUMP_DIR');
   END;
   /

   The cleanup operation generates a log file that uses the name format rds-xtts-delete_xtts_orphaned_files-YYYY-MM-DD.HH24-MI-SS.FF.log in the BDUMP directory.

3. Read the log file generated in the previous step. The following example reads log rds-xtts-delete_xtts_orphaned_files-2023-06-01.09-33-11.868894000.log.

   SELECT * 
   FROM TABLE(rdsadmin.rds_file_util.read_text_file(
          p_directory => 'BDUMP',
          p_filename  => 'rds-xtts-delete_xtts_orphaned_files-2023-06-01.09-33-11.868894000.log'));
   
   TEXT
   --------------------------------------------------------------------------------
   orphan transported datafile datafile_7.dbf deleted.
   orphan transported datafile datafile_8.dbf deleted.

If you imported tablespaces and imported metadata for these tablespaces, but you encountered compatibility errors or other Oracle Data Pump issues, clean up the partially transported data files as follows:

1. List the tablespaces that contain partially transported data files by querying DBA_TABLESPACES.

   SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE PLUGGED_IN='YES';
   
   TABLESPACE_NAME
   --------------------------------------------------------------------------------
   TBS_3

2. Drop the tablespaces and the partially transported data files.

   DROP TABLESPACE TBS_3 INCLUDING CONTENTS AND DATAFILES;