Skip to content

How Oracle Enable Flashback Database

  • by

Enabling 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.

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 following conditions are required:

1. Check Archivelog Mode

Archived logs are used to work with flashback logs in the Flashback Database operation.

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. If not, maybe you should know how to enable archivelog mode.

2. Check FRA

Where the database store its flashback logs. Their file extension is usually *.flb.

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. If not, maybe you should know how to enable fast recovery area.

3. Set DB_FLASHBACK_RETENTION_TARGET

Set DB_FLASHBACK_RETENTION_TARGET on both primary and standby databases.

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.

4. Enable Flashback Database

You can enable database flashback either at MOUNT state or OPEN state.

On Primary Database

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

For those who has standby database, you have to make it play primary role temporarily in order to enable flashback on.

Switchover

DGMGRL> switchover to standb

On Standby Database

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

Switchback

DGMGRL> switchover to primdb

The best practice would be to enable flashback before duplicating the standby database. Or create a control file for standby database before data synchronization.

For no data guard databases, you can optionally disable some specific tablespace to flashback off.

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.

5. Check Result

After enabling Flashback Database, we should check the results:

Check the Status of Flashback Database.

SQL> SELECT flashback_on FROM v$database;

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

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

Check Tablespace Flashback Status

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.