Description:-
- In this article we are going to see the Oracle 18c Datapump-TRANSPORT_DATAFILES Parameter
TRANSPORT_DATAFILES :
It specifies a list of data files to be imported into the target database by a transportable-tablespace mode import.
During the export we need to take dump using these below parameters to use TRANSPORT_DATAFILES in import operation.
TRANSPORT_TABLESPACES=tablespace_name (or) TRANSPORTABLE=ALWAYS
- If you already have a dump file set generated by a transportable-tablespace mode export, then you can perform a transportable-mode import of that dump file, by specifying the dump file (which contains the metadata) and the TRANSPORT_DATAFILES parameter. The presence of the TRANSPORT_DATAFILES parameter tells import that it is a transportable-mode import and where to get the actual data.
- Before the import operation, you must copy the data files from the source system to the target system
- The datafile_name must include an absolute directory path specification that is valid on the system where the target database resides.
Syntax:
TRANSPORT_DATAFILES=datafile_name
SOURCE :-
- Keep USERSDEMO tablespace in Read only mode.
SQL> alter tablespace USERSDEMO read only;
Tablespace altered.
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------
/u01/app/oracle/oradata/PROD18C/system01.dbf
/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
/u01/app/oracle/oradata/PROD18C/sysaux01.dbf
/u01/app/oracle/oradata/PROD18C/undotbs01.dbf
/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
/u01/app/oracle/oradata/PROD18C/users01.dbf
/u01/app/oracle/oradata/PROD18C/usersdemo.dbf
- Manually copy the datafile to the test18c server.
[oracle@18c empdata]$ scp /u01/app/oracle/oradata/PROD18C/usersdemo.dbf oracle@192.168.1.105:/u01/app/oracle/oradata/TEST/datafile
usersdemo.dbf 100% 2048MB 27.3MB/s 01:15
- Export the metadata of the tablespace USERSDEMO.
[oracle@18c empdata]$ expdp DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=test_dir TRANSPORT_TABLESPACES=USERSDEMO Export: Release 18.0.0.0.0 - Production on Thu Aug 23 23:07:59 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=test_dir TRANSPORT_TABLESPACES=USERSDEMO Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS 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 Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/COMMENT Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/empdata/tts.dmp ****************************************************************************** Datafiles required for transportable tablespace USERSDEMO: /u01/app/oracle/oradata/PROD18C/usersdemo.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 23 23:08:48 2018 elapsed 0 00:00:42
The metadata only (no data) for USERSDEMO tablespace is exported to a dump file named tts.dmp. The actual data was copied over to the target database using SCP.
TARGET (test) :-
SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%USERS%'; TABLESPACE_NAME ----------------- USERS
- Perform a transportable tablespace import by mentioning the copied datafile location.
[oracle@test18c empdata]$ impdp system/oracle directory=TEST_DIR DUMPFILE=tts.dmp transport_datafiles='/u01/app/oracle/oradata/TEST/datafile/usersdemo.dbf' logfile=tts_impdp.log
Import: Release 18.0.0.0.0 - Production on Thu Aug 23 23:10:36 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=TEST_DIR DUMPFILE=tts.dmp transport_datafiles=/u01/app/oracle/oradata/TEST/datafile/usersdemo.dbf logfile=tts_impdp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 23 23:10:58 2018 elapsed 0 00:00:21
- Check the tablespace is present in target side
SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%USERS%'; TABLESPACE_NAME ------------------------------ USERS USERSDEMO
- Make the tablespace in READ/WRITE mode.
SQL> alter tablespace USERSDEMO read write; Tablespace altered.
- Check the status of tablespace
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERSDEMO'; TABLESPACE_NAME STATUS --------------- --------- USERSDEMO ONLINE
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba