Long story short we have a server with two Oracle instances running on it: ora1 and ora2. Somebody defined an undo tablespace for ora2 and provided the same datafile of the undo tablespace datafile of ora1 that was already running, so datafile got corrupted and ora1 was shutdown. When trying to reopen the instance ora1, we were faced with the below error:
ORA-01159: file is not from same database as previous files - wrong database id
We tried to alter database datafile .... offline drop; and it went fine without errors but that also didn't allow us to open the database, so we backed up controlfile to trace and re-edited the file removing that corrupted datafile, and ran the new control file only to get the below error this time:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 9460
Session ID: 355 Serial number: 9
Noting that the corrupted datafile is now not listed when selecting from v$datafile nor the tablespace associated to it from v$tablespace.
Is there anyway to edit the data dictionary that Oracle compare upon opening and causing the error? My ultimate goal is to open the database without having to reinstall it, and I'm okay with losing the data as I have an expdp dmp that I can restore for the schema I am using.
Database is Oracle 11gR2 on Oracle Linux server 6.3
Best Answer
There is nothing wrong with the data dictionary.
The moment you overwrote the undo datafile of an active database (which I still wonder how you managed, because file locking does not allow this) you made it impossible to recover the database to a consistent state.
The method you tried would have worked with a regular user tablespace, but undo is different. Your database crashed, it is in inconsistent state. The database can not be recovered to a consistent state, because transactions that were active at the time of the crash must to be rolled back, the rollback needs the undo, but you do not have it anymore. You can not just simply skip undo and remove references to it from the dictionary.
Even if you managed to open an inconsistent database (there are some hidden parameters that allow this in some cases), it may just crash immediately after opened. This scenario is the one before last resort to salvage data from a database that can not be opened anymore and has no backups. (The last resort is contacting 3rd party companies for their data mining tools that can read Oracle datafiles directly and salvage data from them.)
Typically you would just get your RMAN backup, restore and recover the missing file, and you would be over with it - but you have no backup.
You have a dump file, then go ahead, create a new empty database, and import to it. Then schedule a proper backup job.