Why use Transportable Tablespaces (TTS)?
- Up through 11gR2, “Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases.” Starting with 12c, you may find the full transportable feature more efficient for moving source databases with RDBMS version 11.2.0.3 and up to 12c. “Full transportable is more automated than transportable tablespaces because it moves the metadata and user data that resides in non-transportable tablespaces than would previously have been moved in multiple operations. This makes the full transportable feature useful for efficiently moving a database to a new computer system or upgrading to a new release of Oracle Database.
- “The transportable tablespace feature is also useful in a number of scenarios, including:
- Exporting and importing partitions in data warehousing tables
- Publishing structured data on CDs
- Copying multiple read-only versions of a tablespace on multiple databases
- Archiving historical data
- Performing tablespace point-in-time-recovery (TSPITR)
- Migrating databases among RDBMS versions and OS platforms”
Are TTS export/import jobs restartable?
No. The Oracle® Database Utilities guides state that exports/imports done in transportable tablespace mode are not restartable. (START_JOB is not applicable.) If a TTS import fails, you need to drop all of the tablespaces in the target that have been plugged in, likely recopy the datafiles, then import again.
Can I create a transportable tablespace set from a standby database?
Yes, but you must activate and open the standby database in order to do it. For detailed information refer to oracle documentation.
Can I downgrade a database with TTS?
No if the compatibility setting is lower in the target than in the source. According to the Oracle Documentation, Compatibility Considerations for Transporting Data “the database signals an error if the compatibility level of the source database is higher than the compatibility level of the target database.”
Can I move/migrate to both a different RDBMS version and OS platform at the same time?
Yes; must be 10g or higher to move across OS platforms. For detailed information refer to oracle documentation.
Can I migrate EBS via TTS?
Yes. For detailed information refer to oracle documentation.
Can I move a PDB back to a non-CDB, and are adjustments needed?
Yes this move is possible.For detailed information refer to oracle documentation.
Can I move raw files?
Yes, with RMAN.
Can I transport just a single partition?
Yes. For detailed information refer to oracle documentation.
Can I use TTS with ASM?
Yes, with DBMS_FILE_TRANSFER, ASM files can be moved. RMAN may also be needed if endianness is different, depending on RDBMS version.
Can I perform a TTS export with a binary that has a lower RDBMS version than my source database?
No.
Can I move a dictionary-managed tablespace to a database with a locally managed SYSTEM tablespace via TTS?
Yes, but you will not be able to alter it read-write. If you plan to move a dictionary-managed tablespace to another database via transportable tablespaces (TTS), and you want to be able to open it read-write in the target database, you must convert it in the source database to locally managed before doing the TTS export.
Can objects be EXCLUDED from the TTS export or import?
Yes. The datafile size will remain the same. However space inside the datafile that remains unmapped at the end of the import (i.e. not recorded in the database) is reclaimed.
Can Transportable Tablespace Mode run with parallelism (i.e. use the parallel parameter)?
No. The “transport_tablespaces” and “transport_datafiles” parameters are mutually exclusive with the “parallel” parameter; transportable tablespace mode cannot be run in parallel. Trying to do so will yield an “ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams.” However, the full transportable mode (full=y transportable=always) can use the parallel parameter.
Do I have to convert the datafiles?
Yes, if the endianness is different. File conversion is NOT needed for tablespace data files that meet all three of the following requirements: (1) have a source and target OS with the same endianness (bitness does not matter), (2) will be imported into an RDBMS version that contains the patch for unpublished Bug 8973825 (10.2.0.5, or 11.2.0.2 and higher), and (3) do not contain undo and rollback segments (i.e. when a regular tablespace is in read-only mode). If the fix for unpublished Bug 8973825 is not available for your target database version, then you need to use the RMAN convert feature.
Do I have to copy the datafiles? Or can I just use the originals?
You should always copy the original datafiles and use the copy to plug into the new database. You should never use the original source datafiles. To use the original datafiles is extremely risky. You MUST have a viable backup of your database if you are going to use the original datafiles. If anything were to fail during the import of the tablespace in the target database, you might not be able to use the datafile(s) in either database, as the import alters the datafile headers at various points in the import process; restoring the datafiles from backup would be your only recovery option. There is not currently a way to tell at which point in the import process the datafile could be reused and when it could not.
Starting from 12c, during the TTS import operation, it internally sets the datafile status to READ WRITE in order to “clean up unclaimed segments and the TSTZ tables can be fixed up”. However, at the end of TTS import operation, it sets the datafile status back to READ ONLY. Since, it intermediately sets the status to READ WRITE, the datafile header gets changed and so it reports ORA-19721 if reused. The same datafile which was already attached once cannot be reused again.
Do I need to pre-create users in the target database before importing?
Yes. The other option is to use the remap_schema parameter to map exported users to existing users in the target database. See the Oracle Database Administrator’s Guide 11gR2 for details.
Note that if you pre-create a user and it uses a default tablespace that is not yet plugged in, you need to temporarily assign a default tablespace that exists in the target database. After the import is complete, you can alter the user and set the default tablespace to the desired one.
Does TTS guarantee the first fast refresh of a materialized view (mview) will succeed after TTS import?
No, not even if it is a local mview.
Does TTS support Transparent Data Encryption (TDE)?
No. Regular TTS does not support TDE. Only Full Transportable Export/Import (transportable=always) from 11.2.0.3+ supports tables with TDE columns when (1) the import target is V12 (i.e, parameter VERSION=12) and (2) when the endianness is the same between source and target.
Does TTS support Analytical Workspace (AW) objects?
Very Limited Support. TTS supports moving AW objects ONLY when the platform/operating system/word size/endianness/etc. all match between the source and target databases.
How can I use a parfile with multiple lines for the transport_tablespaces and transport_datafiles parameters?
Inside the parfile:
transport_tablespaces=
TABLESPACE001,
TABLESPACE002,
TABLESPACE003
— OR —
transport_datafiles=
‘/oradata/sid/data_01.dbf’,
‘/oradata/sid/data_02.dbf’,
‘/oradata/sid/index_01dbf’
If the TTS import fails part way through, and a datafile has been plugged into the target database via TTS imp/impdp, but the tablespace has not been changed to read-write, is it possible to re-import using that same datafile if the tablespace is dropped from the target first?
The import process can modify the file headers at various points during the import, especially when transportable=always is used; it not currently possible to tell at which point in the import process that has happened, not even from a timestamp. The only thing you can do is drop all of the tablespaces that have been plugged into the target and retry the entire TTS import. If it errors, you will need to recopy/convert all the datafiles again. You cannot do only a partial-tablespace import, as the export file references all tablespaces. Oracle Managed Files (OMF) are dropped whenever the tablespace is dropped even if you do not use the ‘AND DATAFILES’ clause. Also be aware that for ASM files, unless you specify an ASM alias filename during file creation, the file is OMF.
Is there a limit to the number of tablespaces that can be specified in the transport_tablespaces parameter?
No, but there is a character limit for the parameter when compatible < 10.2.0.4. With compatibility < 10.2.0.4, there is a 4000-character limit to the value of the transport_tablespaces parameter, which is changed in 10.2.0.4 to 32K characters. This is a character limit, not a number-of-tablespaces limit; you will be able to specify fewer tablespace names if they are very long names than you would if you had shorter tablespace names.
Is TTS the best way to upgrade/migrate my database?
It depends on a number of factors such as size, downtime, endianness conversion, etc.
What if I don’t wind up with the same number of non-SYS-owned objects in the target database as in the source?
Use the structural export to recreate missing objects for the schemas that were transported. Objects such as PL/SQL, Java classes, callouts, views, triggers, roles, procedures, synonyms, users, privileges, dimensions, directories, and sequences. are not necessarily contained in the tablespaces you transport, since SYSTEM and SYSAUX are not transported. You should use a structural import (i.e. full export/import with no rows) after the TTS import to create any missing objects that you need. Users such as ‘CTXSYS, ‘ORDSYS’, ‘MDSYS’, ‘ORDPLUGINS’, ‘LBACSYS’, ‘XDB’, ‘SI_INFORMTN_SCHEMA’, ‘DIP’, ‘DMSYS’, ‘DBSNMP are treated as objects owned by ‘SYS’ and not exported with TTS.
What is XTTS?
Cross-platform transportable tablespace. Some documents in the knowledge base refer to XTTS vs. TTS when talking about transporting tablespaces across OS platforms. The acronym TTS is just as applicable, however.