Re: Is it possible to remove temporary added datafile from tablespace ?

Thank you for clearing it up (I'm kinda slow). The company I'm consulting with has the exact same problem. The DBA created a data file and then removed it. They are in NOARCHIVELOG mode and are doing cold backups every night. You still can backup the database (Cold) and recover it, when needed. I did not understand your reasoning for not being able to do backups on the Tablespace. Are you running in ARCHIVELOG mode and were reffering to Hot backups? But you said you did an OFFLINE DROP, which only is needed in NOARCHIEVELOG mode.

What I would Do, Assuming no backup of datafile: 1. Creat a Testing enviroment on another machine, and restore from the last full backup. 2. see if thier are any objects on that Offline datafile. They need to be recreated (if possible). It seems you either have no objects in the datafile or objects that are of no grave importance to your database. 3. Create a New Tablespace to take over the damaged one. 4. Export all the objects of the old tablespace's datafiles and import them to the new tablespace. (Easier said than done, I know) 5. Issue the following command to the Damaged Tablespace: DROP TABLESPACE tablespacename INCLUDING CONTENTS.

By the way, the company I'm consulting are doing nothing, thier philosophy..."If the users aren't complaining, no need to correct the problem". In thier case, part of a table resides on the offline datafile, but apparently it doesn't affect thier business needs...as of yet.

Satar
Oracle DBA/UNIX System Admin
Advanced Enterprise Solutions
(949) 756-0588
Oracle Re-Seller

In article <3651f61b.253637241_at_isappp>,   gedau_at_isa.mim.com.au (George Dau) wrote:
>
> No, it's a normal tablespace containing tables. I added an extra data file to
> the tablespace, then deleted this new data file, leaving the tablespace and
the
> tables in it still functioning.
>
> The database could not be shutdown because of the missing datafile; so I did
an
> offline drop to attempt to get rid of the data file.
>
> The file IS now offline, but still in the list of files; it is not "dropped".
> Here is a bit of "backup controfile to trace".....
>
> '/u05/oracle/ip3n/data/m3013n_tabmsf1.add3.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf8.add1.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf3.add1.dbf',
> '/u04/oracle/it3n/data/m3013n_tabmsf4.add1.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf8.add0.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf1.add3.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf7.add0.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf7.add1.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/usr1ip3n.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf6.add4.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf7.add3.dbf',
> '/u05/oracle/ip3n/data/m3013n_tabmsf7.add1.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf4.add2.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf4.add3.dbf',
> '/vol-raidB/oracle/admin/ip3n/dbfiles/m3013n_tabmsf4.add4.dbf',
> '/u05/oracle/ip3n/data/m3013n_idxmsf8.add3.dbf'
> ;
> # Take files offline to match current control file.
> ALTER DATABASE DATAFILE '/u04/oracle/it3n/data/m3013n_tabmsf4.add1.dbf'
OFFLINE;
> # Recovery is required if any of the datafiles are restored backups,
>
> Note that the /u04/.....tabmsf4.add1.dbf file is still listed as a file, but
is
> then taken OFFINE. It wont drop.
>
> This means that I can't put the rest of the tablespace in backup mode, since
> this file is OFFLINE. The file doesn't phisically exist any more, and the
> database does shutdown and startup just fine; only thing is I cant backup this
> tablespace because of a file I don't want.
>
> Fortunatly this is more of a curiosity than a real problem in this case; but
how
> would you handle it with important data that needed to be backed up?
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Nov 18 1998 - 14:07:20 CST