SEO

#1 (**permalink**) 02-24-2008, 10:56 AM

The scenario is
I have deleted a datafile belonging to a Tablespace say A using the command

ALTER DATABASE DATAFILE 'a1.dbf' offline drop;

The datafile exists in the same location .Its not physically deleted from the disk.

Now i want to add the datafile again back to the Tablespace.

What do i need to do

--
Message posted via http://www.oraclemonster.com

#2 (**permalink**) 02-24-2008, 10:56 AM

Hmm...it sounds like your database is in noarchivelog mode. Maybe you
would prefer to have the database in ARCHIVELOG mode if you are going
to start dropping datafiles

It sounds like a complete recovery is
impossible, unless that datafile did not contain any useful data.

Options:

1) If the file did not have any useful data in it, you can just add the
file back by using the alter tablespace <name> reuse 'a1.dbf'. But you
won't get any data in the file back again.

2) If the file had useful data in, you can either restore the database
from a previous backup (but obviously lose all changes between the full
backup and the current version)..

3) ..or you can restore the database to a new location, drop the
tablespace that owned the 'a1.dbf', and transport a copy of the
tablespace from the restored version. Search the manuals for TSPITR

#3 (**permalink**) 02-24-2008, 10:56 AM

You can reuse the physical datafile but you can not salvage the
contents. The alter database drop datafile action is a permanent
action.

If you are trying to add the file back to the same tablespace you will
need to drop the entire tablespace including contents prior to adding
the file to the tablespace.

alter tablespace
add datafile 'xxxxx' size 1024M reuse;

But this will reformat the file so the contents are toast.

You should not issue any command to alter the physical database until
you have researched its effects.

HTH -- Mark D Powell --

#4 (**permalink**) 02-24-2008, 10:57 AM

"Mark D Powell" <Mark.Powell@eds.com> wrote:

>You can reuse the physical datafile but you can not salvage the
>contents. The alter database drop datafile action is a permanent
>action.
>
>If you are trying to add the file back to the same tablespace you will
>need to drop the entire tablespace including contents prior to adding
>the file to the tablespace.
>
>alter tablespace
>add datafile 'xxxxx' size 1024M reuse;
>
>But this will reformat the file so the contents are toast.
>
>You should not issue any command to alter the physical database until
>you have researched its effects.
>
>HTH -- Mark D Powell --

You can add new datafiles without dropping a tablespace..

Alter tablespace <tablespacename> add datafile <somefilename.dbf> 1024M

Do not attempt to reuse a precreated datafile ( like the one you have) - it has no useful data in it, so delete it and create
a new one for added space.

#5 (**permalink**) 02-24-2008, 11:00 AM

Turkbear, normally you can add a new datafile to a tablespace however
in the thread we are talking about a tablespace that has had a file
removed from it via the alter database drop datafile command. We had
to do this in the past but I think we removed all the files associated
with the tablespace so we may not have had a choice. But because the
dictionary will have references to the objects stored in the file I
believe dropping the tablespace including contents will be necessary to
clean up the dictionary entries and bring the database to a consistent
view.

HTH -- Mark D Powell --

#6 (**permalink**) 02-24-2008, 11:00 AM

"Mark D Powell" <Mark.Powell@eds.com> wrote:

>Turkbear, normally you can add a new datafile to a tablespace however
>in the thread we are talking about a tablespace that has had a file
>removed from it via the alter database drop datafile command. We had
>to do this in the past but I think we removed all the files associated
>with the tablespace so we may not have had a choice. But because the
>dictionary will have references to the objects stored in the file I
>believe dropping the tablespace including contents will be necessary to
>clean up the dictionary entries and bring the database to a consistent
>view.
>
>HTH -- Mark D Powell

I believe that dropping the datafile that way (as opposed to an someone deleting a file through the Operating System ) should
have maintained the dictionary entries regarding that tablespace correctly..No sure though, since I have never have tried
that particular action.

#7 (**permalink**) 02-24-2008, 11:02 AM

Turkbear, I have done this and the dictionary information of the
objects in the dropped datafile were still there. In fact we counted
on Oracle to behave that way since the tablespaces in question were all
index tablespaces. We dropped the datafiles, told Oracle to recover,
it did since all data tablespace datafiles were current, then we
generated the index source from the dictionary, dropped the tablespaces
including contents, recreated them, and recreated the indexes.

We only went this route becuase we had a corrected archived redo lot
and could not forward recover past the corruption. Now days Oracle
provides the ability to skip the corruption and continue the recovery.

HTH -- Mark D Powell --

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to add a deleted datafile again