Friday, January 11, 2013

Flashback database fails with read-only tablespace

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 11.2.0.3 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;
For testing databases it is probably no big deal if you set all the tablespaces read only. If you try to set the tablespaces read-write after you have done some changes to the database you want to revert (i.e. testing) the flashback operation will probably fail, possibly with an ORA-600 error, but I have not tested it. In other words, the workaround must be in place before you create the restore point.

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.