RE: 10gR2 RMan Transport Tablespace

  • From: John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
  • To: "japplewhite@xxxxxxxxxxxxx" <japplewhite@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 28 Jan 2009 16:16:46 +0000


Co-incidentally I am just in discussions with Oracle support regarding the 
migration of a 2Tb t/s to another database. Both but that is slightly 
As both use ASM I was hoping that I could use transportable tablespace to 
backup the datafile (only one) to tape and then get the tape catalogued at the 
other site and use expdp to get the metadata across. I want to avoid the 
overhead of export/import (expdp estimates 513gb of data) and the network hit 
when getting the file across.

Another issue I have is that we have no cooked filestore at either side to get 
the file to if I RMAN to disk but RMAN does not support image copies to tape 

Sorry if I have distracted you but I see some similarities in what we are 
doing. If you had split indexes and data, which is the obvious split you would 
not be able to use TT so at least that is a bonus. The metadata data gets 
exported and imported so that will take care of any extents/segments and the 
datafiles should come across in one piece


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of japplewhite@xxxxxxxxxxxxx
Sent: 28 January 2009 15:56
To: oracle-l@xxxxxxxxxxxxx
Subject: 10gR2 RMan Transport Tablespace

Environment: Enterprise DBs on RHEL 4.0 servers

We use a 3rd party Student Information System (SIS) that (mis)uses an Oracle 
database to store over 176,000 tables and 275,000 indexes.  Yes, it's a 
horrible design, but we've been stuck with it for years and have figured out 
how to manage nicely in spite of it.

We refresh our Dev and Test databases weekly with the whole set of tables and 
indexes so our Developers have fresh meat to gnaw.  For years we've done this 
via Export / Drop / Import - multiple jobs in parallel - because Prod and Dev / 
Test were on different OS's.  Now we've finally got them all on the same OS and 
at the same 10gR2 level - was 9iR2 up through December.

Exp / Drop / Imp works OK, but takes all day Sunday.  We've been anticipating 
that Transportable Tablespaces would be the ticket for us, since the SIS stores 
all tables and indexes in a single tablespace.  Plus, the SIS uses nothing but 
the plainest of vanilla datatypes and no fancy database features that would 
preclude us from using TT.  I've perused the RMan docs on Transport Tablespace 
and it looks like we could do it.

I do wonder if there are pitfalls for us, having over 450,000 segments in that 
single 88GB tablespace (11 x 8GB datafiles).  Anyone have any positive or 
negative experience with something like this?

BTW, I've also considered using Data Pump Import on the target DBs over DB 
Links back to Prod, but feel that would use such a huge amount of LAN / WAN 
bandwidth - especially since it'd have to be done for both Dev and Test - that 
impdp is not feasible for us.  Any comments about that approach?


Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9715 (wk)  /  512.935.5929 (pager)

Wm Morrison Supermarkets Plc is registered in England with number 358949. The 
registered office of the company is situated at Gain Lane, Bradford, West 
Yorkshire BD3 7DL. This email and any attachments are intended for the 
addressee(s) only and may be confidential. 

If you are not the intended recipient, please inform the sender by replying to 
the email that you have received in error and then destroy the email. 
If you are not the intended recipient, you must not use, disclose, copy or rely 
on the email or its attachments in any way. 

Wm Morrison Supermarkets PLC accepts no liability or responsibility for 
anything said in the email or its attachments and gives no warranty as to 
accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into 
any contractual or other obligations by email. 

Although we have taken steps to ensure the email and its attachments are 
virus-free, we cannot guarantee this or accept any responsibility, 
and it is the responsibility of recipients to carry out their own virus checks. 

Other related posts: