【Oracle】邏輯備份失敗ORA-39002,39070,29283,06512

檢視備份郵件的時候，發現邏輯導出失敗，日志如下：

20111126-122901 admin:oracle@rac1:

/home/oracle> cat /tmp/exp_dwd.sh.crontab.last.log | more

****************************************************************

***you  login as oracle , please ask somebody to  double check!***

export: release 11.1.0.7.0 - 64bit production on wednesday, 23 november, 2011 20:45:02

copyright (c) 2003, 2007, oracle.  all rights reserved.

connected to: oracle database 11g enterprise edition release 11.1.0.7.0 - 64bit production

with the partitioning, real application clusters, 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 536

cat: exp_dwd__exp.backup.sh.out: no such file or directory

20111126-123031 admin:oracle@rac1:

/home/oracle> cat /tmp/exp_dwdss.sh.crontab.last.log | more

cat: exp_dwdss_dwd__exp.backup.sh.out: no such file or directory

expdp 導出備份都出現一樣的錯誤：ora-39070: unable to open the log file.顯示打不開directory，懷疑和此directory指定的路徑有關：

根據腳本檢視備份directory路徑：

expdp system/pwd  schemas=dwdss  filesize=8000m directory=mydp1   logfile=${expdp_log} parallel=8 .....

檢視路徑權限資訊：

ops$admin@rac1>select directory_name, grantee, privilege

  2  from user_tab_privs t, all_directories d  

  3  where t.table_name(+)=d.directory_name  

  4  order by 1,2,3;

directory_name                 grantee              privilege

------------------------------ -------------------- ----------

backup_dw

dir_for_exp_dw

dir_for_exp_dw_ins2

<b>mydp1               &lt;-------沒有相關權限資訊</b>

ops$admin@rac1&gt;exit

檢視directory_name， directory_path

ops$admin@rac1&gt;select * from dba_directories;

owner           directory_name            directory_path

--------------- ------------------------- ------------------------------------------------------------

sys             oracle_ocm_config_dir     /opt/oracle/products/11.1.0/db_1/ccr/state

sys             data_pump_dir             /opt/oracle/products/11.1.0/db_1/rdbms/log/

sys             dir_for_exp_dw_ins2       /home/oracle/products/11.1.0/dir_for_exp_dw_ins2

sys             dir_for_exp_dw            /home/oracle/products/11.1.0/dir_for_exp_dw

<b>sys             mydp1                     /netapp/asoftdw/backup &lt;---- </b>

sys             backup_dw                 /netapp/dwnew/backup

sys             expdp_tmp                 /home/oracle/expdp_tmp

sys             backup_dw_new             /netapp/dwnew/backup_newdw

8 rows selected.

ops$admin@rac1&gt;

disconnected from oracle database 11g enterprise edition release 11.1.0.7.0 - 64bit production

admin@rac1:/netapp/dwnew/backup&gt;<b>cd /netapp/asoftdw/backup</b>

<b>-bash: cd: /netapp/asoftdw/backup: 沒有那個檔案或目錄</b>

<b>由于沒有/netapp/asoftdw/backup 這個目錄導緻邏輯備份失敗。</b>

<b>解決方法：</b>

增加/netapp/asoftdw/backup 路徑，或者建立新的directory 并修改腳本中關于directory=mydp1 

-------------------------------------------------------------------------

遇到這樣錯誤還有如下情況，<b>都是和directory相關的</b>！

1 系統的oracle 使用者沒有對directory的讀寫權限，所有oracle的程序包括expdp也不能對directory指定的目錄進行讀寫。

解決方法：

檢查directory所指定的目錄的權限，并賦予正确的權限

2 對于rac系統，在執行expdp時使用tns連接配接名：

expdp system/xxxx@db directory=xxxxxxxxxx dumpfile=xxxxxxxx.dmp logfile= xxxxxxx.log schemas=xxxxxx

export: release 11.1.0.7.0 - 64bit production on tuesday, 08 march, 2011 11:16:2 0

copyright (c) 2003, 2007, oracle. all rights reserved.

原因：對于rac 系統tns name 是一個負責均衡的連接配接字元串，當指定tns name連接配接時，expdp程式可能指向另外一個沒有對應目錄或者目錄不可共享的執行個體進而導緻報錯。

解決辦法：對于rac 系統不要指定tns 名稱

3 directory 對于的系統目錄路徑不存在，也就是我遇到的情況

遇到此類問題可以執行下面的語句,檢查directory的權限和具體os對于路徑

set lines 80

col grantee format a20

col privilege format a10

select directory_name, grantee, privilege

from user_tab_privs t, all_directories d  

where t.table_name(+)=d.directory_name

      and d.directory_name='directory_name'  

order by 1,2,3;

set linesize 120

col owner for a15

col directory_path for a60

col directory_name for a25

select * from dba_directories ;