Re: RMAN point-in-time recovery problem

"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 Received on Thu Nov 27 2003 - 20:35:15 CST