If the datafile is from an index only tablespace, why not just drop the tablespace and rebuild the indexes. No need for recovery. If there are primary/unique key constraint related indexes, don't allow access to make sure that there are not duplicate rows inserted. It likely means less downtime than a recovery.
IMHO, this type of situation is one good reason to separate non-constraint indexes from tables and constraint enforcing indexes. May make backup and recovery easier (the operative word is MAY).
Filenames must be unique including their paths. If your backup procedures only see the filename, you have an issue (as has been pointed out). You really need to fix them...or you may need DUDE.
-- Daniel Fink OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Lost Data? http://www.ora600.be/ Shastry(DBA) wrote:
Thanks everyone for all your excellent support an ideas.There was a request from DEV team to restore and recover the previous requested backup and that has become useless due to overwritten of the same index datafile. Also the production backup was also corrupted and fortunately we were safe as we noticed in prior. As Jared said, we will look into the script and put a check if that particular database has duplicate datafiles on different mount points while backup is running and alert back us. For now the issue is resolved as dev team were ok in having the latest backups clone. Thanks again for all your kind support and suggestion. Regards,Ann
