Re: RMAN point-in-time recovery problem

From: Vitalo Pedrotti <vitalisREMOVETHAT_at_numericable.fr>
Date: Fri, 28 Nov 2003 10:28:35 +0100
Message-ID: <pan.2003.11.28.10.28.19.186946.325@numericable.fr>

On Fri, 28 Nov 2003 03:35:15 +0100, Howard J. Rogers wrote:

> "Vitalis" <vitalis_at_numericable.fr> wrote in message
> news:6f5c4d7d.0311260954.2f934c7b_at_posting.google.com...

>> Hi,
>>
>> I'm testing this RMAN feature and I've come across a strange problem.
>>
>> At time T1, I made a full consistent backup of the database and the
>> archivelogs with RMAN and then I opened the database.
>>
>> At time T2 I inserted a row in an empty table test in tablespace TEST,
>> committed the change and switched the redo log file.
>>
>> At time T3 I executed a 'delete from table test', committed the change
>> and switched the redo log file.
>>
>> I wanted to restore the deleted row with RMAN: sql 'alter tablespace
>> test offline immediate'; run {
>> set until time [a timestamp just before T3]; restore tablespace test;
>> recover tablespace test;
>> }
>>
>> RMAN restored my backup and recovered the tablespace with the redo log
>> corresponding to the insertion.
>> But in the alert.log, the error ORA-279 occured and RMAN recovered the
>> next redo logs too.
>>
>> So the row isn't in the table yet.
>> Any idea of the reason of this behavior? And what can I do to restore
>> this row?
>>
>> TIA

>
> Try and think what you're doing here. An incomplete recovery can *not*
> be done at the tablespace level. You can't have half-a-dozen tablespaces
> at time T3 and one at time T2+a bit. That's an inconcistent database,
> and won't open.
>
> Incomplete recoveries require you to restore the entire *database* and
> roll the entire database forward until time T2+a bit. Then it's
> consistent, the T3 transaction isn't replayed, and you get your table
> data back.
>
> By the way... you didn't mention a version, but if you're testing, then
> be aware that since 8i recovery using 'until SCN' is a far better (more
> precise) method of performing incomplete recoveries to fix user
> stuff-ups than 'until time'... because Log Miner exposes the precise SCN
> that you want to avoid repeating. The best an until time can do is
> recover to the second, and in a busy OLTP database, there could be many
> tens of transactions per second, so you'd lose the lot. So, given you
> are testing and learning, learn Log Miner and use it to determine the
> precise moment to recover to.
>
> (When you do, remember that the recovery point is non-inclusive. That
> is, if you say 'set until SCN 13754', then recovery will apply the
> transaction with SCN 13753, and stop. It won't re-perform the
> transaction with the specified SCN.)
>
> Regards
> HJR
Thanks for your explanation Howard.

I've studied tablespace point-in-time recovery (TSPITR) a bit further and what I was trying to do was erroneous for the reasons you gave.

But TSPITR *IS* possible. Following chapter 11 of RMAN User's guide,we can perform the type of recovery I was trying to do. It's a bit complicated because it involves an auxialiary db and exporting/importing metadata. RMAN automates a large part of this procedure.

Regards Received on Fri Nov 28 2003 - 03:28:35 CST