Misconception: Export is a good way to back up the database

 

Jeremiah Wilton

jwilton@speakeasy.net

 

Many guides to backup and recovery give the Export and Import utilities billing equal to physical backup methods.  As a method, Export and Import provide uniformly inferior reliability on backups, as well as recoveries.  In addition to being failure prone, mean time to recovery with Import is much longer than comparative recoveries using physical backups. Export creates “dump” files that contain the DDL for all objects and the table data in the database.

Much of Export’s reliability problem stems from the fact that it extracts data using SQL and read consistency (like a query), making exports prone to “ORA-01555 Snapshot too old: Rollback segment with name <RBS> too small.”  Export stands the highest probability of encountering ORA-01555 when using the CONSISTENT=Y argument.  This feature forces the export to extract all table data consistent as of the point in time that the export is started.  If the export takes an hour to complete, it must generate consistent reads an hour old.  As even moderately utilized databases scale, the likelihood of successfully completing the consistent export becomes slimmer.

Even if export is performed without CONSISTENT=Y, it doesn’t mean that read consistency is “shut off” for the exporting session.  It just means that each table will be exported consistent with itself, but not necessarily consistent with other tables.  If you have a very large table, the possibility of ORA-01555 may be just as likely.  Furthermore, since tables are not exported consistently with each other, it is very likely that during an import, parent-child relationships will be out of sync, and Import will fail to successfully enable foreign key constraints.  This makes potential “recoveries” using Import unpredictable and highly prone to failure.

In addition to the above problems, several steps must be taken prior to using Import to restore an entire database.  The database must be created using CREATE DATABASE.  Unless someone has thought to remember to save the original create database command, it will be necessary to fumble around until this step has been completed.  If any space has been subsequently added to the SYSTEM tablespace since inception, that space must be made available before import, or else the import will fail.

Another reason not to use Import as a backup/recovery method is the poor mean time to recovery, when compared with physical backups/restores.  For every block of data that import restores, it must read from disk, process into an insert statement, use rollback and redo to load it, and eventually commit the data. All told, this probably consists of over 100 calls per block, on average.   In contrast, for every block restored from a traditional physical backup, it is only necessary to read the block from the backup media, and write it to disk.

Many proponents of using Export as a regular part of their backup routine cite the need to do a single-table restore, or a point-in-time recovery of just one table.  Unfortunately, this method does not really allow a point-in-time recovery, unless that point in time happens to be one of the points in time that an export was actually taken.  Since media recovery cannot be applied to exports, you are stuck with the data in the table as it was as of the last export, and any data changed since then is lost, or stale.

The propensity to rely on exports as protection from such single table data loss points out another common misconception, that single-table restores/recoveries cannot be accomplished with physical backups.  On the contrary, with very little practice, a DBA can learn to restore a small subset of the physical backup as a clone of the original database (even on the same host!).  This clone, consisting only of the tablespace containing the desired data, the SYSTEM tablespace, and any tablespaces containing online rollback segments at the time of the backup, can be rolled forward to any point in time.  If someone has accidentally deleted data, truncated or dropped a table, then the clone can be recovered to a point just prior to the damage, and opened.  Once the clone is opened, the table in question can be exported and imported into the production database.