Restore point and tablespace level flashback

SQL> alter system set db_recovery_file_dest_size=4096G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest=+RECO scope=both;
System altered.

SQL> create restore point before_rpe guarantee flashback database;

Restore point created.

So we started the run many SQL scripts on a database and after the finish, we tried to test rollback scenario.

SQL> shutdown immediate

SQL> startup mount;

SQL> flashback database to restore point before_rpe;

ORA-38753: Cannot flashback data file 314; no flashback log data.
ORA-01110: data file 312: ‘+xxxxDATA00/xxxx/datafile/t_itusers.900.759343549’
ORA-38753: Cannot flashback data file 316; no flashback log data.
ORA-01110: data file 312: ‘+xxxxDATA00/xxxx/datafile/t_itusers.989.759343535’

So it gave the error and it said these datafiles sould be taken offline to run restore.

SQL> alter database datafile ‘+xxxxDATA00/bspr/datafile/t_itusers.900.759343549’ offline;
SQL> alter database datafile ‘+xxxxDATA00/bspr/datafile/t_itusers.989.759343535′ offline;

SQL> flashback database to restore point before_rpe;

Flashback complete.

Elapsed: 00:39:05.84

On alert log:

FLASHBACK DATABASE TO RESTORE POINT BEFORE_RPE
Sat Jan 28 15:14:25 2012
SUCCESS: diskgroup xxxxSSD00 was mounted
Sat Jan 28 15:14:25 2012
NOTE: dependency between database xxxx and disk group resource ora.xxxSSD00.dg is established
Sat Jan 28 15:14:35 2012
Flashback Restore Start
Sat Jan 28 15:22:26 2012
db_recovery_file_dest_size of 4194304 MB is 5.77% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM disk group.
Sat Jan 28 15:53:55 2012
Flashback Restore Complete
Flashback Media Recovery Start
started log merger process
Sat Jan 28 15:54:14 2012
Parallel Media Recovery started with 96 slaves
Sat Jan 28 15:54:14 2012
Warning: Datafile 314 (+xxxxDATA00/xxxx/datafile/t_itusers.900.759343549) is offline during full database recovery and will not be recovered
Warning: Datafile 316 (+xxxxDATA00/xxxx/datafile/t_itusers.989.759343535) is offline during full database recovery and will not be recovered
Sat Jan 28 15:54:14 2012
Flashback Media Recovery Log +RECO/xxxx/archivelog/2012_01_28/thread_1_seq_97550.703.773716497
Sat Jan 28 15:54:15 2012
Incomplete Recovery applied until change 11164480619507 time 01/28/2012 01:08:08
Sat Jan 28 15:54:15 2012
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_RPE

SQL> select name from v$tablespace where flashback_on=’NO’;

NAME
——————————
T_ITUSERS

the interesting point in here; When the database is open mode you can enable/disable database level flashback but only disable in tablespace level.

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

But you can disable tablespace level flashback but cannot enable it. You can do that on mount mode

SQL> alter tablespace T_ITUSERS flashback off;

Tablespace altered.

SQL> alter tablespace T_ITUSERS flashback on;
alter tablespace T_ITUSERS flashback on
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance