Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Backup Strategy - Multiple schema database

Re: Backup Strategy - Multiple schema database

From: hpuxrac <>
Date: 15 Dec 2006 09:15:08 -0800
Message-ID: <>

Noghri64 wrote:
> On Dec 15, 8:52 am, "EdStevens" <> 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?

We use EMC on symmetrix. Do a split mirror backup every night. Then leave the split mirror "not synchronized" until the time for the next backup. ( Of course we are still mirrored on the live database ).

If you need to get things certain objects as they were last night then use the split database on the backup host and you can export out what you need.

You could expand that by having more BCV mirror's ... as many as you need or as many as you can afford. Synchronize and then split off each BCV and leave it split as your business needs dictate. Received on Fri Dec 15 2006 - 11:15:08 CST

Original text of this message