Re: Hot Backups

In article <1993Nov23.233517.23889_at_scammell.ecos.tne.oz.au>, Mark Gurry <mag_at_scammell.ecos.tne.oz.au> wrote:
>To: David Crowson from Amoco
>
>According to the articles I have read, Hot Backups are the only form of
>recovery where archiving is turned on the DOESN'T provide PIT recovery.
>Maybe the articles are incorrect. They say the database must be restored
>to a consistent state i.e. all tablespaces must have the same checkpoint
>id... to me this means that you cannot restore an database to 2pm, if
>it is now 6pm. n.b a critical table was accidentally dropped at 2pm, I
>have NO WAY of recovering to it.
>
>I think you mean where you take a full cold backup
>and then run with archiving.
>This definitely provides PIT Recovery.

You can perform point in time recovery using BOTH hot backups and cold backups with archive log mode enabled. We have performed both in the past using Oracle version 6.0.36.x. There are differences in the steps performed, but both were successful.

>Have you ever had a corrupted table in an Oracle Database? How do you get
>an early warning of such an event.... EXPORT LOGS.... and of course Users
>saying they have a strange intermittent error. n.b. Oracle is not the only
>database that has table corruptions, every database I have been associated
>with has had similar problems at some stage, invariably caused by a hardware
>faults.
>
>I think without nighly exports, a site is leaving itself wide open for a
>disaster. Two possible disaster scenarios are (1) where an individual table is
>corrupted, that only an export can pick up (2) where an individual table
>is dropped, has key rows deleted from it or has a faulty program incorrectly
>update rows, requiring recovery to PIT.
>
>Any DBA who has HOT BACKUPs and NO NIGHTLY EXPORT is asking for a DISASTER.
>
>In fact, I believe nightly exports are mandatory part of any backup/recovery
>strategy.

You state three desired goals for your backups:

1. Ability to detect database (physical) corruption.
2. Ability to restore individual tables after user "errors".
3. Ability to perform point in time recovery.

I would add two more for myself:

	4) Ability to keep database up 6x24 (only weekend downtimes)
	5) Full logical consistency _BETWEEN_ tables restored

Here are my thoughts:

      o	Cold backups allow 3) and 5).
      o	Hot backups allow 3), 4) and 5).
      o	Both cold and hot backups allow 2) only with great pain, by restoring
	the entire tablespace (or database) to another machine/environment,
	extracting the tables you want (probably with an export), and then
	restoring from that extract.
      o	Exports allow 1) and 2).  4) and 5) are MUTUALLY EXCLUSIVE when running
	exports.  You can keep the database up, but then you sacrifice
	logical consistency _between_ tables (logical consistency is maintained
	_within_ a table, however).  Or, you can bring the database down
	(either in DBA only mode or by procedurally limiting what programs
	are allowed to run) to guarantee consistency, but then you've lost
	uptime.

Because of this, we have chosen to run hot backups nightly, and full exports in DBA only mode weekly, with the nightly hot backups rotated every couple of weeks, and weekly full exports kept for longer periods. In this manner, we fulfill 3), 4), 5) and some of 2) all the time, and 1) and 2) every week.

If I had the window every night, I'd probably perform a cold backup and a full export in DBA only mode every night, and run in archive log mode. However, in the real world, this isn't an option. So, hot backups and periodic full exports seem to work well for us.

-- 
David Schmitt, Manager, Technical Services	Voice:	(708)538-4699
Motorola, Inc. - Land Mobile Products Sector	FAX:	(708)538-4638
1301 E. Algonquin Rd. (IL02 SH1C)		E-Mail:	cds016_at_email.mot.com
Schaumburg, IL 60196