Backups & different data retention requirements

Hello!

We have a somewhat cookie cutter application (read: a schema) that gets cloned and customized for different customers. We want to be able to implement variable data retention requirements on a schema level, specifically as they pertain to backups. For example, if we have Schema A, we want to ensure that any backups of Schema A are destroyed 90 days after that application itself has been decommissioned and the schema purged. We're a RMAN-to-disk shop, with some logical exports on top of RMAN for easier object-level recovery.

We can handle this via our backup software capabilities so long as Oracle is segregating its backups by application, and therein lies the question... what's the best way to do this? We've considered:

1. Every application gets its own database and instance, then we do standard full/incremental RMAN for each instance. Downside is that we would have potentially dozens of instances at any given time, and even though the data set itself isn't huge, I still loathe the thought of all those fragmented SGAs.
2. Keep all the "clones" in one database (different schemas) and use RMAN tablespace backups to break out backups by schema. Downside is that recovery could be more complicated or time-consuming; RMAN tablespace backups aren't going to preserve roles, users, etc. and we can't do a full database backup without commingling data and impeding our desire to "expire" specific schema backups

We're leaning toward option #1 right now, however are there other options we may have missed?

Bob

Bob Murching
Manager - Oracle Technologies
Budco - The Dialogue Company

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 06 2005 - 10:40:05 CDT