1

For an Oracle 10g/11g database that refuses to start due to datafile (SYSTEM01.DBF) corruption, does RMAN restore command immediately overwrite all current datafiles from the last found full RMAN backup even if the latest RMAN full backup is very old and archived logs starting from the last RMAN full backup is incomplete or corrupted? If yes, is there a way to undo the restore to have the original data files back prior to the restore command?

Lastly, is there a last resort way to force start it up and have Oracle ignore/skip/fix any corrupted part rather than restore and recover from last full RMAN backup and archived logs, at least to recover the latest transactions?

1 Answer 1

0

(Oh dear. You're in quite a pickle, here).

For an Oracle 10g/11g database ...

Oracle 11g "died" two-and-a-bit years ago; 10g over a decade since.
Unless you have a [very expensive] extended support agreement with Our Friends in Redwood City, you're running on dead tech' which, with Oracle Database, is a really, really Bad Idea.

... database that refuses to start due to datafile (SYSTEM01.DBF) corruption ...

The System tablespace holds all the internal workings of your database.
Without that, your database is "toast" and you have to restore it.
But that's OK, because you have backups from which you can restore it.

... does RMAN restore command immediately overwrite all current datafiles ...

Yes. The Recovery MANager's job is to get the database back into a working state and, to do that, it will restore (overwrite) anything and everything it needs to do so.
The first things it will recover (overwrite) are the database Control Files, because they contain the details of everything else that's in the database. Once the Control Files are overwritten, you CANNOT "have another go" at the restore - if it doesn't work, you're stuck with the Control Files you've just restored. (To be fair, this is only really a problem if you frequently add/remove Data Files to/from the database).

... is there a way to undo the restore to have the original data files back prior to the restore command? ...

Kind of. It involves the use of a Recovery Catalog.
This is an extra database that effectively duplicates the information in the Control Files and which does allow RMAN to have multiple "goes" at doing a restore. This is incredibly useful when doing Point-in-Time recoveries because somebody "did something stupid" but can't recall exactly when they did it!
You don't "undo the restore", but you can recover to one point in time, then have another go, restoring to a different point in time, as often as you like. (Just about everything I'm saying here is predicated on your database being in ArchiveLog mode!)
However, I don't know if can add a "misbehaving" database to a Recovery Catalog (if you don't have one already).

... from the last found full RMAN backup ...

The RMAN process is to restore (overwrite) the Control and Data files from the last Level 0 Incremental backup (which is, annoyingly, different from a Full backup) and then to recover those data files by [re-]applying the Archived Redo Logs to them.
If your ArchiveLog backups are also corrupt, then you're in trouble, again.
You should be able to recover up to the point of the first corrupted ArchiveLog backup, but no further.

is there a last resort way to force start it up ...

Again, kind of ...
You would restore/recover as far as you can, then open the database, resetting the Logs (the technical term is creating a new "Incarnation"). That will get you something.

... and have Oracle ignore/skip/fix any corrupted part ...

No.
Oracle Corporation have spent "squillions" of dollars creating a DBMS that, properly used, practically guarantees zero data loss but, ironically, most of that protection is outside the database itself (multiple, multiplexed, logs, data backups, log backups, Flashback Database logs, everything else in the Fast Recovery Area, etc.)
This isn't MySQL (which you can tell: "Start up and I don't care what I lose").

... rather than restore and recover from last full RMAN backup and archived logs, at least to recover the latest transactions?

You cannot get to those latest transactions without first restoring the earlier ones and, if you don't have those, you're stuck.

The really hard lesson you've learned here is that your backups are not adequate (recent enough or working) to be able to get your database back into a working state. Sorry to say it but, IMHO, that's a "hanging offence" for a Database Administrator, because ensuring database recoverability is their job.

You need to seriously consider your Recovery Strategy, which boils down to two, critical metrics:

  • Recovery Time Objective
    How long do I have to get a broken [Production] database working again?
  • Recovery Point Objective
    How much Data am I allowed to lose whilst doing so?

Once you have these two agreed (with company Management), you can start putting measures in place to ensure you can meet this. With Oracle database, you have lots of other tools to "play" with, but your Backups are your "backstops" that absolutely have to work. You need to be

  • Taking Backups often enough to support your Recovery Strategy,
  • Keeping them safe, definitely on another machine and preferably off-site,
  • Testing/rehearsing the restore process periodically to ensure that
    (a) The backups actually can be restored, and
    (b) You are well practised in the process itself, which is especially important if you get called in to an "emergency" and have to start recovering database[s] at stupid o'clock in the morning!
1
  • @clj You could perform an OS-level backup of all files on the system - including the control files - to save your system in its current broken state and return to it for another RMAN restore attempt if things don't go well, but it doesn't sound like you have a good working RMAN backup from which to restore in the first place. Without that, as PhilW said, you really have no means of restoring your data to its most recent state. Best you can hope for is to restore up to the last uncorrupted or first missing archivelog. There's no way to restore past a gap in the transaction history.
    – pmdba
    Mar 31, 2023 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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