The Magic Time Machine | Flashback Database & Guaranteed Restore Points!

“If my calculations are correct, when this baby hits 88 miles per hour, you’re gonna see some serious sh*t.” — Back to the Future, Dr. Emmett Brown, AKA Doc

While we may not need to reach 88 miles per hour for time travel, I find this quote intriguing and the analogy fitting for current situations. Often, we yearn for a magical time travel mechanism to correct our errors and start anew. Brace yourself, as you’re about to embark on an extraordinary journey. Are you prepared for the ride?

As you’ve already gathered from the caption, I’m delving into the world of flashback databases, or more specifically, “Guaranteed Restore Points.”

Drawing inspiration from Mike Dietrich’s invaluable blog, which has served as my guiding light in my initial steps, I’ve ventured into this realm myself. My intention now is to furnish you with a more comprehensive elucidation, or better yet, a hands-on article that allows you to witness the outcomes.

Within this article, I will undertake the following:

1. Requirements for GRP/Flashback Database
2. Querying Restore Points
3. Creating (Guaranteed) Restore Points
4. Querying Flashback Database
5. Enabling/Disabling Flashback Database
6. Navigating Between Two GRPs
7. Considerations When Using GRPs in a RAC Environment

First and foremost, it’s imperative to fulfill certain prerequisites in order to utilize this feature. The good news is that the requirements are quite manageable:

alter system set db_recovery_file_dest_size=XXG;
alter system set db_recovery_file_dest='<your-location>';

Two important hints to keep in mind:

1. Set the size before defining the destination.
2. You can’t configure these settings if LOG_ARCHIVE_DEST_N or LOG_ARCHIVE_DUPLEX is already set. In such cases, clearing the parameter (setting it to ‘’) is sufficient.

Let’s delve into querying restore points:

Given that you can create and maintain multiple restore points, it’s essential to identify which restore points exist within your database.

This serves two purposes: understanding the specific points in time you can revert to and being mindful that guaranteed restore points, as the name implies, are “guaranteed.”

This means Oracle ensures that you can perform a flashback to these points, and this requires space in the Flash Recovery Area (FRA). Don’t forget to clean up old restore points! Here’s a query for retrieving this information:

select NAME, GUARANTEE_FLASHBACK_DATABASE, SCN 
from v$restore_point;

And a crucial reminder: restore points are not equivalent to backups. Oracle enumerates scenarios where a restore point won’t be helpful:

List of Limitations of Flashback (https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/limitations-of-flashback-database.html)

Now i have a guaranteed restore point named GRP1. This GRP, created in a 19.3 environment, serves as my fallback strategy for various scenarios:

1. Mishaps during patching
2. Uncertain behavior following an upgrade in the new release, necessitating a rollback

Creating a guaranteed restore point is a straightforward process:

create restore point -yourname- guarantee flashback database;

At this juncture, Oracle places a high priority on the security of this restore point to facilitate potential returns to this specific point in time.

Flashback Database, as I must clarify, isn’t mandatory for utilizing GRPs. Flashback offers a comprehensive version of the time machine, enabling you to navigate within your database to any point since Flashback Database was activated. You can check its status using:

select flashback_on from v$database;

To toggle Flashback Database on/off:

alter database flashback on/off;

For my purposes, GRPs suffice for specific tasks like patching or upgrades.

Now, the truly exhilarating part: utilizing the time machine!

Should you encounter issues after an upgrade or patching task and wish to return to a prior state, the following steps are taken:

shutdown immediate;
startup mount;
flashback database to restore point <point>;

In my experience, the flashback process is generally swift, even after significant changes to the database.

Two scenarios emerge:

Scenario 1:
You’re in the same environment. No out-of-place patching or changes to ORACLE_HOME occurred.

alter database open resetlogs;

This step suffices to reopen the database. Personally, the “open resetlogs” phase always feels like a pivotal moment, as it signifies a point of no return. However, even after this, you can still return to your initial GRP — quite fantastic, isn’t it?

Scenario 2:
You’ve introduced a new ORACLE_HOME for a fresh version and migrated the database using autoupgrade.
Understanding your environment is crucial at this point.

Hint #1: Always execute the flashback command on the currently running database, not on the intended running location.

Hint #2: Only perform the “open resetlogs” operation on the database location where it should be running after the flashback.

Details of the process are meticulously outlined, including pre-upgrade, post-upgrade, and the steps for returning to each environment.

Finally, a special note for Oracle RAC (Real Application Clusters):

In RAC configurations, two instances access the datafiles. Consequently, you must shut down one instance to leverage this feature. A sequence of commands is provided to facilitate the process.

srvctl stop database -db MYDB
srvctl start instance -i INSTANCE1 -db MYDB -o mount

sqlplus / AS SYSDBA
flashback database to restore point;
alter database open resetlogs;
exit;

srvctl start instance -i INSTANCE2 -db MYDB
srvctl status database -db MYDB

As you conclude this journey, don’t overlook the need to delete your GRPs once your tasks are completed. Your actions have the potential to shape the database’s timeline, akin to the profound words of Dr. Emmett Brown.

Thank you for reading.

exit;