Linux 下 oracle 匯入資料庫—資料泵方式匯入dmp檔案

標籤：

 

dmp匯出資料來源的oracle版本是12c,我本地oracle版本是11g,這在匯入資料過程中導致出現錯誤

一、用system 使用者登入oracle，我忘記了system密碼，解決辦法：

[[email protected] ~]$sqlplus "/as sysdba"

SQL>startup

SQL>conn /as sysdba

SQL>alter user system identified by password;

SQL>alter user system account unlock;

SQL>conn system/password

SQL> Create directory TEST as ‘/home/oracle/Desktop/test‘;

 

SQL>  Grant read,write on directory TEST to scott;    

SQL> SELECT * FROM  dba_DIRECTORIES;

 

問題： 1、日誌和dmp檔案不能有路徑，dmp檔案放到directory所建立的路徑下（ ‘/home/oracle/Desktop/test‘），log檔案oracle會自動儲存在$ORACLE_HOME目錄下

[[email protected] ~]$ impdp scott/youyou full=y directory=TEST dumpfile=/home/oracle/Desktop/ExpIirspublish20141117.dmp logfile=/home/oracle/Desktop/imp_icdmain_csd_X.log ignore=y

Import: Release 11.2.0.1.0 - Production on Mon Jan 5 10:24:27 2015

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

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

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39088: file name cannot contain a path specification

 

 

[[email protected] ~]$ impdp scott/youyou full=y directory=TEST dumpfile=/home/oracle/Desktop/ExpIirspublish20141117.dmp logfile=imp_icdmain_csd_X.log ignore=y Import: Release 11.2.0.1.0 - Production on Mon Jan 5 10:24:50 2015

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

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

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39088: file name cannot contain a path specification

問題2：版本所引發的問題，解決高版本向低版本

[[email protected] ~]$ impdp scott/youyou full=y directory=TEST dumpfile=ExpIirspublish20141117.dmp logfile=imp_icdmain_csd_X.log ignore=y

Import: Release 11.2.0.1.0 - Production on Mon Jan 5 10:26:09 2015

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

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

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 4.1 in dump file "/home/oracle/Desktop/test/ExpIirspublish20141117.dmp"

源端資料庫版本12c,目標端資料庫版本11g，源端資料庫版本高於目標端資料庫版本，源端匯出資料時加上目標端version，

查詢版本SQL> show parameter compatible;

 

Linux 下 oracle 匯入資料庫—資料泵方式匯入dmp檔案