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:
- ARCHIVELOG mode must be enabled.
- Fast Recovery Area (FRA) must be enabled.
- FRA must be in a shared storage for RAC environments.
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.