RE: 10gR2 RMan Transport Tablespace

Thanks for the encouraging words. The SIS does routinely drop and rebuild indexes. To explain why leads me to reveal an even uglier feature of the SIS than 175,000 tables. The Oracle database is actually only a District repository of Student data. The actual OLTP databases used at the Campuses (about 160 of them) are - get ready - dBase4! Yes, 25 year old technology is alive and well in this product!

Each night SIS processes upload any changed DB4 files at the Campuses to tables in the District Oracle database. The processes drop indexes, truncate tables, populate tables, then re-create the indexes in the app owner's default tablespace, which has to match the configuration of the Upload processes. Since we spread the datafiles around for I/O, we've just left tables and indexes in a single tablespace to keep it simple for the simple-minded SIS.

Fortunately (or not) we'll only be on this SIS a couple more years, so these problems will go away. The not-so-fortunate aspect is that the replacement SIS is a product of the same Vendor that designed our current abomination. There's just not a bunch of choices out there for such a complex product as comprehensive Student Info. / Management System.

One more goody. Even if we wanted to use this product for years and years, we couldn't, because embedded in each and every table name is the single year-specific digit identifying the SchoolYear that table belongs to. Yes! This product experiences Y2K every ten years! We have 2001 through 2009 sets of tables so far. Come 2011 the SIS wouldn't know the difference between 2011 and 2001. Ya gotta laugh! At least it requires constant DBA vigilance. Nothing like bad apps to keep DBAs employed!

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

"Michael Fontana" <mfontana_at_enkitec.com>  

01/28/2009 10:10 AM

To
<japplewhite_at_austinisd.org>, <oracle-l_at_freelists.org> cc

Subject
RE: 10gR2 RMan Transport Tablespace

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of japplewhite_at_austinisd.org Sent: Wednesday, January 28, 2009 9:56 AM 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)

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