Re: Backup questions

On Thu, 22 Mar 2001 20:18:59 +1100, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>In no way, and at no time, is it an instruction to prevent normal access to
>the datafiles themselves by the likes of DBWR. Updates, Inserts and Deletes
>take place absolutely AS PER NORMAL! The contents of a datafile in hot
>backup mode are therefore in constant, ordinarily flux. It's just the
>header of the file which is locked from updates.

Beautiful explanation. Is this in any Oracle doc or is it just your years in the trenches? :)

>
>>
>> 2. After issuing a BEGIN BACKUP and during copying the tablespace's
>> datafile, there is a disk failure and the datafile is lost. How does
>> one recover in this case?
>
>One recovers as one normally would. By restoring the same datafile from a
>previous backup and applying redo to it via the 'recover datafile' command.
>I've never tried it myself, but theory suggests that before applying redo,
>one would also have to reset the bit in the Controlfile that thinks that
>tablespace is in hot backup mode by issuing an 'alter database datafile X
>end backup' command.

Let me elaborate:

I do a ALTER TABLESAPCE BEGIN BACKUP
Instead of doing a 'host cp ... ...', I do a 'host rm ...' (Oops! :-)

I immediately realize my "mistake" and I proceed to do a ALTER TABLESPACE END BACKUP. Oracle will proceed to update the datafile header, right? But the file no longer exists! How would Oracle handle this?

Would it even let me shutdown the instance if one of the tablespaces is in BACKUP mode?

Worse even, I dont even realize that I did the 'rm' instead of the 'cp'. I proceed on my merry way. When I access data belonging to that datafile, I would obviously get an error at which point I realize that the file isnt there!

So, I proceed to do my recovery. (Assuming Oracle lets me shutdown the database), can I recover from an earlier hot backup or would a cold backup be required?

>> Under what situation would I need to do ALTER DATABASE DATAFILE <file>
>> OFFLINE DROP?
 
>You can't. That's what "drop" means. If you simply want to offline a file,
>so that the database can be opened, and so that you can then perform
>recovery on a tablespace whilst the database is opened, just use the
>"offline" command. "offline drop" means you have no intention of recovering
>that file.

Again, nice explanation. Thanks.

On a related note:

Suppose a table is accidentally dropped. I want to recover it. How can I apply point-in-time recovery here? i.e. I know that the table was dropped at 10:00 a.m. So, I want to recover to 9:59 a.m.

Can I simply restore from my last night's backup and RECOVER TABLESPACE UNTIL TIME '9:59 a.m.'? How would this work exactly?

In general, can I do PITR only for a datafile/tablespace and not for the entire database? If the answer is yes, wouldnt that tablespace/datafile be at a different time i.e. inconsistent with the rest of the database? How is this handled?

[I know that Oracle 8i has the new Tablespace Point-in-Time recovery feature, but lets leave that aside for a moment and go back to first principles]

Regards... Received on Thu Mar 22 2001 - 08:00:01 CST