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

satar_at_my-dejanews.com wrote:

]I'm sorry, I do not think I understand the question. You "offline drop" the
]datafile belonging to the Roll Back Segment Tablespace?
]I'm not sure if you can recreate this tablespace, if anyone can answer this
]question, I'd greatly appreciate it.
]According to the Backup and Recovery Guide, It states:
]ORACLE WORLDWIDE SUPPORT FAQ
]Problem: File needs Media Recovery at Startup
]*If the datafile is in the SYSTEM or ROLLBACK Segment Tablespace, you must
]restore the entire database to your most recent full backup. The database will
]now contain data as of the time you took your full backup.

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?

--

 ,-,_|\   George Dau,                                             __
/    * \  Unix (Solaris, DEC Unix, Linux), Oracle, Internet.     (OO)
\_,--\_/  Home: gedau_at_pobox.com      Work: gedau_at_isa.mim.com.au ( \/ )
      v   WWW:  http://pobox.com/~gedau                          W--W

MIM Internet policy item 3.4 compliance - "I am clearly stating that any views or opinions expressed above do * NOT * necessarily represent the views or opinions of MIM." Received on Tue Nov 17 1998 - 16:35:57 CST