RE: ora-01113 after incorrect order of moving datafiles

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