Oracle7 Server Utilities

Incremental, Cumulative, and Complete Exports

Incremental, cumulative, and complete exports provide time- and space-effective backup strategies. This section shows how to set up and use these export strategies.

Restrictions

You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). Only users who have the EXP_FULL_DATABASE role can run incremental, cumulative, and complete exports. This role contains the privileges needed to modify the system tables that track incremental exports. Those tables are discussed at the end of this section.

Base Backups

If you choose to use cumulative and incremental exports, it is advised that you periodically perform a complete export to create a base backup. Following the complete export, the administrator can take frequent incremental exports and occasional cumulative exports. After a givenperiod of time, the cycle should begin again with another complete export.

Incremental Exports

An incremental export backs up only tables that have changed since the last incremental, cumulative, or complete export. An incremental export exports the table's definition and all its data, not just the changed rows. Incremental exports are typically done more often than cumulative or complete exports.

Figure 1 - 3 shows an incremental export at time 1, after 3 tables have been modified. Only the modified tables (and associated indexes) are exported.

Figure 1 - 3. Incremental Export at Time 1

Figure 1 - 4 shows another incremental export at time 2, after 2 tables have been modified. Table 3 was modified a second time, so it is exported at time 2 as well as at time 1.

Figure 1 - 4. Incremental Export at Time 2

Note: Incremental exports cannot be specified as read-consistent.

Cumulative Exports

A cumulative export backs up tables that have changed since the last cumulative or complete export. In essence, a cumulative export compresses a number of incremental exports into a single cumulative export file. It is not necessary to save incremental export files taken before a cumulative export because the cumulative export file replaces them.

Figure 1 - 5 shows a cumulative export at time 1, after 3 tables have been modified. The modified tables (and associated indexes) are exported. This export is equivalent to an incremental export.

Figure 1 - 5. Cumulative Export at Time 1

Figure 1 - 6 shows a cumulative export at time 2, after 2 tables have been modified. This time, the two tables modified at time 2 are exported, and the tables modified at time 1 are exported as well.

Figure 1 - 6. Cumulative Export at Time 2

This cumulative export file is a combination of the incremental exports from time 1 and time 2, except that table 3 (which was modified at both times) only occurs once in the export file. In this way, cumulative exports save space over multiple incremental exports.

Complete Exports

A complete export establishes a base for incremental and cumulative exports. It is equivalent to a full database export, except that it also updates the tables that track incremental and cumulative exports.

Figure 1 - 7 shows a complete export at time 2. With the complete export, all objects in the database are exported regardless of when (or if) they were modified.

Figure 1 - 7. Complete Export

Benefits

Incremental and cumulative exports help solve the problems faced by administrators who work in environments where many users create their own tables. For example, administrators can restore tables accidentally dropped by users.

The benefits of incremental and cumulative exports include:

smaller export files

less time to export

These benefits result because not all tables have changed. So the time and space required for an incremental or cumulative export is shorter than for a full database export.

A Scenario

The following scenario shows how you can use cumulative and incremental exports.

Assume that as manager of a data center, you do the following tasks:

a complete export (X) every three weeks

a cumulative export (C) every weekend

an incremental export (I) every night

Your export schedule follows:

DAY: 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21

     X  I  I  I  I  I  C  I  I  I  I  I  I  I  C  I  I  I  I I  X

To restore through day 18, you would first import the system information from the incremental export taken on day 18. You would then import the data from:

the complete export taken on day 1

the cumulative export taken on day 7

the cumulative export taken on day 15

and three incremental exports taken on days 16, 17, and 18

The incremental exports on days 2 through 6 can be discarded on day 7, when the cumulative export is done, because it incorporates all of them. Similarly, the incremental exports on days 8 through 14 can be discarded after the cumulative export on day 15.

Note: The section "Incremental, Cumulative and Complete Imports" on page 2-29 explains how to do an incremental import.

Which Data Is Exported?

The purpose of an incremental or cumulative export is to identify and export only those database objects (such as clusters, tables, views, and synonyms) that have changed since the last export. Each table is associated with other objects that you can export. Besides the data itself, there are indexes, grants, audits, and comments.

The entire grant structure for tables or views is exported with the underlying base table(s). Indexes are exported with their base table, regardless of who created the index.

Export automatically exports a read-consistent view of a table, even if the table is being updated during the export.

Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for export.

Also, if database structures have changed in the following ways, then the underlying base tables and data are exported:

a table is created

a table definition is changed by an ALTER TABLE statement

comments are added or edited

auditing options are updated

grants (of any level) are altered

indexes are added or dropped

