RE: 10gR2 RMan Transport Tablespace
- From: japplewhite@xxxxxxxxxxxxx
- To: "Michael Fontana" <mfontana@xxxxxxxxxxx>
- Date: Wed, 28 Jan 2009 10:39:33 -0600
Michael,
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@xxxxxxxxxxx>
01/28/2009 10:10 AM
To
<japplewhite@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
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@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: 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)
Other related posts:
