Re: Can I restore tablespaces(datafiles) to a new blank database?

TG wrote:
> 9.2.0.6 on Windows server
>
> Here is the situation:
>
> Junior dba wanted to free up some space on a server, so he deleted
> datafiles from a shut down database that he *thought* was his test, not
> his active development database, of course it turns out he deleted the
> wrong set of files and this database was (inexplicably) not backed up.
>
> I go in with a file recovery tool, recover most of the datafiles, and
> more importantly, recover the ones that have the critical info that can
> save a long & costly rebuild of the dev db.
>
> I create a new blank database on another machine, then I thought I could
> rebuild the controlfile with the recovered datafiles from the deleted
> database added. When I created the new database I created it with a
> slightly different name, no problem I think, thinking I could use "set
> database" and alter the init.ora and call it the same as the deleted
> database, so I do the following:
>
> backup control file to trace in new database, shut it down, edit the
> init.ora with new (old) name, startup nomount and try to recreate the
> control file with "set database "newname" resetlogs" and the datafiles
> from the deleted database added at the end of the create controlfile
> script. The new database is being set to the same name as the deleted
> one, but I am getting "ORA-01159: file is not from same database as
> previous files - wrong database id" errors from the added file(s)
>
> I missed something here, do I need to recreate the new database from
> scratch with the same name as the deleted one? Is it possible to do what
> I am attempting? I thought it should be, and this is the only option I
> have in any case.

TG:

I believe the DBID is written everywhere - controlfiles, datafiles, possibly redos, etc. There's an NID (new ID) utility in 9i that you can use to change the DBID. It also works with 8i. I don't know/remember what, if any, additional functionalitty it might have that could help you either set a specific DBID, or somehow change them all to be identical.

I have to say, I'd be leary of doing what you're trying to do - though I certainly understand why!

It sounds like you had something backed up. You were able to recover most, though not all of the datafiles?

Although it is considered a "not to be done" thing, I have seen a database backed up live via Legato NetWorker withOUT the Oracle BusinessSuite Module restored, opened and recovered and it worked. I was shocked. That was a couple years back.

Charles R. Whealton
Charles Whealton @ pleasedontspam.com Received on Tue Jul 18 2006 - 20:39:30 CDT