Re: Backup questions

<vikasa_at_despammed.com> wrote in message
news:0c0kbt07lcg65di7kbh1t66c0l3r956jri_at_4ax.com...
> 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?
>

You're in luck. Today is the last day of the backup and recovery course (so all the databases become expendable!). I'll test this exact scenario out for you, and let you know.

I know already, however, that you can indeed shutdown an Instance during hot backup -I demo a shutdown abort to simulate Instance failure, and that works a treat! I'll try a shutdown immediate, though, as well.

> >> 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?
>

Absolutely not. As soon as you think of incomplete recovery (aka point-in-time), you must immediately abandon any hope of recovering just parts of the database. Incompletes require the entire database to be shut down, and opened to the mount stage, ALL data files to be restored, and then a 'recover database'. No varaiations allowed! If you were to try a 'recover tablespace', you'd be left with one part of your database at 9.59, and the rest of it at 10.00am -so the data files would be inconsistent with each other. At the end of the recovery, you'd get a message along the lines of 'recovery complete but attempt to open the database with resetlogs would get the following error: File 1 needs more recovery to be consistent'.

Resetlogs won't cure this problem: it does what it says it will do, and resets the logs back to time 1, but it doesn't force inconsistent data files to agree with each other.

> 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?
>

See above. Incompletes MUST be done on the entire database. What I think you should be looking at is called "tablespace point in time recovery", which involves cloning just the relevant part of your database, and doing a point in time recovery on that cloned part. That gets you your table back in the clone, and you can then use boring old export and import to transfer the table back into the production system. It all gets a bit tricky, but it can be done -but only on a partial clone.

> [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]
>

TSPITR has been around for a while, and isn't 8i-specific (though the transportable tablespace feature gives you options when it comes both to the cloning and to the transfer back to the main system).

Regards
HJR
> Regards...
>
Received on Thu Mar 22 2001 - 13:53:17 CST