Speed Up Schema Copy with Transportable Tablespaces

Using Transportable Tablespace is a way to speed schema copy vs. regular Datapump. The drawback is that it does copy only what's inside a Tablespace :

 

1. Tables : Heap , IOT , Partitioned tables — only if all partitions/subpartitions are in the transported set  

2. Indexes : B‑tree indexes Bitmap indexes Function‑based indexes Index partitions/subpartitions 

3. LOB segments            LOB data segments LOB index segments

4. Materialized view logs : If their segments reside in the tablespace.

5. Object types stored as segments : Object tables, Nested tables (if their storage tables are in the same tablespace)

6. Any other segment‑based objects : Clusters , Hash clusters , SecureFiles segments

The others objects ( = Schema Metadata ) will have to be taken care of, using another method : PL/SQL , Views …

Copy Duration depends on the size of Datafiles to be transferred.

For our example : Schema to be copied = HHR ; Tablespaces List : TS_01,TS_02,TS_03

Pre-requisite : self-contained TS

 

CONN / AS SYSDBA

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TS_01,TS_02,TS_03', incl_constraints => TRUE);

SELECT * FROM transport_set_violations;

è  No rows : OK

A] Copy whole Oracle DB Schema with Downtime ( Read-Only on source TS )

In this part, we are using using

-          Transportable Tablespace for Data

-          and Datapump for Schema Metadata.

Downtime on source DB is allowed, as the Tablespaces will be put into READ-ONLY mode.

Schema = HHR ; Tablespaces List : TS_01,TS_02,TS_03

 

A.1] Copy DATA using TT

1.1 Source Server

Put TS in Read-Only

select 'alter tablespace '||tablespace_name||' READ ONLY ; ' from dba_tablespaces

where tablespace_name in ( 'TS_01','TS_02','TS_03' ) order by 1

Export the Tablespace Metadata

 

expdp system/$psw directory=DP_DIR transport_tablespaces= TS_01,TS_02,TS_03

dumpfile=HHR.TT.dmp logfile=e_HHR.TT.log

Copy the datafiles to the appropriate location on the destination database server ( and copy also the dump file ).

with DBF as (select file_name from dba_data_files where tablespace_name in

( 'TS_01','TS_02','TS_03' ) )

select 'scp -p '||file_name||' <dest_server>:'||replace(file_name,'<source_db>','<target_db>/<dest_path>')||' ' from DBF

order by 1

1.2 Target Server : import

time impdp system/$psw@<target_pdb> directory=DP_DIR dumpfile=HHR.TT.dmp logfile=HHR.TT.log \

transport_datafiles='/data/<target_db>/<dest_path>/TS_01.dbf','/data/<target_db>/<dest_path>/TS_02.dbf','/data/<target_db>/<dest_path>/TS_03.dbf'

Switch the new tablespace into read write mode

sqlplus system/$psw@<target_pdb>

ALTER TABLESPACE TS_01 READ WRITE;

ALTER TABLESPACE TS_02 READ WRITE;

ALTER TABLESPACE TS_03 READ WRITE;

A.2] Copy non-DATA using Datapump ( Schema Metadata )

Connect to source DB to export the Schema Metadata

expdp system/$psw SCHEMAS=HHR directory=DP_DIR dumpfile= HHR.DDL.dmp logfile=e_ HHR.DDL.log parallel=2 EXCLUDE=TABLE EXCLUDE=INDEX flashback_time=systimestamp

Connect to target DB

time impdp system/$psw@<dest_PDB>  directory=DP_DIR dumpfile=HHR.DDL.dmp logfile=i_HHR.DDL.log parallel=2

B] Copy whole Oracle DB Schema with NO-Downtime using

Source PDB = PDB1 , Schema = HHR ; Tablespaces List : TS_01

In the following, we still use Transportable Tablespace, but will put Tablespaces in READ-ONLY not on the source DB, but

-          (1) In an Auxilliary Instance created with RMAN from backups

-          (2) In the Standby Database, if we have one.

B.1] using RMAN

Method (1) failed as RMAN needs to restore also the XDB Tablespace to be able to export the Transportable Tablespace metadata. We got the error :

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-00376: file 191 cannot be read at this time
ORA-01110: data file 191: '/data/<pdb>/xdb_01.dbf'

When adding the XDB TS in the list to export, it is working, but it is unacceptable to import the XDB TS on Target DB !

 

