Can you open the dictionary leaving any incomplete tablespaces offline?
Then check if any of the tablespaces with offline datafiles have user
objects.
IF you're that lucky, just drop those tablespaces including contents. I
suppose I would clone what you have, including controlfiles and so forth and
attempt an alter open rename so you can experiment safely. Remember to clone
the online redologs and control files to a safe location so that you can try
more than once if your first attempt fails.
Good luck. I can't remember if the datafile ready software was updated to
work on 10 in case trying everything reasonable fails.
Obviously if there is actually data you need in the missing datafiles you
are s outta luck. I can't remember if there is a way to open tablespaces
with some datafile offline such that you can read it and just toss an error
if you try to read blocks from offline files.
Finally, if this is one file per tablespace, then you should be able to just
do an offline drop including contents for each tablespace that has no files.
Maybe I should have said that first.
mwf
From: oaktable-bounce@xxxxxxxxxxxxx [mailto:oaktable-bounce@xxxxxxxxxxxxx]
On Behalf Of Frits Hoogland
Sent: Wednesday, November 11, 2020 5:28 AM
To: oaktable@xxxxxxxxxxxxx
Subject: [oaktable] drop offline datafile in Oracle 10.2
Aloha mighty knights of the oaken table,
I got a client that has offlined datafiles in a locally managed tablespace
in an oracle 10.2 database (10.2.0.5, 3 node RAC cluster on Windows 2008R2.
Yes, some guys have all the luck). So far that works. The offlined datafiles
do not exist, and are very probably the result of (rogue) recovery, or utter
clumsiness.
However, because they run the risk of running out of support, they decided
to migrate the database to Oracle 19. (about the risk of support: that was a
joke. but they are going to oracle 19).
The strategy for migration is transportable tablespaces. Transportable
tablespaces require the tablespace to be transported to be made readonly.
Turning a tablespace read only requires all datafiles to be online. And that
conflicts with their situation.
What I find on MOS and the internet is: recreate or restore the datafile and
recover until it was turned offline (which almost guaranteed will not be
possible, because the reason for offlining it was the datafile being
missing, otherwise they would have onlined the datafile theirselves), or
move out the segments and drop the tablespace.
Is this all that can be done?
Frits Hoogland
http://fritshoogland.wordpress.com ;<http://fritshoogland.wordpress.com/>
frits.hoogland@xxxxxxxxx
Mobile: +31 6 14180860
