Basics of Oracle 12c's Flashback Database - dummies

Basics of Oracle 12c’s Flashback Database

By Chris Ruel, Michael Wessler

You could argue that moving the database forward and back with Oracle 12c Recovery Manager with good backups is possible. However, restoring a large database to a previous point is time-consuming and tedious. Also, if you don’t go back far enough, you have to start over from the beginning.

You might want to flash back the database for these reasons:

  • Repeated testing scenarios: Say you have an application that you’re testing in your development environment. Every time you run the application, it changes your data. You want to reset the data to its original values before the next test. Flashback is an excellent tool for this.

  • Logical Data Corruption: Perhaps someone accidentally ran the wrong program in your production environment; you need to return to a point before the mistake occurred. You could do this with a data recovery, but Flashback is quicker and easier.

  • Deployment procedures: Perhaps you’re releasing a new version of your code that updates all sorts of objects in your production schema with both DDL and DML. You can easily roll it back if the application isn’t working properly in target performance parameters.

Flashback Database works by recording extra information that allows you to roll back transactions without doing a full database recovery. Not only that, but it works very quickly. The Flashback Database has these quick features, among others:

  • You can open the database in read only mode to see whether you went back far enough.

  • Not far enough? Quickly roll back farther.

  • Too far? Roll forward again.

You can perform all these tasks with simple commands inside SQL*Plus or Oracle Enterprise Manager. To do them with RMAN, you’re talking multiple full restores and lots of time in between.

How to configure and enable Flashback database

Flashback Database works differently than the Flashback features. With Flashback Database, Oracle stores a file called a flashback log. Flashback logs have the data to roll back blocks to a previous time. Flashback logs are stored in the flash_recovery_area.

Two variables come into play here:

  • How far back do you want to go?

  • How much data is changed in your database within that time period?

The farther back you go and the more changes you have, the more flashback logs you generate. Be sure you have enough space to store those logs, or you won’t be flashing anywhere.

If you’re considering implementing Flashback Database, you may need to enlarge the parameter db_recovery_file_dest_size. How much you enlarge it depends on the two variables: how far back and how much data? If you want a good starting point, use this formula:

New Flash Recovery Area Size = Current Flash Recovery Area Size + Total Database Size × 0.3

In essence, you’re trying to reserve roughly 30 percent of your total database size in the Flash Recovery Area for flashback logs.

From then on, you can monitor how much space the flashback logs are consuming.

After you configure the Flash Recovery Area, turn on the Flashback feature in the database by following these steps:

  1. Consider how far back you want to be able to flash back.

    The default value is 24 hours (or 1,440 minutes). Say you want to be able to flash back up to 48 hours.

  2. Configure how far back you want to go with the parameter db_flashback_retention_target; to do so, log in to SQL as SYSDBA and type

    <alter system set db_flashback_retention_target =2880;>

    In this example, the time is set for 2,880 minutes (48 hours).

    You should see the following for any amount of time you choose.

    System altered.
  3. Shut down your database and restart it in mount mode.

  4. Put the database in flashback mode by typing this:

    <alter database flashback on;>

    You should see this:

    Database altered.
  5. Open the database by typing this:

    <alter database open;>

    You should see this:

    Database altered.

    Now that the database is in flashback mode, you can flash back to any time within your Flashback window.

How to roll your Oracle 12c database back

When the database must be flashed back, don’t worry. The process is relatively easy.

Flashing back a database removes any change that occurred after the point in time chosen to return. Don’t take this consideration lightly.

To see how far back you can go, type this:

< select oldest_flashback_time
from v$flashback_database_log;>

You should see something like this:

14-AUG-2013 06:34:03

db_flashback_retention_target should be about the limit of that time frame. You may find it to be longer if space isn’t a concern and the database hasn’t yet purged old flashback logs.

Say a user accidentally dropped the HR schema from your database about an hour ago.

  1. Shut down your database.

  2. Restart it in mount mode.

  3. Type the following, where 1 is the number of hours you want to flash back:

    < flashback database to timestamp sysdate - 1/24;>

    You should see this:

    Flashback complete.
  4. Check the flashback before making it permanent:

    < alter database open read only;>

    You should see this:

    Database altered.
  5. If you’re satisfied with the result, go to Step 6. If you’re not satisfied with the time, skip to Step 9.

  6. Shut down the database.

  7. Start the database in mount mode.

  8. Open the database with Resetlogs:

    < alter database open resetlogs;>

    You should see this:

    Database altered.
  9. Restart the database in mount mode.

  10. Type the following:

    <recover database;>

    You should see this:

    Media recovery complete.
  11. Start your database in mount mode.

    If you want to flash back to a timestamp, go to Step 12. If you want to flash back to a previously created restore point, go to Step 13.

    For more on restore points, see the nearby “Using restore points” sidebar.

  12. Type the following:

    <flashback database to timestamp
    to_timestamp('14-AUG-2013 13:00:00','DD-MON-YYYY HH24:MI:SS');>

    You should see this:

    Flashback complete.
  13. Type the following if you want to flash back to a restore point:

    <flashback database to restore point pre_deploy_15AUG2013;>

    You should see this:

Flashback complete.