export & import matrix

오라클 export & import 를 할때, 어떤 버전끼리 되는지 고민되는 때가 있는데, 이에 대한 문서를 보면 도움이 된다..

[Metalink 문서번호 132904.1]

PURPOSE
-------
This article describes several issues related with the compatibility of the
different versions of the Oracle EXPORT and IMPORT utilities.

SCOPE & APPLICATION
-------------------
The article is intended for users of the Oracle7, Oracle8, Oracle8i,
Oracle9i, and Oracle10g databases who wish to use the EXPORT and IMPORT
utilities to export data from an Oracle database release x and import
this data into an Oracle database release y.  The article gives information
which version of the utility to use when performing the export and import,
and how to prepare the Oracle data dictionary before exporting the data.
For the transportable tablespaces export/import, see:
Note 291024.1 "Compatibility and New Features when Transporting Tablespaces
with Export and Import"

SUMMARY
-------
1. Export the data with the Export utility of the lowest database version
  involved.

2. Import the data with the Import utility of the target database.

3. Running an Oracle7 Export utility against an Oracle9i database is not
  supported.

COMPATIBILITY MATRIX FOR EXPORT & IMPORT BETWEEN DIFFERENT ORACLE VERSIONS
--------------------------------------------------------------------------

Introduction.
-------------

With the Oracle EXPORT and IMPORT utilities you can transfer data objects
between Oracle databases, even if they reside on platforms with different
hardware and software configurations.

The Oracle EXPORT utility extracts the object definitions and table data
from an Oracle database and stores them in an Oracle binary-format export
dump file located typically on disk or tape.  The files can then be
transferred using FTP or physically transported (in the case of tape)
to a different site.  When transferring the export dump file over a network,
the file has to be transmitted in binary mode.  Transmitting export files in
character mode (ASCII) causes errors when the file is imported, resulting
most likely in: IMP-9 or IMP-10, followed by IMP-0.

The Oracle IMPORT utility reads the object definitions and table data
from the export dump file and then inserts the information into the Oracle
database.

Starting with Oracle10g Release 1 (10.1.0.x) we have introduced the new
Export DataPump (expdp) and Import DataPump (impdp) utilities.  Export
dumpfiles created with Export DataPump can only be read by Import DataPump.
And export dumpfiles created with the original Export utility cannot be read
by the Import DataPump client.

Data Dictionary Views.
----------------------

Before using the EXPORT or IMPORT utility, the Data Dictionary has to be
prepared with the views that are needed by these utilities.  This can be done
by running the script:

  UNIX   : SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql

  Windows: SQL> @%ORACLE_HOME%\rdbms\admin\catexp.sql

This script CATEXP.SQL has to be run by the user SYS.  The script is called
automatically by the CATALOG.SQL script.  Both scripts CATEXP.SQL and
CATALOG.SQL need to be run only once on a database.  Normally, you never
need to run it again before you perform an export or import (the errors
EXP-24 or IMP-23 indicate that the views are not correct anymore).

The script CATEXP.SQL performs the following tasks to prepare the database
for an export and/or import:

- create the required export and import views in the data dictionary;
- create the EXP_FULL_DATABASE role and the IMP_FULL_DATABASE role;
- assign all necessary privileges to the EXP_FULL_DATABASE role and the
  IMP_FULL_DATABASE role;
- assign EXP_FULL_DATABASE and IMP_FULL_DATABASE to the DBA role;
- update table sys.props$ with the version of the export and import views
  (see the notes at the end of the article).

When creating an Oracle7 export file from an Oracle8/8i database by running
the Oracle7 EXPORT utility against the Oracle8/8i server (see details below),
the user SYS must have run the CATEXP7.SQL script on the Oracle8/8i database:

  UNIX   : SQL> @$ORACLE_HOME/rdbms/admin/catexp7.sql

  Windows: SQL> @%ORACLE_HOME%\rdbms\admin\catexp7.sql

This script creates the export views that make the database look, to EXPORT,
like an Oracle7 release database.  This means that the views will not see any
new Oracle8/8i specific objects, so the export file can be imported
without problems in the Oracle7 database.

