If the system is Oracle 10.2.0.5 then doesn't this version support dropping
datafiles with no objects assigned to them from a tablespace without
requiring recovery? Thaw was a new feature with version 10g. Check the
SQL and DBA Administration manual for the version.
On Wed, Nov 11, 2020 at 5:28 AM Frits Hoogland <frits.hoogland@xxxxxxxxx>
wrote:
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
frits.hoogland@xxxxxxxxx
Mobile: +31 6 14180860
