Re: dropped datafile problem
did you drop the tablespace as well? --- Andrey Bronfin <[EMAIL PROTECTED]> wrote: > Dear gurus ! > > I have an "offline dropped" datafile at my database. The file was > physically > deleted at the OS level after "offline drop". > Before dropping the file , all the objects that had extents allocated > in > that file (from dba_extents) were dropped. > Now , V$datafile shows this file as of size=0 and status = 'RECOVER' > which > is fine. > BUT , file$ show this file as of size 8GB, which is the file size as > it used > to be before being dropped. > Moreover , fet$ shows 8GB as free extents residing at this file . > And , after all , tables get created in that file , i.e. dba_extents > show > extents with FILE_ID of that file ! > When a select statement tries to access a table with extents residing > in > that file , i get an error message , off course. > > So , the question is , how do i make the file's extents disappear > from fet$ > ? > Thanks a lot. > > Andrey. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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 video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: dropped datafile problem
You have gone about a totally wrong way of dropping the datafile. To drop a datafile from a tablespace, u have to export the tablespace by taking a series of user exports in that tablespace, dropping the tablespace, deleting the datafile at the OS level, recreating the tablespace with the proper datafiles and importing back the contents of the tablespace. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 21 January 2002 14:40 To: Multiple recipients of list ORACLE-L Dear gurus ! I have an "offline dropped" datafile at my database. The file was physically deleted at the OS level after "offline drop". Before dropping the file , all the objects that had extents allocated in that file (from dba_extents) were dropped. Now , V$datafile shows this file as of size=0 and status = 'RECOVER' which is fine. BUT , file$ show this file as of size 8GB, which is the file size as it used to be before being dropped. Moreover , fet$ shows 8GB as free extents residing at this file . And , after all , tables get created in that file , i.e. dba_extents show extents with FILE_ID of that file ! When a select statement tries to access a table with extents residing in that file , i get an error message , off course. So , the question is , how do i make the file's extents disappear from fet$ ? Thanks a lot. Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir 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).
RE: dropped datafile problem
nope. The file was physically deleted at the OS level after "offline drop". Thanks DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -Original Message- Sent: 21 January 2002 17:51 To: Andrey Bronfin esoft.com> cc: Subject: RE: dropped datafile problem 21/01/02 15:39 Hi Mike & other gurus! Thanks for your reply. I'm aware of what u have said - i just have no choice , i can't rebuild the tablespace. I just need to prevent Oracle from allocating extents in that "bad" datafile. Thanks! -Original Message- Sent: 21 January 2002 16:51 To: 'Andrey Bronfin'; 'LazyDBA.com Discussion' OFFLINE DROP does not allow the dropping of a datafile. There is no supported way to drop an existing datafile from a tablespace without dropping the entire tablespace. The answer is, you can't without hacking the data dictionary which voids your support. You can try recreating the control file without the datafile mentioned but success with this has been spotty. Mike -Original Message- Sent: Monday, January 21, 2002 9:38 AM To: LazyDBA.com Discussion Dear gurus ! I have an "offline dropped" datafile at my database. The file was physically deleted at the OS level after "offline drop". Before dropping the file , all the objects that had extents allocated in that file (from dba_extents) were dropped. Now , V$datafile shows this file as of size=0 and status = 'RECOVER' which is fine. BUT , file$ show this file as of size 8GB, which is the file size as it used to be before being dropped. Moreover , fet$ shows 8GB as free extents residing at this file . And , after all , tables get created in that file , i.e. dba_extents show extents with FILE_ID of that file ! When a select statement tries to access a table with extents residing in that file , i get an error message , off course. So , the question is , how do i make the file's extents disappear from fet$ ? Thanks a lot. Andrey. Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms: http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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).
RE: dropped datafile problem
no , and i can not do so (it's a more than 1TB tablespace , i can't recreate it = no time frame). thanks ! -Original Message- Sent: 21 January 2002 18:01 To: Multiple recipients of list ORACLE-L did you drop the tablespace as well? --- Andrey Bronfin <[EMAIL PROTECTED]> wrote: > Dear gurus ! > > I have an "offline dropped" datafile at my database. The file was > physically > deleted at the OS level after "offline drop". > Before dropping the file , all the objects that had extents allocated > in > that file (from dba_extents) were dropped. > Now , V$datafile shows this file as of size=0 and status = 'RECOVER' > which > is fine. > BUT , file$ show this file as of size 8GB, which is the file size as > it used > to be before being dropped. > Moreover , fet$ shows 8GB as free extents residing at this file . > And , after all , tables get created in that file , i.e. dba_extents > show > extents with FILE_ID of that file ! > When a select statement tries to access a table with extents residing > in > that file , i get an error message , off course. > > So , the question is , how do i make the file's extents disappear > from fet$ > ? > Thanks a lot. > > Andrey. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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 video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Andrey Bronfin 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).
RE: dropped datafile problem
you HAVE to drop the tablespace. You do not have a consistent database, and your database is a disaster recovery scenario waiting to happen. The question is, why did you drop the datafile without researching the ramifications? --- Andrey Bronfin <[EMAIL PROTECTED]> wrote: > no , and i can not do so (it's a more than 1TB tablespace , i can't > recreate > it = no time frame). > thanks ! > > > > -Original Message- > Sent: 21 January 2002 18:01 > To: Multiple recipients of list ORACLE-L > > > did you drop the tablespace as well? > > > --- Andrey Bronfin <[EMAIL PROTECTED]> wrote: > > Dear gurus ! > > > > I have an "offline dropped" datafile at my database. The file was > > physically > > deleted at the OS level after "offline drop". > > Before dropping the file , all the objects that had extents > allocated > > in > > that file (from dba_extents) were dropped. > > Now , V$datafile shows this file as of size=0 and status = > 'RECOVER' > > which > > is fine. > > BUT , file$ show this file as of size 8GB, which is the file size > as > > it used > > to be before being dropped. > > Moreover , fet$ shows 8GB as free extents residing at this file . > > And , after all , tables get created in that file , i.e. > dba_extents > > show > > extents with FILE_ID of that file ! > > When a select statement tries to access a table with extents > residing > > in > > that file , i get an error message , off course. > > > > So , the question is , how do i make the file's extents disappear > > from fet$ > > ? > > Thanks a lot. > > > > Andrey. > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Andrey Bronfin > > 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 video emails in Yahoo! Mail! > http://promo.yahoo.com/videomail/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > 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: Andrey Bronfin > 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 video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: dropped datafile problem
Andrey,
I agree with Rachel. You need to fix this problem immediately. You are one
step short of a complete disaster-recovery scenario.
Your choices are:
Restore the database prior to when you deleted the data file and issued the
"drop datafile" command.
or
Export, drop the tablespace, and import the data to a new tablespace
or
Create a new tablespace, and issue "alter table {tablename} move
{newtablespace}" and "alter index {indexname} rebuild" for all tables in the
old tablespace. When complete, drop the old tablespace.
I would schedule option #3 - by far, the easiest option (if you are running
under 8.1.6 and above).
You need to do one of the above ASAP! You have a tablespace that is in a
bad situation, and there is no other option (unless Oracle support can
perform some magic for you).
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, January 21, 2002 3:35 PM
To: Multiple recipients of list ORACLE-L
you HAVE to drop the tablespace. You do not have a consistent database,
and your database is a disaster recovery scenario waiting to happen.
The question is, why did you drop the datafile without researching the
ramifications?
--- Andrey Bronfin <[EMAIL PROTECTED]> wrote:
> no , and i can not do so (it's a more than 1TB tablespace , i can't
> recreate
> it = no time frame).
> thanks !
>
>
>
> -Original Message-
> Sent: 21 January 2002 18:01
> To: Multiple recipients of list ORACLE-L
>
>
> did you drop the tablespace as well?
>
>
> --- Andrey Bronfin <[EMAIL PROTECTED]> wrote:
> > Dear gurus !
> >
> > I have an "offline dropped" datafile at my database. The file was
> > physically
> > deleted at the OS level after "offline drop".
> > Before dropping the file , all the objects that had extents
> allocated
> > in
> > that file (from dba_extents) were dropped.
> > Now , V$datafile shows this file as of size=0 and status =
> 'RECOVER'
> > which
> > is fine.
> > BUT , file$ show this file as of size 8GB, which is the file size
> as
> > it used
> > to be before being dropped.
> > Moreover , fet$ shows 8GB as free extents residing at this file .
> > And , after all , tables get created in that file , i.e.
> dba_extents
> > show
> > extents with FILE_ID of that file !
> > When a select statement tries to access a table with extents
> residing
> > in
> > that file , i get an error message , off course.
> >
> > So , the question is , how do i make the file's extents disappear
> > from fet$
> > ?
> > Thanks a lot.
> >
> > Andrey.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Andrey Bronfin
> > 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 video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> 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: Andrey Bronfin
> 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 video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.c
RE: dropped datafile problem
ORACLE-L Digest -- Volume 2002, Number 023 > -- > > From: [EMAIL PROTECTED] > Date: Tue, 22 Jan 2002 09:09:23 -0500 > Subject: Re:RE: dropped datafile problem > > While the damage here has already been done, it should be a point of > caution for us all. I make a point of dropping the tablespace in Oracle > first followed by judicious use of the 'fuser' command in HP-UX to see > that all processes have released the file. Now you know it's deletable. > BTW: anyone know of an equivalent to fuser for NT/2000?? (coming in late since I am in digest mode, sorry if this was already answered) Dick, Since (for better or worse) Oracle is threaded on NT, other than the main Oracle process, there aren't any processes to be concerned about are there? regards, ep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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).
