Oracle 21c Enhancements for TTS Export/Import

In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail.

Improvement 1: Parallel Execution of TTS Export/Import

In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

ORA-39002: invalid operation

ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams.

Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import:

[oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check=y parallel=2

Export: Release 21.0.0.0.0 – Production on Tue Oct 12 06:48:54 2021

Version 21.3.0.0.0

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Master table “USEF”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for USEF.SYS_EXPORT_TRANSPORTABLE_01 is:

  /home/oracle/TTS_DUMP01.dmp

  /home/oracle/TTS_DUMP02.dmp

******************************************************************************

Datafiles required for transportable tablespace TBS1:

/oracle21c/base/oradata/DB21C/CD8420A04746726FE0530488200ADFC8/datafile/o1_mf_tbs1_jpbro11o_.dbf

Datafiles required for transportable tablespace TBS2:

/oracle21c/base/oradata/DB21C/CD8420A04746726FE0530488200ADFC8/datafile/o1_mf_tbs2_jpbro6x9_.dbf

Job “USEF”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at Tue Oct 12 06:49:17 2021 elapsed 0 00:00:16

Improvement 2: Resume Functionality for Interrupted TTS Export/Import

In Oracle 19c, if a TTS export/import operation was interrupted abnormally, the entire process had to be restarted as resuming was not supported:

[oracle@oLinux7 ~]$expdp  attach=SYS_EXPORT_TRANSPORTABLE_01

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

ORA-39002: invalid operation

ORA-39025: jobs of type TRANSPORTABLE are not restartable

This limitation has been resolved in Oracle 21c. Here's how resuming works in the updated version:

–session 1:

[oracle@oLinux7 ~]$ expdp usef/a@192.168.56.4:1521/pdb1 directory=drm dumpfile=tbl_dmp_resume.dmp transport_tablespaces=tbs1,tbs2 transport_full_check=y reuse_dumpfiles=true

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

Starting “USEF”.”SYS_EXPORT_TRANSPORTABLE_01″

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

–session 2:

SQL> alter system kill session ‘382,50546’ immediate;

ORA-00031: session marked for kill


[oracle@oLinux7 ~]$  expdp  attach=SYS_EXPORT_TRANSPORTABLE_01

Export: Release 21.0.0.0.0 – Production on Tue Oct 12 07:25:50 2021

Version 21.3.0.0.0

Export> status

Job: SYS_EXPORT_TRANSPORTABLE_01

  Operation: EXPORT                        

  Mode: TRANSPORTABLE                 

  State: IDLING                        

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Job heartbeat: 4

  Dump File: /home/oracle/tbl_dmp_resume.dmp

    bytes written: 28,672

  Worker 1 Status:

  Instance ID: 1

  Instance name: db21c

  Host name: oLinux7

  Object start time: Tuesday, 12 October, 2021 7:23:42

  Object status at: Tuesday, 12 October, 2021 7:23:42

  Process Name: DW00

  State: UNDEFINED 

Export> START_JOB

Export> CONTINUE_CLIENT

Job SYS_EXPORT_TRANSPORTABLE_01 has been reopened at Tue Oct 12 07:25:56 2021

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table “USEF”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for USEF.SYS_EXPORT_TRANSPORTABLE_01 is:

  /home/oracle/tbl_dmp_resume.dmp

Job “USEF”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at Tue Oct 12 07:30:28 2021 elapsed 0