Transporting tablespaces - Amazon Relational Database Service
Importing transported tablespacesImporting transportable tablespace metadataListing orphaned filesDeleting orphaned data files
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Transporting tablespaces

Use the Amazon RDS package rdsadmin.rdsadmin_transport_util to copy a set of tablespaces from an on-premises Oracle database to an RDS for Oracle DB instance. At the physical level, the transportable tablespace feature incrementally copies source data files and metadata files to your target instance. You can transfer the files using either Amazon EFS or Amazon S3. For more information, see Migrating using Oracle transportable tablespaces.

Importing transported tablespaces to your DB instance

Use the procedure rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces to restore tablespaces that you have previously exported from a source DB instance. In the transport phase, you back up your read-only tablespaces, export Data Pump metadata, transfer these files to your target DB instance, and then import the tablespaces. For more information, see Phase 4: Transport the tablespaces.

Syntax

FUNCTION import_xtts_tablespaces(
    p_tablespace_list IN CLOB,
    p_directory_name  IN VARCHAR2,
    p_platform_id     IN NUMBER DEFAULT 13,
    p_parallel        IN INTEGER DEFAULT 0) RETURN VARCHAR2;

Parameters

Parameter name Data type Default Required Description

p_tablespace_list

CLOB

—

Yes

The list of tablespaces to import.

p_directory_name

VARCHAR2

—

Yes

 The directory that contains the tablespace backups.

p_platform_id

NUMBER

13

No

Provide a platform ID that matches the one specified during the backup phase. To find a list of platforms, query V$TRANSPORTABLE_PLATFORM. The default platform is Linux x86 64-bit, which is little endian.

p_parallel

INTEGER

0

No

The degree of parallelism. By default, parallelism is disabled.

Examples

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

Importing transportable tablespace metadata into your DB instance

Use the procedure rdsadmin.rdsadmin_transport_util.import_xtts_metadata to import transportable tablespace metadata into your RDS for Oracle DB instance. During the operation, the status of the metadata import is shown in the table rdsadmin.rds_xtts_operation_info. For more information, see Step 5: Import tablespace metadata on your target DB instance.

Syntax

PROCEDURE import_xtts_metadata(
    p_datapump_metadata_file IN SYS.DBA_DATA_FILES.FILE_NAME%TYPE,
    p_directory_name         IN VARCHAR2,
    p_exclude_stats          IN BOOLEAN DEFAULT FALSE,
    p_remap_tablespace_list  IN CLOB DEFAULT NULL,
    p_remap_user_list        IN CLOB DEFAULT NULL);

Parameters

Parameter name Data type Default Required Description

p_datapump_metadata_file

SYS.DBA_DATA_FILES.FILE_NAME%TYPE

—

Yes

The name of the Oracle Data Pump file that contains the metadata for your transportable tablespaces.

p_directory_name

VARCHAR2

—

Yes

The directory that contains the Data Pump file.

p_exclude_stats

BOOLEAN

FALSE

No

Flag that indicates whether to exclude statistics.

p_remap_tablespace_list

CLOB

NULL

No

A list of tablespaces to be remapped during the metadata import. Use the format from_tbs:to_tbs. For example, specify users:user_data.

p_remap_user_list

CLOB

NULL

No

A list of user schemas to be remapped during the metadata import. Use the format from_schema_name:to_schema_name. For example, specify hr:human_resources.

Examples

The example imports the tablespace metadata from the file xttdump.dmp, which is located in directory DATA_PUMP_DIR.

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

Listing orphaned files after a tablespace import

Use the rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files procedure to list data files that were orphaned after a tablespace import. After you identify the data files, you can delete them by calling rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import.

Syntax

FUNCTION list_xtts_orphan_files RETURN xtts_orphan_files_list_t PIPELINED;

Examples

The following example runs the procedure rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files. The output shows two data files that are orphaned.

SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files);

FILENAME       FILESIZE
-------------- ---------
datafile_7.dbf 104865792
datafile_8.dbf 104865792

Deleting orphaned data files after a tablespace import

Use the rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files procedure to delete data files that were orphaned after a tablespace import. Running this command 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. Use the procedure rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import to find the orphaned files. You can read the log file by calling the procedure rdsadmin.rds_file_util.read_text_file. For more information, see Phase 6: Clean up leftover files.

Syntax

PROCEDURE cleanup_incomplete_xtts_import(
    p_directory_name IN VARCHAR2);

Parameters

Parameter name Data type Default Required Description

p_directory_name

VARCHAR2

—

Yes

The directory that contains the orphaned data files.

Examples

The following example deletes the orphaned data files in DATA_PUMP_DIR.

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

The following example reads the log file generated by the previous command. 

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.