Read Only Partitions for Speeding Backups

[hurleyb]

A tech bod at our clients has suggested that we use read-only table partitions to speed up backups. Our table is partioned on a numeric representation of the date (e.g. 20020830) and whilst we need to have the data online, we don't expect to need to update anything more than a week (say) old. The logic goes that if the partitions were read only then there would be no need to back them up more than once.

Is this plausible and, if so, what is the mechanism?

We are using Oracle 8.1.7 on a Solaris box.

[slimdave]

It's feasable to use this kind of approach, but maybe you would do better to look at using RMAN. The backup can be much more aware of what data has changed, and would not continually backup the same unchanged data every time.

[jmodic]

Originally posted by hurleyb
Is this plausible and, if so, what is the mechanism?

Sure this is plausible, in fact it is a very common aproach with very large databases.

The mechanism? Well, you can't make a table or a partition a "read only" there is no such option. However you can make a tablespace read only. So the tables/partition that are not subject to changes should be moved to such read-only tablespaces. If you are putting partitions on read-only tablespaces, do not forget to do the same thing with their corresponding local indexes!

Once those segments are put on the read-only tablespaces you have to back those tablespaces only once as nothing ever change in them. Of course this is just in theory - in praxis you would still backup those tablespaces periodicaly, but much much less often than you back up your other read-write tablespaces.

[hurleyb]

Thanks for the reply, that all sounds plausible and I have something to go on with.