Full Transportable Export/Import (FTEX)

Introduction

Full Transportable Export/Import (FTEX), introduced in Oracle Database 12c, extends the transportable tablespaces (TTS) feature to enable whole-database migration. The technique combines the performance benefits of physical datafile copying with the flexibility of Data Pump metadata handling. FTEX supports migrations from non-CDB architectures to multitenant PDB destinations, allowing consolidation of hardware migration and multitenant conversion within a single project. The method also handles Transparent Data Encryption (TDE) natively, migrating encrypted tablespaces without additional decryption steps, provided that the migration does not involve cross-endianness conversion.  

It’s helpful to clarify couple of acronyms which will be used frequently later on:

  • User’s objects: Objects created by the applications/Users. This excludes any oracle-managed object belongs to the data dictionary. The same description will be used with “Users’ data” acronym
  • Administrative tablespaces: Tablespaces which hold objects belong to the data dictionary. This Primarily refers to SYSTEM and SYSAUX tablespaces. In this discussion, this acronym will be extended to other tablespaces which hold partial copies of user’s data like UNDO or temporary tablespaces.
  • Non-Administrative tablespaces: Tablespaces hold user’s data
  • Physical Objects: Data-bearing objects, which have physical segments and consequentially datablocks associated with them. Examples for such objects are Tables, partitions, indexes, Lob segments.
  • Logical Objects: Objects which is constructed solely of DDL with no associated datablocks. These objects lives in data dictionary tablespaces. Example of these objects are Views, PLSQL procedures, triggers, database links ….

How it works

Full transportable Export/Import (FTEX) is a variant of transportable tablespace, also a special mode of data pump. It’s initiated by specifying Two parameters (FULL=YES and TRANSPORTABLE=ALWAYS) along with other Data pump export job parameters.

Before discussing how FTEX works, we go briefly through how the predecessors of FTEX (Data Pump, TTS) work since internally it borrows from both techniques, then discuss how it bridges their functionality

Conventional Data Pump export unloads table data using direct path or external tables access methods, storing exported data in Data Pump’s proprietary dump file format. Index structures are not copied, only their DDL definitions are captured as metadata and used for indexes rebuild during import at the destination system. The same goes for other logical objects. Data pump is a logical migration method, this means it handles each object separately, taking into consideration object dependency.

Transportable Tablespaces (TTS) ON the other hand is a physical migration method, where it handles tablespaces’ datafiles. The datafiles will be copied between both systems and data pump will generate a special dump files, which links the physical constructs (segments, extents) to the logical objects structure in dictionary. With that in mind, data transports in a TTS job can be categorized into two groups:

  1. Data stored in non-administrative tablespaces, which hold user’s data. Example for such objects are tables, partitions, indexes, LOB segments .. etc. Other logical objects, with no associated physical segments, like views, procedures, Jobs are stored in the administrative tablespaces, and do not belong to this group. The CONSISTENT datafiles for these non-administrative tablespaces are copied to the destination system.
  2. Metadata related to the transported objects in the earlier groups. This metadata is exported to a special dump file, which will be later imported to the destination system. This dump file can also be directly imported to the destination systems using data pump network mode. This metadata primarily includes the description of the physical objects included in the transported datafiles and also the mapping of these objects to the physical constructs like segments, which are stored in internal dictionary tables like SYS.TAB$, SYS.SEG$ …

Both groups above do not include logical database objects, which are stored in data dictionary. This limitation makes TTS less desirable approach for Full database migrations solutions, since additional handling is necessary for other objects not included in TTS transportable set. On a side note, operationally TTS can be more complex compared to a conventional Data Pump Job.

Full Transportable Export/Import (FTEX) combines strength of both approaches (TTS, Data Pump) providing one solution for a full Database migrations, which also offer a superior performance compared to a conventional Data Pump job. This is achieved by reusing copies of the non-administrative datafiles while leveraging capabilities of data pump to evaluate dependency and inter-play between database objects in different schemas. This careful consideration of the complex relationship between user objects is necessary for a successful full database import.

FTEX Import Set consists of two set of files:

  1. CONSISTENT Copy of non-administrative tablespaces’ datafiles. These files carry physical objects of user’s data like tables, indexes, partitions, and LOBs. This set is exactly the same as the first groups of TTS. getting a consistent version of these datafiles requires putting the database in read-only mode , in case of FTEX. Restart of the database is recommended before starting this process .
  2. Transportable dump file which contains the following:
    1. Objects definitions (DDL) of both physical and logical user objects
    2. Physical objects metadata mapping, describing the physical storage attributes (segments, extents, and datafile layout) that already exist in the transported files, which is equivalent to the groups of TTS.

FTEX can be executed in network mode without intermediate transportable dump files. This mode is initiated by four parameters: FULL=YES, TRANSPORTABLE=ALWAYS, NETWORK_LINK (Database link name to the source database) and TRANSPORT_DATAFILES (list of all datafiles in the transport set). Data pump loads necessary metadata and data directly from source to destination during import. Despite the apparent simplicity of eliminating dump file staging, a dumpfile-based FTEX will deliver superior performance for large, complex databases. The reason for that is network mode import datapump job processes metadata serially through the database link, while a dump-based import job can parallelize metadata processing across multiple worker threads. However, the parallelism algorithm itself considers object dependency and object types, which also leads to idle parallel workers. Therefore, a real life test is necessary to identify the best option for a given environment ( different database model, infra-structure, …. ).

