Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: exp and archive available.... recover table

Re: exp and archive available.... recover table

From: joel garry <>
Date: Mon, 09 Jul 2007 14:35:05 -0700
Message-ID: <>

On Jul 9, 7:37 am, "" <> wrote:
> On Jul 9, 9:31 am, Steve Howard <> wrote:
> > On Jul 9, 10:24 am, "" <> wrote:
> > > On Jul 9, 9:05 am, Alexander Skwar <> wrote:
> > > > <> wrote:
> > > > > An export is a logical copy of the object or objects exported at the
> > > > > time the object copy began. It is not a backup, even in the loosest
> > > > > sense of the word.
> > > > What makes this a "non backup"? You can use an EXP dump to recover
> > > > all the data, can't you? You'll of course just need to create an
> > > > "empty" database before you can run IMP.
> > > > Alexander Skwar
> > > The inability to recover from a corrupt or lost/deleted data file. An
> > > export simply cannot recover tablespaces or datafiles, only objects
> > > within them, and only from the point in time when the object copy
> > > began. Having to create a new database to restore data from an .dmp
> > > file proves that exp is not a backup.
> > > Of course you're welcome to prove me wrong.
> > > David Fitzjarrell
> > I think we have too strict a definition of "backup" at times. We have
> > used exp (and now datapump) to take a single table backup (yes, I
> > think that term is appropriate) when mass data changes will be made
> > to it and we want to be able to go back to it at that exact point in
> > time (without worrying about having enough undo to flashback the
> > table). We like the way the table looks, have taken into account any
> > constraints, and a quick and dirty export allows us to put it back to
> > that point with a minimum of fuss/muss.
> > In that sense, yes, it is a backup for us.
> > Regards,
> > Steve- Hide quoted text -
> > - Show quoted text -
> And you understand the limitations of the technique and have accepted
> them. You may consider such a backup, and so may managment. I'd hate
> to rely on such techniques to restore an entire database due to some
> catastrophic failure, or even the loss of a single data file. Yes,
> exp/imp can create a missing tablespace if done properly, however it
> cannot restore one missing datafile in a tablespace which may have
> several files associated with it.

Of course, that is why it is called a logical backup, in Oracle's own docs - the concept of a datafile doesn't apply.

You've hit on the magic word - restore.

> You're using the term backup a bit loosely, in my opinion. But, if it
> meets your (limited) criteria more power to you.

It's when you start talking about recovery that the terms tighten up. What most of us here are talking about (as well as most of the backup documentation) is transactional recovery. The OP's problem (and what would get a DBA fired) is trying to mix transactional recovery and logical restoration. It just can't be done. As has been pointed out, mining the logs just isn't gonna get you there.

The serious problem that Steve Robin (and similar posts here) have is the lack of policies and procedures that support proper recovery criteria. Sybrand is right about showing a DBA the door where this is screwed up (I'd go further and say the PHB that is responsible for it should be, too, or maybe instead). But Sybrand is wrong about it not being a backup. It is, it just has limited applicability, as you seem to be saying. It adds value beyond a proper backup, too, both in ease of getting data for certain types of errors and in the inherent redundancy of backing up with completely different methods.

You don't have to use RMAN to back up everything in every situation - a data warehouse may be rebuildable from other feeds, a baseline db may be recreated in various ways. It just matters that you can get to where you need to be when things go wrong. Certainly for all OLTP production situations I've seen, _both_ exports and RMAN are appropriate, either alone would be insufficient (though I could understand those who argue against the necessity of export - I'm not saying everything needs to be exported, but a low justification level is helpful). But I've also seen other situations.


-- is bogus.
Received on Mon Jul 09 2007 - 16:35:05 CDT

Original text of this message