Michael,
So to summarize you have an offline tablespace that is consistent as of a
past point in time (because alter tablepace does a checkpoint) and you know
you didn't have changes related to it (because it is an old partition). But
Oracle doesn't know that without going through the redo stream (because
controlfile says it has been onlined since then), which is not available
because the database is in noarchivelog.
I don't think there is a simple way to do that because you have no way to
get SYSTEM (and SYSAUX and UNDO) at the same checkpoint time as this file,
so oracle will never accept to open it.
Probably you can do this with "_allow_resetlogs_corruption" on a copy (with
your datafile and SYSTEM SYSAUX UNDO ones from a backup nearby) and get
data from your sandbox with a select from ... partition. transportable
tablespace will not work as it is not self-contained (or play with exchange
partitions maybe)
Those are just guesses, of course, but it can be fun to try (only if you
have spare time).
Franck.
On Wed, May 19, 2021 at 6:27 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
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
