Incremental Backups

Oracle Export can be used to get individual data objects, functions, packages, user definitions, permissions, entire tablespaces, and even the entire database into operating-system files. Import can then import what was exported, to either the same Oracle instance or another Oracle instance. Export and Import tend to be rather slow, are sometimes limited by operating-system file limits, and are not well suited for very large databases (VLDB).

Oracle Export and Import also provide the method for incrementally backing up only those objects that have changed since the prior incremental backup. There are three levels of export for object backup and recovery: complete export, cumulative export, and incremental export. The level of the export is controlled by a parameter on the command line or in the Export parameter file. There are also three objects that track the time and type of Export backup: SYS.INCEXP, SYS.INCFIL, and SYS.INCVID, which are optionally created by the CATEXP.SQL file at database install time.

These three levels of export backup all build on one another. Incremental backups only have changes made to objects from the most recent incremental or cumulative export, in this order. Cumulative exports have all changes made to objects from the most recent cumulative or complete export backup. Complete export backups have all changes to objects from the next previous complete export. The three levels of export supersede each other, in that, when a complete export is done, all prior complete, cumulative, and incremental exports become obsolete. Similarly, when a cumulative export is done, all incremental exports to the next-most-recent cumulative export become obsolete. Incremental exports are the lowest level of export in this backup scenario.

The reason for all these levels is a factor of time. It takes longer to do the complete export than to do the cumulative and/or incremental export. This system was designed with the following model in mind. Do complete exports once a month. Do cumulative exports on each of the following weekends, and do incremental exports daily. Each cumulative export will contain all the changes to the database from either the previous cumulative or the complete export. The incremental exports will contain the tables (and all of their rows) with changes made on that particular day. This scenario is best used in a development or an environment with many end users doing their own object manipulation. This method of export/import gives the DBA great flexibility to restore single objects or data that was inadvertently dropped or deleted.

---

NOTE:  
Export with the Direct Path option is not available for complete, cumulative, or incremental exports.

---

The syntax for the Export process is the same as previously discussed with these additional options: FULL=Y and INCTYPE = COMPLETE (or CUMULATIVE or INCREMENTAL).

The Import process also has additional options: FULL=Y and INCTYPE = SYSTEM or RESTORE. The SYSTEM parameter will only restore changes owned by SYS. No user objects or data will be restored. Conversely, RESTORE will restore all other changes except for those owned by SYS.

When restoring a series of exported files, it is important to do the last export first with the INCTYPE=SYSTEM, and then do the necessary imports to restore the non-SYSTEM objects, in the correct chronological order, using the INCTYPE=RESTORE option.

---

NOTE:  
Import behaves differently in this mode in that it does not attempt to drop any tables first. It will be necessary to manually drop user objects to prevent duplicate rows in the object.

---

Figure 23.2 shows a typical incremental backup scenario utilizing the Export process. Point A on the time line is the complete backup. Point B is the first incremental backup and contains all of the changed objects from Point A to Point B. Point C is the first cumulative backup. At this point, the cumulative file contains all of the changed objects to the database from Point A, including those changed objects captured in the incremental backup of Point B. At Point C, the incremental backup from Point B is no longer of any value. The same is true of the relationship of Point C to Point F. Point F will contain all of the changed objects to the database from Point C, the last most-previous-incremental backup at the same level. Each level (complete, cumulative, or incremental) will contain the changed objects from the last incremental export of the same level.

Figure 23.2.  A typical incremental Export scenario.

To review a recovery scenario, say we have two weeks worth of incremental-type exports (Listing 23.1), beginning at the first of the month with a complete export, having cumulative exports on the weekend, and daily incremental exports. The filenames have the day of the week and the type of export: F being complete, C being cumulative, and I being incremental.

Listing 23.1. Incremental export example.

 1: F1.exp

 2: I2.exp

 3: I3.exp

 4: I4.exp

 5: I5.exp

 6: I6.exp

 7: C7.exp

 8: I8.exp

 9: I9.exp

10: I10.exp

11: .

12: .

13: .

Now say some objects were corrupted on the 11th and were last updated on the 7th. A DBA could use the following scenario (Listing 23.2) to restore those objects. Remember, run the last export twice, once at the beginning with the SYSTEM parameter set and again last with the RESTORE parameter set.