Keep in mind that both the scripts CATEXP.SQL and CATEXP7.SQL only need to
be run once: CATEXP.SQL creates the exu8% views which are used by the
Oracle8/8i/9i/10g EXPORT utilities, and CATEXP7.SQL creates the exu7% views
which are used by the Oracle7 EXPORT utilities.
So if both scripts have been run by SYS, you do not need to run them again.

The procedure mentioned above, also applies for creating an Oracle6 export
dump file from an Oracle7 database.  In this case the user SYS must have run
the CATEXP6.SQL script on the Oracle7 database.

Note that running an Oracle7 Export utility against Oracle9i database is not
supported: in a downgrade scenario, we only support one functional release
back. This also means that running an Oracle8 Export utility against an
Oracle10g database is not supported (currently, it is only supported to run
the Oracle8i 8.1.7 export utility or higher against the Oracle10g database).

Database Migration to Oracle9i / Oracle10g with a full database export/import.
------------------------------------------------------------------------------

If the database is migrated to Oracle9i or Oracle10g, there is a limitation
regarding a direct upgrade with a full database export and a full database
import.  This *only* applies to the FULL mode (FULL=Y) of the database
import from an export dump file created in FULL mode.
It remains possible to:
- import data in USER mode (FROMUSER=... TOUSER=...)
- import data in TABLE mode (FROMUSER=... TABLES=...)
- export and import data in TABLESPACE mode (TRANSPORT_TABLESPACE=Y)
Note that an import client is technically capable to read export dump files
created by EXPORT release 5.1.22 and higher (up to same version).
This migration limitation is related to the available upgrade paths
as described in the Migration manual. The main reason for only supporting
a direct migration from the terminal release of a major version (like 8.1.7
for Oracle8i), to a new major version (like 9.2.0 for Oracle9i) is the fact
that this terminal release was the latest release. I.e.:
- for Oracle7, the release 7.3.4. is the latest released version,
- for Oracle8, the release 8.0.6. is the latest released version,
- for Oracle8i, the release 8.1.7. is the latest released version.
Any known problems (and in particular data dictionary related issues)
that were identified in the earlier versions of the major release
(like 8.1.5 and 8.1.6) are very likely fixed in the latest release (8.1.7)
or with the latest patchset for that release (8.1.7.4.0).
See also sections 'Basic Compatibility' and 'Limitations' below for details.

1. Migration to Oracle9i release 2 - 9.2.0.x :
  -------------------------------------------
  Direct migration with a full database export and full database import
  is only supported if the source database is:
  - Oracle7 : 7.3.4
  - Oracle8 : 8.0.6
  - Oracle8i: 8.1.7
  - Oracle9i: 9.0.1

2. Migration to Oracle10g release 1 - 10.1.0.x :
  ---------------------------------------------
  Direct migration with a full database export and full database import
  is only supported if the source database is:
  - Oracle8 : 8.0.6
  - Oracle8i: 8.1.7
  - Oracle9i: 9.0.1 or 9.2.0

3. Migration to Oracle10g release 2 - 10.2.0.x :
  ---------------------------------------------
  Note that you must first apply the specified minimum patch release
  (or any higher patch release) !
  Direct migration with a full database export and full database import
  is only supported if the source database is:
  - Oracle8i :  8.1.7.4
  - Oracle9i :  9.0.1.4 (or higher) or 9.2.0.4 (or higher)
  - Oracle10g: 10.1.0.2 (or higher)

Examples:
1. From 8.1.7.4 to 9.2.0.7: Full database export with the 8.1.7.4 export
  utility, and full database import with the 9.2.0.7 import utility is
  a supported migration method.

2. From 8.0.5.0 to 10.1.0.2: Full database export with the 8.0.5.0 export
  utility, and full database import with the 10.1.0.2 import utility is
  *NOT* a supported migration method.
  Possible alternatives:
  a. First upgrade the 8.0.5.0 database to 8.0.6.0, apply latest patchset
     8.0.6.3 and afterwards you can migrate with a full database export
     with the 8.0.6.3 export utility, and a full database import with the
     10.1.0.2 import utility.
  b. Or do a full database export with the 8.0.5.0 export utility,
     pre-create the users in the Oracle10g datatabase, and do a schema level
     import with the 10.1.0.2 import utility.

