Oracle Transportable Tablespaces with Data Pump

Transportable tablespaces move user tablespace data files plus a Data Pump metadata dump. They can be much faster than unloading and reloading large data sets, but the tablespace set must be self-contained and compatible with the target.

1. Check prerequisites and containment

BEGIN
  DBMS_TTS.TRANSPORT_SET_CHECK(
    ts_list          => 'APP_DATA,APP_INDEX',
    incl_constraints => TRUE,
    full_check       => TRUE
  );
END;
/

SELECT * FROM transport_set_violations;

The violations query must return no rows. Also compare database compatibility, character sets, time-zone file requirements, target users and roles, encryption, and platform endianness. Administrative tablespaces such as SYSTEM and SYSAUX cannot be part of a normal transportable set.

2. Make the source tablespaces read-only

ALTER TABLESPACE app_data  READ ONLY;
ALTER TABLESPACE app_index READ ONLY;

Re-run the containment check after application writes have stopped.

3. Export metadata with Data Pump

expdp system   DIRECTORY=DP_DIR   DUMPFILE=app_tts.dmp   LOGFILE=app_tts_export.log   TRANSPORT_TABLESPACES=APP_DATA,APP_INDEX

Use an Oracle directory object that points to secured storage. Do not put a password on the command line.

4. Copy and convert the data files

Copy the dump and all listed tablespace data files to the target using a verified transfer. Compare checksums. If source and target have different endian formats, use a supported conversion method such as RMAN CONVERT before import.

5. Import metadata at the target

impdp system   DIRECTORY=DP_DIR   DUMPFILE=app_tts.dmp   LOGFILE=app_tts_import.log   TRANSPORT_DATAFILES='/u02/oradata/app_data01.dbf','/u02/oradata/app_index01.dbf'

Create required users, roles, directory objects, and security policies as appropriate. Review the import log rather than treating a completed process as proof that every object was handled.

6. Validate and open for writes

ALTER TABLESPACE app_data  READ WRITE;
ALTER TABLESPACE app_index READ WRITE;

Validate object counts, invalid objects, application access, constraints, and recovery. Restore the source tablespaces to read/write only if the migration plan calls for it. For encrypted tablespaces or cross-platform moves, review the exact limitations for the installed Oracle release before the outage.