expdp匯出報錯ORA-39002、ORA-39070解決辦法

來源:互聯網
上載者:User
oracle@centos5 ~]$ expdp gys directory= dmp_dir dumpfile=stream.dmp schemas=stream

Export: Release 10.2.0.4.0 - Production on Wednesday, 16 October, 2013 17:51:37

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
開始以為是dmp_dir 許可權問題引起的,重新建立directory資料庫物件測試下。
SQL> col OWNER format a6
SQL> col DIRECTORY_NAME for a20
SQL> col DIRECTORY_PATH for a30
SQL> select * from dba_directories;

OWNER  DIRECTORY_NAME       DIRECTORY_PATH
------ -------------------- ------------------------------
SYS    DATA_PUMP_DIR        /u01/oracle/db_1/rdbms/log/
SYS    STREAM               /u01/testdata/dump
SYS    ORACLE_OCM_CONFIG_DI /u01/oracle/db_1/ccr/state
                                                                       R
SYS    DMP_DIR              /u01/testdata/dump

SQL> drop directory dmp_dir;

Directory dropped.

SQL>  select * from dba_directories;

OWNER  DIRECTORY_NAME       DIRECTORY_PATH
------ -------------------- ------------------------------
SYS    DATA_PUMP_DIR        /u01/oracle/db_1/rdbms/log/
SYS    STREAM               /u01/testdata/dump
SYS    ORACLE_OCM_CONFIG_DI /u01/oracle/db_1/ccr/state
       R
已經刪除dmp_dir

重新建立
SQL> create directory dmp_dir as '/u01/testdata/dump';

Directory created.
SQL> grant read,write on directory dmp_dir to stream;

Grant succeeded.
SQL> !
[oracle@centos5 ~]$ expdp stream directory= dmp_dir dumpfile=stream.dmp schemas=stream

Export: Release 10.2.0.4.0 - Production on Wednesday, 16 October, 2013 17:51:37

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

報錯依舊。。。
想到,建立了DUMP的目錄,是不是linux下oracle使用者對這個目錄沒有許可權,導致系統不能對該目錄寫入資料。
GO。。。
[oracle@centos5 u01]$ ls -lrt
total 28
drwxr-xr-x 3 oracle oinstall 4096 Aug 29 12:41 oracle
drwxrwx--- 6 oracle oinstall 4096 Aug 29 13:01 oraInventory
drwxr-x--- 2 oracle oinstall 4096 Oct 12 09:26 flash_recovery_area
drwxr-x--- 3 oracle oinstall 4096 Oct 16 12:47 oradata
drwxr-x--- 3 oracle oinstall 4096 Oct 16 12:47 admin
drwxr-xr-x 2 oracle oinstall 4096 Oct 16 14:47 orabak
drwxrwxrwx 3 oracle oinstall 4096 Oct 16 17:15 testdata
[oracle@centos5 u01]$ cd /u01/testdata
[oracle@centos5 testdata]$ ls -lrt
total 4
drwxr-xr-x 2 root root 4096 Oct 16 17:15 dump
發現問題,dump的使用者權限是root oracle對該目錄沒有許可權
[oracle@centos5 ~]$ su - root
Password:
[root@centos5 ~]# cd /u01/testdata
[root@centos5 testdata]# chown -R oracle:oinstall *
[root@centos5 testdata]# ls -lrt
total 4
drwxr-xr-x 2 oracle oinstall 4096 Oct 16 17:15 dump
修改該目錄所有者。
[root@centos5 testdata]# su - oracle
[oracle@centos5 ~]$ expdp stream directory= dmp_dir dumpfile=stream.dmp schemas=stream

