解决ORA-01111, ORA-01110, ORA-01157

最新推荐文章于 2022-06-10 11:41:55 发布

吥輕誩放棄

最新推荐文章于 2022-06-10 11:41:55 发布

阅读量6.2k

点赞数 3

分类专栏： ORA - ERROR 文章标签： Oracle

本文链接：https://blog.csdn.net/sandy9919/article/details/84956266

版权

ORA - ERROR 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

在备用数据库中创建文件为UNNAMED或MISSING的原因有很多，包括备用站点上的磁盘空间不足（或）与文件管理相关的不正确的参数设置。

STANDBY_FILE_MANAGEMENT启用或禁用自动备用文件管理。启用自动备用文件管理后，将在备用数据库上复制主数据库上的操作系统文件添加和删除。

例如，如果我们在主服务器上将参数STANDBY_FILE_MANAGEMENT设置为MANUAL时在主服务器上添加数据文件，而恢复过程（MRP）正在尝试应用存档，由于该参数设置，它将在$ ORACLE_HOME / dbs中创建一个未命名的文件将导致杀死MRP进程，错误将如下所示。

警报日志文件中的错误： -

#limsdbdg实例alert日志告警信息
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Beginning Standby Crash Recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Standby Crash Recovery aborted due to error 1111.
Errors in file /app/oracle/diag/rdbms/lmisdbdg/lmisdbdg/trace/lmisdbdg_ora_5174.trc:
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
Completed Standby Crash Recovery.
Errors in file /app/oracle/diag/rdbms/lmisdbdg/lmisdbdg/trace/lmisdbdg_ora_5174.trc:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-10458 signalled during: alter database open...

跟踪文件： -

*** 2018-12-11 09:40:38.470
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
Managed Recovery: Real Time Apply enabled.
Managed Recovery: Startup posted.
Managed Recovery: Initialization posted.

*** 2018-12-11 09:40:38.470
Started Serial Media Recovery
*** 2018-12-11 09:40:38.479 4329 krsh.c
Managed Standby Recovery starting Real Time Apply
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'

*** 2018-12-11 09:40:38.502
Completed Media Recovery
Managed Recovery: Not Active posted.
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005'
Managed Recovery: Real Time Apply enabled.

*** 2018-12-11 14:34:34.181
Managed Recovery: THROUGH ALL SWITCHOVER posted.
Managed Recovery: DISCONNECT posted.
Managed Recovery: Startup posted.

*** 2018-12-11 14:44:39.505
Managed Recovery: Cancel posted.

*** 2018-12-11 14:44:56.218
Managed Recovery: Real Time Apply enabled.
Managed Recovery: THROUGH ALL SWITCHOVER posted.
Managed Recovery: DISCONNECT posted.
Managed Recovery: Startup posted.

故障排除： -

检查是否需要恢复文件。

SQL> select * from v$recover_file where error like '%FILE%';

     FILE# ONLINE  ONLINE_ ERROR								CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- -------------------
	 5 ONLINE  ONLINE  FILE MISSING 							      0
	 6 ONLINE  ONLINE  FILE MISSING 							      0

确认主库数据文件

SQL> select file#,name from v$datafile where file# in (5,6);

     FILE# NAME
---------- ------------------------------------------------------------------
	 5 /oradata/lmis/LMIS01.dbf
	 6 /oradata/lmis/LMIS02.dbf

识别在（备库）中创建的虚拟文件名

SQL> select file#,name from v$datafile where file# in (5,6);

     FILE# NAME
---------- ------------------------------------------------------------------
	 5 /app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005
	 6 /app/oracle/product/11.2.4/db_1/dbs/UNNAMED00006

检查没有运行MRP，并且在待机状态下创建文件后可以启用STANDBY_FILE_MANAGEMENT

SQL> alter system set standby_file_management=manual scope=both;

System altered.

SQL> alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005' as '/oradata/lmisdbdg/LMIS01.dbf';

Database altered.

SQL> alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00006' as '/oradata/lmisdbdg/LMIS02.dbf';

Database altered.

检查虚拟文件是否被修复

SQL> select * from v$recover_file where error like '%FILE%';

no rows selected

启用STANDBY_FILE_MANAGEMENT为AUTO并启动MRP。

SQL> show parameter standby_file_management

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 MANUAL
SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

创建文件后，MRP将开始在备用数据库上应用存档。

