ORA-00301及ORA-01516错误解析

这是一个极小的错误,由于一个空格导致。但是我个人认为非常有必要整理出来,以提醒自己,谨小慎微!

 

今天在测试OFFLINE数据文件时出现错误:

SQL> alter database datafile ‘/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf ‘ offline;
alter database datafile ‘/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf ‘ offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
“/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf ”

 

查询错误代码:

ORA-01516: nonexistent log file, datafile, or tempfile “string”

Cause: An attempt was made to use ALTER DATABASE to rename a log file, datafile, or tempfile; or to change attributes of a datafile or tempfile (e.g., resize, autoextend, online/offline, etc.); or to re-create a datafile. The attempt failed because the specified file is not known to the database’s control file or is not of a type supported by the request.

Action: Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

提示的错误原因为指定的文件不被control file所知,可是我是复制粘贴的名字啊!

仔细查看,在dbf和引号间有个空格,重新输入命令,成功:

SQL> alter database datafile ‘/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf’ offline;

Database altered.

 

由此我联想到几天前客户咨询我如何添加重做日志组。我当时还感到奇怪,因为在报告中已详细说明,他怎么会再次问我呢?原因同上面一样,因为文件名和引号之间有个空格!让我们测试下:

SQL> alter database add logfile group 4 ‘ /home/oracle/app/oracle/oradata/orcl/redo04.log ‘ size 100m;

alter database add logfile group 4 ‘ /home/oracle/app/oracle/oradata/orcl/redo04.log ‘ size 100m

*

ERROR at line 1:

ORA-00301: error in adding log file ‘ /home/oracle/app/oracle/oradata/orcl/redo04.log ‘ – file

cannot be created

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

 

错误代码:

ORA-00301: error in adding log file ‘string’ – file cannot be created

Cause: The creation of the redo log file failed

Action: Check: 1) there is enough storage space on the device 2) the name of the file is valid 3) the device is online 4) an IO error occurred Also, it is possible REUSE was specified on the command line and a file of the incorrect size exists. Either do not specify REUSE or use a file of the correct size.

再次去看之前的语句,果然,文件名与引号之间有空格,去掉空格后再次尝试,成功:

SQL> alter database add logfile group 4 ‘/home/oracle/app/oracle/oradata/orcl/redo04.log’ size 100m;

Database altered.

 

总结:只是一个空格,却带来了这么多麻烦。如果不是今天鬼使神差的去看dbf,如果没有亲自尝试offline数据文件,如果没有多加那一个空格,即使到现在我还不清楚客户为什么会问我那样一个问题。要想做一个DBA,除了扎实的基础,丰富的知识,冷静的头脑,还需谨慎细心。这个细,没有极限。要站在客户的角度,给客户提供最简单易行,最快捷有效的解决方案。路还很长。

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress