mount状态下解决ora-01157

最新推荐文章于 2024-09-10 19:51:03 发布

bamuta

最新推荐文章于 2024-09-10 19:51:03 发布

阅读量850

点赞数

文章标签： database sql file

本文链接：https://blog.csdn.net/bamuta/article/details/7436964

版权

troubleshoot 专栏收录该内容

69 篇文章 0 订阅

订阅专栏

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>

如果有需要再进行重建该表空间，如果有相应的备份可以按常规恢复方法恢复