诗檀软件专业数据库修复团队
ORA-01157
oerr ora 1157
01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
// *Cause: The background process was either unable to find one of the data
// files or failed to lock it because the file was already in use.
// The database will prohibit access to this file but other files will
// be unaffected. However the first instance to open the database will
// need to access all online data files. Accompanying error from the
// operating system describes why the file could not be identified.
// *Action: Have operating system make file available to database. Then either
// open the database or do ALTER SYSTEM CHECK DATAFILES.
适用于：
Oracle Server - Enterprise Edition – 版本 10.2.0.1 到 10.2.0.3 [Release 10.2]
本文信息适用于任何平台。
此问题可能出现在任何平台。
症状
从ASM实例中删除数据文件的Alias 导致使用该Alias的RDBMS实例在下一次启动失败，显示ORA-01157。
原因
你不应当drop Alias，因为数据库实例通过数据文件的别名来引用它，一旦创建了别名就不再使用系统生成名称。
从RDBMS 实例中:
Create TABLESPACE test DATAFILE '+DG1/ORCL/DATAFILE/test' SIZE 50m;
ALTER TABLESPACE test ADD DATAFILE '+DG1/ORCL/DATAFILE/test2' size 49m;
从ASM 实例中：
ASMCMD> cd +DG1/ORCL/DATAFILE
ASMCMD> ls
SYSAUX.257.627668821
SYSTEM.256.627668819
UNDOTBS1.258.627668821
USERS.259.627668821
ASMCMD> pwd
+DG1/ORCL/DATAFILE
ASMCMD>
从RDBMS实例中：
SQL>Create TABLESPACE test DATAFILE '+DG1/ORCL/DATAFILE/test' SIZE 50m;
从ASM实例中：
ASMCMD> ls
SYSAUX.257.627668821
SYSTEM.256.627668819
TEST.265.627767963
UNDOTBS1.258.627668821
USERS.259.627668821
test
ASMCMD>
从RDBMS实例中：
ALTER TABLESPACE test ADD DATAFILE '+DG1/ORCL/DATAFILE/test2' size 49m;
从ASM 实例中：
ASMCMD> ls
SYSAUX.257.627668821
SYSTEM.256.627668819
TEST.265.627767963
TEST.266.627768241
UNDOTBS1.258.627668821
USERS.259.627668821
test
test2
ASMCMD>
SQL> select name from v$asm_alias;
NAME
------------------------------------------------
ORCL
DATAFILE
SYSTEM.256.627668819
SYSAUX.257.627668821
UNDOTBS1.258.627668821
USERS.259.627668821
TEST.265.627767963
test
TEST.266.627768241
test2
可以从asmcmd 的输出以及查询v$asm_alias 看出，使用绝对路径添加一个数据文件会为其创建别名和系统生成名称。
你尝试（从asm实例中）删除别名：
SQL>ALTER DISKGROUP dg1 DROP ALIAS '+DG1/ORCL/DATAFILE/
test2';
Diskgroup altered.
SQL> select name from v$asm_alias;
NAME
------------------------------------------------
ORCL
DATAFILE
SYSTEM.256.627668819
SYSAUX.257.627668821
UNDOTBS1.258.627668821
USERS.259.627668821
TEST.265.627767963
test
TEST.266.627768241
Here Alias "+DG1/ORCL/DATAFILE/
test2" is removed.
现在当你关闭数据库并重启它时会得到以下错误：
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1262284 bytes
Variable Size 209718580 bytes
Database Buffers 230686720 bytes
Redo Buffers 2928640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DG1/orcl/datafile/test2'
所以你不应该drop Alias，因为数据库实例通过数据文件的别名来引用它，一旦别名创建就不会使用系统生成名称。
解决方案
在这种情况下，你尝试（从ASM实例）添加回相同的别名。没有明确的方法来找出被drop的别名所属的数据文件。我们可以通过比较在DBA_DATA_FILES视图（RDBMS）和v$asm_alias中数据文件名来完成。系统生成数据文件名在v$asm_alias而不在dba_data_files就是被drop的别名。
SQL>ALTER DISKGROUP dg1 ADD ALIAS '+DG1/ORCL/DATAFILE/test2' FOR '+DG1/ORCL/DATAFILE/TEST.266.627768241';
Diskgroup altered.
Then shutdown and startup RDBMS instance, it opens normally:
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1262284 bytes
Variable Size 213912884 bytes
Database Buffers 226492416 bytes
Redo Buffers 2928640 bytes
Database mounted.
Database opened.