Re: 10gR2 RMan Transport Tablespace

  • From: japplewhite@xxxxxxxxxxxxx
  • To: knecht.stefan@xxxxxxxxx
  • Date: Wed, 28 Jan 2009 10:45:42 -0600

Good suggestion, Stefan, except that we have numerous schemas for our 
home-grown Apps in the database.  We can't just overwrite the Dev and Test 
databases with a duplicate of Prod.  It would be almost as much trouble to 
preserve and replace the App schemas (some of which are large in their own 
right) in Dev and Test after a refresh as to just transport the SIS data.

We've gotten a few of the Apps out of the SIS database, but accessing all 
the SIS tables from another DB via DB Links has proven problematic, 
especially since a lot of the SIS tables use LONGs.

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

Stefan Knecht <knecht.stefan@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01/28/2009 10:23 AM
Please respond to

mfontana@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Re: 10gR2 RMan Transport Tablespace

For something like this I would not worry about the metadata / # of 
objects at all, and simply go with duplicate database RMAN offers.



On Wed, Jan 28, 2009 at 5:10 PM, Michael Fontana <mfontana@xxxxxxxxxxx> 
Congratulations on doing your best to overcome what is an almost 
unimaginably bad design.  However, I am sure many on this list have had to 
cut their teeth on one if not many of these.  Some major applications 
(even those owned by Oracle) are also often needlessly complicated and 
make poor use of Oracle tablespaces, each in their own way (think SAP, 
Peoplesoft, Siebel, etc). 
The number of tables and indices you have make this an absolutely perfect 
situation for the use of transportable tablespaces and RMAN.  It should be 
fairly straightforward and easy to script a refresh operation, but keep in 
mind you will have to use one of the export/import methods (classic 
export/import or datapump) to migrate metadata.  Since this is a newer 
operation and your metadata will probably be quite large, it might be 
worth it to use the newer datapump features including multiple processes 
during migration over the LAN.  I have used this quite awhile with no 
pitfalls except an occasional network glitch or unplanned outage.  Still, 
using this method should DECREASE the amount of data you are migrating and 
mitigate such issues.
On a final note – are you saying that even the indices are in the same 
tablespace?  While there are not as many reasons to so as there once was, 
I would consider rebuilding them in your production system and placing 
them in a separate tablespace as a manageability issue.  It would make 
operations such as this much easier to accomplish.  You might even 
consider putting larger indices in a separate tablespace from smaller ones 
for space efficiency.  This should be fairly easy to accomplish with a 
phased approach over time, but of course would probably have to be 
scheduled during an outage window.  Usually applications such as this make 
no reference to the tablespace where an index is located, but I have seen 
some that routinely drop and/or rebuild indices within application 
processes, so watch for that.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
On Behalf Of japplewhite@xxxxxxxxxxxxx
Sent: Wednesday, January 28, 2009 9:56 AM
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 

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 


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

Other related posts: