Oracle Flashback Guaranteed Restore Point Misunderstanding

Oracle Flashback Guaranteed Restore Point Misunderstanding

Recently I had a conversation with a DBA that I work with regarding Guaranteed Restore Points (GRP) in Oracle 11gR2. They had the understanding that if a GRP was created regardless if the database flashback feature was on or off then it would only be created with only the flashback logs necessary to flashback or rewind the database back to the GRP point in time. In other words the flashback logs would never grow in size for a GRP since they did not believe those would be needed by Oracle. This conversation came up when they had to wait several minutes to drop a GRP in the database, they did not expect it to take so long since there should only be a limited amount of flashback logs created for the GRP that would need to be dropped.In reality their understanding was not correct at all and here is the reason why, please see below the supporting information for this.

For example if you create a new GRP in your database then the view v$restore_point will be populated as follows, please note the STORAGE_SIZE column which I rounded up to Megabytes(MB) and in this case a new GRP is only 6MB.

Let’s take a look at another GRP that has existed for some time in another database, please note I rounded the STORAGE_SIZE column to Gigabytes(GB) and in this case the GRP is 75GB in size. This shows and proves that when a GRP is created it is small but as activity runs on the database it will grow and in fact ALL flashback logs will be retained since the GRP is created up until it is finally dropped.

In the database alert log you will see the following output when dropping a GRP with lots of activity since it was created. You will see the message “Deleted Oracle managed file” repeatedly.

Fri Aug 16 11:21:17 2013

Drop guaranteed restore point BEFORE_RELEASE

Deleted Oracle managed file +RECO_EXAD/odssit/flashback/log_1.5780.819737031

Deleted Oracle managed file +RECO_EXAD/odssit/flashback/log_2.42344.819737033

Deleted Oracle managed file +RECO_EXAD/odssit/flashback/log_3.5651.819737037

Deleted Oracle managed file +RECO_EXAD/odssit/flashback/log_4.5322.819737041

Deleted Oracle managed file +RECO_EXAD/odssit/flashback/log_5.51038.819737285

…

Also per the Oracle Support Documentation which also states all flashback logs will be kept to satisfy the restore point.

“If you enable Flashback Database and define one or more guaranteed restore points, then the database performs normal flashback logging. In this case, the recovery area retains the flashback logs required to flash back to any arbitrary time between the present and the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure if they are required to satisfy the guarantee.”