Re: Backup strategy

In article <3qn1ae$m7u_at_hpg30a.csc.cuhk.hk>, ernestyik_at_cuhk.hk says...
>
>We are now planning for a backup strategy for our Oracle 7.1 running on
>HP-UX 9.04. Our database has a very stable tablespace and table
>structure, there will be very few or no modifications to tablespaces.
>The data will be updated very frequently.
>
>Am I right to think like this? Make a full backup one day, and then
>we'll only have to backup the data with export. When anything happens
>with the data we can import them back, and if it's so bad that the
>tablespaces are corrupted or the HD crashed then we will restore the
>full backup and import the data. So we don't have to do other online
>backup etc. Any pitfalls with this strategy? Thanks for any info.

There are several problems with this. The most obvious is, if you experience a crash with loss of data files, you will only be able to recover up to the point the export file was made. For example, if you export each night at 2:00 AM and lose a drive at 4:00 PM. you will lose all of the changes written to the tables in the lost data files after 2:00 AM when the export was made.

The data in the recovered tables will also be out of synch with the rest of your database. You could, in theory, trash your entire database and rebuild it from your export, but that would throw out ALL changes since the export.

This also ignores the fact that the export will be consistent within a table, but not between tables (i.e., you could get part of a transaction that involved updates to three tables). This can be fixed by exporting with the CONSISTENT option set to Y, but I don't recommend this for an entire database that is subject to high update activity.

The correct way (IMHO) is to set your database up to run in archive log mode. Shut down the database and perform a cold backup. Then archive the offline redo logs each night (and possibly perform nightly hot backups, depending on your update levels). If you experience a drive failure, you would need to recover the data files from the most recent cold or hot backup, and then apply the archive logs from that point on. You will be able to recover your database up to the time of the crash, losing only the uncommitted transactions at that time.

Yes, its a pain to run in archive log mode, but if you need your data (and who doesn't), it is really the best option.

As far as when you would need to run hot backups, that is up to you. You need to weigh how many archive logs you are willing to apply to recover a database. If you cut three 10 meg archive logs a day, you could get away with a cold backup once a week, and archiving the logs each night. If you cannot shut down once a week, you could replace the hot backup with a cold backup. If you cut more or bigger archive logs, you might want to consider backing up (either hot or cold) more often.

I run a 6.5 gig database. I shut down for a cold backup Friday nights. I perform a hot backup M-Th nights. I back up my archive logs each night.

I use exports to cover me from user errors (so THAT's what truncate does!), not to protect my database as a whole. I use exports to allow me to retrieve a table or two if they have been accidentally dropped, updated, deleted, etc. I leave protection of my database from media failure to my backup/archive log procedures.

Hope this helps.

Matt... Received on Fri Jun 02 1995 - 00:00:00 CEST