oracle跨版本exdp 从高版本到低版本从18c到10g

最新推荐文章于 2024-09-20 19:57:42 发布

zuozhiji

最新推荐文章于 2024-09-20 19:57:42 发布

阅读量1k

点赞数 10

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/jolly10/article/details/139675714

由于工作需要，有时候需要将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

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1) - 晟数学苑 - 学习塑造未来