Flashback – Part 6 (Flashback Database)

Hi people,

We’re almost there to finish this Flashback Tutorial 🙂

To check the previous posts, please go through:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 7 (Flashback Data Archive)

Today’s post is gonna be about Flashback Database, a pretty good feature for non-production levels of your structure, I would say.

It’s very unlikely that you are going to rollback your entire production database to a point-in-time in the past, right? But if you need to, this facility is there.

Why do I say that it is great for non-production environments?

For example, I have my DEV/TEST database and I know that my database is running perfectly fine now, then as a test measure, I change a lot of things and end up messing up with the database, affecting a lot of ends. Then, as magic, you can move back your ENTIRE DATABASE with Flashback Database to point in the past where everything were fine.

Different from all other flashback operations, Flashback database is not undo-based, it has his own Flashback Logs, that are used to perform these operations. We can see how far we can go back by querying the V$FLASHBACK_DATABASE_LOG view, columns OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME.

To make sure that you can perform Flashback Database operations, please make sure that you have enabled the Flashback, as:

SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES

If it is set to NO, we need to do the following:

• Shutdown the database
• Startup Mount:
• run:

alter database flashback on;

• Open the database.

Using the FLASHBACK DATABASE operation:

To use flashback operations, make sure that your database is in MOUNT mode, otherwise you won’t be able to do so.

Once your database is properly setup for flashback database operations, we have 3 ways to perform this:

• SCN
• Timestamp
• Restore Point

The first two, you must be already familiar, you can go back to a specific past SCN or a Time in the past using Timestamp.  The commands to execute it, follows the following syntax:

FLASHBACK DATABASE TO SCN 73834;

FLASHBACK DATABASE TO TIME "TO_DATE('09/20/05','MM/DD/YY')";

Executing this, you are rolling back your whole database to the point in time defined.

Then you have the Restore Point feature, which is nothing more than YOU, manually, mark the database at some point, and then turn back to this point. The good part here, is that you can name this point-in-time as your preference.

Let’s do an example:

CREATE RESTORE POINT BEFORE_CHANGES;

The name of our restore point is BEFORE_CHANGES, but it can be named as your preference. Thinking about our first example for non-production databases, we can use just like we said:

• Create the restore point
• Perform all the changes that you need to do
• Go back in time with the whole database using the restore point created.

To perform the recovery using the Restore Point, you must have your database in MOUNT mode. Once you have it, you are going to need to execute:

FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';

When the Database finish the Flashback Operation, you will need to open the database with RESETLOGS operation:

alter database open resetlogs;

There you go guys, as we could have seen, we have several ways to use the flashback database operation, and it is very useful for a lot of situations. I have just illustrated the most common one (for me).

I hope that it has been a good reading for you guys and not boring.

We have only one flashback type left to publish (flashback data archive), and then we are going to move on to different subjects 🙂

Have a wonderful week everyone!

Cheers,
Rafael.