RE: 10gR2 RMan Transport Tablespace

Jack,
Co-incidentally I am just in discussions with Oracle support regarding the
migration of a 2Tb t/s to another database. Both 11.1.0.7 but that is slightly
irrelevant.
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
(grrrr).
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
John
www.jhdba.wordpress.com
________________________________
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: 10.2.0.4 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?
Thanks.
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.
______________________________________________________________________