ORA-01157错误

5dm

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/data2/PMS_DATA.dbf'

問題解決如下:

[oracle@Pyzs-DB-Test3 oracle]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jul 25 08:53:17 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 370218364 bytes
Fixed Size 451964 bytes
Variable Size 150994944 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/data2/PMS_DATA.dbf'

SQL> alter database create datafile '/data2/PMS_DATA.dbf';

Database altered.

SQL> alter database datafile '/data2/PMS_DATA.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

5dm

转另一帖

 

 

解决ORA-01157问题

博客分类：
• ORACLE

OracleSQL 

SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2088184 bytes
Variable Size             528483080 bytes
Database Buffers          377487360 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/var/opt/gssyneeadb/gssy_neeadb.dbf'


SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;

System altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2088184 bytes
Variable Size             528483080 bytes
Database Buffers          377487360 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database datafile '/var/opt/gssyneeadb/gssy_neeadb.dbf' offline drop;

Database altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2088184 bytes
Variable Size             528483080 bytes
Database Buffers          377487360 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL>

5dm

一、查找报错文件号(Fill#)所在的表空间

svrmgrl> startup mount

svrmgrl> SELECT Fill#,TS#,NAME FROM V$DATAFILE WHERE FILL#=报错的数据文件号;

svrmgrl>SELECT TS#,NAME FROM V$TABLESPACE WHERE TS#=上语句检索的ts#;



二、检查一下该数据文件所在的卷组是否处于非激活状态,如是将其激活,否则转下一步即该数据文件发生损坏。



三、根据数据文件所在运行模式及所归属的表空间的类型作相应的处理

一)数据库处于ARCHIVELOG模式下

1、使用recover database 或使用recover datafile命令进行数据库恢复

2、以alter database open resetlogs 命令打开数据库

二)数据库处于NOARCHIVELOG模式下

1、损坏的数据文件属于system表空间

如无物理级备份则须重建数据库

2、损坏的数据文件属于temp表空间,此时可以不丢失数据完成恢复

因临时表空间不包含数据,所以可以将临时表空间Drop掉,重建即可

3、损失的是控制文件

1、如并非所有的控制文件都损坏的话,可以将损坏的文件从init<SID>.ora中去掉,启动数据库即可

2、如所有控制文件皆损坏，可使用dd命令进行控制文件恢复。

4、损坏的数据文件属于用户表空间,有以下两种解决方案:

a.用户的表空间可以被轻易地重建,重建表空间以及所有的对象.

svrmgrl> startup mount

svrmgrl> alter database datafile filename offline drop;

svrmgrl> alter database open;

svrmgrl> drop tablespace tablespace_name including contents;

重建表空间及所有对象.

b.用户的表空间不能够被轻易地重建

如无备份则用户数据数据会全丢失

5、损坏的数据文件属于回滚表空间

1)确认数据库完全关闭

2)修改init.ora文件,注释"rollback-segment"

2)svrmgrl> startup restrict mount

3)svrmgrl> alter database datafile filename offline drop;

4)svrmgrl> alter database open;

基于出现的结果: "statement processed" 转7),"ORA-00604,ORA-00376,ORA-01110"转6)

6)svrmgrl> shutdown immediate

修改init.ora文件,增加如下一行:

_corrupted_rollback_segments = (,...)

svrmgrl> startup restrict

7) svrmgrl> drop tablespace tablespace_name including contents;

8) 重建表空间及回滚段

9) svrmgrl> alter system disable restricted session;

10) 修改init.ora文件