Thanks Deepak, Slight problem though. The database has been opened with RESETLOGS so previous archive logs are useless. v$recover_file reports this file# is offline/missing file. I think my only other option is to drop then rebuild/recreate the tablespace and import the objects in it from an export dump.
Ross > -----Original Message----- > From: Deepak Thapliyal [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 6 February 2002 11:45 > To: Multiple recipients of list ORACLE-L > Subject: Re: Creating a missing datafile > > > Hi Ross, > > have u by any chance restored a backupcontrol file > that was backedup before you added this datafile. > > Is this the case? If yes than what oracle is doing is > putting a placeholder entry for this file and calling > it missing file.. here is what u can do (assumes that > you have all the archivelogs available since this file > was created .. > > restore the db from backup and mount it .. > > SQL> select name from v$datafile where file#=41; > > SQL> ALTER DATABASE > CREATE DATAFILE > 'use the out of above sql' as > '/u2/db/archive2_2.dbf'; > > SQL> recover database using backup controlfile ; > > Use Auto till it asks for non-existent arch (supply ur > online log) > > Media recovery complete after online log is applied > > technically u should be able to open the db in > noresetlogs mode at this time .. however i have seen > that it does not .. so here is the workaround .. > > SQL> alter database backup controlfile to trace; > SQL> create controlf file ..< from ur trace> > SQL> recover database; -- takes one sec to complete > SQL> Alter database open; > > hth > Deepak > --- Ross Collado <[EMAIL PROTECTED]> wrote: > > > > Env. > > HP-UX 10.2 > > Oracle 7.1.6 > > Archivelog mode > > > > > > Found this in the alert.log > > > > Dictionary check beginning > > File #41 found in data dictionary but not in > > controlfile. > > Creating OFFLINE file 'MISSING0041' in the > > controlfile. > > Dictionary check complete > > > > I did a dump of the controlfile to trace and > > checked. It had MISSING0041 in > > it. However, I did a find on this file and couldn't > > find it. Does this > > mean MISSING0041 was just updated in the controlfile > > and was not actually > > created as a physical file? > > I know the complete path/filename of what this > > MISSING0041 datafile should > > be (it should be /u2/db/archive2_2.dbf). This is > > actually the 3rd datafile > > of ARCHIVE tablespace. If this file existed then it > > would have been an easy > > rename of datafile via "ALTER TABLESPACE ... RENAME > > DATAFILE...". But there > > is no physical file to rename. > > I don't think I can do a create a file via "ALTER > > DATABASE CREATE > > DATAFILE..." in this case because the controlfile > > won't have the correct > > path/filename of this datafile (it's got > > 'MISSING0041' SIZE 500M). > > How do I go about recreating a physical file having > > the correct filename and > > size? > > > > TIA, > > > > Ross > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Ross Collado > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California -- Public Internet > > access / Mailing Lists > > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > __________________________________________________ > Do You Yahoo!? > Send FREE Valentine eCards with Yahoo! Greetings! > http://greetings.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deepak Thapliyal > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ross Collado INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
