How to resolve ORA-01110: UNNAMED File Issue

ORA-16004 ORA-01157 ORA-01111 ORA-01110: data file

In an Oracle Data Guard environment, adding a new datafile to the primary database might fail to replicate automatically on the standby database. This leads to errors such as:

ORA-16004: backup database required recovery
ORA-01157: cannot identify/lock data file 46 - see DBWR trace file
ORA-01111: name for data file 46 is unknown - rename to correct file
ORA-01110: data file 46: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00046'
ORA-16004 signalled during: alter database open read only

Identify UNNAMED Files on the Standby Database

SELECT file#, name, status FROM v$datafile;
SELECT file#, name, status FROM v$tempfile;

If the issue exists, you will see a file named UNNAMED in the output:

FILE#   NAME
-----   --------------------------------------------------
46      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00046

Also, check for recoverable files:

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

FILE#   NAME
-----   --------------------------------------------------
46      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00046

Check the size of the data file added to the primary database:

SELECT (bytes)/1024/1024 AS MB FROM v$datafile WHERE file# = 46;

MB
------
100

If the STANDBY_FILE_MANAGEMENT parameter on the standby database is not set to MANUAL, it must be temporarily changed to MANUAL before modifying the datafile.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

Cancel the managed recovery by

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Manually Add Datafile to Standby Database

ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00046'
AS '+DATA' SIZE 100M;

Verify

SELECT file#, name, status FROM v$datafile;

Revert to Automatic Mode: Once the datafile creation and synchronization are complete, revert the setting to AUTO

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

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

I hope this helped you! If you need more help, feel free to contact me on linkedin

Best regards!

“Source:

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (Doc ID 1416554.1)”