Last week, we had an application-level migration rehearsal on a billing test database. There would be huge changes in tables. Unfortunately, there was no rollback plan on the application side. So we decided to use creating a restore point before migration. If migration failed we would back to this point. Before we set db_recovery_file_dest,db_recovery_file_dest_size parameter.
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
Summary:
Be careful when you use restore point to sure that all tablespaceses are on flashback mode.