Troubleshooting ORA-01111, ORA-01110, and ORA-01157 Errors in Oracle Standby Database

-

Error Description:

During the recovery process on the standby database, you encounter errors such as:

  • ORA-01111: Name for data file is unknown.
  • ORA-01110: Data file is missing.
  • ORA-01157: Cannot identify/lock data file.

These errors typically occur when a data file is created as "UNNAMED" in the $ORACLE_HOME/dbs directory, causing the Managed Recovery Process (MRP) to fail.

Error Log:

Errors in file /home/app/diag/rdbms/sandy04/SANDY/trace/SANDY_ora_59972.trc:
ORA-01111: name for data file 30 is unknown - rename to correct file
ORA-01110: data file 30: '/home/app/product/11.2.0/db_1/dbs/UNNAMED00030'
ORA-01157: cannot identify/lock data file 30 - see DBWR trace file
ORA-01111: name for data file 30 is unknown - rename to correct file
ORA-01110: data file 30: '/home/app/product/11.2.0/db_1/dbs/UNNAMED00030'
Completed standby crash recovery.

Reason:

The issue arises because the MRP process, while applying archives, creates an unnamed file in the $ORACLE_HOME/dbs directory.

Solution:

Recreate the data file in the correct location on the standby database.

Steps to Resolve:

On Primary Database:

  1. Verify Data File Location:

SQL> SELECT file#, name FROM v$datafile WHERE file# = 30;


  FILE# NAME

  ---------- --------------------------------------------------

      30 /oradata/SANDY/DATAFILE/sandy_R_30.dbf

On Standby Database:

  1. Check Recovery Files:

SQL> SELECT * FROM v$recover_file WHERE error LIKE '%FILE%';


FILE# ONLINE ONLINE_ ERROR                             CHANGE# TIME          CON_ID

---------- ------- ------- ------------------------------ ---------- --------- ----------

30      ONLINE ONLINE FILE MISSING                            0                    0

  1. Identify Data File:

SQL> SELECT file#, name FROM v$datafile WHERE file# = 30;


FILE# NAME

---------- -------------------------------------------------------

30 /home/app/product/11.2.0/db_1/dbs/UNNAMED00030

  1. Check Standby File Management:

SQL> SHOW PARAMETER standby_file_management;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

  1. Set Standby File Management to Manual:

SQL> ALTER SYSTEM SET standby_file_management=MANUAL SCOPE=BOTH;

System altered.

  1. Create the Data File in Correct Location:

SQL> ALTER DATABASE CREATE DATAFILE '/home/app/product/11.2.0/db_1/dbs/UNNAMED00030' AS '/oradata/SANDY/DATAFILE/sandy_R_30.dbf';


Database altered.

  1. Set Standby File Management Back to Auto:

SQL> ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;


System altered.

  1. Verify Recovery Files:

SQL> SELECT * FROM v$recover_file WHERE error LIKE '%FILE%';


no rows selected

  1. Verify Data Files:

SQL> SELECT name FROM v$datafile;


NAME

--------------------------------------------------------------------------------

/oradata/SANDY/DATAFILE/sandy_R-SYSTEM_-1

/oradata/SANDY/DATAFILE/sandy_R-SYSAUX_-2

/oradata/SANDY/DATAFILE/sandy_R-UNDOTBS1_-3

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-4

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-5

/oradata/SANDY/DATAFILE/sandy_R-NOLOGGING_TS_-6

/oradata/SANDY/DATAFILE/sandy_R-OMS_ARCHIVES_-7

/oradata/SANDY/DATAFILE/sandy_R-RMANDB_TS_-8

/oradata/SANDY/DATAFILE/sandy_R-MUBASHER_AUDIT_TS_-9

/oradata/SANDY/DATAFILE/sandy_R-MUBASHER_MIS_TS_-10

/oradata/SANDY/DATAFILE/sandy_R-PROFILE_MANAGER_TS_-11

 

NAME

--------------------------------------------------------------------------------

/oradata/SANDY/DATAFILE/sandy_R-PERFSTAT_TS_-12

/oradata/SANDY/DATAFILE/sandy_R-RMAN_TS_-13

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-14

/oradata/SANDY/DATAFILE/sandy_R-USERS1_-15

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-16

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-17

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-18

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-19

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-20

/oradata/SANDY/DATAFILE/sandy_R-SYSTEM_-21

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-22

 

NAME

--------------------------------------------------------------------------------

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-23

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-24

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-25

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-26

/oradata/SANDY/DATAFILE/sandy_R-SYSTEM_-27

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-28

/oradata/SANDY/DATAFILE/sandy_R-SYSTEM_-28

/oradata/SANDY/DATAFILE/sandy_R-sandy_TS-30

 

30 rows selected.

By following these steps, you should resolve the ORA-01111, ORA-01110, and ORA-01157 errors and ensure that the MRP process completes successfully.

 

Comments

Post a Comment

Popular posts from this blog

[FATAL] [DBT-10503] Template file is not specified

-
I faced an error [FATAL] [DBT-10503] Template file is not specified and terminated operation when I was going to create 12c instance in silent mode .   ERROR: [FATAL] [DBT-10503] Template file is not specified.   REASON: This is due to the you are not set the “templateName” in response file.   SOLUTION: Set templateName=General_Purpose.dbc and execute the dbca again   ACTION: 1.        Open the dbca.rsp and set templateName as General_Purpose.dbc 2.        Save the dbca.rsp 3.        Execute the instance create statement again (dbca)
Read more

Resolving ORA-10635 and ORA-39171 Errors During BLOB Tablespace Maintenance

-
Recently, I came across an interesting situation while performing a space management task on one of our databases. The challenge involved a table that contained BLOB data distributed across multiple tablespaces . What seemed like a simple shrink operation ended up turning into a deeper learning experience that I’d like to share. 🧩 Background In this environment, the main table BINARY_ARCHIVE was stored in the MAIN_DATA tablespace. Its BLOB segment was placed separately in the ATTACHMENTS_DATA tablespace to improve performance and manage large object storage efficiently. Additionally, another table called BINARY_OBJECT_LINK maintained a foreign key reference to the BLOB data and resided in the LINK_DATA tablespace. When I attempted to reclaim space from the BLOB segment using the SHRINK SPACE command, Oracle returned the following error. ⚠️ The Error ORA -10635 : Invalid segment or tablespace type 10635. 00000 - " Invalid segment or tablespace type " *Ca...
Read more

How to resolve RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2

-
In my case there are two oracle homes. so when I schedule a cron job for archive clearing following error was raised. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 17 13:41:49 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-03000: recovery manager compiler component initialization failed RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2 SOLUTION: I have added following line to the oraenv file.  export PATH=$ORACLE_HOME/bin:$PATH
Read more