How to Enable Flashback Database

  • by

Flashback Database

Flashback Database can rewind an entire database back to a state of point-in-time, just before operation failures which involved whole database. The result is the same as database point-in-time recovery (DBPITR) performed by RMAN, but rather simple to operate.

If you want to save a recently truncated table, Flashback Table helps none, but Flashback Database can be an option for you to bring this table back, but other tables will be compromised of losing some data.

In fact, there’re several ways that can recover truncated tables.

Another advantage of Flashback Database is to support fast-start failover to reinstate a standby database (former primary database) automatically, and can get Data Guard back to normal. Of course, some data will be abandoned.

Preconditions

According to Oracle documents, several preconditions would better be satisfied before enabling Flashback Database: Using Flashback Database and Restore Points

I divided them into two groups, one is necessary conditions, they must be satisfied first. The other is sufficient conditions, they can facilitate your database to run Flashback Database more smoothly.

The necessary conditions are:

  1. ARCHIVELOG mode must be enabled.
  2. Fast Recovery Area (FRA) must be enabled.
  3. FRA must be in a shared storage for RAC environments.

The sufficient conditions are:

  1. Sufficient I/O channel capacity.
  2. Same value of DB_FLASHBACK_RETENTION_TARGET on both primary and standby databases.
  3. LOG_BUFFER is at least 8 MB initially.
  1. Check Archivelog Mode:
  2. SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     23
    Next log sequence to archive   24
    Current log sequence           24

    The database is in ARCHIVELOG mode, and use FRA to store log files.

  3. Check FRA:
  4. SQL> show parameter db_recovery_file_dest

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +DATA
    db_recovery_file_dest_size           big integer 20G

    Which means you have enabled FRA.

Enabling Flashback Database

Now, let’s enable Flashback Database:

  1. Set DB_FLASHBACK_RETENTION_TARGET on both primary and standby databases.
  2. SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;

    System altered.

    Set how long (in minutes) the flashback log retained in FRA, but there is no guarantee. If you accept the default value of 1440 minutes, you can skip this step.

  3. Enable Flashback Database on primary database.
  4. SQL> ALTER DATABASE FLASHBACK ON;

    Database altered.

    For enabling flashback on the standby database, you have to make it play primary role temporarily.

  5. Switchover to the standby database.
  6. DGMGRL> switchover to standb
  7. Enable Flashback Database on standby database.
  8. SQL> ALTER DATABASE FLASHBACK ON;

    Database altered.

  9. Switchback to the primary database.
  10. DGMGRL> switchover to primdb
  11. Optional: Disable a specific tablespace to flashback.
  12. SQL> ALTER TABLESPACE EXAMPLE FLASHBACK OFF;

    Tablespace altered.

    To ease the burden of Flashback Database, you can selectively take one or two tablespaces to be disabled.

After enabling Flashback Database, we should check the results:

  1. Check the Status of Flashback Database.
  2. SQL> SELECT flashback_on FROM v$database;

    FLASHBACK_ON
    ------------------
    YES

    The database shows “YES”. Which means we have enabled Flashback Database.

  3. Check Tablespace Flashback Status
  4. SQL> SELECT name, flashback_on FROM v$tablespace;

    NAME                           FLA
    ------------------------------ ---
    SYSTEM                         YES
    SYSAUX                         YES
    UNDOTBS1                       YES
    USERS                          YES
    TEMP                           YES
    EXAMPLE                        NO
    UNDOTBS2                       YES

    7 rows selected.

    The tablespace EXAMPLE shows “NO” on FLASHBACK_ON column, which means the tablespace is excluded from Flashback Database.

    Please note that, you should not disable flashback on any tablespace if the database is working for data guard. Any disabled tablespace will make the whole database re-instate fail.

It’s important to keep Flashback Database enabled, if you want to rescue truncated tables or reverse failures on whole database in the future.

Leave a Reply

Your email address will not be published. Required fields are marked *