Re: Backup Strategy - Multiple schema database

Noghri64 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.

Sounds like a job for having one or a few tablespaces per client, since the granularity of restoration is the tablespace. Perhaps you could then also use transportable tablespaces after the PITR to move back to production. Of course, you could restore directly to production if your SLA requires.

>
> 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?

Could be good.

jg

--
@home.com is bogus.
"The Lexus has collided with the olive tree, and its crumpled hulk
spins in a ditch as the orchard smolders." - Bruce Sterling