connect target *
connect catalog *
 
CONFIGURE DEFAULT DEVICE TYPE TO sbt_tape;
CONFIGURE DEVICE TYPE sbt_tape PARALLELISM 8;
CONFIGURE CHANNEL DEVICE TYPE sbt_tape PARMS='SBT_LIBRARY=/opt/commvault/Base64/libobk.a(shr.o),BLKSIZE=1048576';
 
run {
 
 set until time "to_date('14-APR-2026 21:15:00','DD-MON-YYYY HH24:MI:SS')";
 
transport tablespace PDBNAME:TS_01,PDBNAME:XDB
tablespace destination '/RMAN_NFS/PPR/cdbname/TSPITR'
auxiliary destination  '/RMAN_NFS/PPR/cdbname/TSPITR'
;
 
}

B.2] Using Dataguard

 Method (2) worked as follow :

 

Standby DB is activated as Primary to allow the TT Export. Then it is rewinded to the Restore point created before activation.

In our test case, we will export Data of the HHR schema from PDB1 ( Tablespace : TS_01 ).

There is a Standby DB : PDB1S that will be used to Transportable Tablespace export.

2.0 Setting up a FRA Flash Recovery Area

$ sqlplus / as sysdba
 
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
SQL> alter system set db_recovery_file_dest='/arch/<db_name>/fra';
System altered.

 Default value for Retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440 

2.1 Pre-requisite

Standby DB

$ sqlplus / as sysdba

                Cancel the MRP(Managed recover process).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
                Create guaranteed restore points(GRP) on standby.
SQL> CREATE RESTORE POINT GRP_TT GUARANTEE FLASHBACK DATABASE; 

Primary DB

 
$ sqlplus / as sysdba
 
                On Primary defer the log_archive_dest_2, which is sending logs to standby

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

2.2 Export on Standby DB PROD
 
Previous Standby State was « OPEN READ ONLY »
 
                Activate and open the standby database.
 
$ sqlplus / as sysdba
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
 
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
 
SQL> ALTER DATABASE OPEN;
Database altered.
 
SQL> alter pluggable database PDB1 open ;
Pluggable database altered.
 
                Place the tablespace in read only mode.
 
$ sqlplus system/${psw}@PDB1S
SQL> alter tablespace TS_01 read only;

 
                Do the TS Metadata export, and copy files. 

 
$ expdp system/${psw}@PDB1S dumpfile=PDB1.HHR.TTS.dmp directory=DP_DIR transport_tablespaces=TS_01 transport_full_check=y
 
Datafiles required for transportable tablespace TS_01:
  /data/<pdbs>/ts_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Apr 30 10:30:26 2026 elapsed 0 00:15:43
 
$ scp -p /data/<pdbs>/ts_01.dbf <target server path>

2.3 Post-Action

Standby DB

                Place the standby database in mount mode again.

SQL> alter system set dg_broker_start = FALSE ;
System altered.
 
SQL> SHUTDOWN IMMEDIATE
ORACLE instance shut down.
 
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area 1912572976 bytes
Fixed Size                  9342000 bytes
Variable Size            1191182336 bytes
Database Buffers          704643072 bytes
Redo Buffers                7405568 bytes
Database mounted.
 

                Flashback to GRP

 
SQL> FLASHBACK DATABASE TO RESTORE POINT GRP_TT ;
Flashback complete.
 
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

                Start up the MRP again if not yet.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT USING CURRENT LOGFILE;

Database altered.
 
SQL> alter system set dg_broker_start = TRUE ;
System altered.
 
SQL> select open_mode from v$database ;
OPEN_MODE
--------------------
MOUNTED

                Open READ ONLY Access

 
SQL> alter database open read only;
Database altered. 

                Drop guaranteed restore points(GRP): 

SQL> DROP RESTORE POINT GRP_TT ;
Restore point dropped.
 

Primary DB

                Enable the log_archive_dest_2 on primary

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

2.4 Import at Target

                Import TTS metadata. 

$ impdp system/${psw}@<targetPDB> DUMPFILE=PDB1.HHR.TTS.dmp DIRECTORY=DP_DIR TRANSPORT_DATAFILES='/data/<pdbs>/ts_01.dbf' 

                Put Plugged Tablespace in RW Mode 

sqlplus system/$psw@<target_pdb>

ALTER TABLESPACE TS_01 READ WRITE;