3. From 9.2.0.1 to 10.2.0.1: First apply the 9.2.0.4 patchset (or any higher
  patchset release, such as 9.2.0.7). Full database export with the 9.2.0.4
  export utility (resp. 9.2.0.7), and full database import with the 10.2.0.1
  import utility is a supported migration method.

Basic Compatibility.
--------------------

Definition Source database = the database where the data is exported from.
Definition Target database = the database where the data is imported into.

1) Always use a version of the EXPORT utility that is equal to the
  lowest version of either the source or the target database.
  This means:
  a) When creating an export dump file for an import into a higher release
     database (e.g.: from Oracle8i to Oracle9i), use a version of the
     EXPORT utility that is equal to the version of the source database
     (= lowest version = Oracle8i in this case).
     The export fails if you use a higher release export version.
     For example, if you use the export 9.0.1 utility the export data
     from an 8.1.7 database, you will get the errors:
        EXP-56 Oracle error 942 encountered
        ORA-942 table or view does not exist
        EXP-0 Export terminated unsuccessfully
     Solution: use the lowest release export utility (8.1.7 in this case).
  b) When creating an export dump file for an import into a lower release
     database (e.g.: from Oracle9i to Oracle8i), use a version of
     the EXPORT utility that is equal to the version of the target database
     (= lowest version = Oracle8i in this case).
     (1) When creating an Oracle6 export file from an Oracle7 database by
         running the Oracle6 EXPORT utility against the Oracle7 server,
         the user SYS must first run the CATEXP6.SQL script on the Oracle7
         database. This script creates the export views that make the
         database look, to EXPORT, like an Oracle6 release database.
     (2) When creating an Oracle7 export file from an Oracle8/8i database
         by running the Oracle7 EXPORT utility against the Oracle8/8i
         server, the user SYS must first run the CATEXP7.SQL script on the
         Oracle8/8i database. This script creates the export views that
         make the database look, to EXPORT, like an Oracle7 release
         database.
     (3) When creating an Oracle8/8i export file from an Oracle9i database
         by running the Oracle8/8i EXPORT utility against the Oracle9i
         server, you do not have to take any special steps. I.e., you do
         not need to run the 8i version of catexp.sql against the 9i database.
         You only need to run the 8i exp executable against the 9i database.
         Be aware that several Oracle9i specific features are not
         supported/exported (e.g. no LOBs and objects are exported when
         using DIRECT=YES).  See the "Oracle9i Database Utilities" manual
         for a complete list of these restrictions).
2) With some patchsets the data dictionary export views are also changed.
  As a result it will not be possible to run this patched Export utility
  against an unpatched database. Therefore the same basic rule also applies
  to the patchset release: Export the data with the Export utility of the
  lowest database version involved.
  Example: an export with 9.2.0.5 export client from a 9.2.0.1 database can
  give the errors:
     EXP-00008: ORACLE error 942 encountered
     ORA-00942: table or view does not exist
     EXP-00024: Export views not installed, please notify your DBA
     EXP-00000: Export terminated unsuccessfully
  To successfully export from a 9.2.0.1 database (select status, version,
  comp_id from dba_registry;) that is located in a 9.2.0.1 $ORACLE_HOME
  (select * from v$version;), use the 9.2.0.1 export utility, and not
  the 9.2.0.2 or higher export utility.
  Or an export with 9.2.0.6 export client from a 9.2.0.5 database will give
  the following errors on the export a partitioned table with a subpartition
  template:
     EXP-00056: ORACLE error 6550 encountered
     ORA-06550: line 1, column 48:
     PLS-00302: component 'CHECK_MATCH_TEMPLATE' must be declared
     ORA-06550: line 1, column 14:
     PL/SQL: Statement ignored
  Solution: use the 9.2.0.5 export client if your intention is to import
  back into a 9.2.0.5 or any higher release database.
