由于工作需要,有时候需要将oracle18c的数据同步到另一个环境的10g版本
测试环境 :
源数据库版本:18.13.0.0
目标数据库版本:10.2.0.4
结论:
1 从低版本导出,导入到高版本,可以,不需要加参数version
2 从高版本导出,导入到低版本,不可以。需要在导出的时候,加上version参数。在导入的时候加参数version无效。
源数据库:
[oracle@dp]$ expdp l5m/l5m directory=DPPATH schemas=TTT file=TTT.dmp version=10.2.0.4
Export: Release 18.0.0.0.0 - Production on Fri Jun 14 10:25:21 2024
Version 18.13.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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=TTT.dmp" Location: Command Line, Replaced with: "dumpfile=TTT.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.
Starting "L5M"."SYS_EXPORT_SCHEMA_03": l5m/******** directory=DPPATH schemas=TTT dumpfile=TTT.dmp version=10.2.0.4 reuse_dumpfiles=true
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 "TTT"."T1" 72.79 KB 428 rows
Master table "L5M"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for L5M.SYS_EXPORT_SCHEMA_03 is:
/u01/dump/dp/data/TTT.dmp
Job "L5M"."SYS_EXPORT_SCHEMA_03" successfully completed at Fri Jun 14 10:27:29 2024 elapsed 0 00:02:07
导出之后将文件传到目录服务器,直接通过imdp就可以了,不需要加version参数。
[oracle@data]$ impdp l5m/l5m dumpfile=TTT.dmp directory=dumppaht
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 13 June, 2024 22:41:42
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMPPAHT is invalid
[oracle@l5mDev121 data]$ impdp l5m/l5m dumpfile=TTT.dmp directory=dumppath
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 13 June, 2024 22:41:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "L5M"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "L5M"."SYS_IMPORT_FULL_01": l5m/******** dumpfile=TTT.dmp directory=dumppath
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TTT"."T1" 72.79 KB 428 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "L5M"."SYS_IMPORT_FULL_01" successfully completed at 22:41:53
如何从高版本数据库导出dmp至低版本数据库导入? - 墨天轮
https://www.oracle.com/a/ocom/docs/oracle-data-pump-best-practices.pdf