Re: Backup Strategy - Multiple schema database

On Dec 15, 10:50 am, "Noghri64" <cbeauche..._at_gmail.com> wrote:
> On Dec 15, 8:52 am, "EdStevens" <quetico_..._at_yahoo.com> wrote:
>
>
>
> > Noghri64 wrote:
> > > At a high level, what is the recommendedbackupstrategyfor a
> > > multi-schema(100s) 24/7 database?
> > > Database has a high volume of DML activity.
> > > Point-in-time recovery is required on occasions for specific schemas.
>
> > > Thanks.EVERY Oracle database is multi-schema. As soon as you shake it out of
> > the box you have schemas for SYS, SYSTEM, DBSNMP, and several others
> > based on the installation options you chose. The schemas you build
> > yourself are just more of the same.
>
> > Backupstrategyremains the same. Run in archivelog mode and take
> > regular on-line backups of the db and the archivelog files. For a
> > little extra flexibility, add a daily export with consistent=y.
>
> > I'm not aware of the ability to do a point-in-time recovery of an
> > individualschema, but I've never explored it. Given the need for
> > complete database consistency, I don't see how that would be possible,
> > but am open for further education. Here's my reasoning: given tables
> > in theschemaFRED and theschemaDAVE; given a transaction that
> > updates a table in both FRED and DAVE. Now, how do you achive a PIT
> > recovery of just FRED and yet maintain consistency across the database?The way we are doing it now (which is very painful) is restoring the
> whole db (all schemas) to a PIT on another db. And then exporting the
> required schema, to be able to import in the production db. I guess
> not too many people are doing this.
> We have schemas for each our clients, and need to be able to give them
> a PIT recovery when needed.
>
> What database snapshot technology? Does anybody have experience with
> that? I'm thinking that if we had 4 points throughout a day (for
> example) where we would take snapshots, it might be easier to recover a
> specific schema. Basically, restore the snapshot, export the schema,
> import the schema in production.
>
> Thoughts on that?

You could do this with a standby database and ship the logs manually to you apply only what you want.
Link the databases and do CTAS (create table as select * from) for table recovery.
This would eliminate the restoring portion as the database would always be rolled forward.
As long as you don't recover or resetlogs on the primary, you won't have to keep restoring.
Still though, seems like the DBAs have bought a problem that should be at the app layer. Received on Fri Dec 15 2006 - 11:04:23 CST