thanks for the replies.
Yes, Franck thanks for the great summary.
I'm going swap out the partions with empty dummy partiions and drop the
tablespace.
noarchivelog means they have to accept data loss. looks like internal politics
got in the way of common sense with this particular db.
________________________________
From: Franck Pachot <franck@xxxxxxxxxx>
Sent: May 19, 2021 11:40 PM
To: Mark W. Farnham <mwf@xxxxxxxx>
Cc: ganstadba@xxxxxxxxxxx <ganstadba@xxxxxxxxxxx>; Oracle-L Freelists
<oracle-l@xxxxxxxxxxxxx>
Subject: Re: ora-01113 after incorrect order of moving datafiles
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<mailto: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>
[mailto: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<mailto: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
