How to use Data Pump between different Oracle versions

According to the documentations, if the target database is higher than the source database, the VERSION parameter does not have to be specified. There is an exception, if full export perform from 11g to 12c. In this case, VERSION parameter should be used in order to include complete set of Oracle internal component metadata.

In the below example, I move a schema from 18c to 11g. Therefore, VERSION parameter should be specified. In these kind of cases, keep in mind that some features may be unavailable. For example, if you import to 10g and your export contains data compression feature then your import can cause an error because compression was not supported in 10g release 1.

expdp DIRECTORY=ORACLE_BASE DUMPFILE=TEST_USER.DMP SCHEMAS=TEST_USER LOGFILE=SCHEMA_LOG.LOG VERSION=11.2

Log file content:

;;; 
Export: Release 18.0.0.0.0 - Production on Fri Dec 4 11:41:14 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  SYS/******** AS SYSDBA DIRECTORY=ORACLE_BASE DUMPFILE=TEST_USER.DMP SCHEMAS=TEST_USER LOGFILE=SCHEMA_LOG.LOG VERSION=11.2 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST_USER"."T_CEK_YENI"                    1.040 MB    1000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\TMTEST\TEST_USER.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Dec 4 11:42:00 2020 elapsed 0 00:00:39

After moving the dump file to the target server. We are ready to import.

impdp DIRECTORY=DATAPUMP_DIR DUMPFILE=TEST_USER.DMP SCHEMAS=TEST_USER LOGFILE=SCHEMA_LOG.LOG

As can be seen from the log file, the process was completed successfully.

;;; 
Import: Release 11.2.0.4.0 - Production on Cum Ara 4 11:48:42 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Bağlanılan: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
"SYS"."SYS_IMPORT_SCHEMA_01" ana tablosu başarıyla yüklendi/yüklemesi kaldırıldı
"SYS"."SYS_IMPORT_SCHEMA_01" başlatılıyor:  SYS/******** AS SYSDBA DIRECTORY=DATAPUMP_DIR DUMPFILE=TEST_USER.DMP SCHEMAS=TEST_USER LOGFILE=SCHEMA_LOG.LOG 
SCHEMA_EXPORT/USER nesne türü işleniyor
SCHEMA_EXPORT/SYSTEM_GRANT nesne türü işleniyor
SCHEMA_EXPORT/ROLE_GRANT nesne türü işleniyor
SCHEMA_EXPORT/DEFAULT_ROLE nesne türü işleniyor
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA nesne türü işleniyor
SCHEMA_EXPORT/TABLE/TABLE nesne türü işleniyor
SCHEMA_EXPORT/TABLE/TABLE_DATA nesne türü işleniyor
. . "TEST_USER"."T_CEK_YENI"                    1.040 MB    1000 satır içe aktarıldı
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS nesne türü işleniyor
"SYS"."SYS_IMPORT_SCHEMA_01" işi Cum Ara 4 11:51:37 2020 elapsed 0 00:01:09 konumunda başarıyla tamamlandı

I also would like to mention that if the time zone file was different between the two databases, you may get an error. Tim Hall explains this situation in the following article.

https://oracle-base.com/blog/2020/02/19/data-pump-between-database-versions-its-not-just-about-the-version-parameter/

You can check the time zone file with the following command.

SELECT * FROM v$timezone_file;

I hope you found it helpful.

Posted in DBA
Leave a Reply

Your email address will not be published.