ORA-01180: can not create datafile 1 during restore

I came across this error at work and thought it was worth blogging. Maybe someone else can benefit from it and not spend hours looking for solution.

During a Disaster Recovery exercise, my task was to drop already existing database (it was left there from previous restore) and restore database from current backup.

It is important to note that a database already existed in ARCHIVELOG mode (later on it’ll become clear why I am mentioning this).

So, after deleting the database using dbca, I executed restore database command.  Immediately after, I got the ugly confusing not enough information error

RMAN-03002: failure of restore command at 09/10/2015 17:04:24
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DGDATA01/db1/datafile/system.211.849181900'

I had gotten this error before and it was due to missing directory in ASM.  But this time directory already existed.  So I looked at my old notes and did some google search and found out this error could have something to do with database incarnation.

At first this didn’t make any sense to me.  I am trying to restore database, how and where database incarnation come into play?  I haven’t even open the database using resetlogs.

I started to investigate and here is what I found:

select name, open_mode, resetlogs_change#, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
--------- -------------------- -------------------- -------------------- --------------------
db1       MOUNTED                      160342856897         175422004618         175421783462

1 row selected.

RMAN> list incarnation;</div>

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ------  ---------- ----------
1       1       db1      504953174        PARENT  1            03-july     -2014 08:22:41 am
2       2       db1      504953174        PARENT  160342856897 11-july     -2014 08:06:17 am
3       3       db1      504953174        CURRENT 174815835735 27-august   -2015 09:02:24 am

The SCN from incarnation number 2 matched with what was in control file, which is good.  But how? why? from where? did the new incarnation number 3 came from?

I had to find the answer otherwise I would not be able to sleep. 🙂

I did some research on My Oracle Support and came across Doc ID 1573040.1

“When a BACKUP controlfile is used with a Flash Recovery Area defined, an  implicit crosscheck of the FRA is done and any files found belonging to the database are catalog’d to the controlfile.
Archivelogs created after a resetlogs operation will cause a new incarnation to be registered in the controlfile. 
The new incarnations meant the database backup needed for restore  no longer belonged to the current incarnation.”

hmmm….remember at the beginning I mentioned that database in archivelog mode already existed?

I did have archive logs and autobackups of controlfile and spfile from old database in FRA.  So, that explains the new incarnation that I was seeing.  Now I can sleep better 🙂 .

So lesson learned is to clean up FRA when you drop a database.
Once I cleaned up FRA and restarted the restore procedure, everything worked as expected.

Related Posts

Leave a Reply

Your email address will not be published.