I just asked about backing up our database server. (There's a new backup 
procedure coming in.) For my trouble, they asked if I could just script 
the database backup to put the files onto a different server.

This is no use at all; I need to backup the entire server, including the 
OS, so that if there's a problem I can just shove in a tape and put 
everything back to normal, rather than manually installing the OS, 
device drivers, Oracle, etc.

But here's the worrying this: The database admin from HQ sent me a copy 
of "the backup script we use here".

And guess what? It isn't a backup. It's an export.

For those of you who aren't Oracle experts:

- A *backup* is where you take the database files and copy them.

- An *export* is where you run a special program that extracts the data 
from the database and puts it into a (binary) flat file.

An export allows you to *recreate* the database, but it isn't "the 
database itself". It contains all the _data_ from the database, but none 
of the _metadata_.

In particular, to use an export file, you "import" it. This is 
equivilent to running lots of SQL "insert" statements. In particular, 
importing data causes triggers to fire. The documentation says you need 
to think about whether triggers should be disabled before the import begins.

How great would that be? You "restore" your "backup" and the system 
modifies it as you restore it!

The thing is, exports are really designed for transferring data from DB 
to DB, possibly running incompatible versions of Oracle. It's not really 
a good idea for "backups". (Although some of the official Oracle 
documentation suggests that is it.)

Now a *real* backup includes all the database configuration metadata and 
so forth. And best of all, if you follow best practices, you don't have 
to shut the database down to back it up, and you can do point-in-time 
recovery. Basically you can rewind the database to any second in time, 
not just to the instant when a "backup" was taken.

Export, on the other hand, runs as a normal database transaction. In 
particular, since it is usually a very *long* transaction, it can cause 
problems with overflowing transaction logs and so forth.

In summary:

- Backups
   - Run while the database is in use. (Slows it down slightly, obviously.)
   - Recover the database to any point in its history, not just when a 
"backup" happened.
   - The restored database is *identical* to the original, down to the 
bit-pattern level.

- Exports
   - Slower than backups. (Produce smaller files though.)
   - Contain "logical" structures only. No undo data, no transaction 
logs, nothing.
   - Running an export is a transaction. The undo buffer has to be large 
enough to copy.
   - Running an import is a transaction. Applicable triggers will fire.
   - Point-in-time recovery is impossible.
   - Can move data from DB to DB. (This is the designed purpose, actually.)

If you do an export and your server goes down, once the server OS is 
back and Oracle is installed, you have to create an empty database, 
recreate all the user account info, and then import all the data. Then 
you need to put back any triggers you're supposed to have, possibly move 
tables into the correct tablespaces, and probably recreate your indexes. 
(Alternatively, keep the indexes in the export and watch it go even slower.)

To recover using a backup, once the OS and Oracle are back, you copy the 
files back to their original locations, and start Oracle. It will 
complain about the files being inconsistent. Tell it to perform a 
recover. (Usually takes a few seconds.) Now open the database. You are done.



I am SORELY TEMPTED to tell this admin what an idiot he is...

...but on the other hand, it's been a while since I did any serious 
Oracle stuff. If I don't get all of my facts straight, it will be *me* 
who looks stupid. I'd hate to write a long email explaining what a 
smart-arse I am and then discover that I got some small technical detail 
wrong and actually the other guy was right all along.

Oh the frustrations of working here! >_<

Post a reply to this message