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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Michael McMullen
> *Sent:* Wednesday, May 19, 2021 9:31 AM
> *To:* oracle-l_at_freelists.org
> *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
>
>
>

--
http://www.freelists.org/webpage/oracle-l