Starting Control File and SPFILE Autobackup at 05-NOV-11piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766409773_7c9czltz_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 05-NOV-11

However, the table that has data inserted by regular DML, using a normal INSERT has been recovered !

The RECOVER was able to read from the Online Redo Logs.

This case also shows how I did not have to do a Full Database Backup or a Full Database Restore+Recover. The single Tablespace was recovered to a consistent point in time with the rest of the database as evidenced below :

4 comments:

Maybe I'm just not getting it, but this looks to me like it is showing that the ctas is not converted to nologging. It just simply isn't recoverable if you go beyond what is in redo, since you can't dependably do media recovery in noarchivelog mode. Even though the log says you are doing media recovery and you are actually doing media recovery. What you are really doing is simulating a device going offline and online, then crash recovery. This is because db writing is asynchronous, and has little to do with what the db thinks is committed - that is entirely redo, and the datafile is always fuzzy unless shutdown. Copying in a non-fuzzy data file just hides the issue, as though you could write to a data file that has never come up.

I took the Tablespace OFFLINE. That Checkpoints the file, closes it and leaves it non-fuzzy. I then used RMAN RESTORE -- which does a clean restore (and should raise an error if the target file isn't closed). BTW, that CTAS in NOARCHIVELOG is a NOLOGGING operation has been documented and noted. Once I get those references, I will post them. Also note how the normal table is readable (although I understand that your observations about async, fuzzy and file copy might explain that table ; yet these don't apply because I used OFFLINE and RESTORE).