Practical Steps to Extract Transportable Tablespaces with RMAN and Import via SQL Script

— Smart Migration using Automatically Generated Scripts instead of Data Pump —

Oracle’s Transportable Tablespace (TTS) is the optimal solution for large-scale data migration. But did you know there is a way to import them without using Data Pump (impdp)?

When you use the RMAN TRANSPORT TABLESPACE command, an SQL script for importing metadata (impscrpt.sql) is automatically generated. By utilizing this, you can perform a SQL-based import of the state at a specific point in the past (UNTIL TIME) without ever having to put the source tablespaces into READ ONLY mode.

✅ Advantages of This Method
🛠 Workflow of Execution Steps
Step 1: Outputting Transportable Tablespace with RMAN
Step 2: Copy Files to the Destination
Step 3: Execute Import via SQL*Plus
1. 【Reference: Import Script Contents】
❓ FAQ: Practical Questions
📝 Operational Notes
🏁 Summary

✅ Advantages of This Method

Item	Content
Operational Flexibility	No need to set tablespaces to `READ ONLY` (RMAN processes this using an auxiliary instance).
No Data Pump Required	Simply execute the RMAN-generated `impscrpt.sql` from SQL*Plus to complete the process.
Point-in-Time Recovery	By specifying `UNTIL TIME`, you can extract data as it was yesterday or before a specific patch was applied.
Automatic Conversion	RMAN automatically handles endian conversion during cross-platform migrations.

🛠 Workflow of Execution Steps

Extract with RMAN: Generate the data files and the SQL import script.
File Transfer: Copy the generated files to the target server.
Execute SQL: Run impscrpt.sql to attach the tablespace.

Step 1: Outputting Transportable Tablespace with RMAN

First, perform the extraction using RMAN. In this example, we use UNTIL TIME to extract the state as it was one minute ago.

rman target /

RMAN> TRANSPORT TABLESPACE test_tbs
      TABLESPACE DESTINATION '/backup/tbs_transport'
      AUXILIARY DESTINATION '/backup/aux'
      DATAPUMP DIRECTORY dpump_dir
      DUMP FILE 'test_tbs_meta.dmp'
      EXPORT LOG 'rman_test_tbs.log'
      UNTIL TIME 'SYSDATE-1/1440';

This command outputs the following files to /backup/tbs_transport:

test_tbs.dbf (Data file)
test_tbs_meta.dmp (Metadata dump – used internally by the script)
impscrpt.sql (The main feature: SQL script for importing)
rman_test_tbs.log (Export log)

Detailed Execution Log:

RMAN> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST_TBS                       ONLINE

RMAN> TRANSPORT TABLESPACE test_tbs
2> TABLESPACE DESTINATION '/backup/tbs_transport'
3> AUXILIARY DESTINATION '/backup/aux'
4> DATAPUMP DIRECTORY dpump_dir
5> DUMP FILE 'test_tbs_meta.dmp'
6> EXPORT LOG 'rman_test_tbs.log'
7> UNTIL TIME 'SYSDATE-1/1440';

Current log archived.
RMAN-05026: WARNING: the following set of tablespaces will be applied to the specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance (SID='cBsr')

initialization parameters used for automatic instance:
db_name=V19
db_unique_name=cBsr_pitr_V19
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=1472M
processes=200
db_create_file_dest=/backup/aux
log_archive_dest_1='location=/backup/aux'
#No auxiliary parameter file used


starting up automatic instance V19

Oracle instance started

Total System Global Area     1543500120 bytes