3) Always use a version of the IMPORT utility that is equal to the
  version of the target database.

Limitations.
------------

The following limitations apply to the EXPORT and IMPORT utilities (see
details in the matrix below):

1. Oracle export dump files can only be read by the Oracle IMPORT utility
  because these dump files are stored in a special Oracle-binary format.
2. Every export dump file is importable into all future major, patch, and
  maintenance releases of Oracle.
3. The export dump files cannot be read by IMPORT utilities of previous
  maintenance releases and versions.  So, a version 10gR2 (10.2.0.x) export
  dump file cannot be imported by a version 10gR1 (10.1.0.x) IMPORT utility
  (possible errors: ORA-2248), and a version 9 export dump file cannot be
  imported by a version 8i IMPORT utility (possible errors: IMP-10 and
  IMP-21), and a version 8 export dump file cannot be imported by a
  version 7 IMPORT utility (possible errors: IMP-69 and IMP-21).
  In all these cases, the import will terminate with the error IMP-0
  "Import terminated unsuccessfully".
4. IMPORT can read export dump files created by EXPORT release 5.1.22 and
  higher (up to same version).
5. IMPORT cannot read export dump files created by the EXPORT utility of a
  higher maintenance releases or versions.  So, a release 8.1 export dump
  file cannot be imported by a release 8.0 IMPORT utility, and a
  version 8 export dump file cannot be imported by a version 7 IMPORT utility.
6. The Oracle6 (or earlier) EXPORT utility cannot be used against an Oracle8
  or Oracle8i or higher release database.
7. Whenever a lower version EXPORT utility runs with a higher version of the
  Oracle Server, any categories of database objects that did not exist
  in the lower version are excluded from the export.  For example,
  partitioned tables are not exported by the version 7 EXPORT utility.
  If you need to move a version 8 partitioned table to a version 7 database,
  then first reorganize the table into a non-partitioned table.

Matrix 1: Which EXPORT utility to use when importing into an Oracle8
         or a lower database release
         (always use IMPORT utility of the target database):

Remarks:
1) IMPORT can read export dump files created by EXPORT release 5.1.22 and
  higher (up to same version).
2) An Oracle5 or Oracle6 export dump and an Oracle7 IMPORT:
  see the Oracle Utilities Manual, Chapter 2 "Import" for special
  considerations to keep in mind.
3) To export from Oracle7 for import into an Oracle6 database: user SYS
  must run script CATEXP6.SQL on the Oracle7 database first (this script
  needs to be run only once to have the version6 views been created).
4) To export from Oracle8, Oracle8i for import into an Oracle7 database:
  user SYS must run script CATEXP7.SQL on the Oracle8/8i database first
  (this script needs to be run only once in order to create the version7
  views).
5) Only the terminal Oracle8 release (8.0.6) was supported against an
  Oracle9i database: to export from Oracle9i and import into 8.0.6,
  use the Oracle8 export utility.

Matrix 2: Which EXPORT utility to use when importing into an Oracle8
         or a higher database release
         (always use IMPORT utility of the target database):

Remarks:
1) IMPORT can read export dump files created by EXPORT release 5.1.22 and
  higher (up to same version).
2) An Oracle5 or Oracle6 export dump and an Oracle7 IMPORT:
  see the Oracle Utilities Manual, Chapter 2 "Import" for special
  considerations to keep in mind.
3) To export from Oracle7 for import into an Oracle6 database: user SYS
  must run script CATEXP6.SQL on the Oracle7 database first (this script
  needs to be run only once to have the version6 views been created).
4) To export from Oracle8, Oracle8i for import into an Oracle7 database:
  user SYS must run script CATEXP7.SQL on the Oracle8/8i database first
  (this script needs to be run only once in order to create the version7
  views).
5) Only the terminal Oracle8 release (8.0.6) was supported against an
  Oracle9i database: to export from Oracle9i and import into 8.0.6,
  use the Oracle8 export utility.

Matrix 2: Which EXPORT utility to use when importing into an Oracle8
         or a higher database release
         (always use IMPORT utility of the target database):