Re: Partial Database Recovery

In article <34229607.A993360E_at_nauticom.net>, "Mark E. Porterfield" <portrfld_at_nauticom.net> writes
>masint_at_istar.ca wrote:
>
>> >
>> > Is it possible to do a partial tablespace recovery? Let's
>> > suppose that one user accidently drop a table. The table are located
   

>> > in tablespace A. Then, I recover from last backup then datafile of
>> > tablespace A and recover it until one minute before the drop.
>> >

Not in so many words No. But..

>> > I got the information that this is only possible with a FULL
>> > tablespace recovery, when I restore ALL datafiles... If I restore
 just
>> > one tablespace datafile I can just do FULL recovery.
>> >
>>
>> With release 8.0.3 and up, you can perform point-in-time tablespace
>> recovery from line-mode server manager NOT the Enterprise Manager. I
>> have
>> no details other that the technical PR I have seen. Ken Jacobs from
>> Oracle Corp. said the facility is technically complicated for Oracle
>> to
>> have implemented, but it should be looked at if and when required.
>>

FULL database recovery to a point in time was available in versions of Oracle 7.

>Oracle cannot do this (at least previous to Oracle8). I believe that if
>you are utilizing partial backup and your archive log mode is true (You
>are using the archive redo logs) you could recover the database (not
>tablespace for relational integrity reasons) to a particular archive
>redo log. Of course, you have to know what is in that log and I can't
>help you there. What I do know is that point-in-time recovery is
>explicity usefull and CA-Ingres and CA-OpenIngres have been able to do
>this since Oh about 1987-88 or thereabouts. If Oracle 8 can do this it
>will be a nice feature. Of course you will likely have to take the hit
>on using archive logs.
>

I cannon imagine why anyone would want to permanantly take 1 tablespace from a database back to a time prior to the other tablespaces. This could only lead in inconsistencies in the database. This is why Oracle only allows full database recovery to a point in time.

The scenario mentioned above (ie user drops a table) requires the following actions. (Can only be done in Archive Log mode)

1. Do a FULL offline backup of the database as it is now. (including online redo logs, control files etc)
2. Restore a backup of ALL the datafiles in the database from a time prior to the table being dropped. (You may need to restore a prior control file if the database structure has changed)
3. Restore the required Archived redo logs.
4. Recover the database to a point in time Before the table was dropped

      SVRMGR>STARTUP MOUNT;
      SVRMGR>recover database until time 'YYYY-MM-DD:HH:MI:SS';

4. The database will have to be opened using RESETLOGS.    

      SVRMGR>ALTER DATABASE OPEN RESETLOGS;     This will reset the logfile numbers. At this point, all data entered after the specified time is lost, and is the reason why point in time recovery on a single tablespace is not allowed, as this could lead to inconsistencies between tablespaces.

5. Export the table that was dropped.

6. Shutdown the database and restore the FULL offline backup taken at the beginning of this process.

6. Startup the database and Import the dropped table.

Hope this helps.

-- 
Neil Roberts


Views expressed above are my own, and do not represent the views of BG Transo
or Rebus Resourcing.