RE: Merge datafile to Another DATABASE

Why would someone trying to create a mini-clone of just one tablespace need SYSAUX from the original database? They would need the UNDO tablespace in almost every circumstance, which I don't think anyone has mentioned yet in this thread. If the source database were copied open, or were not shut down in IMMEDIATE, TRANSACTIONAL or NORMAL mode, or if the OP wanted to recover the tablespace in question to an arbitrary point in time, UNDO is needed to perform roll back after roll forward.

So to review, you need the following to create a mini-clone:

• SYSTEM
• UNDO
• the tablespace(s) you want to open
• a CREATE CONTROLFILE script naming the files from above three tablespaces (easy to write yourself)

Also, use of the TTS (transportable tablespace) feature is optional, since row-wise data can be exported in the conventional way from the mini-clone. For large data sets, TTS certainly would be quite efficient.

Use of a mini-clone for single table point in time recovery has been a standard part of the Oracle recovery toolset for a long time. However, with modern features like flashback table, flashback query and Logminer have made the mini-clone approach decreasingly necessary. Finally, the TSPITR (tablespace point in time recovery) feature does the whole mini-clone and TTS operation for you behind the scenes with little manual effort required. These modern features should be placed ahead of any manual efforts like the one described here in any recovery plan.

Thanks,

Jeremiah Wilton
ORA-600 Consulting
http://www.ora-600.net

Elliott, Patrick wrote:
> That is correct.  Of course you will want to back everything up before
> you even think about starting this process.
>
> Prasad [mailto:p4cldba_at_gmail.com] wrote:
>> In order to create the new db I guess it needs the system and sysaux
>> data files also...
>>
>> Elliott, Patrick < patrick.elliott_at_medtronic.com> wrote:
>>> The A_USER_Datafile would need to be the only datafile for the
tablespace
>>> that owns it.  You would need to create a new controlfile with just the
>>> A_USER_Datafile in it in a new database.  Then you would need to do a
>>> transportable tablespace export using this new database after making the

>>> tablespace for this datafile readonly.  Then import the transportable
>>> tablespace export into database B.

--
http://www.freelists.org/webpage/oracle-l