oracle数据库备份39002,【Oracle】逻辑备份失败ORA-39002,39070,29283,06512

最新推荐文章于 2025-03-03 11:22:02 发布

夏天的紫薇花

最新推荐文章于 2025-03-03 11:22:02 发布

阅读量1.6k

点赞数

文章标签： oracle数据库备份39002

这篇博客记录了在Oracle数据库备份过程中遇到的错误，包括ORA-39002、ORA-39070和ORA-29283。错误源于无法打开日志文件，原因是指定的directory路径不存在或权限问题。解决方案包括检查目录权限、确保目录存在并可访问，以及在RAC系统中避免使用TNS名称。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查看备份邮件的时候，发现逻辑导出失败，日志如下：

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

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

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

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 ;