RE: 10gR2 RMan Transport Tablespace
Date: Wed, 28 Jan 2009 16:16:46 +0000
Message-ID: <A92AF970568F9A4BAB18E10F9C64ED604D4D115D1E_at_EXCH2.morrisonsplc.co.uk>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of japplewhite_at_austinisd.org Sent: 28 January 2009 15:56
To: oracle-l_at_freelists.org
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 28 2009 - 10:16:46 CST