Re: Is it possible to add existing datafiles to an oracle database?
- From: "Jack van Zanen" <jack@xxxxxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Tue, 29 Apr 2008 08:08:59 +1000
Haven't tried this before, but....
Can't you change the init file to undo management manual and recreate the
controlfile without that tablespace.
There may be some rollback that you need for crash recovery in which case
you're stuffed
On 28/04/2008, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
> 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:
>
>
>
> 1) Someone put a different file in place or somehow truncated the
> file at the operating system level.
>
> 2) 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
> recover it.
>
> 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
> arena."
>
>
>
> 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.
>
>
>
> Regards,
>
>
>
> mwf
>
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Srinivas Chintamani
> *Sent:* Sunday, April 27, 2008 11:48 PM
> *To:* Robert Freeman
> *Cc:* David Pintor; oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: Is it possible to add existing datafiles to an oracle
> database?
>
>
>
> Hi Robert,
> 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
> again?
>
> Regards,
> Srinivas.
>
> <snip>
>
--
J.A. van Zanen
Other related posts:
