Skip to Main Content
  • Questions
  • How many Not guaranteed Restore Points can I use at given point

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bernie.

Asked: February 04, 2020 - 10:04 am UTC

Answered by: Chris Saxon - Last updated: February 04, 2020 - 2:32 pm UTC

Category: Database Administration - Version: 12.2

Viewed 100+ times

You Asked

Hello!
We have a development database where we won't do any backup of the database. Our method to recreate it will be rman duplicate from the production one.
But we want to give the option to our development team to do a flashback of that database by using Restore Points to a previos time. Our idea is to create one Restore Point Not Guaranteed every night. The idea of creating the Restore Points as Not guaranteed is to avoid the database go in Halt because one day they do a lot of activity in the database and the FRA gets full.
We don't wont to keep flashback logs either, so we are just giving the option to go back to N days (to N restore points), but as I said before we don't guarantee how many days. Depending on the activity those days could be 20, 4 or none.
In this situation is there a query that I can execute at any given time, which gives me the oldest Restore Point I could use at that time to flashback the database?
To make mayself clear, if our procediment to create not guaranteed restore points also deletes the restore points older than 30 days, is there any way to know which one of those 30 restore points is the oldest one I could flashback the database to? Because maybe the info nedded to flashback to 15 days ago aged out of the FRA.
Thanks in advance

and we said...

You can find the oldest point you can restore to by querying V$FLASHBACK_DATABASE_LOG.

Then compare this to the SCNs/times for the restore points from V$RESTORE_POINT.

https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-performing-flashback-dbpitr.html#GUID-3EB4D790-77C7-49CB-8D96-0940394E6BC8

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.