1

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

  • The data dictionary has wrong information? I suggest you open an SR with Oracle Support. Prior to that, you could try a PITR. But, that requires real backups (RMAN). Data Pump files are NOT backups. – Michael Kutz Oct 26 '18 at 16:41
  • yes but unfortunately we don't have RMAN backups available. I don't need the data only to make the database open in whatsoever way :) – amyassin Oct 26 '18 at 16:44
2

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.

  • I also don't get why it wasn't locked and how could the second database use it anyway.. Can you guide me to the hidden parameters you referred to that would allow me to open the database in inconsistent state? That would help me in my requirement. It's not production database so stability is not a concern I just need it open for a small period of time. – amyassin Oct 26 '18 at 19:52
  • 1
    @amyassin _CORRUPTED_ROLLBACK_SEGMENTS, _OFFLINE_ROLLBACK_SEGMENTS, _ALLOW_RESETLOGS_CORRUPTION, _MINIMUM_GIGA_SCN, _ALLOW_READ_ONLY_CORRUPTION – Balazs Papp Oct 26 '18 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.