This content has been marked as final. Show 17 replies
You have to restore this datafile from a backup, and then recover it.
RMAN> RESTORE DATAFILE N;
RMAN> RECOVER DATAFILE N;
Thank you! We do not have RMAN setup. We only have manual scripts for hot backups that are written to tape.
Restore is done by whatever OS utility you use (cp / tar / cpio / NetBackup etc)
Then, use SQLPlus AS SYSDBA to issue the RECOVER command
RECOVER DATAFILE n;
The scenario here is, one of our previous DBA's had added a datafile to one of the data tablespaces with a index datafile name and realized immediately that the naming convention was wrong and changed the size of the datafile to 0 bytes through OEM and then tried to bring it offline. This is how the datafile had gone into a recover mode. We do not have any backups of this datafile for us to recover. What is the best possible way to deal with this issue.
I would definitely contact Oracle support for something like this on my production database.
Here are some ideas, but some will require some production downtime to complete.
1) Export everything that is in the tablespace that contains the datafile. Drop the tablespace including contents, create the tablespace and import everything back in......Not a good solution but it is a solution...of course there are a lot of things to consider like triggers, foreign keys and similar. This should be tested a lot.
2) Create logical standby with corrected structure and switch to logical standby for production when complete. This will take a lot of work and testing on a test system.
3) Another possibility would be to move tables within this tablespace to a new tablespace and then drop the old tablespace. (Performance hit but safe and no down time).
4) If indexes then drop and recreate new indexes (Performance hit but safe and no down time.)
Once you get everything cleared out of the tablespace you should be able to drop it including contents and storage.
Of course I would try any thing I do out on a test environment before doing anything to my production environment.
If the datafile was "OFFLINE"d properly, you should be able to issue a RECOVER DATAFILE and then bring it ONLINE again. Oracle should detect that no archivelogs need to be applied to it.
If the DBA had done an "ALTER DATABASE DATAFILE .. OFFLINE FOR DROP" and the Database was in NOARCHIVELOG mode, then you can only drop the tablespace. However, as your Database would have been in ARCHIVELOG mode, Oracle would have ignored the "FOR DROP" and done a normal OFFLINE.
Have you attempted to issue the RECOVER DATAFILE command ?
Did he really resize the file to 0 (zero) bytes ? Oracle allowed that ?
The file should be a few (probably 2 or 4 Oracle Blocks) KBs in size, at the minimum.
In any case, have you attempted a RECOVER DATAFILE command (without having to do a RESTORE) ?
I am not sure about your statement "Oracle should detect that no archiuvelogs need to be applied to it."?
How would Oracle know that it did not need to apply archivelogs unless it actually looked at the archivelogs for changes. To the best of my knowledge and experience the "OFFLINE"d file will have a file header that contains that last SCN applied to that datafile. When Oracle goes to RECOVER that datafile it will have to travel through each archivelog file checking for updates to that datafile until it gets to the current SCN.
If you have experience or documentation to the opposite I would like to hear about it.
We did attempt to do a recover data file, Oracle asks for archive log to be applied from 11/20/08 when this had happened. When we just do a CANCEL without applying the archive log, media recovery is canceled. The data file shows as 0 bytes from OEM.
This is the output from v$datafile for that file:
Output from dba_data_files:
and none of the space related fields in the output from dba_data_files table are populated for this file.
Output from v$recover_file:
So you now have to bring the file ONLINE.
ALTER DATABASE DATAFILE fileid ONLINE;
Edited by: Hemant K Chitale on Apr 23, 2009 9:53 AM
I tried getting it online and this is the error it throws:
SQL> alter database datafile 586 online;
alter database datafile 586 online
ERROR at line 1:
ORA-01113: file 586 needs media recovery
ORA-01110: data file 586: '/u25/oradata/prod11i/xkbx06.dbf'
Oracle requires the first archivelog after the file went offline. It does NOT require all the archivelogs since then.
See the example I had posted earlier
Very clearly explained. We will try to implement this by getting the archive log from 20th November when this datafile was taken offline.