In your immediately previous post, you noted that the startup problem was
that the UNDO tablespace had a datafile size check fail. Still no mention of
the cause of the crash. Do you have alert logs? They might tell you
something. Did someone unplug the disk array? Did someone start an OS level
compress on UNDOTBS01.DBF?
There are two cases that I can think of, though for the file to be the wrong
size on the restart attempt:
Someone put a different file in place or somehow truncated the file
at the operating system level.
Autoextend was on for the UNDO tablespace's file and the dictionary
was updated before the completion of the file system write(s) actually
increasing the size of the file.
For 1), you just get a full size version of the file from a backup and
For 2), which I didn't think could happen (though a virtual file system or a
sparse file system could possibly be set up to allow the OS to lie to Oracle
that the extension was complete when in fact it was deferred), you might
possibly have to recover both SYSTEM and UNDO from an older file set, and
this time have enough real space for the extension to complete.
As for a checklist, there are n (where n is a pretty big number including
Robert's own book and the Oracle manuals) sources of the various backup and
recovery processes for Oracle databases. Knowing what caused the crash in
the first place is usually a good starting point for recovery, right after
preserving an on line copy of the online redo logs. If you're using RMAN,
that pretty much keeps track of what to do next. If you're managing your own
physical backups, then you need to be sure you have complete file images of
all the tablespace files, all the interceding archived redo logs since the
oldest of the file images, and a reasonable control file image (either
current or backup). Usually instance recovery from the existing images on
line completes automagically just by starting the database. If a piece of
the database is missing, Oracle tells you what is wrong. In your case it
told you that a file of the UNDO tablespace was not all there. So in your
specific case the checklist was: "Find out what happened to the rest of my
file. Fix that. Recover."
You keep repeating that it is not a simple thing to recover a crashed db.
That is not true. I wonder why you keep repeating that falsehood.
It is true that it may be impossible to recover anything if arbitrary
actions are taken that are incompatible with recovery. Try recovering sql
server if half the file has been removed.
Now you have written:
"Since, I do have a full file sytem backup of the relevant Oracle files
(Control files, datafiles, archived redo log files), I was trying to see if
I can recover any of the data in those datafiles into a new oracle db. My
apologies, if I sound too simplistic... as I said, I am a rookie in this
So it may indeed still be possible to get your whole database back.
Apparently you may have overwritten your online redologs, so you might not
be able to get all the way to the present (the unarchived changes will be
lost, and transacations in progress at that point will be rolled back.) I am
a bit troubled by your use of the word "relevant" but if you really have all
the files you should be able to recover. You might need an older vintage of
the file data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF', since that
file is damaged as of the startup attempts prior to your ill advised notion
to create a new database of the same name. (By the way if the "full file
system backup of the relevant Oracle files" is from after you did this, then
you don't have a backup of your old database unless you have an older set of
files and the interceding archived redo logs.).
I really do wish you good luck, and I think it is possible you may still
recover, but please stop insisting that this is not simple. That just makes
me think you work for Microsoft and this is a complete dodge to paint
Oracle's incredibly good recovery model as something less than it is.
Creating the best possible recovery strategy to handle site disasters and
business continuation is a complex undertaking because of the highly
variable needs of businesses and the cost benefit tradeoff analysis of
building the infrastructure to handle something like that, but the simple
backup and recovery of a single local Oracle database is just that: simple.
On Behalf Of Srinivas Chintamani
Sent: Sunday, April 27, 2008 11:48 PM
To: Robert Freeman
Cc: David Pintor; oracle-l_at_freelists.org
Subject: Re: Is it possible to add existing datafiles to an oracle database?
I agree... I didn't really know what I was into before this db crashed and I
had the devs breathing down my neck to get their db up and running. Now
that I know its not a simple thing to recover a crashed db, would you have a
check-list that I might use to prevent this kind of mishap from happening