Re: backup versus export of db

In article <4884rs$65r_at_earth.alpha.net>, Milwaukee Tool <metco_at_earth.execpc.com> wrote:
>
>I've been told by a Value-Added-Resaler of Oracle that if i want to backup
>an oracle 7 db, i should first do a full export, and then backup up the
>exported file.
>They said that a simple backup of the files used by oracle isn't good enough.
>The reason given went something like this,
>
> "while oracle is running, it leaves certain files open. Consequently,
> if you do a simple backup of the filesystem on which oracle resides,
> even if no db transactions are pending, you aren't guaranteed to
> capture the complete, latest state of the data."
>
First of all, there are books inches thick out there describing how to backup an Oracle database. Do not plan your backup strategy based on a couple sentences from a VAR - or anyone for that matter. That being said and easing my conscience, the answer is yes, they are correct, if you backup the database files while the database is running.

Very (VERY) briefly, while Oracle is running background processes are looking at your database and making decisions on what they should do. If your online redo logs are running, and one of them is nearly full and you begin an operating system backup of the database files, you are likely to end up in trouble. When the current redo logfile gets full, LGWR will perform a log switch and begin writing to the next file. This causes a checkpoint. A checkpoint causes Oracle to write the current status number to all of the datafiles. This means that all of your system data files will be synchronized with the control file at the same number, let's say 50. SO, you backed up files with a status number of 49, a log switch occurred, and now you're backing up files with a status number of 50. If you should ever restore these files and attempt to restart the database, you will be in for a big surprise. The database will see that the status numbers are different and require media recovery. You won't have any media to recover.

There are two ways to avoid this - only do complete database file backups when the database is not running, or do hot backups by issuing the statement "alter tablespace name begin backup" and then immediately perform the backjup followed by an "alter tablespace name end backup" command. This will force Oracle to not try and write dirty blocks to the tablespace until the end backup command.

>Can you shed any light on this statement? If basically true, what is really
>meant by "leaving files open"? My interest in knowing goes beyond the
>question of how to backup the db - I am also trying to decide if i there's
>enough benefit to warrant mirroring the disk partition(s) that hold the db.
>
If you can, and performance won't suffer so much that mirroring is okay, then mirror and backup.

>Thanks much for any help you lend.
>
>Jon Detert, metco_at_execpc.com, 414.783.8527
>

Jim Mumper Received on Tue Nov 14 1995 - 00:00:00 CET