RE: Is it possible to add existing datafiles to an oracle database?
Date: Fri, 25 Apr 2008 12:49:44 -0400
".we just did a file system backup of all the files belonging to the crashed db"
If you actually have all the files belonging to the crashed db, you just need to reload them and start the database.
Of course you also wrote: "After trying to get the database to start for about a full day,." with no indication of what was wrong at that point.
For all we know you lost a disk drive. We have no idea what Oracle was telling you, either interactively or through the alert logs and dump files, about what was wrong with the database or system at that point. IF the files were corrupted you'll need to go back to an older backup set and roll forward.
Backing up and recovering Oracle databases is simple and has been since at least 1988, including the ability to recover from a complete set of files to any point in time equal to or future to the present from the youngest of the files of the available fileset, even if hetereochronus component files must be used due to lost media in some backup sets.
Apparently your database was down when you made a file system backup. Your likely starting point is to reload that. If you didn't get all the files though, or if your files were corrupt already when you made the backup the best you can do is one of the datafile unloaders, either from Oracle or at least two fine consultant/vendors you can find on this list's archives.
By the way, your statement "We couldn't change the name of the db, because a lot of developers were on this crashed db :( " makes no sense at all. Recreating Oracle databases of several different names and connecting to similar schema within them is merely a question of network addresses and instance name connections together with user names and passwords. No application in Oracle really needs to know the name of the database it is connecting to except for maintenance utilities like RMAN or OEM Grid that may need to know the name to keep multiple track of multiple databases, instances, and the components thereof.
Finally, there is actually a way to add files from one oracle database to another. That is called "transportable tablespaces" and it does require specific advanced planning so that the metadata is prepared for the database dictionary to be made aware of what is in the files to be added. It allows the exchange of an arbitrarily large volume of data between databases at a cost only proportional to the amount of the metatdata required to describe the contents. I'll go out on a limb here and guess you're unlikely to have prepared your tablespaces for transport.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Srinivas Chintamani
Sent: Friday, April 25, 2008 10:03 AM
To: Toon Koppelaars
Subject: Re: Is it possible to add existing datafiles to an oracle database?
"which refuses to start"
--> What is the error that you receive on startup?
>>>>> The error it __gave__ was "UNDOTbs1.dbf size X is smaller than Y blocks".
"The crashed database is deleted now and I only have the datafiles of the crashed database somewhere on my filesystem"
--> If I read this carefully you are contradicting yourself here.... not?
>>>>> After trying to get the database to start for about a full day, we just did a file system backup of all the files belonging to the crashed db and later on went on to create a new db with the same name. We couldn't change the name of the db, because a lot of developers were on this crashed db :(