Restore PoInt and Tablespace Level Flashback

Last week, we had a application level migration rehearsal on billing test database. There would be huge changes on tables. Unfortunately there was no rollback plan on application side. So we decided to use creating 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 script on database and after 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 diskgroup 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 diskgroup.
Sat Jan 28 15:53:55 2012
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger 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

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

Conclusion:

Be careful when you use restore point to sure that all tablespaceses are on flashback mode.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir