Restore point and tablespace level flashback

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.

What do you think?

Related articles

Contact us

Partner with Us for Comprehensive IT

Our expertise is available to address your concerns and work together to identify the optimal services for your needs.

Your benefits:
What happens next?
1

We Schedule a call at your convenience 

2

We do a discovery and consulting meting 

3

We prepare a proposal 

Schedule a Free Consultation