【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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-712144/