Some months ago I worked on a project where we thought it would be a good idea to use multiple guaranteed restore points to be able to navigate through time back and forth. Sadly we ran into some unexpected behaviour of the Fast Recovery Area.
Prologue.
This was basically a consolidation project. A rather complicated one: many tables had to be read from five other databases and its data needed to be inserted into the corresponding tables in the main database, whilst maintaining all constraints. Special code was written for it and needed to be tested in subsets of tables and/or schemas.
Since each test might take several hours (the whole process taking about 32 hours!) and the tests needed to be done in a certain order, it was worthwhile te create a restore point after a successfull test before testing the next part. That way we could revert each failed attempt within minutes, as opposed to a restore/recover action that would take a few hours.
Conclusion.
This works, but pay attention: the behaviour of the flashback logging was not as we expected it to be. As long as the oldest restore point existed, the flashback logs would neither be deleted nor reused. We ended up with a huge recovery area, piled up with flashback logs. So, if you want to use restore points this way, be sure to have enough disk space at hand.
Examples an proof.
We’ve got a database in a basic state. Flashback is on and the Flashback Retention Time is set to 5 minutes. After all, it’s all about the restore points and not about some random time.
We want to be able to return to this state from any point in the conversion.
SQL> create restore point Zero guarantee flashback database; Restore point created. SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 52428800 2
Next, we create a million rows in a certain table and see what it does to the FRA.
SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 104857600 2
So as expected (or hoped) a new flashback file has been created and the storage needed to satisfy the restore point’s need has doubled.
Now it’s time to flashback to the restore point. I expect the storage need to drop back to 52428800 since, as a result of the flashback, we will be back at scn 1117335 and there is no use for the extra flashback logs.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1135747072 bytes Fixed Size 2227664 bytes Variable Size 335544880 bytes Database Buffers 788529152 bytes Redo Buffers 9445376 bytes Database mounted. SQL> flashback database to restore point zero; Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 104857600 2
Look at the storage size, it hasn’t decreased. Strange? Maybe. But what about the automatic management of the FRA? It might not start deleting obsolete flashback logs if there is enough space available. Keep that in mind, I’ll get back to it later on.
First we’ll add some more data and restore points and investigate the behaviour of flashback.
Add a million rows and then
SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 157286400 2 SQL> create restore point second guarantee flashback database; Restore point created. SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 52428800 2 SECOND 3095533 104857600 3
So, we forced an extra flashback log to be created (which can be concluded from the increased storage size) and then created a second restore point in order to be able to flashback to either this point or restore point Zero. But watch how the storage size of restore point Zero has decreased to its original value and some other has been ‘transferred’ to restore point Second.
Add two million rows, observe the storage needs, flashback to a point before the two million rows were added and re-observe the storage need. Allthough I don’t understand this behaviour, I’m beginning to expect that the need won’t decrease:
SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 52428800 2 SECOND 3095533 157286400 3 ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1135747072 bytes Fixed Size 2227664 bytes Variable Size 335544880 bytes Database Buffers 788529152 bytes Redo Buffers 9445376 bytes Database mounted. SQL> flashback database to restore point second; Flashback complete. SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 52428800 2 SECOND 3095533 157286400 3
This starts to get annoying. I’ll drop that second restore point and flashback to restore point Zero.
SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 209715200 2 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1135747072 bytes Fixed Size 2227664 bytes Variable Size 335544880 bytes Database Buffers 788529152 bytes Redo Buffers 9445376 bytes Database mounted. SQL> flashback database to restore point zero; Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 209715200 2
So, we’ve still got 200MB worth of storage allthough it’s obvious that we don’t need it. We can not flashback to the scn of the former restore point Second, that’s not what flashback is all about. Here’s the proof.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1135747072 bytes Fixed Size 2227664 bytes Variable Size 335544880 bytes Database Buffers 788529152 bytes Redo Buffers 9445376 bytes Database mounted. SQL> flashback database to scn 3095533; flashback database to scn 3095533 * ERROR at line 1: ORA-38743: Time/SCN is in the future of the database.
For those of you that want to issue a flashforward command, be my guest:)
Now there’s only one possible reason that the flashback files are still there and that is that there is plenty of room in the FRA and there is no need to start deleting the logs. After all, flashback logs are managed by the databse and AFAIK there is no way to take over management.
Next I set the size of the FRA to be slighly more then what’s actually needed right now and start adding rows again. And then, sure enough, we hit a well known error.
SQL> select NAME,SCN,STORAGE_SIZE,DATABASE_INCARNATION# from v$restore_point; NAME SCN STORAGE_SIZE DATABASE_INCARNATION# -------- ---------- ------------ --------------------- ZERO 1117335 262144000 2 Unable to allocate flashback log of 1946 blocks from current recovery area of size 4464836608 bytes. Recovery Writer (RVWR) is stuck until more space is available in the recovery area. Unable to write Flashback database log data because the recovery area is full, presence of a guaranteed restore point and no reusable flashback logs.
I made sure that there was enough space, if only obsolete logs had been deleted or reused. Sorry, no proof of that, but trust me.
Now finally I dropped restore point Zero and looked in the alert log.
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c319x8ck_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31bmm6x_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31mjtkf_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rm5mm_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rnlsm_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31ro1wt_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rvnfy_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rvojt_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rx3hb_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rx481_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rycld_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c31rzrtl_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c3212bt3_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c32kclqn_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c33z37lx_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/test03/TEST03/flashback/o1_mf_c35273xp_.flb Guaranteed restore point ZERO dropped
Nice detail: appearantly the restore point is only dropped after all the corresponding flashback logs have been deleted.
Now I have been led to believe that those flashback logs didn’t disappear because they were needed (quoting Oracle here):
Flashback Database Through Resetlogs
This feature supports flexible data recovery that enables movement of the database back and forth in time even after you perform a resetlogs operation.
Bottom line: As long as you leave at least one guaranteed restore point intact, flashback logs will accumulate. Use it wisely and be sure to have enough storage at hand.