#lmisdbdg实例修复过程中的日志信息
Tue Dec 11 14:21:23 2018
alter database recover managed standby database cancel
ORA-16136 signalled during: alter database recover managed standby database cancel...
Tue Dec 11 14:23:12 2018
alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005' as '/oradata/lmisdbdg/LMIS01.dbf'
ORA-1275 signalled during: alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005' as '/oradata/lmisdbdg/LMIS01.dbf'...
Tue Dec 11 14:23:47 2018
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005' as '/oradata/lmisdbdg/LMIS01.dbf'
Tue Dec 11 14:24:07 2018
Completed: alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00005' as '/oradata/lmisdbdg/LMIS01.dbf'
Tue Dec 11 14:24:33 2018
alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00006' as '/oradata/lmisdbdg/LMIS02.dbf'
Tue Dec 11 14:24:43 2018
Completed: alter database create datafile'/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00006' as '/oradata/lmisdbdg/LMIS02.dbf'
Tue Dec 11 14:34:17 2018
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
Tue Dec 11 14:34:34 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (lmisdbdg)
Tue Dec 11 14:34:34 2018
MRP0 started with pid=52, OS id=15498 
MRP0: Background Managed Standby Recovery process started (lmisdbdg)
 started logmerger process
Tue Dec 11 14:34:39 2018
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 20 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /arch/1_21_992735965.arc
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Tue Dec 11 14:34:49 2018
Recovery created file /oradata/lmisdbdg/LMIS_HIS01.dbf
Successfully added datafile 7 to media recovery
Datafile #7: '/oradata/lmisdbdg/LMIS_HIS01.dbf'
Recovery created file /oradata/lmisdbdg/INF01.dbf
Successfully added datafile 8 to media recovery
Datafile #8: '/oradata/lmisdbdg/INF01.dbf'
Tue Dec 11 14:35:01 2018
Recovery created file /oradata/lmisdbdg/WCS01.dbf
Successfully added datafile 9 to media recovery
Datafile #9: '/oradata/lmisdbdg/WCS01.dbf'
Tue Dec 11 14:35:13 2018
Recovery created file /oradata/lmisdbdg/LMIS_HIS02.dbf
Successfully added datafile 10 to media recovery
Datafile #10: '/oradata/lmisdbdg/LMIS_HIS02.dbf'
Recovery created file /oradata/lmisdbdg/LMIS_HIS03.dbf
Successfully added datafile 11 to media recovery
Datafile #11: '/oradata/lmisdbdg/LMIS_HIS03.dbf'
Tue Dec 11 14:35:25 2018
Media Recovery Log /arch/1_22_992735965.arc
Media Recovery Log /arch/1_23_992735965.arc
Media Recovery Log /arch/1_24_992735965.arc
Media Recovery Log /arch/1_25_992735965.arc
Tue Dec 11 14:35:37 2018
Media Recovery Log /arch/1_26_992735965.arc
Media Recovery Log /arch/1_27_992735965.arc
Media Recovery Log /arch/1_28_992735965.arc
Media Recovery Log /arch/1_29_992735965.arc
Tue Dec 11 14:35:49 2018
Media Recovery Log /arch/1_30_992735965.arc
Media Recovery Log /arch/1_31_992735965.arc
Media Recovery Log /arch/1_32_992735965.arc
Media Recovery Log /arch/1_33_992735965.arc
Media Recovery Log /arch/1_34_992735965.arc
Tue Dec 11 14:36:01 2018
Media Recovery Log /arch/1_35_992735965.arc
Media Recovery Log /arch/1_36_992735965.arc
Media Recovery Waiting for thread 1 sequence 37 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 37 Reading mem 0
  Mem# 0: /oradata/lmisdbdg/standby04.log

检查主备库归档应用情况

--lmis实例归档日志信息
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	18 2018-11-26 13:38:24 2018-11-30 12:12:48 YES
	19 2018-11-30 12:12:48 2018-12-03 15:55:06 YES
	20 2018-12-03 15:55:06 2018-12-07 00:14:24 YES
	20 2018-12-03 15:55:06 2018-12-07 00:14:24 NO
	21 2018-12-07 00:14:24 2018-12-08 22:13:14 NO
	21 2018-12-07 00:14:24 2018-12-08 22:13:14 NO
	22 2018-12-08 22:13:14 2018-12-08 22:13:17 NO
	22 2018-12-08 22:13:14 2018-12-08 22:13:17 NO
	23 2018-12-08 22:13:17 2018-12-08 22:13:22 NO
	23 2018-12-08 22:13:17 2018-12-08 22:13:22 NO
	24 2018-12-08 22:13:22 2018-12-08 22:14:40 NO

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	24 2018-12-08 22:13:22 2018-12-08 22:14:40 NO
	25 2018-12-08 22:14:40 2018-12-09 00:16:43 NO
	25 2018-12-08 22:14:40 2018-12-09 00:16:43 NO
	26 2018-12-09 00:16:43 2018-12-09 05:11:13 NO
	26 2018-12-09 00:16:43 2018-12-09 05:11:13 NO
	27 2018-12-09 05:11:13 2018-12-09 09:42:43 NO
	27 2018-12-09 05:11:13 2018-12-09 09:42:43 NO
	28 2018-12-09 09:42:43 2018-12-09 14:17:43 NO
	28 2018-12-09 09:42:43 2018-12-09 14:17:43 NO
	29 2018-12-09 14:17:43 2018-12-09 19:09:43 NO
	29 2018-12-09 14:17:43 2018-12-09 19:09:43 NO

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	30 2018-12-09 19:09:43 2018-12-10 00:00:10 NO
	30 2018-12-09 19:09:43 2018-12-10 00:00:10 NO
	31 2018-12-10 00:00:10 2018-12-10 04:39:43 NO
	31 2018-12-10 00:00:10 2018-12-10 04:39:43 NO
	32 2018-12-10 04:39:43 2018-12-10 09:35:13 NO
	32 2018-12-10 04:39:43 2018-12-10 09:35:13 NO
	33 2018-12-10 09:35:13 2018-12-10 14:54:04 NO
	33 2018-12-10 09:35:13 2018-12-10 14:54:04 NO
	34 2018-12-10 14:54:04 2018-12-10 16:53:31 NO
	34 2018-12-10 14:54:04 2018-12-10 16:53:31 NO
	35 2018-12-10 16:53:31 2018-12-10 16:59:19 NO

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	35 2018-12-10 16:53:31 2018-12-10 16:59:19 NO
	36 2018-12-10 16:59:19 2018-12-11 07:15:46 NO
	36 2018-12-10 16:59:19 2018-12-11 07:15:46 NO

