Picture the scene, it’s the last Friday of the month and the Financial Controller crashes into the IT department looking for a DBA shaped miracle.
In a rush to close out the financial reporting before the bank holiday weekend, someone has closed the period too early! While this is a good thing for getting the reports printed on time, it’s bad news for the content as they’re missing huge chunks of revenue. Of course we can perform an emergency point in time restore of the database, but this database is 3TB and it takes many hours to complete such a restore, completely killing any dream of meeting the reporting deadline, not to mention the bank holiday weekend. Thankfully the clever DBA does indeed have said miracle, because he enabled Flashback Database!
Flashback Database was introduced in Oracle 10g to provide the ability to “Rewind” a database to a point in time. When used in conjunction with the Oracle Database Recovery Manager (RMAN) it provides a hugely powerful part of a database recovery solution. Traditionally, Point-In-Time recovery was achieved by restoring to an appropriate backup and then rolling the database forward to the desired point in time. However, if the restore point is close to the current time, it is often far more efficient to rewind the database using flashback logs.
Flashback logs are maintained by the database in order to provide a rolling flashback window (30min by default). Although, using restore points is a great way of enhancing the flashback capability:
Setup Flashback Database
As with archivelog mode, Flashback Database can only be enabled or disabled when the database is in Mount mode:
SQL> alter system set db_recovery_file_dest=’/rman/FRA’ scope=spfile;
SQL> alter system set db_recovery_file_dest_size=50G scope=spfile;
SQL> alter system set db_flashback_retention_target=4320; # default is 1440 minutes
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database flashback on;
SQL> alter database open;
Flash Back a Database
In normal running, the database will automatically manage the level of flashback logs retained based on the retention period. We can check the current flashback potential using commands similar to:
select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;
Assuming that the point we want to flash back to is within this window then we can execute via sqlplus or RMAN using the following procedure:
1. Shutdown the database and start in mount mode
SQL> shutdown immediate
SQL> startup mount
2. Flashback the database
SQL> flashback database to scn <scn number>;
OR
SQL> flashback database to time “to_date(<date>,’mm/dd/yy’)”;
3. Open the database and resetlogs.
SQL> alter database open resetlogs
Flashback restore points
One of the really useful features of Flashback Database is the ability to create Restore Points. In principle these are simply “markers” that are added to the flashback logs, but are incredibly useful in identifying and flashing back to specific points in time. If a restore point is set before an activity, then flashing back to that specific point becomes much easier.
SQL> flashback database to restore point <restore_point_name>;
In addition, If the restore point is created as a “Guaranteed Restore” point, then the database will not remove those flashback until the restore point is removed, thereby, as the name suggests, guaranteeing that you can flashback to that point in time. However, it will continue to generate flashback logs until the FRA is exhausted so Guaranteed Restore points should be used with caution.
Restore points can be managed using commands similar to the following;
SQL> create restore point “PRETEST” guarantee flashback database;
SQL> select name, scn, time, restore_point_time, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
OR
RMAN> LIST RESTORE POINT ALL;
These can then be used to flashback the database to specific points without having to identify specific SCN or times.
SQL> flashback database to restore point PRETEST;
Common uses
- Emergency “rewind” of the database to remove a destructive or incorrect action.
- Perform iterative testing (e.g. performance testing) on a known baseline without having to restore from backup each time.
- Provide a safety net for high risk production updates
More details on Flashback database can be found in the Database Backup and Recovery User’s Guide.
Claremont offer the best in Oracle functional and technical support. If you have any questions or if you would like further information please get in touch today.