Depending on what you have available you *may* be able to do an old
fashioned partial database complete recovery followed by getting the results
back into your database via transport.
So you need "old enough",controlfile, system, sysaux, possibly undo, and a
set of archivelogs from "old enough" and a copy of the all the files in the
tablespace "old enough" or younger. If you separate data from indexes, you
only need the data (and be sure to understand that index organized TABLEs
are still data, likewise clusters).
*SOMEWHERE* preferably an isolated host so you don't have to play name
games. Put everything not on the list above offline (or generate a text
version of the control file and create a controlfile having just what you
need.)
Do that recovery, open that database (again being a rename if you are not
"database server isolated" from the current server), make the tablespace
transportable, and transport it.
IF you can't do that, possibly one of the scan Oracle datafiles into
loadable objects companies is still in business, but that is probably even
more difficult today with encryption and whatnot than it was ages ago.
good luck.
Your horror story is an example of why I have always said disk is cheap and
it is excellent insurance. Before doing an operation that is even faintly
dangerous to the integrity of recoverability I recommend building a full
plex of "whatever" at the storage level and securely splitting that plex off
until the operation is complete.
"Always" equates to the time of the ability of Sequent's SVM product to add
plexes beyond two (up to seven if I recall correctly), split them off, and
later "resilver" (using the silly mirror metaphor instead of the more
intelligent "plexes") and reconnect the plex efficiently only scanning for
and changing blocks at the OS level that had changed.
Again, good luck. I hope you have the pieces to avoid losing data.
Doh. And now I see noarchivelog. So, do the above with the last save of the
datafile(s) for the tablespace in question and *Hope* that a checkpoint
completed contemporaneously with the offline. I cannot remember whether an
offline automatically forces a checkpoint and does not default to immediate.
You'll probably need the key words "using backup controlfile" in the
recovery. If complete recovery fails, you may be able to dither out the SCN
of the offline command and recover incompletely (which might be complete) to
that.
IF you added or dropped objects in that tablespace since the system backup,
you're probably screwed, but it's worth a try if the alternative is lost
data.
Third time, good luck.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael McMullen
Sent: Wednesday, May 19, 2021 9:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ora-01113 after incorrect order of moving datafiles
11.2.0.4
asm
zfs
noarchivelog
ASM datafiles get moved to zfs storage for long term storage. DB is in
noarchivelog mode. The datafile is part of a tablespace and contains only
the one datafile. The data is partitioned tables about two months old and
would no longer have inserts/updates/deletes.
A colleague has done the following.
alter tablespace my_tbs offline;
moved datafile using rman to zfs
alter database rename file .... -- at this stage they made a mistake with
naming so the command failed as it should
alter tablespace my_tbs online
alter tablespace my_tbs read write
here they realized they forgot to rename the file so they issued
alter tablespace my_tbs offline -- i believe at this point they should have
redid the whole copy but they didn't
alter database rename file 'my_asm_file' to 'my_zfs_file'
asm happily deleted the file and did the rename.
alter tablespace my_tbs online
then ora-01113 was returned.
they waited a few days to ask me what to do. I don't think there's much to
do except offline drop the datafiles and get rid of the partitions that were
in the tablespace. Online redologs would have cycled by now.
Just thought I would ask to make sure.
yes I know this is a terrible process but it's the process they have to live
with.
Thanks
Mike
