Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Restores using Incrementals

Re: Restores using Incrementals

From: Howard J. Rogers <howardjr_at_www.com>
Date: 2000/07/08
Message-ID: <3967bac5@news.iprimus.com.au>#1/1

"I hate Spam" <tbf_at_nospam.stam.dk> wrote in message news:agi9ms0ja48iurrplsolthe47mii1eb1qf_at_4ax.com...
> On Wed, 05 Jul 2000 09:31:21 -0700, mkb
> <mkb125NOmkSPAM_at_yahoo.com.invalid> wrote:
>
>
> >I assume that the test db can only be read-only since if
> >anything changes, I'll need to retore the full export from Day 1
> >and apply the incrementals.
> >

I'm sorry I can't seem to see the start of this thread, but the line right here is going to trip you up horribly. That's because an incremental export does NOT work on a row-by-row basis, but on an object-by-object basis. In other words, the *entire* EMP table will get included in an incremental export if just one row in it has been modified. What this means is that, when it comes time to restore, you go to the incrementals FIRST, and if the object you want is not there, only then do you drop back on to earlier incrementals, and ultimately onto the full export.

This is always a killer, because it is exactly NOT how everyone thinks of incrementals. You can get all sorts of quirky results doing it the way you suggest.

For example: Day 1: Bob's salary = 600. Full Export A. Day 2: Insert new record for Sue, salary=500. Incremental Export B

Export B includes both Bob at 600 and Sue at 500 (because the entire table is exported).

Day 3: Update Bob's salary to 800. Incremental Export C.

Now, if someone drops the EMP table, and you restore from Export A, you get Bob at 600 -and Sue's a no-show. So then you apply Incremental B. Assuming you remember to say 'ignore=y' (ie, ignore the error you'll get when Import tries to re-create the EMP table which already exists), you do indeed get a successful insert into EMP of Sue's record. So now you have Bob at 600 and Sue at 500. So now you try to apply Incremental C. Even if you have 'ignore=y', nothing happens: the EMP table already exists and, more to the point, the attempt to insert Bob's 800 (and Sue's 500) results in failure -assuming you have a primary key defined- because Bob's already happily in the table -only at the 'wrong' salary!

Actually, it gets much worse if you haven't got a primary key defined: you end up with three records for Bob, and two for Sue.

If you imagine a graph with time on the horizontal axis, the correct approach with incrementals is to start on the 'righthand side' and work backwards (ie, use the most recent incremental first, and step back ever earlier as needed).

Sorry. Probably got zilch to do with what you were actually asking about, but whatever it is you're trying to achieve, it'sprobably best you understand the export/import process before you start!

Regards
HJR
> >So, is this feasible, or will I have to restore the full exports
> >each day?
>
> You can create the test db as a standby server. This can be mounted as
> a standby server or opened in read only mode.. No problems switching
> between the two. Only if you _activate_ the standby i.e.
> 'open read write'
> they will separate.
>
> To make the daily update simply copy the archived redo logs from
> production to test computer and start recovery in standby mode.
>
> Hans Erik Busk
> oz1dcv_at_busk.dk
>
> Yours Hans Erik Busk
> tbf_at_cn.stam.dk
Received on Sat Jul 08 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US