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

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

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

服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com

 

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.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号