How To Recreate A Database Using TTS (Transportable TableSpace)

(Doc ID 733824.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
*** Reviewed 5-Feb-2016 ***

Goal

HowTo recreate/rebuild a database using TTS (Transportable TableSpace):
---------------------------------------------------------------------

In a number of cases there is a need to recreate the database (dictionary corruption for instance) and not being able to restore/recover the database.
The option left in such case would be exp{dp}/create dbs/imp{dp}. Drawback of this approach is the time needed to recreate the database and thus the downtime involved (original database can only be used in READONLY mode while the recreation runs).

A quicker solution might be the usage of TTS, the difference in time needed is due to the fact that TTS:
- only exports the metadata of the objects present in the tablespace and not the physical data (rows)
- no creation of indexes

The following restrictions do apply for this Note:
1) the original + new database are on the same OS + Oracle version
2) actions as documented do apply for original/new database only, so it might be that additional actions are required for other (remote) databases/sqlnet configuration/... due to relocation/rename of database
3) during the recreation of the database it has to be ensured that there will no conflict arise by doing actions/modifications from the original + new database (replication/database jobs/..)
4) the original database is kept available till it has been proven that all is working fine using the new database
5) no data will be exported out of objects residing in SYSAUX, which might result in creation of empty objects in new tablespace (these objects are exported as part of step full export norows, excluding of SYSAUX only is not possible). A check should be done to get clear which schema's/applications do have segments in SYSAUX after which a plan can be made if data needs to be extracted out of SYSAUX and if true how (for example: APEX has it's own exporting utility, RMAN could be done by a user export, ...)
6) check note:1264715.1 to check of  known issues of expdp/impdp (datapump) which might apply in your case/setup/...

...
99) not all scenario's have been tested

In case the actions are to be performed on the same system you should be very careful not to overwrite/damage/use/...  a file of the original database.

In case XMLSchema's are being used I do advice to use exp/imp instead of expdp/impdp. Expdp/impdp do have some restrictions/errors related to XMLSchema's which I have not observed with exp/imp.
(tested versions: 9.2.0.8.0 + 10.2.0.4.0 + 11.1.0.6.0)

A thing to keep in mind is that the application in question might have specific requirements/steps as needed as part of recreate of database (direct grants on SYS objects for instance/...). Check if applicable with the application vendor if there are additional steps to perform as part of exp/create dbs/imp.
Example for APP's R12: <Note:741818.1> Export/import process for R12 using 11gR1

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms