Datapump 사용시 주의사항 ; newer version --> older version 자료이관시

exp, imp 를 사용해서 자료를 옮기는 경우에는 최신버전의 파일로는 옛날버전의 DB로는 데이터를 옮길 수 없습니다.

하지만 datapump는 export 할 때 version을 셋팅함으로써 가능하게 되었습니다.

$ expdp 필요한옵션들 version=대상DB의버전(10.2)

호환성과, 버전에 대한 정보는  http://www.myoracleguide.com/s/DPEipov.htm 에서 확인하세요

유사한 포스팅 : http://elflord.egloos.com/4623061  

실습:

아무런 옵션없이 11gR2에서 datapump export한 후 10gR2로 import시도

>>에러발생합니다.

$ impdp scott/tiger dumpfile=scott.dmpdp directory=datapump table_exists_action=truncate

Import: Release 10.2.0.1.0 - Production on Saturday, 24 March, 2012 0:31:34

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/datapump/scott.dmpdp"

이후 11gR2 windows에서 대상버전에 맞게 옵션을 줘서 export 해보겠습니다.

C:\Users\Younghoon>expdp schemas=scott directory=datapump dumpfile=scott.dmpdp version=10.2.0.1

Export: Release 11.2.0.1.0 - Production on Sat Mar 24 00:54:18 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=scott direct
ory=datapump dumpfile=scott.dmpdp version=10.2.0.1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.68 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT

....생략....

. . exported "SCOTT"."TEST110"                               0 KB       0 rows
. . exported "SCOTT"."TT100"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  C:\USERS\YOUNGHOON\DESKTOP\ORACLE\WINDOW\DATAPUMP\SCOTT.DMPDP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:54:46

이후 10gR2에서 impdp

$ impdp directory=datapump dumpfile=scott.dmpdp

Import: Release 10.2.0.1.0 - Production on Saturday, 24 March, 2012 0:57:49

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: scott/tiger

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=datapump dumpfile=scott.dmpdp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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

....생략....

Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 00:58:06

version옵션을 사용하게 되면, 성공적으로 import가 가능합니다.