While doing a crash test that involved losing all the content of the ASM Disk Group Data, I noticed that a set of 3 transported tablespaces that was plugged in and left read only, were not part of the backup set.
When trying to execute alter database open resetlogs I've got:
ORA-01135: file n accessed for DML/query is offline
ORA-01110: data file n: '<plugged-in RO tablespace datafile>'
Workaround is implemented following these steps:
- Offline drop the datafiles comprising the plugged in read only tablespaces,
- Backup controlfile to trace, the script will exclude the readonly datafiles,
- Recreate the controlfile,
- Drop the tablespace of the offlined datafiles.
Comments (1)
This issue is not specific to RMAN
We hit this issue when trying to open our BCP environment with transportable tablespace datafiles :
When the plugged-in datafiles are ONLINE: get the following error trying to open resetlogs:
ORA-19729: File 4152 is not the initial version of the plugged in datafile
When the plugged-in datafiles are marked OFFLINE or OFFLINE drop, we get the following error trying to open resetlogs:
ORA-01135: file 4149 accessed for DML/query is offline
ORA-01110: data file 4149:
Metalink shows the following bug:
Bug 8367917 - ORA-1135 in a Recovery with a plugged in offline datafile
Look forward to your alternate ways of recovery especially since we have a DW with about 14K files and recreation of controlfile is not an easy option.
Posted by Ravi | May 21, 2009 7:08 PM
Posted on May 21, 2009 19:08