오라클 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):
