Re: Ramifications of Consistent Export

Margaret Peacock <peacockm_at_nt.com> wrote:
>I need to do a full, consistent backup of a large database once a week
> (daily backups are done as well). I am confused as to which method of
> weekly, cosistent backup I should use. Should I Do a CONSISTENT EXPORT?
> Or should I bring the database down and do a backup on the file system
> level? Any advice would be greatly appreciated. Thanks is advance.

Hi,

it depends on why you are doing the backups. export/import is not really a backup technique. It allows you to unload your database into a portable format enabling you to reload it onto another machine with different architecture or physical disc structure. Obviously, you can use it to reload your current database if you trash it, but it has some big disadvantages:

1. As a restore procedure, it is very slow, taking many times what it would simply to do file restores of the files which make up your database.
2. You can't rollforward from an import using the redo logs.

However, it has the (one) advantage that you can restore individual tables from the export, but you'll have to understand the relationships between the tables in your application for this to be useful.

The difference between a consistent export and a normal one is that in a normal backup each table is internally consistent, ie. you get a "consistent view" of the data in the table as at the moment that table was exported ( just as normal queries return a consistent view ), BUT there is no guarantee of consistency between tables. eg. if someone updates table A and table B whilst the export is processing table A, the change to table A will not be in the export because it occurred after the export of table A started, but the change to table B will be, as the update to B completed before the export of table B started. In a consistent export, consistency *between* the tables is also guaranteed, ie. no updates started after the export has commenced will be included in the export file.

Assuming you are performing your backups in order to recover from a disc crash or similar, then you'd be much better off either performing a cold backup of your entire database ( ie. taking the database down and backing up ALL files which make up the database ( data files, log files, control files ) using standard operating system backup software ), or doing hot backups of your database ( ie. with database running in ARCHIVELOG mode, backing up each tablespace one at a time. You can backup your entire database over a number of days using this technique. Maybe you already are? ).

Most textbooks recommend that if you have a hot backup strategy, you should still occasionally ( weekly? ) perform a cold backup, but it isn't really necessary and often not practical. As long as you've got sufficient tapes covering all the tablespace backups which make up the database, and all the archived redo logs since the oldest tablespace backup, you'll be able to recover.

Just in passing, what's the nature of these 'daily' backups you're doing? Full exports?, incremental exports?, cold database backups?, hot tablespace backups?

Dave.
--
Remove the no-spam bit from my email address to reply. Received on Fri Oct 30 1998 - 11:00:58 CST