Section 16.8. Logical Backups

16.8. Logical Backups

Physical backups protect you from physical damage, such as a damaged disk drive. A logical backup protects you from logical damage, such as when your DBA accidentally deletes an important table. Logical backups are done by Oracle's data pump or export utility, which stores the data in a binary file that is useful only to Oracle.

Data pump and export are really used only for data exchange between databases. It has a number of disadvantages when using it for backups:

No transaction recovery

It is very important to note that exports are a picture of the database at some point in time, and they can be used to recover that table only to that point in time. There is no way to apply transaction logs (redo logs) on top of an imported table to bring it up to date. So although it might be quicker to restore a table using an import, it might be better just to restore the entire tablespace. The recover until change option can be used to redo all transactions except for the one that deleted the table.

Longer than physical backups

Exports usually take longer than physical backups because there is a lot of checking going on during the process. Depending on the speed of the system, this may be a little bit longer or quite a bit longer!

Full export requires restrict mode or the consistent=y option

You need to ensure that the export is consistent. One way to do that is to shut down and then open the database in restrict mode. That way no one can make changes to the database while the export is running. (Of course, this means that no one can access it either.) Another way is to add the consistency=y option to the export. This causes the export to view the entire database as it existed at the moment the export started.

16.8.1. Performing a Logical Backup

The type of export covered here is an export of the entire database, also known as a full export. The commands to do a full export are found in Example 16-8. Substitute the appropriate username, passwd, and file_name. (The username and password need to have the appropriate permissions to do an export.)

Example 16-8. Sample database export

$ exp userid=username/passwd full=Y consistent=Y constraints=Y file=file_name

This performs a full (every table) export of ORACLE_SID to file_name. For more information on the exp command, consult the manual.

16.8.2. Recovering with a Logical Backup

If you made any logical backups, or exports, using Oracle's exp utility, they may be imported using Oracle's imp utility. In order to use the following command, you must substitute the appropriate username, passwd, and file_name. For level, you need to use system or restore. (When to use these levels is covered next.)

$ imp  username/passwd   inctype= level   full=Y file= file_name