Oracle 在drop一个alias后数据库启动时ORA-01157

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]

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.