Fixed Size                     8925528 bytes
Variable Size                369098752 bytes
Database Buffers            1157627904 bytes
Redo Buffers                   7847936 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "SYSDATE-1/1440";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-03-29
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=37 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250329-01
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250329-01 tag=TAG20250329T203629
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/backup/aux/V19/controlfile/o1_mf_myhq1fof_.ctl
Finished restore at 25-03-29

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "SYSDATE-1/1440";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "/backup/tbs_transport/test_tbs.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 5;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /backup/aux/V19/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 25-03-29
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /backup/aux/V19/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /backup/aux/V19/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /backup/aux/V19/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /backup/tbs_transport/test_tbs.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/023ljblu_2_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/023ljblu_2_1_1 tag=TAG20250329T203614
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-03-29

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1197060050 file name=/backup/aux/V19/datafile/o1_mf_system_myhq1mq9_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1197060050 file name=/backup/aux/V19/datafile/o1_mf_undotbs1_myhq1mqq_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1197060050 file name=/backup/aux/V19/datafile/o1_mf_sysaux_myhq1mql_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1197060050 file name=/backup/tbs_transport/test_tbs.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "SYSDATE-1/1440";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TEST_TBS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  5 online

Starting recover at 25-03-29
using channel ORA_AUX_DISK_1

executing: alter database datafile 7 offline
starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_14_1153872185.dbf
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_14_1153872185.dbf thread=1 sequence=14
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-03-29

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TEST_TBS read only';
}
executing Memory Script

sql statement: alter tablespace  TEST_TBS read only

Exporting metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_cBsr_aDxa":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_cBsr_aDxa" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_cBsr_aDxa is:
   EXPDP>   /u01/app/oracle/dpump/test_tbs_meta.dmp
   EXPDP> ******************************************************************************
   EXPDP> Transportable tablespace TEST_TBS requires datafiles:
   EXPDP>   /backup/tbs_transport/test_tbs.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_cBsr_aDxa" successfully completed at Sat Mar 29 20:41:35 2025 elapsed 0 00:00:30
Export completed

Not performing table import after point-in-time recovery
/*
   The following command may be used to import the tablespaces.
   Substitute values for <logon> and <directory>.

   impdp <logon> directory=<directory> dumpfile= 'test_tbs_meta.dmp' transport_datafiles= /backup/tbs_transport/test_tbs.dbf
*/

--
--
--
--
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/backup/tbs_transport/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
--
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;

--
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;

--
  ts_names       dbms_streams_tablespace_adm.tablespace_set;
BEGIN
--
  dump_file.file_name :=  'test_tbs_meta.dmp';
  dump_file.directory_object := 'dpump_dir';

--
  tbs_files( 1).file_name :=  'test_tbs.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';

--
  dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);

--
  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line('imported tablespace '|| ts_names(i));
    END LOOP;
  END IF;
END;
/

--
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

Removing automatic instance
Automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /backup/aux/V19/datafile/o1_mf_temp_myhq29yp_.tmp deleted
auxiliary instance file /backup/aux/V19/onlinelog/o1_mf_3_myhq25w3_.log deleted
auxiliary instance file /backup/aux/V19/onlinelog/o1_mf_2_myhq24gm_.log deleted
auxiliary instance file /backup/aux/V19/onlinelog/o1_mf_1_myhq248h_.log deleted
auxiliary instance file /backup/aux/V19/datafile/o1_mf_sysaux_myhq1mql_.dbf deleted
auxiliary instance file /backup/aux/V19/datafile/o1_mf_undotbs1_myhq1mqq_.dbf deleted
auxiliary instance file /backup/aux/V19/datafile/o1_mf_system_myhq1mq9_.dbf deleted
auxiliary instance file /backup/aux/V19/controlfile/o1_mf_myhq1fof_.ctl deleted

RMAN> exit


Recovery Manager complete.
[oracle@v19single ~]$

Step 2: Copy Files to the Destination

Transfer the generated files to the target environment using scp or a similar tool.