Limitation & Consideration

Note the following limitations in full transportable export/import of the database:

  • General transportable limitations apply
    Standard transportable tablespace constraints also govern FTEX operations, which are discussed here
  • User-defined objects partially stored in administrative tablespaces
    FTEX does not support transporting objects fully or partially stored in administrative tablespaces. Such objects should be migrated separately using conventional Data Pump export or alternatively modified to have all their segments stored in user-defined tablespaces.
  • Audit records in administrative tablespaces are not migrated
    Audit trail data stored in SYSTEM or SYSAUX tablespaces is not transferred through FTEX and must be handled separately, if mandated by internal audit policy .
  • Migration between Platforms with different endianness is not supported
    Source and destination platforms must share identical endianness formats.
    To verify platform compatibility, query V$TRANSPORTABLE_PLATFORM for endianness info of all supported platforms, join with V$database on PLATFORM_ID column to get the database-specific output
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d      WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

FTEX implementation steps

The following steps are applicable for FTEX through dump file:

(1) Prepare the target system

Oracle Home for the target database recommended to be patched with the latest RU patches. Moreover, Database options, features and configurations need to match the source database. More details are discussed here under general transportable article.

(2) Set user-defined tablespaces to read only

It is recommended to perform a database restart before initiating FTEX Export / import. This ensures that dirty buffers in memory are flushed to disk, in-flight transactions are committed, and releases any rollback segments.
Set user-managed tablespaces to read-only mode before initiating the export.

Generate a script to set user tablespaces in read-only mode.

SQL> ALTER TABLESPACE tbs_1 READ ONLY;

(3) Export configuration

Configure Export parameter file with the parameters (TRANSPORTABLE=ALWAYS, FULL=Y) to initiate Full transportable mode. If source database contains encrypted tablespaces or tables with encrypted columns, specify either ENCRYPTION_PWD_PROMPT=YES or the ENCRYPTION_PASSWORD parameter.

The generated Dump file will contain metadata for user defined objects in non-Admin tablespace and also data and metadata from admin tablespaces (SYSTEM, SYSAUX).

The following is an example parameter file for a FTEX Export job , which generates transportable dump file:

full=ytransportable=alwaysdumpfile=ftex_exp.dmpdirectory=dpump_dirmetrics=y
exclude=statistics
parallel=4
encryption_password=TheP@ssw0rd321645 #necessary for TDE
logfile=FTEX_export.log

(4) Export execution

The SYSTEM user or any user granted the DATAPUMP_EXP_FULL_DATABASE privilege can initiate the export via a Data Pump connection to the source PDB. It’s preferred to configure the DIRECTORY parameter to an NFS mount accessible from both source and destination systems. This eliminates the need for intermediate file transfers. Otherwise, simply copy the dump file to the destination system after export completes.

Ensure export completed successfully by carefully checking errors/warning in the export log file. Moreover, Note (and verify) the list of exported datafiles. This list will be used by the import job at the target system

(5) Copy Datafiles of non-administrative tablespaces

After datafiles of the non-administrative tablespace have been placed in read-only mode in step (2) above, they can be safely copied to the target system. copying files between ASM on different host are discussed in MOS KB82495.

Note: Datafile of Administrative tablespace (SYSTEM, SYSAUX, Undo, temporary) do not need to (and cannot) be transported, they’re recreated at the target system. FTEX import job connects to a target database (PDB), where these administrative tablespaces already exist.

(6) Transportable Import

SYSTEM user or any user granted the DATAPUMP_IMP_FULL_DATABASE privilege can initiate the import via a Data Pump connection to the destination PDB. Configure Import parameter file with the parameters FULL=Y  and TRANSPORT_DATAFILES with the full list of datafiles exported. Refer to the export datapump for the datafile list.

The following is an example for data pump parameter file for Full transportable Import, which loads the transportable set exported earlier:

full=y
dumpfile=ftexexp.dmp
directory=dpump_dir
TRANSPORT_DATAFILES=
'/u01/app/oracle/oradata/mydb/sales01.dbf',
'/u01/app/oracle/oradata/mydb/cust01.dbf',
'/u01/app/oracle/oradata/mydb/emp01.dbf'
logtime=all
logfile=FTEX_import.log

FTEX through database Link

FTEX through Network link requires simpler steps as follows:

(1) Prepare the target system

(2) Set user-defined tablespaces to read only

(3) Copy Datafiles of non-administrative tablespaces

(4) Import configuration

Configure Import parameter file with the following 4 parameters

  • FULL=Y
  • TRANSPORTABLE=ALWAYS
  • NETWORK_LINK with the DB_link to be used for Import
  • TRANSPORT_DATAFILES parameter with full list of datafiles

The following is an example parameter file for a FTEX import job which loads the transportable set directly from the source database through database link:

full=y
network_link=sourcedb 
transportable=always
transport_datafiles=
'/u01/app/oracle/oradata/mydb/sales01.dbf',
'/u01/app/oracle/oradata/mydb/cust01.dbf',
'/u01/app/oracle/oradata/mydb/emp01.dbf'
exclude=statistics
logtime=all
logfile=FTEX_import.log

## for TDE tablesace 
encryption_password=TheEncryptionP@ssw0rd01032026
# or use
encryption_pwd_prompt=yes