RE: Recovery of offline datafile

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Thu, 6 Nov 2008 01:16:22 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F07C87D98@AAPQMAILBX02V.proque.st>

Ok, so, don't use export/import.

As I said previously, move the tables to a new tablespace and rebuild the indexes.

Here's a bit more detail:
1.) create tablespace your_new_tablespace .....; 2.) for each table in the problem tablespace, do

            alter table table_name move nologging tablespace your_new_tablespace; 3.) for each index in the problem tablespace, do

            alter index index_name rebuild nologging tablespace your_new_tablespace;

Then drop the problem tablespace.

Only tables, indexes, and clusters are segments. Objects such as views, synonyms, and stored procedures only exist in the data dictionary, and will not have any components in any user or application tablespaces.

Hope that helps,

-Mark

---

From: md najeeb [mdnajeeb_1634_at_yahoo.co.in] Sent: Thursday, November 06, 2008 12:56 AM To: Bobak, Mark
Subject: RE: Recovery of offline datafile

Hi,

      Thanks for your reply, the file has made offline 8 months ago.

I have two tablespaces FINDATA and OPERA_DATA with 4 and 7 datafiles respectively.

One datafile of FINDATA and one datafile of OPERA_DATA has made offline in march 2008

and thus these two datafile's needs media recovery.these are newly added datafile and i

think it has no data in it and the archives for the month of march has been deleted. so

how can i recover those two files.

                           If i export and import the data, then what about  the  indexes, views, synonyms and other database objects.Do all those objects will become invalid.Plz suggest me.

Thanks & Regards

• On Tue, 4/11/08, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote: From: Bobak, Mark <Mark.Bobak_at_proquest.com> Subject: RE: Recovery of offline datafile To: "mdnajeeb_1634_at_yahoo.co.in" <mdnajeeb_1634_at_yahoo.co.in>, "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Date: Tuesday, 4 November, 2008, 9:03 PM

Well, first, try a ‘recover datafile ‘/path/to/offline/datafile’

It’s possible, depending on how long the file has been offline, that you’ll recover it from just the on-line redo. (Perhaps not likely, but it’s worth a try.)

If recovery is not possible, the next best option is to drop the tablespace containing the file. If there’s data in that tablespace that you want to retain, you could create a new tablespace, and then move any tables out of the problem tablespace into a new tablespace, and rebuild indexes into the new tablespace, then drop the problem tablespace.

Those are the most simple and straightforward solutions.

Will either one work for you?

-Mark

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com>
www.proquest.com<http://www.proquest.com/>
www.csa.com<http://www.csa.com/>

ProQuest...Start here.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of md najeeb
Sent: Tuesday, November 04, 2008 8:35 AM
To: oracle-l_at_freelists.org
Subject: Recovery of offline datafile




HI,
Iam Mohammed Najeeb working in saudia arabia for al-haif company I have oracle 10g installed and the issue i want to discuss is accidentally one of my datafile belonging to
opera_data tablespace has made offline.and when i try to make it online, i received error message that the file needs media recovery and i lost my archive log files.and that
datafile is not much important for me.just i want to get out of error and want to drop the datafile or recover the datfile.
         but the other datafiles are important and my database is open.so plz suggest me what i should do to recover or to drop the datafile with no archives available.

Thanks & Regards
Mohmmed Najeeb




________________________________

Bollywood news, movie reviews, film trailers and more! Click here.<http://in.rd.yahoo.com/tagline_movies_1/*http:/in.movies.yahoo.com/?wm=n/>




________________________________

Add more friends to your messenger and enjoy! Invite them now.<http://in.rd.yahoo.com/tagline_messenger_6/*http:/messenger.yahoo.com/invite/>




________________________________

Add more friends to your messenger and enjoy! Invite them now.<http://in.rd.yahoo.com/tagline_messenger_6/*http://messenger.yahoo.com/invite/>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 06 2008 - 00:16:22 CST