【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 ;
Oracle存储过程迁移ODPS-02（专有云）：循环逻辑修改一例（构造代码表） 总有同学提问，说我原来在oracle的存储过程中的循环现在maxcompute支持了，我的程序怎么迁移。这个问题其实非常难以回答，一般来说99%的oracle的代码都可以通过maxcompute的SQL和函数来替代，这个也包括一般的循环语句要解的问题。