Flashback database fails with read-only tablespace

January 11, 2013

flashback, oracle

Flashback database, (active) duplicate with RMAN, and tablespaces that are read-only. You can only have two out of three from my recent testing. One database with a few read-only tablespaces was cloned by using active duplicate in RMAN. Before the testing (using Real Application Testing replay) a restore point with guarantee flashback database was created. After the testing I tried to perform a flashback database, but it failed with:

SQL> flashback database to restore point b4_test;  
flashback database to restore point b4_test  
ERROR at line 1:  
ORA-01122: database file 38 failed verification check  
ORA-01110: data file 38: '+DATA/clonedb/datafile/foo_bar002.443.804434293'  
ORA-01207: file is more recent than control file - old control file  

The file in question belongs to a read-only tablespace and is certainly not newer than the control file. It looks to be a bug on and I found a way to work around it if you take some actions before you start testing. I did not find the same error stack mentioned together with flashback, or anything directly relevant on My Oracle Support, but similar symptoms made me think the problem would go away if I set the read-only tablespaces read-write. I generated SQL with this:```

select ‘alter tablespace ' || tablespace_name || ' read write;’
from dba_tablespaces
where status=‘READ ONLY’;

After I executed the generated statements, I created a new restore point with:

drop restore point b4_test;
create restore point b4_test guarantee flashback database;

I don't know why this happens for databases that are duplicated with RMAN. Flashback database on the source database works OK even with read-only tablespaces. The source database was created from a restore of an RMAN backup taken on a physical standby database. Also the duplication itself works OK with the read-only tablespace. Two out of three, then.