Export/Import with different Oracle Versions
Backup and Recovery Tips
Export/Import with different Oracle Versions Export and Import are not backwardly compatible. That is, running the 9i version of export against an 8i database will produce errors. Similarly, running 8i export against a 7 or 8.0 database will produce fireworks. To prove the point, here’s what happens when I try to run 9i export against my 8.1.6 database: C:\>EXP SYSTEM/MANAGER@HJR816 FULL=Y EXPORT: RELEASE 9.0.1.1.1 - PRODUCTION ON TUE OCT 30 07:40:45 2001 (C) COPYRIGHT 2001 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE8I ENTERPRISE EDITION RELEASE 8.1.6.0.0 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 8.1.6.0.0 - PRODUCTION EXP-00056: ORACLE ERROR 942 ENCOUNTERED ORA-00942: TABLE OR VIEW DOES NOT EXIST EXP-00000: EXPORT TERMINATED UNSUCCESSFULLY Much the same happens when I try to use the 8.1.7 version of export against an 8.1.6 database (which goes to show that point releases are frequently significant): D:\ORACLE\ORA817\BIN>EXP SYSTEM/MANAGER@HJR816 FULL=Y FILE=TEST.DMP EXPORT: RELEASE 8.1.7.0.0 - PRODUCTION ON TUE OCT 30 07:47:29 2001 (C) COPYRIGHT 2000 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE8I ENTERPRISE EDITION RELEASE 8.1.6.0.0 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 8.1.6.0.0 - PRODUCTION EXPORT DONE IN WE8ISO8859P1 CHARACTER SET AND WE8ISO8859P1 NCHAR CHARACTER ABOUT TO EXPORT THE ENTIRE DATABASE ... . EXPORTING TABLESPACE DEFINITIONS . EXPORTING PROFILES . EXPORTING USER DEFINITIONS . EXPORTING ROLES . EXPORTING RESOURCE COSTS . EXPORTING ROLLBACK SEGMENT DEFINITIONS EXP-00008: ORACLE ERROR 904 ENCOUNTERED ORA-00904: INVALID COLUMN NAME EXP-00000: EXPORT TERMINATED UNSUCCESSFULLY
SET
You’ll notice it gets a bit further than version 9 export against the 8i database, but essentially, it still falls over.
Copyright © Howard Rogers 2001
30/10/2001
Page 1 of 6
Export/Import with different Oracle Versions
Backup and Recovery Tips
The same, of course, happens if you try to run version 8.x or 9i export against a version 7 database. What we learn from this is that the higher version of export can NOT be used against a lower version of the database. 9i export won’t work when pointed at 8i or 8.0 databases; 8i export won’t work when pointed at 7 or 8.0 databases (nor even when pointed at a lower point release of 8i). And so on. However, it is perfectly permissible to use the lower version of export against a higher version of the database. For example, if I run the 8.1.6 version of export against the 8.1.7 database, this is what happens (trimmed a bit for space reasons): D:\ORACLE\ORA81\BIN>EXP SYSTEM/MANAGER@HJR817 FULL=Y FILE=TEST.DMP EXPORT: RELEASE 8.1.6.0.0 - PRODUCTION ON TUE OCT 30 08:10:27 2001 (C) COPYRIGHT 1999 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE8I ENTERPRISE EDITION RELEASE 8.1.7.0.0 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 8.1.7.0.0 - PRODUCTION EXPORT DONE IN WE8ISO8859P1 CHARACTER SET AND WE8ISO8859P1 NCHAR CHARACTER
SET
ABOUT TO EXPORT THE ENTIRE DATABASE ... . EXPORTING TABLESPACE DEFINITIONS . EXPORTING PROFILES . EXPORTING USER DEFINITIONS . EXPORTING ROLES . EXPORTING RESOURCE COSTS . EXPORTING ROLLBACK SEGMENT DEFINITIONS [SNIP] . ABOUT TO EXPORT SYSTEM'S TABLES VIA CONVENTIONAL PATH ... DEF$_AQCALL 0 ROWS EXPORTED . . EXPORTING TABLE . . EXPORTING TABLE DEF$_AQERROR 0 ROWS EXPORTED . . EXPORTING TABLE DEF$_PUSHED_TRANSACTIONS 0 ROWS EXPORTED DEF$_TEMP$LOB 0 ROWS EXPORTED . . EXPORTING TABLE . . EXPORTING TABLE SQLPLUS_PRODUCT_PROFILE 0 ROWS EXPORTED [SNIP] . EXPORTING REFRESH GROUPS AND CHILDREN . EXPORTING DIMENSIONS . EXPORTING POST-SCHEMA PROCEDURAL OBJECTS AND ACTIONS . EXPORTING USER HISTORY TABLE . EXPORTING DEFAULT AND SYSTEM AUDITING OPTIONS . EXPORTING STATISTICS EXPORT TERMINATED SUCCESSFULLY WITHOUT WARNINGS.
Copyright © Howard Rogers 2001
30/10/2001
Page 2 of 6
Export/Import with different Oracle Versions
Backup and Recovery Tips
What about 8.1.6-version export being applied to a 9i database? Does that work? D:\ORACLE\ORA81\BIN>EXP SYSTEM/MANAGER@HJR9 FULL=Y FILE=TEST.DMP EXPORT: RELEASE 8.1.6.0.0 - PRODUCTION ON TUE OCT 30 08:16:33 2001 (C) COPYRIGHT 1999 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE9I ENTERPRISE EDITION RELEASE 9.0.1.1.1 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 9.0.1.1.1 - PRODUCTION EXPORT DONE IN WE8ISO8859P1 CHARACTER SET AND UTF8 NCHAR CHARACTER SET SERVER USES WE8MSWIN1252 CHARACTER SET (POSSIBLE CHARSET CONVERSION) ABOUT TO EXPORT THE ENTIRE DATABASE ... . EXPORTING TABLESPACE DEFINITIONS [SNIP] . ABOUT TO EXPORT SYSTEM'S TABLES VIA CONVENTIONAL PATH ... . . EXPORTING TABLE AQ$_INTERNET_AGENTS [SNIP] . ABOUT TO EXPORT OUTLN'S TABLES VIA CONVENTIONAL PATH ... OL$ 0 . . EXPORTING TABLE [SNIP] . ABOUT TO EXPORT DBSNMP'S TABLES VIA CONVENTIONAL PATH ... . ABOUT TO EXPORT SCOTT'S TABLES VIA CONVENTIONAL PATH ... . . EXPORTING TABLE BLAH 0 . . EXPORTING TABLE DEPT 4 EMP 14 . . EXPORTING TABLE [SNIP] EXPORT TERMINATED SUCCESSFULLY WITHOUT WARNINGS.
0
ROWS EXPORTED
ROWS EXPORTED
ROWS EXPORTED ROWS EXPORTED ROWS EXPORTED
…obviously, it does. So, lower versions of export can quite happily run against higher versions of the database, but the dump file produced is a lower-version dump file. It’s also important to realise that the lower version of export may encounter new data structures in the higher version of the database –and won’t have a clue as to how to deal with them. All higher-version structures are therefore ignored during the export, and won’t get included in the dump file. Incidentally, the rules I’ve outlined here break down slightly for version 7. The version 7 export can not be run against a version 8 or higher database… not unless, at any rate, you run the catexp7.sql script provided by Oracle (and found in ORACLE_HOME/rdbms/admin) in the version 8 or higher database first. That script creates a bunch of views which the version 7 export utility needs to see before it can function properly. So, to sum up: a lower version of export can be run against a higher version of the database, but the dump file produced will be lower version compatible, ignoring any higher version-specific features it encounters during the export. Version 7 of export is a Copyright © Howard Rogers 2001
30/10/2001
Page 3 of 6
Export/Import with different Oracle Versions
Backup and Recovery Tips
law unto itself, and requires the running of a special script in the higher version of the database before it can work against that higher version database. Import has its own set of rules. First, a higher version of import has no trouble understanding the contents of a lower version dump file (that is, a dump file produced by running a lower version of export, regardless of the version of the database it was run against). To prove, the point, here we see a dump file produced by version 8.1.6 of export being successfully interpreted by a 9i version of import (which is loading into a 9i database): D:\>IMP SYSTEM/MANAGER FILE=D:\ORACLE\ORA81\BIN\816.DMP FROMUSER=SCOTT TABLES=EMP IGNORE=Y IMPORT: RELEASE 9.0.1.1.1 - PRODUCTION ON TUE OCT 30 08:31:52 2001 (C) COPYRIGHT 2001 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE9I ENTERPRISE EDITION RELEASE 9.0.1.1.1 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 9.0.1.1.1 - PRODUCTION EXPORT FILE CREATED BY EXPORT:V08.01.06 VIA CONVENTIONAL PATH . IMPORTING SCOTT'S OBJECTS INTO SCOTT "EMP" 14 ROWS . . IMPORTING TABLE ABOUT TO ENABLE CONSTRAINTS... IMPORT TERMINATED SUCCESSFULLY WITHOUT WARNINGS.
IMPORTED
But if we try running 8.1.6 against a version 9i dump file, what happens? D:\ORACLE\ORA81\BIN>IMP SYSTEM/MANAGER@HJR816 FILE=D:\9011.DMP FROMUSER=SCOTT TABLES=EMP IMPORT: RELEASE 8.1.6.0.0 - PRODUCTION ON TUE OCT 30 08:38:21 2001 (C) COPYRIGHT 1999 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE8I ENTERPRISE EDITION RELEASE 8.1.6.0.0 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 8.1.6.0.0 - PRODUCTION IMP-00010: NOT A VALID EXPORT FILE, HEADER FAILED IMP-00000: IMPORT TERMINATED UNSUCCESSFULLY
VERIFICATION
Clearly, the lower version of import can make no sense of the higher version dump file. Bear in mind that it is the versions of import and the dump file that are at issue here, not what version of the database the dump file originally came from. So, for example, using an 8.1.6 dump file, and version 8.1.6 of import, I can import the EMP table into a 9i database without any difficulties: Copyright © Howard Rogers 2001
30/10/2001
Page 4 of 6
Export/Import with different Oracle Versions
Backup and Recovery Tips
D:\ORACLE\ORA81\BIN>IMP SYSTEM/MANAGER@HJR9 FILE=816.DMP FROMUSER=SCOTT TABLES=EMP IMPORT: RELEASE 8.1.6.0.0 - PRODUCTION ON TUE OCT 30 08:44:54 2001 (C) COPYRIGHT 1999 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE9I ENTERPRISE EDITION RELEASE 9.0.1.1.1 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 9.0.1.1.1 - PRODUCTION EXPORT
EXPORT:V08.01.06 VIA CONVENTIONAL PATH WE8ISO8859P1 CHARACTER SET AND UTF8 NCHAR CHARACTER SET IMPORT SERVER USES WE8MSWIN1252 CHARACTER SET (POSSIBLE CHARSET CONVERSION) . IMPORTING SCOTT'S OBJECTS INTO SCOTT "EMP" 14 ROWS IMPORTED . . IMPORTING TABLE ABOUT TO ENABLE CONSTRAINTS... IMPORT TERMINATED SUCCESSFULLY WITHOUT WARNINGS. FILE CREATED BY
IMPORT DONE IN
Certainly, at any rate, this works with simple User tables… quite what happens when you try using 8.1.6 import to load system tables into a higher version of the database is anyone’s guess (I’m not brave enough to try it out!), but I wouldn’t recommend it. The real point is that version 8.1.6 import can interpret the 8.1.6 version dump file (and you’ll recall that the dump file version is governed by the export version used, not what version database export happened to be run against). What about using 9.0.1 version import with a 9.0.1 dump file to load into an 8.1.6 database? Well, that doesn’t work: D:\>IMP SCOTT/TIGER@HJR816 TABLES=EMP FILE=9011.DMP IMPORT: RELEASE 9.0.1.1.1 - PRODUCTION ON TUE OCT 30 09:00:10 2001 CONNECTED TO: ORACLE8I ENTERPRISE EDITION RELEASE 8.1.6.0.0 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 8.1.6.0.0 - PRODUCTION IMP-00003: ORACLE ERROR 942 ENCOUNTERED ORA-00942: TABLE OR VIEW DOES NOT EXIST IMP-00023: IMPORT VIEWS NOT INSTALLED, PLEASE IMP-00000: IMPORT TERMINATED UNSUCCESSFULLY
NOTIFY YOUR
DBA
Whilst this version of import has no trouble reading the dump file, we encounter another rule: that a higher version of import cannot be used to load into a lower version of the database, even if the import version can make perfect sense of the dump file. The net result of this entire discussion is that multi-versions of import and export don’t have to be a problem, provided you keep your wits about you. Lower versions of export Copyright © Howard Rogers 2001
30/10/2001
Page 5 of 6
Export/Import with different Oracle Versions
Backup and Recovery Tips
can be run against higher versions of databases; higher versions of import can make sense of lower version dump files, but can’t be run against a lower version of the database. And all of that can perhaps best be summarised by the following table: Database Version
Export Version
Dump File Version
Import Version that works
7.x 8.0.x
7.x 7.x(#) 8.0.x 7.x(#) 8.0.x 8.1.6 7.x(#) 8.0.x 8.1.6 8.1.7 7.x(#) 8.0.x 8.1.6 8.1.7 9.0.1
7.x 7.x 8.0.x 7.x 8.0.x 8.1.6 7.x 8.0.x 8.1.6 8.1.7 7.x 8.0.x 8.1.6 8.1.7 9.0.1
7.x 8.0.x, 7.x 8.0.x, 8.1.6, 8.0.x, 7.x 8.1.6, 8.0.x 8.1.6 8.1.7,8.1.6,8.0.x,7.x 8.1.7,8.1.6,8.0.x 8.1.7,8.1.6 8.1.7 9.0.1, 8.1.7,8.1.6,8.0.x,7.x 9.0.1, 8.1.7,8.1.6,8.0.x 9.0.1, 8.1.7,8.1.6 9.0.1, 8.1.7 9.0.1
8.1.6
8.1.7
9.0.1
(#) provided catexp7.sql is run in the database first.
Interpreting the table Given an 8.1.7 database (the fourth main division of the table), we can use 8.1.6 to export from it (row three in that division), thus producing an 8.1.6 dump file. Now we locate 8.1.6 in the “Dump File Version” column, and see what options we have. • • • •
We could, for example, use the 8.1.6 version of import to load it into an 8.1.6 database (main division 3, row 3, only option). Or we could use 8.1.7 import to load it into a 8.1.7 database (main division 4, row 3, first option). Or we could use 8.1.6 import to load it into a 9.0.1 database (main division 5, row 3, third option). But we can’t use 9.0.1 import to load it into a 8.1.7 database (main division 4, no rows have 9.0.1 as an import option). That would violate the rule about higher versions of import not running against lower versions of the database.
I confess to not having tested the table’s predictions for 7.x and 8.0.x (neither of which versions run on my Windows 2000 test-bed). Perhaps you’d like to try it?!
Copyright © Howard Rogers 2001
30/10/2001
Page 6 of 6