Alejandro Vargas' Blog

  • April 30, 2007

Rman Behaviour with Transported Tablespaces Plugged -In Read-Only

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

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>'

This problem has been described as affecting read only plugged in tablespaces. Rman silently excludes them from any backupset.

Workaround is implemented following these steps:
  1. Offline drop the datafiles comprising the plugged in read only tablespaces,
  2. Backup controlfile to trace, the script will exclude the readonly datafiles,
  3. Recreate the controlfile,
  4. Drop the tablespace of the offlined datafiles.
You may online these tablespaces and backup them using backup as copy, and then offline them again, if you need them to be offline.

Join the discussion

Comments ( 1 )
  • Ravi Thursday, May 21, 2009
    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.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.