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

楊奇龍發表於2011-11-26
Oracle
檢視備份郵件的時候，發現邏輯匯出失敗，日誌如下：
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
ORA-29283: invalid file operation
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
****************************************************************
***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
ORA-29283: invalid file operation
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
MYDP1              
ops$admin@RAC1>exit
檢視DIRECTORY_NAME， DIRECTORY_PATH
ops$admin@RAC1>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
SYS             MYDP1                     /netapp/asoftdw/backup
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>
ops$admin@RAC1>exit
Disconnected from 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
admin@RAC1:/netapp/dwnew/backup>cd /netapp/asoftdw/backup
-bash: cd: /netapp/asoftdw/backup: 沒有那個檔案或目錄
由於沒有/netapp/asoftdw/backup 這個目錄導致邏輯備份失敗。
解決方法：
增加/netapp/asoftdw/backup 路徑，或者建立新的directory 並修改指令碼中關於directory=mydp1 
-------------------------------------------------------------------------
遇到這樣錯誤還有如下情況，都是和directory相關的！
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.
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
ORA-29283: invalid file operation
原因：對於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 ;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-712144/，如需轉載，請註明出處，否則將追究法律責任。

相關文章