mount状态下解决ora-01157

来源：互联网发布：南宁广电网络收费标准编辑：程序博客网时间：2024/06/08 05:29

mount状态下解决ora-01157

错误是由于数据文件丢失造成的。解决过程如下：

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/edm01/TBAOCM01.DBF'

SQL> l
1* select STATUS,name from V$datafile
SQL> /

STATUS                NAME
--------------------- --------------------------------------------------
SYSTEM                /apps/oradata/tan30/system01.dbf
ONLINE                /apps/oradata/tan30/undotbs01.dbf
ONLINE                /apps/oradata/tan30/sysaux01.dbf
ONLINE                /apps/oradata/tan30/users01.dbf
ONLINE                /edm01/TBAOCM01.DBF
ONLINE                /apps/oradata/tan30/partion01.dbf
ONLINE                /apps/oradata/tan30/partion02.dbf
ONLINE                /apps/oradata/tan30/partion03.dbf
ONLINE                /apps/oradata/tan30/partion04.dbf
ONLINE                /apps/oradata/tan30/biglob01.dbf

10 rows selected.

QL> select name from V$tablespace;

NAME
--------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TBSOCM
PARTION01
PARTION02
PARTION03
PARTION04
BIGLOB

11 rows selected.

SQL>
SQL> select name ,CHECKPOINT_CHANGE# from V$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/apps/oradata/tan30/system01.dbf                              9811429
/apps/oradata/tan30/undotbs01.dbf                             9811429
/apps/oradata/tan30/sysaux01.dbf                              9811429
/apps/oradata/tan30/users01.dbf                               9811429
/edm01/TBAOCM01.DBF                                           8044741
/apps/oradata/tan30/partion01.dbf                             9811429
/apps/oradata/tan30/partion02.dbf                             9811429
/apps/oradata/tan30/partion03.dbf                             9811429
/apps/oradata/tan30/partion04.dbf                             9811429
/apps/oradata/tan30/biglob01.dbf                              9811429

10 rows selected.

SQL>

继续检查发现这个文件物理上找不到。

解决办法：删除该表空间，如有需要再重建。

SQL> alter database datafile '/edm01/TBAOCM01.DBF'
2 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace TBSOCM including contents;

Tablespace dropped.

SQL>