This content has been marked as final. Show 4 replies
Can you elaborate a bit on how you moved the datafile?
From what you've told us so far, you did it with the database opened, putting only the tablespace offline and then moving it. How long was the tablespace offline during this whole operation?
Here is what I believe happened:
- You put the tablespace offline while the database was opened. (possibly in immediate mode, thus not applying any dirty blocks to the datafiles)
- DML kept on being processed, and so the SCN kept advancing.
- You try to online the datafile, but the datafile is now on an outdated SCN, not in sync with the controlfile and the other datafiles.
- Controlfile now asks for recover of datafile, but since you are in noarchivelog you cannot bring that datafile to the same SCN as the rest of the database.
In NOARCHIVELOG mode, it is my opinion that you should have moved your datafile by the other method, shutting down cleanly your database, mounting it, and then moving it with alter database rename file command, thus avoiding your current situation.
What you can do is try the:
recover datafile 9;
And then when he asks for the archive, try giving him the complete path and name of the redologs. It's possible that the CHANGE# required to recover the datafile are still in the redo logs. You can get the names of the redo logs in the MEMBER.V$LOGFILE .
If that doesn't work, then, oracle needs info from redo logs that have already been re-written and not archived (due to noarchivelog). In that case you have the option of 1)restoring last consistent backup (loss of data) or 2) force the database open inconsistently and then doing a full export/import. That should not be done without assistance from Oracle SUpport.
I recommend you read MOSC : How To Recover NOARCHIVELOG DB Using The Online Redo Log [ID 186137.1]
Hope it helps,
How did you set the tablespace Offline ? Did you correctly use "OFFLINE" or "OFFLINE NORMAL" or did you happen to use "OFFLINE IMMEDIATE" ? An Offline Immediate would not have caused a Checkpoint and would require Recovery when attempting to bring the tablespace/datafile online.
You may be able to use an online redo log as the presumed archivelog .
Hemant K Chitale
you might have offline immediate clause during tablespace offline which caused the issue.
now, when it is asking for archivelog during recovery, try to give name of redo log files. if still redolog files are not over written, then there is a chance that your datafile is recovered and tablespace will be online.
if that doesn't work, I afraid if you can do online... :-(
by this, please get importance and enable archivelog mode for your database.
Thanks alvaro, hemanth, pavan for the inputs
We did not did offline immediate and we used just offline and we can not give redolog file as it asking log sequence number which some what older
alter tablespace <name> offline;
mv source destination
actually moving the file took some time then we did
alter database rename file <source> to <destination>;
then while doing
alter tablespace <name> online; we got the error ORA-01113 and while doing recovery it is asking the archivelog file which is few sequence number older and as db in no archivelog mode redo logs are overwriten
and this database is development so we droped the tablespace and recreated and did refresh from production now it is working fine