scp /backup/tbs_transport/* oracle@target:/backup/tbs_transport/

Step 3: Execute Import via SQL*Plus

On the target DB side, execute the impscrpt.sql file created by RMAN. This script internally calls dbms_streams_tablespace_adm.attach_tablespaces to let the DB recognize the physical files.

sqlplus / as sysdba

-- Check current status
SQL> SELECT tablespace_name, status FROM dba_tablespaces;

-- Execute the import script
SQL> @/backup/tbs_transport/impscrpt.sql

-- The tablespace is READ ONLY immediately after import, so change to READ WRITE
SQL> ALTER TABLESPACE test_tbs READ WRITE;

-- Final verification
SQL> SELECT tablespace_name, status FROM dba_tablespaces;

Target DB Verification Log:

[oracle@v19single ~]$ ls -l /u01/app/oracle/dpump
total 156
-rw-r--r--. 1 oracle oinstall    839 Mar 29 20:41 rman_test_tbs.log
-rw-r--r--. 1 oracle oinstall 155648 Mar 29 20:41 test_tbs_meta.dmp

[oracle@v19single ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 29 21:06:42 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> set lin 1000 pages 1000
SQL> col tablespace_name for a20
SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME      STATUS
-------------------- ---------------------------
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE

SQL> @/backup/tbs_transport/impscrpt.sql  ★Execute import script

Directory created.

PL/SQL procedure successfully completed.

Directory dropped.

SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME      STATUS
-------------------- ---------------------------
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE
TEST_TBS             READ ONLY

6 rows selected.

SQL> ALTER TABLESPACE test_tbs READ WRITE;

Tablespace altered.

SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME      STATUS
-------------------- ---------------------------
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE
TEST_TBS             ONLINE

6 rows selected.

【Reference: Import Script Contents】

[oracle@v19single ~]$ cat /backup/tbs_transport/impscrpt.sql
/*
   The following command may be used to import the tablespaces.
   Substitute values for <logon> and <directory>.

   impdp <logon> directory=<directory> dumpfile= 'test_tbs_meta.dmp' transport_datafiles= /backup/tbs_transport/test_tbs.dbf
*/

CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/backup/tbs_transport/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;
  ts_names      dbms_streams_tablespace_adm.tablespace_set;
BEGIN
  dump_file.file_name :=  'test_tbs_meta.dmp';
  dump_file.directory_object := 'dpump_dir';

  tbs_files( 1).file_name :=  'test_tbs.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';

  dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);

  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line('imported tablespace '|| ts_names(i));
    END LOOP;
  END IF;
END;
/

DROP DIRECTORY STREAMS$DIROBJ$1;

❓ FAQ: Practical Questions

Q: Why is this method without impdp convenient?

A: While impdp is feature-rich, it requires directory definitions and complex parameter specifications at runtime. The RMAN-generated SQL script already contains the import logic optimized for that specific extraction. For simple “attach” tasks, this simplifies the procedure and reduces human error.

Q: Are backups necessary when using UNTIL TIME?

A: Yes. Backups of the data files and archive logs up to the desired extraction time are mandatory. This is because RMAN launches an “auxiliary instance” behind the scenes to perform a temporary recovery before extraction.

Q: Where should I look if an error occurs during execution?

A: Check the RMAN execution log (rman_test_tbs.log) and the alert log of the auxiliary instance. In many cases, the cause is insufficient space in the /backup/aux area or temporary memory shortages.

📝 Operational Notes

Schema Pre-verification: Ensure the target database has a user with the same name and the necessary privileges before importing.
Directory Paths: Verify that the paths written in impscrpt.sql match the actual paths in the target environment, and edit them if necessary.
Cleanup: Safely delete the metadata dump and scripts remaining in /backup/tbs_transport after completion.

🏁 Summary

Migration via RMAN + impscrpt.sql is a highly rational method that combines RMAN’s powerful recovery features (allowing point-in-time extraction) while bypassing the manual effort required by Data Pump.

This technique proves its true value in scenarios where a DBA wants to perform reliable work via scripts or when you need to extract data without impacting the production environment (i.e., without setting it to READ ONLY).

[reference]
Oracle Database Utilities, 19c

Oracle Data Pump Complete Guide: Architecture, Usage, SCOTT Schema Setup, and Execution Examples

Oracle Data Pump is a powerful utility for high-speed data export and import operations. This guide provides a complete …