V6 - INCREMENTAL EXPORT/IMPORT

------------------------------
Incremental export is an enhancement to export/import in v6 of the Rdbms. Thisnew feature of export allows, under certain conditions, the dba to shorten theexport time, but still have the reliability of export/import for recoverypurposes. Incremental exports will only export tables that have changedsince the last export taken with the inctype parameter. If one row of atable is updated, then the entire table is exported NOT just the changed data.The benefits occur when you have historical tables, or tables that areprimarily query only. This is where the savings in time occurs. Therefore,if all (or most) of the tables in your database are updated daily, littlebenefit is gained from incremental exports. Another important factor toconsider is the fact that incremental imports, at recovery time, will drop thetable before importing it. For a database that have very large tables thatchange everyday, recovery can be time consuming.

Three types of incremental exports exist, the differences between the threeis the amount of data they export.

1. Complete export (equivalent to full database export) exports all objects and clears the incremental export tables. Although complete exports will export the same information as full exports, complete will also mark the tables, which is essential for the next cumulative or incremental export.

2. Cumulative export will export all changed objects since the last'complete' or 'cumulative' export.


3. Incremental export will export all changed objects since the last complete', 'cumulative' or 'incremental' exports.

Each export will insert information into the incremental export tables, however,Complete exports will clear the tables before inserting new information.
For example, here is the export scheme for a mock company:

1. Complete export on the first day of the month.
2. Cumulative exports every Sunday.
3. Incremental exports every day.

Below is a picture of this backup scheme:
*******************************************************************************
M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30X I I I I I C I I I I I I C I I I I I I C I I I I I I C I I
*******************************************************************************


The cumulative export on the 7th, will include all the objects changed since thecomplete on the 1st. This cumulative contains the same information as incrementexports on the 2nd through the 6th and more, therefore cumulative exports arenot manditory, their benefit comes during recovery. Instead of six files toapply, there is only one.

RECOVERY:
In order to recover from incremental exports, you must initialize the database,and begin importing. Since many of the export files contain the same tables,incremental imports will always drop the object before importing. This prevents duplicate data from being imported. This is how an incrementalimport creates tables:

. importing user JACK "DROP TABLE "SALGRADE"" "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) P" "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 10240 NEXT 102" "40 MINEXTENTS 1 MAXEXTENTS 99
PCTINCREASE 50) TABLESPACE "SYSTEM""


Before beginning to import the data, you must have the database structure backto the last export. This is done by importing the last incremental exportfirst with "inctype=system" specified. The only information imported aresystem objects including tablespaces, users, and rollback segments. Now thatthe database structure has been recovered and the users all exist, the datacan be imported. We begin with the complete export, with "inctype=restore"specified. Continue with the cumulative exports, and finally the incrementalexports.

NOTE: the last incremental export is imported twice, once at the beginningof the recovery with 'inctype=system' for system objects, and again with'inctype=restore' for the current copy of the data.

Recovery will consist of the following imports:
IMP SYSTEM/MANAGER INCTYPE=SYSTEM FULL=Y FILE=I30 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=X1 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C7 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C21 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=C28 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=I29 IMP SYSTEM/MANAGER INCTYPE=RESTORE FULL=Y FILE=I30


Below is a description of the incremental export tables owned by SYS and whatinformation they store:

SQL> desc sys.incexp Name Null? Type -------------------- -------- ---- OWNER# NOT NULL NUMBER - id # of owner = user_id in dba_users. NAME NOT NULL CHAR(30) - name of object*TYPE NOT NULL NUMBER(1) - type of object # CTIME DATE - date exported ITIME NOT NULL DATE - EXPID NOT NULL NUMBER(3) - id of file
*Key to column TYPE: 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE


SQL> desc sys.incvid Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) - HOLDS NEXT ID NUMBER.

SQL> desc sys.incfil Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) = (sys.incexp.EXPID)*EXPTYPE NOT NULL CHAR(1) - represents type of exp EXPFILE NOT NULL CHAR(100) - filename EXPDATE NOT NULL DATE - date exported EXPUSER NOT NULL CHAR(30) - user exported.
*Key to column EXPTYPE: X=Complete C=Cummulative I=Incremental

Read more at http://oracleonline.info