ORA-01244: unnamed datafile(s) added to control file by media recovery

Using Point in Time Recovery to migrate a database from one server to another is a common method: you take an RMAN backup from the old server while it’s running and restore it on the new server, then you keep copying archive logs from the old system to the new system and recover the database with them until the point you shutdown the old server, do a final recover and switch your application to the new system.

With this way, you keep the downtime to the minimum and the new system update to date. Depending on how long you need to keep the new system in sync with the old system manually, you might see new data files added in the old system after you take the backup and restore it on the new server. If you new system has the same disk structures for database data files as the old one’s, you are lucky and there is nothing special which needs your attention during the recovery.

However, quite often we are utilizing this opportunity to redefine the disk layouts since the old server has running for years and the size of database has grown a lot. It means the new server would have a totally different disk structures than old one’s. For existing data files coming with the backup, we could use “set newname for datafile” and “swtich datafile” to change the location during the restore. For newly added data files after the backup, if you monitor the system closely, you will notice they are added and you just resotre them individually after restore of the whole database. But if you don’t keey track of it, and just do the recovery with the archive logs, you might running into the following error:

creating datafile file number=1150 name=/data4/oracle/oradata/PROD/DATA453.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2020 01:44:48
RMAN-20505: create datafile during recovery
ORA-01119: error in creating database file '/data4/oracle/oradata/PROD/DATA453.DBF'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/dbase/archivelog/12c_archivelog/arch_1_201265_923911048.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 1150: '/data4/oracle/oradata/PROD/DATA453.DBF'

As I just mentioned, doing recovery with the archive logs fails with this type of error due to non-existing directory for the data file. What you need to do is to set up the correct path and name for it, and restore it, then continue the recovery with the archive logs. For example:

run {
	set newname for datafile 1150 to "/dbase/data3/PROD/data453.dbf";
	restore datafile 1150;
	switch datafile 1150;
}

recover database until sequence 201298;

One thing to note, even you have recovered the database up-to-date, you will see the status from v$datafile for this file as “RECOVER”, not as “ONLINE” as others restored with the database.

SQL> select file#, name, status from v$datafile where status='RECOVER';

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
      1150 /dbase/data3/PROD/data453.dbf                                RECOVER

But the status from v$datafile_header does show it as “ONLINE”.

SQL> select file#, name, status from v$datafile where file#=1150;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
      1150 /dbase/data3/PROD/data453.dbf                                ONLINE

In the example above, the file header had online status and the control file would be updated to ONLINE when the database was opened with resetlogs. I didn’t know that and was hoping to bring the status consistent by recovering the file, but Oracle didn’t allow that because the backup of control file was used:

SQL> alter database datafile 1150 online;
alter database datafile 1150 online
*
ERROR at line 1:
ORA-01113: file 1150 needs media recovery
ORA-01110: data file 1150: '/dbase/data3/PROD/data453.dbf'


RMAN> recover datafile 1150;

Starting recover at 27-DEC-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1888 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=5 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=379 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/27/2020 08:36:12
RMAN-06067: RECOVER DATABASE required with a backup or created control file

The RMAN message “RMAN-06067” clearly indicated that only using “recover database“. So I just sticked to my recovery with archive logs:

RMAN> recover database until sequence 201481;

After I opened the database with resetlogs, file status has changed to ONLINE.

Reference:

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)

One thought on “ORA-01244: unnamed datafile(s) added to control file by media recovery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s