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、如果匯入和匯出是在同一台機器上操作的,則匯出前的建立目錄和賦權的步驟都可以省略。