ORA-01244: UNNAMED DATAFILE(S) ISSUE ON STANDBY DATABASE

UNNAMED datafile: 

ORA-01244: ORA-01111: ORA-01110: ORA-01157:

Primarily there are 2 reasons for UNNAMED file getting created on Standby database.

1. Insufficient available space on filesystem/disk group for the datafile to be created on standby database.

2. If “standby_file_management” parameter is set to “MANUAL”, and you add a datafile in Primary DB then while applying archives on Standby, MRP will create UNNAMED datafile due to the parameter setting and MRP will get terminated.

In Our case it was because we were using the Standard Edition of ORACLE

ERROR IN ALERT LOG FILE:

ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Sep 11 16:38:39 2019
Media Recovery Log /arch/GINESYS/archivelog/2019_09_11/o1_mf_1_18039_gqkh4ym3_.arc
Wed Sep 11 16:38:39 2019
File #22 added to control file as 'UNNAMED00022'. Originally created as:'/datafile/GINESYS/undotbs02.dbf'
Errors with log /arch/GINESYS/archivelog/2019_09_11/o1_mf_1_18039_gqkh4ym3_.arc
Wed Sep 11 16:38:39 2019
Media Recovery failed with error 1244
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 3651704254 but controlfile could be ahead of datafiles.
Wed Sep 11 16:38:39 2019
Errors in file /u01/Oracle/diag/rdbms/ginesys/GINESYS/trace/GINESYS_pr00_24640.trc:
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 22: '/datafile/GINESYS/undotbs02.dbf'
ORA-283 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Sep 11 16:39:39 2019
ALTER DATABASE RECOVER database using backup controlfile
Wed Sep 11 16:39:39 2019
Media Recovery Start
Started logmerger process
Wed Sep 11 16:39:39 2019
Media Recovery failed with error 1111
Wed Sep 11 16:39:40 2019
Recovery Slave PR00 previously exited with exception 283

Follow below steps to resolve UNNAMED datafile(s) issue:

• Check for the missing files on Standby

Execute below queries to find the details of file.

select * from v$recover_file where error like '%FILE%';
select file#, error, name from v$datafile_header where ERROR='FILE MISSING';
select file#, name from v$datafile where file#=??;

select name,open_mode,database_role from v$database;

NAME           OPEN_MODE          DATABASE_ROLE
---------           --------------------       ----------------
GINESYS      MOUNTED              PRIMARY

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/datafile/GINESYS/system01.dbf
/datafile/GINESYS/users07.dbf
/datafile/GINESYS/sysaux01.dbf
.
.
/datafile/GINESYS/INDX03
/datafile/GINESYS/users08.dbf
/u01/Oracle/Ora12cDB/dbs/UNNAMED00022

• Check the file details on Primary

Find details of file on Primary database.

select file#, name from v$datafile where file#=??;

• Change the value of parameter “STANDBY_FILE_MANAGEMENT” to “MANUAL”

show parameter standby

NAME                                      TYPE      VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest               string        ?/dbs/arch
standby_file_management       string        MANUAL

If the value of parameter is not already set to “MANUAL” then change it as shown below on standby database,
but in our case this is not to be done as it is Oracle SE 

alter system set standby_file_management=MANUAL sid='*';

• Manually create missing datafile

Now create the missing datafile manually as shown below,

alter database create datafile '/u01/Oracle/Ora12cDB/dbs/UNNAMED00022' as '/datafile/GINESYS/undotbs02.dbf';

Database altered.

If the file names are being managed by OMF, then you won’t be allowed to create datafile since the format is maintained by ASM.

To create datafile if using OMF, execute the below command.

alter database create datafile '/u01/Oracle/Ora12cDB/dbs/UNNAMED00022' as new;

• Start recovery on standby database

Now start managed recovery on standby database.

recover automatic database using backup controlfile;

I hope this will help you 😊