ORA-01206: File is not part of this database – wrong database id

Posted in: Technical Track

This was posted yesterday on Oracle-L by Li Li. I feel I should blog about it to spread the word, especially since not everyone in this world performs test-restores.

Li was executing a test-restore and hit a problem at the end of the point-in-time recovery phase:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01122: database file 9 failed verification check 
ORA-01110: data file 9: 'H:\xxx\xxx\xxx.dbf'
ORA-01206: file is not part of this database - wrong database id

Datafile 9 was a read-only tablespace, and the source database was actually created with RMAN DUPLICATE. Datafile 9 was read-only during that duplicate operation, and the status hadn’t changed since then. As you can imagine, the read-only datafiles were not changed and their headers still contained the DBID of the database that was the source of the RMAN DUPLICATE. A similar situation could probably happen if tablespaces were imported using transportable tablespaces feature, and left read only.

The fix in this case is to make tablespaces read-write for a moment, and then change back to read-only. The read-write operation will write new datafile headers and, consequently, put there the “right” DBID. IMPORTANT — this has to be done before backup and not after a disaster strikes. This case just emphasizes again the most important rule of any backup/recovery strategy is to do regular test-restores.

If it’s too late and something hit the fan — well, you probably have a chance to offline drop those tablespaces and, hopefully, be able to import them back, if those are transportable tablespaces, and the metadata dump file is still available.

Another idea would be to offline datafiles and then online them after OPEN RESETLOGS. Should someone try that — let us know if it works.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

2 Comments. Leave new

open resetlogs did not work for me… said resetlogs option only valid after an incomplete database recovery… recover database said “no recovery required”.

Alex Gorbachev
April 14, 2008 9:00 pm

David, what was your restore/recovery scenario?


Leave a Reply

Your email address will not be published. Required fields are marked *