Re: Database point-in-time recovery: no errors during recobvery and no changes visible

"Sergey Adamenko" <adamenko_at_i.com.ua> wrote in message news:aaf5ko$2m3t$3_at_news.dg.net.ua...
>
> Hi, ALL!
> Did someone get used of such thing as DSPITR (Database point-in-time
> recovery)?
> My base works with ARCHIVELOG mode.
> I'm trying to rollback an hour ago.

Not being flippant, but this is your first mistake. Database don't ever get rolled back. They can only ever roll forward. It sounds like a meaningless distinction when you first hear it, but it actually has profound implications.

If you could roll a database back, you'd take your perfectly consistent and working database, and issue some sort of rollback command, and transactions would be removed from the database step by step.

But actually, you can only roll a database forward. So if you want to see it as it was, say, an hour ago, you actually have to restore the entire database from a backup taken at some point *before* that hour (say, last night's backup) and roll it forward. Transactions are therefore *added* to the database step by step, until you get to the transactions that were performed an hour or so ago, at which point you tell Oracle to stop adding back transactions. Now you have a database that *looks* like it has gone backwards an hour, but actually, physically, it's a 10-hour old database that has rolled forward 9 hours.

Understand that difference, and the problem you are having should be obvious (but just in case it isn't, read on!):

>Using SRVMAN, I do the following:
>
> Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
>
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> JServer Release 8.1.7.0.0 - Production
>
> SVRMGR> connect
> Username: internal
> Connected.
> SVRMGR> ALTER SYSTEM ARCHIVE LOG CURRENT;
> Statement processed.
> SVRMGR> ALTER database close immediate;
> Statement processed.
> SVRMGR> ALTER DATABASE RECOVER AUTOMATIC UNTIL TIME
> '2002-04-27:23:30:00';
> Statement processed.

So here's your problem. You shut a working database down and attempt a time-based incomplete recovery. Where's the restore of all the datafiles in between? You are trying to roll *back* the database, when you actually have to roll an old one forward. So there needs to be a restore of all datafiles from a prior backup before the "recover until time" command can do anything.

> SVRMGR> shutdown immediate
> ORA-01109: database not open
> Database dismounted.
> ORACLE instance shut down.
> SVRMGR> startup nomount
> ORACLE instance started.
> Total System Global Area 43501596 bytes
> Fixed Size 75804 bytes
> Variable Size 34959360 bytes
> Database Buffers 8388608 bytes
> Redo Buffers 77824 bytes
> SVRMGR> alter database mount;
> Statement processed.
> SVRMGR> alter database open resetlogs;
> Statement processed.
> SVRMGR> alter database open resetlogs;
>
> Now the database in open, but I see no changes: none of my operations is
> rolled back until the specified time.
> What did I wrong being so naive?
>

Fundamentally, what you did wrong was to think you could roll a database back. But you can't. Restore an old one and roll it forward, and then it will work.

Having said all of that, time-limited recoveries such as you are attempting are incredibly expensive affairs. The 'open resetlogs' you have to perform at the end of them mean that all prior backups and archives are rendered useless, because the command causes the database to enter what's called a new 'incarnation', and backups of a prior incarnation of a database cannot be used to recover the new incarnation. Therefore, your database suddenly has no backups or archives to protect it. Therefore, you are supposed to shut the database down immediately and take a new backup (lots of downtime). You can do a new hot backup, of course, but until the entire backup is complete, you are at risk of losing data.

What's more, whether you take a backup or not, the 'recover until time X' means that all transactions entered after time X are totally lost, across the entire database.

Now if that's what you need to achieve, so be it. You live with the costs. But I'm wondering what you are actually trying to achieve.

You could, for example, use log miner to see what transactions affected a particular table. With a bit of logical thinking, you could work out what a table looked like an hour ago by mentally reversing the transactions you see in log miner.

In 9i, incidentally, one particularly nice feature is "flashback". That permits you to issue standard select commands, but the results displayed are from whatever time you've selected to flashback to.

You also have the possibility of doing tablespace point in time recovery, which is effectively to clone your database, recover it to the state it was in an hour ago, and then use export and import to get particular tables from the clone back into production. All sorts of possibilities, therefore, but it's difficult to know which one to recommend without really understanding what you are really trying to achieve.

Regards
HJR
> With respect,
> Sergey Adamenko
>
>
Received on Sat Apr 27 2002 - 19:29:01 CDT