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

OK, last questions first:

Attempting to shut a database down with the immediate, transactional or normal options whilst a hot backup is in progress produces the following error:

ORA-01149: cannot shutdown - file 3 has online backup set ORA-01110: data file 3: '/dbaclass/user15/DATA/DISK3/data01.dbf'

So, no, Oracle sensibly won't allow an inadvertent shutdown whilst backups are taking place. You can, however, as I mentioned elsewhere do a shutdown abort.

Second, and I don't know if this answers your question precisely or not, but here's what I did this afternoon:

1. Place tablespace DATA01 into hot backup mode
2. Do a shutdown abort
3. Delete data01.dbf
4. Try and startup - File 3 cannot be identified
5. Restore data01.dbf from a backup taken when file 3 was NOT in hot backup mode
6. Try and open the database -naturally, Oracle spots that File 3 is not consistent with the other datafiles, and prompts for recovery
7. Issue the 'recover datafile 3' command
8. Redo is applied apparently normally
9. At the "Media Recovery Complete" message, try an 'alter database open'
10. The database opens perfectly normally, and a check of v$backup demonstrates that file 3 is not considered to be in hot backup mode.
11. Shutdown immediate, followed by a startup -the database opens perfectly normally.

That result actually surprised me, because I expected the 'hot backup' bit in the Control File to remain set, but clearly the recovery process handles that. I was expecting to have to issue an 'alter database datafile 'blah/bong/data01.dbf' end backup' command -but it proved entirely unnecessary.

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

See above. Recovery is handled perfectly normally. The nature of your backup is totally irrelevant, as it is for any recovery considerations. Oracle *never* cares whether you have a hot or cold backup, provided you have all the redo from the moment the backup started (ie, all archives and all online redo).

It's funny, I had a room full of students this week, most apparently aware of runours 'on the grapevine' that hot backups were somehow more restrictive or more tricky to use than cold backups. Absolutely untrue. There is nothing you can do with a cold backup that you cannot also do with a hot backup, with the usual 'all redo must be available' proviso. Another backup and recovery myth which I can't seem to do much to stop rolling along, try as I might ;-)

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

I answered this bit of your query this morning in another post.

Regards
HJR
> 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 Fri Mar 23 2001 - 03:25:21 CST