Home » RDBMS Server » Backup & Recovery » Importing dump taken from higher version to lower version
| Importing dump taken from higher version to lower version [message #120904] |
Tue, 24 May 2005 10:31  |
abc123
Messages: 24
Registered: March 2005
Location: sa
|
Junior Member |
|
|
Hi all,
I have already taken dump from oracle 9.0.2 database without using exp utility of lower verion and alo without running catexp.sql script prior to export.
Now i need to import this dump into 9.0.1 database.It gives error due to backward-incompability.
Is there any way to import without re-exporting whole database again using exp utility of lower version against higher version.
|
|
|
|
| Re: Importing dump taken from higher version to lower version [message #123420 is a reply to message #120904] |
Mon, 13 June 2005 07:16  |
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
|
Member |
|
|
I think you have to catexp.sql if you are importing to a lower verion, hope ths doc is helpful
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
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.
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)
See also sections 'Basic Compatibility' and 'Limitations' below for details.
1. Migration to Oracle9i release 1 - 9.0.1.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.5 or 8.1.6 or 8.1.7
2. 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
3. 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
Examples:
1. From 8.1.7.4 to 9.2.0.4: Full database export with the 8.1.7.4 export utility, and full database import with the 9.2.0.4 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.
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).
c) 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, e.g.: an export with 9.2.0.5 export 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.
2) 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 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 both 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.
In order to check the revision of the export and import views, you can query the table sys.props$ with the following statement:
SQL> SELECT * FROM sys.props$ WHERE name LIKE 'EXPORT%';
NAME VALUE$ COMMENT$
------------------------- ----------- ------------------------
EXPORT_VIEWS_VERSION 8 Export views revision #
The value of the property EXPORT_VIEWS_VERSION has the following meaning:
+-------------------------------+
| EXPORT_VIEWS_VERSION |
+-------+-----------------------+
| Value | Introduced in Release |
+-------+-----------------------+
| *)| 7.0.* | *) no compatibility - assume zero
| 1 | 7.1.3 |
| 2 | 7.2.1 |
| 3 | 7.2.3 |
| 4 | 8.0.1 |
| 5 | 8.0.2 |
| 6 | 8.0.3 |
| 7 | 8.0.4 |
| 8 | 8.1.6 |
+-------+-----------------------+
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 10:59:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
