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

Well, never had a look at this that way. You do a recovery back in time because your database is up-to-date till now. In my opinion in-time recovery was intended to roll forward a restored database to a time not being the most recent time available in the archives.

I think you can only achive what you want to do by: a. first: shutdown the db and make a safety copy of at least 1 control file and all redo log files.
b. restore all database files from the latest backup before the timestamp you want to recover to (NOT the control files and redo log files); that can be a hot or cold (online or offline) backup. c. make sure all archived redo from that time till the timestamp to recover to are available

On a mounted database some useful views are available to support the recovery:

a. V$RECOVERY_LOG: shows you the archives needed for a (complete) recovery
b. V$LOG_HISTORY shows you when redo are switched.
c. V$ARCHIVED_LOG shows same and more info as V$LOG_HISTORY
d. V$DATAFILE shows file info and among others SCN numbers

Because this is an incomplete recovery the database should be opended with resetlogs after cancellation of the recovery until time. And because of that all database files must be restored and not only a few of them.

Tip: just today a read an advice in this newsgroup to NOT use commands like 'alter database recover...'. For each "alter database ..." command there is an alternative by just removing "alter database". So do "recover database until time ...". This will give much more output on what is going on.

Get your Generic Documentation CD delivered with the software and look for the book "Oracle 8i Backup and Recovery Guide". Chapter 5 paragraph "Performing incomplete media recovery" section "Performing time based recovery" discribes this process in detail.

Sergey Adamenko <adamenko_at_i.com.ua> schreef in berichtnieuws 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. 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.
> 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?
>
> With respect,
> Sergey Adamenko
>
>
Received on Sat Apr 27 2002 - 17:28:34 CDT