ORA-01111 & ORA-01110

原创已于 2022-03-13 00:15:42 修改 · 3.3k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #database

于 2022-03-10 21:29:03 首次发布

DBA 专栏收录该内容

1069 篇文章

订阅专栏

当Oracle数据库的某个数据文件丢失或未正确命名时，会导致错误如ORA-01111和ORA-01110。解决这个问题需要一系列步骤：首先，确认备库空间满导致的问题，然后在Mount状态下重建数据文件，指定新的路径。接着，应用归档日志以恢复数据，并监控恢复进度。最后，打开数据库并确保MRP进程正常运行。整个过程涉及数据库管理、故障恢复和存储管理。

问题

ORA-01111: name for data file 66 is unknown - rename to correct file
ORA-01110: data file 66: '/opt/oracle/product/dbs/UNNAMED00066'
ORA-01157: cannot identify/lock data file66 - see DBWR trace file
ORA-01111: name for data file 66 is unknown - rename to correct file
ORA-01110: data file 66: '/opt/oracle/product/dbs/UNNAMED00066'

原因

df -h

备库空间满了导致数据文件创建到本地目录

备库查询

select * from v$recover_file error like '%FILE%';
66 online online file missing

select file#,name from v$recover_file error like '%FILE%';

主库查询

select file#,name from v$datafile where file# in 66;

select file#,name from v$datafile where file# in 67; --查看下一个数据文件是否正常

解决方案

1 扩容数据空间

2 启动数据库至mount状态

shutdown immediate ；  

startup mount ;

3 重建数据文件

alter system set standby_file_management=manual;
alter database create datafile '/opt/oracle/pdoduct/dbs/UNNAMED00066' as '/data/xxxx/datafile/datafile66.dbf';
alter system set standby_file_management=auto;

4 开启mrp进程应用归档

shutdown immediate;

startup mount;

alter database recover managed standby database using current logfile disconnect from session;

5 开启数据库至open状态

查看日志等待应用归档完成后，关闭mrp进程，然后打开数据库，再次开启mrp

set lines 200
col type format a15
col ITEM format a26
col units format a15
col comments format a25
select * from v$recovery_progress where START_TIME = (select max(START_TIME) from v$recovery_progress);

alter database recover managed standby database cancel;

alter database open ;

alter database recover managed standby database using current logfile disconnect from session;

select PID,PROCESS,STATUS,SEQUENCE#,THREAD#,BLOCKS,DELAY_MINS from v$managed_standby;

col value for a20
col name for a24
col DATUM_TIME for a20
col TIME_COMPUTED for a25
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;