36 rows selected.

--lmisdbdg实例归档日志信息
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	 9 2018-11-22 18:26:23 2018-11-22 18:41:30 YES
	10 2018-11-22 18:41:30 2018-11-22 18:45:14 YES
	11 2018-11-22 18:45:14 2018-11-22 19:05:07 YES
	12 2018-11-22 19:05:07 2018-11-22 19:07:06 YES
	13 2018-11-22 19:07:06 2018-11-22 19:12:05 YES
	14 2018-11-22 19:12:05 2018-11-22 16:11:44 YES
	15 2018-11-22 16:11:44 2018-11-22 16:11:48 YES
	16 2018-11-22 16:11:48 2018-11-22 16:12:12 YES
	17 2018-11-22 16:12:12 2018-11-26 13:38:24 YES
	18 2018-11-26 13:38:24 2018-11-30 12:12:48 YES
	19 2018-11-30 12:12:48 2018-12-03 15:55:06 YES

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	20 2018-12-03 15:55:06 2018-12-07 00:14:24 YES
	21 2018-12-07 00:14:24 2018-12-08 22:13:14 NO
	22 2018-12-08 22:13:14 2018-12-08 22:13:17 NO
	23 2018-12-08 22:13:17 2018-12-08 22:13:22 NO
	24 2018-12-08 22:13:22 2018-12-08 22:14:40 NO
	25 2018-12-08 22:14:40 2018-12-09 00:16:43 NO
	26 2018-12-09 00:16:43 2018-12-09 05:11:13 NO
	27 2018-12-09 05:11:13 2018-12-09 09:42:43 NO
	28 2018-12-09 09:42:43 2018-12-09 14:17:43 NO
	29 2018-12-09 14:17:43 2018-12-09 19:09:43 NO
	30 2018-12-09 19:09:43 2018-12-10 00:00:10 NO

 SEQUENCE# FIRST_TIME	       NEXT_TIME	   APPLIED
---------- ------------------- ------------------- ---------
	31 2018-12-10 00:00:10 2018-12-10 04:39:43 NO
	32 2018-12-10 04:39:43 2018-12-10 09:35:13 NO
	33 2018-12-10 09:35:13 2018-12-10 14:54:04 NO
	34 2018-12-10 14:54:04 2018-12-10 16:53:31 NO
	35 2018-12-10 16:53:31 2018-12-10 16:59:19 NO
	36 2018-12-10 16:59:19 2018-12-11 07:15:46 NO

28 rows selected.

注意：-

设置STANDBY_FILE_MANAGEMENT为AUTO使Oracle在备用数据库上自动创建文件，并在某些情况下覆盖现有文件。设置时必须小心STANDBY_FILE_MANAGEMENT，DB_FILE_NAME_CONVERT以免意外覆盖现有备用文件。

吥輕誩放棄

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
解决ORA-01111, ORA-01110, ORA-01157

在备用数据库中创建文件为UNNAMED或MISSING的原因有很多，包括备用站点上的磁盘空间不足（或）与文件管理相关的不正确的参数设置。STANDBY_FILE_MANAGEMENT启用或禁用自动备用文件管理。启用自动备用文件管理后，将在备用数据库上复制主数据库上的操作系统文件添加和删除。例如，如果我们在主服务器上将参数STANDBY_FILE_MANAGEMENT设置为MANUAL时在主...
复制链接

扫一扫