index storage parameters are changed by an ALTER INDEX statement

In addition, the following data is backed up:

all system objects (including tablespace definitions, rollback segment definitions, and user privileges, but not including temporary segments)

information about dropped objects

clusters, tables, views, and synonyms created since the last export

Command Syntax

The command syntax is as follows:

where:

INCREMENTAL Exports all database objects that have changed since the last incremental export, as tracked by table SYS.INCEXP, then updates the table with a new ITIME and EXPID.
CUMULATIVE Exports all database objects that have changed since the last cumulative export, as tracked by SYS.INCEXP, then updates the table with a new CTIME, ITIME, and EXPID.
COMPLETE Exports all objects and updates the tables SYS.INCEXP and SYS.INCVID. (A FULL=Y export does not update these tables unless you specify the INCTYPE parameter.).

Note: See for definitions of ITIME, EXPID and CTIME.

Note: For incremental and cumulative exports, a row is also added to table SYS.INCFIL to identify the export file and the user doing the export. For more information on the system tables that support incremental export, see . When exporting with the parameter "INCTYPE = COMPLETE", all the previous entries are removed from SYS.INCFIL and a new row is added specifying an "x" in the column EXPTYPE.

Example Incremental Export Session

The following example shows an incremental export session after the tables SCOTT.EMP and SCOTT.DEPT are modified:

> exp system/manager inctype=incremental

...

About to export the entire database ...

. exporting tablespace definitions

. exporting profiles

. exporting user definitions

. exporting roles

. exporting resource costs

. exporting rollback segment definitions

. exporting database links

. exporting sequence numbers

. exporting job queues

. exporting refresh groups and children

. exporting cluster definitions

. exporting stored procedures

. about to export SYSTEM's tables ...

. about to export SCOTT's tables ...

. exporting table                    DEPT        7 rows exported

. exporting table                     EMP       22 rows exported

. about to export ADAMS's tables ...

. about to export JONES's tables ...

. about to export CLARK's tables ...

. about to export BLAKE's tables ...

. exporting referential integrity constraints

. exporting triggers

. exporting posttables actions

. exporting synonyms

. exporting views

. exporting default and system auditing options

. exporting information about dropped objects

Export terminated successfully without warnings.

System Tables

SYS owns three tables (INCEXP, INCFIL, and INCVID) maintained by Export/Import. None of these tables should be altered in any way.

SYS.INCEXP

Export maintains a table to track which objects were exported in specific exports. That table, SYS.INCEXP, contains these columns:

OWNER# The userid of the schema containing the table.
NAME The object name. With OWNER#. The primary key consists of OWNER#, NAME and TYPE.
TYPE The type of the object (a code standing for INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY).

CTIME The date and time of the last cumulative export that included this object.
ITIME The date and time of the last incremental export that included this object.
EXPID The ID of the incremental or cumulative export, also found in table SYS.INCFIL.

You can use this information in several ways. For example, you could generate a report from SYS.INCEXP after each export to document the export file.

SYS.INCFIL

The table SYS.INCFIL tracks the incremental and cumulative exports and assigns a unique identifier to each. This table contains the following columns:

EXPID The ID of the incremental or cumulative export, also found in table SYS.INCEXP.

EXPTYPE The type of export (incremental or cumulative).
EXPFILE The name of the export file.
EXPDATE The date of the export.
EXPUSER The username of the user doing the export.

SYS.INCVID

A third table, SYS.INCVID, contains one column for the EXPID of the last valid export. This information determines the EXPID of the next export.

INCREMENTAL	Exports all database objects that have changed since the last incremental export, as tracked by table SYS.INCEXP, then updates the table with a new ITIME and EXPID.
CUMULATIVE	Exports all database objects that have changed since the last cumulative export, as tracked by SYS.INCEXP, then updates the table with a new CTIME, ITIME, and EXPID.
COMPLETE	Exports all objects and updates the tables SYS.INCEXP and SYS.INCVID. (A FULL=Y export does not update these tables unless you specify the INCTYPE parameter.).

OWNER#	The userid of the schema containing the table.
NAME	The object name. With OWNER#. The primary key consists of OWNER#, NAME and TYPE.
TYPE	The type of the object (a code standing for INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY).

CTIME	The date and time of the last cumulative export that included this object.
ITIME	The date and time of the last incremental export that included this object.
EXPID	The ID of the incremental or cumulative export, also found in table SYS.INCFIL.

EXPTYPE	The type of export (incremental or cumulative).
EXPFILE	The name of the export file.
EXPDATE	The date of the export.
EXPUSER	The username of the user doing the export.