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.
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 necessary conditions are:
- ARCHIVELOG mode must be enabled.
- Fast Recovery Area (FRA) must be enabled.
- FRA must be in a shared storage for RAC environments.
The sufficient conditions are:
- Sufficient I/O channel capacity.
- Same value of DB_FLASHBACK_RETENTION_TARGET on both primary and standby databases.
- LOG_BUFFER is at least 8 MB initially.
- Check Archivelog Mode:
- Check FRA:
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.
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:
- Set DB_FLASHBACK_RETENTION_TARGET on both primary and standby databases.
- Enable Flashback Database on primary database.
- Switchover to the standby database.
- Enable Flashback Database on standby database.
- Switchback to the primary database.
- Optional: Disable a specific tablespace to flashback.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
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.
SQL> ALTER DATABASE FLASHBACK ON;
For enabling flashback on the standby database, you have to make it play primary role temporarily.
DGMGRL> switchover to standb
SQL> ALTER DATABASE FLASHBACK ON;
DGMGRL> switchover to primdb
SQL> ALTER TABLESPACE EXAMPLE FLASHBACK OFF;
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:
- Check the Status of Flashback Database.
- Check Tablespace Flashback Status
SQL> SELECT flashback_on FROM v$database;
The database shows “YES”. Which means we have enabled Flashback Database.
SQL> SELECT name, flashback_on FROM v$tablespace;
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.