Export: Release 10.2.0.4.0 - Production on Wednesday, 16 October, 2013 17:58:05

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "GYS"."SYS_EXPORT_SCHEMA_01":  gys/******** directory= dmp_dir dumpfile=gys/********.dmp schemas=gys/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.489 GB
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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "STREAM"."TABWSALE"                         1.109 GB 11853316 rows
. . exported "STREAM"."TABSHIFTFLOW"                     33.49 MB  365378 rows
. . exported "STREAM"."TABSHIFTREPORT"                   32.32 MB  406880 rows
. . exported "STREAM"."TABSWEIGHT"                       8.763 MB  148977 rows
. . exported "STREAM"."TABVTIMEOUT"                      7.701 MB  239696 rows
. . exported "STREAM"."TABLOG"                           5.226 MB   92782 rows
. . exported "STREAM"."TABFINDAYREPORT"                  3.901 MB   35089 rows
. . exported "STREAM"."TABSALEDAYREPORT"                 3.278 MB   29743 rows
. . exported "STREAM"."TABPASSPORTREPORT"                1.773 MB   48059 rows
. . exported "STREAM"."TABRISKVRECORD"                   2.119 MB   23010 rows
. . exported "STREAM"."TABDESTROY"                       1.494 MB   34739 rows
. . exported "STREAM"."TABFINDAYREPORT_OLD"              1.028 MB    9218 rows
. . exported "STREAM"."TABPSTOCK"                        1.476 MB   34951 rows
. . exported "STREAM"."TABVECLIB"                        789.4 KB   18425 rows
. . exported "STREAM"."TABQUARTERS"                      788.2 KB   17181 rows
. . exported "STREAM"."TABTICKETDESTROY"                 9.031 KB       2 rows
. . exported "STREAM"."TABBHBW"                          113.3 KB    2762 rows
. . exported "STREAM"."TABPRINTPLAN"                     143.7 KB    1830 rows
. . exported "STREAM"."TABBHB"                           94.88 KB    2670 rows
. . exported "STREAM"."TABBHBS"                          109.7 KB    2762 rows
. . exported "STREAM"."TABCHECKUSER"                     8.406 KB      41 rows
. . exported "STREAM"."TABCLASSORDER"                    5.664 KB       9 rows
. . exported "STREAM"."TABCLIENTINFO"                    8.398 KB      13 rows
. . exported "STREAM"."TABLANE"                          7.703 KB       6 rows
. . exported "STREAM"."TABQUERYHAND"                     74.53 KB    1366 rows
. . exported "STREAM"."TABUSER"                          8.992 KB      37 rows
. . exported "STREAM"."TABWTOLLRATE"                     10.86 KB      30 rows
. . exported "STREAM"."TABCONFIG"                        7.507 KB     104 rows
. . exported "STREAM"."TABCONTRACT"                      17.36 KB     101 rows
. . exported "STREAM"."TABDEP"                           6.265 KB       6 rows
. . exported "STREAM"."TABDESTROYSERIAL"                 11.07 KB     178 rows
. . exported "STREAM"."TABEMPLOYEE"                      17.85 KB      49 rows
. . exported "STREAM"."TABFUNC"                          10.20 KB      57 rows
. . exported "STREAM"."TABINSTORE"                       7.039 KB      46 rows
. . exported "STREAM"."TABMONTHTOLL"                     15.20 KB     131 rows
. . exported "STREAM"."TABPOWERASSIGN"                   17.80 KB     612 rows
. . exported "STREAM"."TABRISKCONFIG"                    12.03 KB     152 rows
. . exported "STREAM"."TABRYZNB"                         5.273 KB       3 rows
. . exported "STREAM"."TABSHIPERMAN"                     11.71 KB     127 rows
. . exported "STREAM"."TABSHIPPROPERTY"                  9.898 KB      10 rows
. . exported "STREAM"."TABSPECIALVRECORD"                16.92 KB     133 rows
. . exported "STREAM"."TABSTATION"                       9.031 KB       2 rows
. . exported "STREAM"."TABSYNCCHECK"                     5.351 KB       5 rows
. . exported "STREAM"."TABTIMEFLAG"                      5.578 KB       3 rows
. . exported "STREAM"."TABUSE"                           9.578 KB     110 rows
. . exported "STREAM"."TABVERIFYRECORD"                  15.48 KB     386 rows
. . exported "STREAM"."PLAN_TABLE"                           0 KB       0 rows
. . exported "STREAM"."TABEMPTYSHIP"                         0 KB       0 rows
. . exported "STREAM"."TABTICKETREPEATSCAN"                  0 KB       0 rows
. . exported "STREAM"."TABWEIGHTVFLOW"                       0 KB       0 rows
Master table "GYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/testdata/dump/stream.dmp

Job "GYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:05:16


==========================================================================================

oracle11g用exp匯出資料庫的時候,空表是導不出來的,所以必須用資料泵匯入匯出oracle資料庫。

一、匯出資料用expdp

1、建立目錄 文法:SQL> create directory 目錄名(如:dmpbx) as 儲存地址(如:'/root/usr/……');
例如:SQL> create  directory  dmpbx  as 'D:\backup\';
2、給目錄賦許可權
文法:SQL> grant read,write on directory 目錄名 to public;
例如:SQL> grant read,write on directorydmpbx to public;
    SQL> exit

3、執行匯出語句

文法:[Oracle@data ~]$expdp 使用者名稱1/密碼@執行個體 dumpfile=備份檔案名(ecology.dmp) directory= 目錄名(dmpbx)  例如:[oracle@data ~]$ expdpuser1/pass1 dumpfile=data20140805.dmp directory=dmpbx 
 
二、匯入用impdp

1、建立目錄 文法:SQL> create directory 目錄名(如:dmpbx) as 儲存地址(如:'/root/usr/……');
例如:SQL> create  directory  dmpbx  as 'D:\Oracle\';
2、給目錄賦許可權
文法:SQL> grant read,write on directory 目錄名 to public;
例如:SQL> grant read,write on directory dmpbx to public;
    SQL> exit

3、執行匯入語句 文法:impdp 使用者名稱2/密碼@執行個體 directory=目錄名 dumpfile=備份檔案名.dmp logfile=日誌名.log REMAP_SCHEMA=使用者名稱1:使用者名稱2
例如:impdp user2/pass2 directory= dmpbx dumpfile= data20140805 .dmp logfile= 20140805.log REMAP_SCHEMA= user1: user2

註:1、目錄建立只需建立一次就行了

2、如果匯入和匯出是在同一台機器上操作的,則匯出前的建立目錄和賦權的步驟都可以省略。

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.