Using Export and Import

Export and Import are two character-mode utilities supplied by Oracle Corporation as early as Oracle4. The utilities perform a function that their name implies: export creates operating-system files of data from Oracle tables and import reads these operating-system files and creates the tables and loads the data back into the tables. The two utilities are used together primarily to back up and restore data, move data to other Oracle databases, and migrate data from earlier releases of Oracle to newer releases.

The common utilities Export and Import also have some serious enhancements. Export and Import now support all objects in a particular tablespace as well as wild-card selections when table objects are selected for export.

Export and Import can perform many important tasks in the Oracle9i environment. Export can be used to store data in archives, removing rows that are not being used but can easily be added with Import if the need exists.

Export and Import can play an important backup and recovery role that will be discussed later in this section. Export and Import can be used to create test environments; they have the ability to capture all of a particular user's tables, indexes, and data and re-create these objects in another Oracle instance.

Export/Import Syntax

The operation of the Import and Export utilities is quite straightforward. Export writes the DDL (table definitions, index definitions, privileges, and so on) as well as the data itself. There are many options available to both Export and Import, such as just capturing the DDL information and not the data. Export then saves this information to named operating system files. The operating system files that Export creates are known as dump files. The dump files, which are in an Oracle proprietary format, are only use-ful to the Import utility. These dump files can be given specific names (operating-system dependent) or allowed to default to a preassigned name of EXPDAT.DMP.

Listing 17.14 shows the various parameters available for Export, and Listing 17.15 shows the various parameters available for Import.

Listing 17.14. Export Help Information

Invoking SQL*Export: $ exp help=y Export: Release 9.0.1.0.0 - Production on Sat Aug 11 15:38:22 2001 Copyright 2001 Oracle Corporation. All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password: Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords: Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) DIRECT direct path (N) TRIGGERS export triggers (Y) LOG log file of screen output STATISTICS analyze objects (ESTIMATE) ROWS export data rows (Y) PARFILE parameter filename CONSISTENT cross-table consistency CONSTRAINTS export constraints (Y) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file FLASHBACK_SCN SCN used to set session snapshot back to FLASHBACK_TIME time used to get the SCN closest to the specified time QUERY select clause used to export a subset of a table RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE TTS_FULL_CHECK perform full or partial dependency check for TTS VOLSIZE number of bytes to write to each tape volume TABLESPACES list of tablespaces to export TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TEMPLATE template name which invokes iAS mode export Export terminated successfully without warnings.

Listing 17.15. Import Help Information

Invoking SQL*Import: $ imp help=y Import: Release 9.0.1.0.0 - Production on Sat Aug 11 15:38:44 2001 Copyright 2001 Oracle Corporation. All rights reserved. You can let Import prompt you for parameters by entering the IMP command followed by your username/password: Example: IMP SCOTT/TIGER Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords: Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL import entire file (N) BUFFER size of data buffer FROMUSER list of owner usernames FILE input files (EXPDAT.DMP) TOUSER list of usernames SHOW just list file contents (N) TABLES list of table names IGNORE ignore create errors (N) RECORDLENGTH length of IO record GRANTS import grants (Y) INCTYPE incremental import type INDEXES import indexes (Y) COMMIT commit array insert (N) ROWS import data rows (Y) PARFILE parameter filename LOG log file of screen output CONSTRAINTS import constraints (Y) DESTROY overwrite tablespace data file (N) INDEXFILE write table/index info to specified file SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N) FEEDBACK display progress every x rows(0) TOID_NOVALIDATE skip validation of specified type ids FILESIZE maximum size of each dump file STATISTICS import precomputed statistics (always) RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE COMPILE compile procedures, packages, and functions (Y) VOLSIZE number of bytes in file on each volume of a file on tape The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE import transportable tablespace metadata (N) TABLESPACES tablespaces to be transported into database DATAFILES datafiles to be transported into database TTS_OWNERS users that own data in the transportable tablespace set Import terminated successfully without warnings.

Export Example

Export and Import are easily initiated with the EXP or IMP syntax. All the options can be specified on the command line, redirected from a file, or both Export and Import will prompt you for the options if no options appear on the command line. Figure 17.2 illustrates this dialog box for Import.

Using Export/Import for Backup and Recovery

Oracle9i has many backup and recovery features, including cold backups (database down and all files backed up), hot backups (occurs at the tablespace level with the database fully operational), and using RMAN (covers a variety of recovery scenarios).

Oracle's Export and Import utilities provide a different method of backing up and recovering database objects. The Oracle utilities Export and Import provide many functions to the Oracle RDBMS environment. Both are useful in moving individual objects or entire groups of objects owned by a single user from one Oracle RDBMS to another. These same utilities provide the Oracle9i environment with a way of incrementally backing up only those objects that have changed since the prior incremental backup. The hot and cold backup methods concentrate on copying the database files assigned to the tablespaces. This incremental backup method utilizing Export and Import concentrates on copying the objects out of the tablespace and has nothing to do with the database files assigned to the tablespaces.

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. Consult with your DBA if there is some question as to whether this CATEXP.SQL script was installed or not.

There are some different settings for the export to support this incre-mental backup mode. These options are FULL=Y and INCTYPE=COMPLETE (or CUMULATIVE or INCREMENTAL).

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.

Figure 17.3 illustrates 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 the changed objects from Point A to Point B. Point C is the first cumulative backup. At this point, the cumulative file contains all 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 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.

The reason for all these levels is time. It takes longer to do the complete export than to do the cumulative and/or incremental exports. This system was designed to work best with the following scenario: perform complete exports once a month, perform cumulative exports on each of the following weekends, and perform 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 changes made on that particular day. This incremental backup 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.

Listing 17.16 illustrates two weeks' worth of backups. This scenario performs a complete export (begins with an F) on the first of the month and cumulative backups on the weekends. An incremental backup is performed daily. Each of the incrementals only contains changes from the prior complete (F) or cumulative. The cumulative contains all the changes from the last cumulative or the last complete.

Listing 17.16. Complete, Cumulative, and Incremental Backup Scenario

F_day1.exp I_day2.exp I_day3.exp I_day4.exp I_day5.exp I_day6.exp C_day7.exp I_day8.exp I_day9.exp I_day10.exp . . .

When restoring a series of exported files, it is important to do the last export first with the INCTYPE=SYSTEM to restore the Oracle9i data dictionary.

This parameter needs to be done first to restore any changes to objects owned by SYS. An INCTYPE=RESTORE will restore all other objects except those owned by SYS. Run these non-SYS restores in the order that they were created.

The recovery scenario in Listing 17.17 is based on the same backups illustrated in Listing 17.16. For the example, let's say a user's table got dropped on the 11^th and was last updated on the 7^th. The recovery scenario in Listing 17.17 would recover that lost object. The last backup needs to be run first with the INCTYPE=SYSTEM first, then the series of export files needs to be recovered with import using the INCTYPE=RESTORE.

Listing 17.17. Complete, Cumulative, and Incremental Backup Scenario

IMP system/manager FULL=Y INCTYPE=SYSTEM FILE=I_day10.exp IMP system/manager FULL=Y INCTYPE=RESTORE FILE=C_day7.exp IMP system/manager FULL=Y INCTYPE=RESTORE FILE=I_day8.exp IMP system/manager FULL=Y INCTYPE=RESTORE FILE=I_day9.exp IMP system/manager FULL=Y INCTYPE=RESTORE FILE=I_day10.exp