Oracle Database: Flashback Vs. Recovery

If you consider “normal” recovery as anything having to do with RMAN (and what it replaced), then you’re actually limiting yourself in terms of what Oracle has to offer. Recovery – in the traditional sense – isn’t just recovery anymore, but more like recovery plus various flashback options.

Right along with the skills a database administrator needs to backup an Oracle database, are the skills needed to perform a recovery. If you consider “normal” recovery as anything having to do with RMAN (and what it replaced), then you’re actually limiting yourself in terms of what Oracle has to offer. Recovery – in the traditional sense – isn’t just recovery anymore, but more like recovery plus various flashback options. Oracle has even gone as far as suggesting that flashback be your first choice in some situations.

The idea of flashback is pretty simple, but how it is implemented or used within the database can be a bit confusing. The confusion arises from where flashback lives in the database and what timeframes are involved. In what I’ll call RMAN recovery, we know recovery comes from backups and the application of archived redo logs. The timeframe for RMAN recovery is as long as you want to maintain backups and related archived redo logs (which can be essentially forever). Flashback lives in a couple of places in Oracle. One location is in the UNDO tablespace, and the other is in flashback log files. Both of these areas are limited by space, which is related to time, but less of it. Flashback, depending on what is being flashed back, can also require archived redo logs. Yet another way to look at flashback is to consider what it is used for: error analysis or error recovery.

Considering where flashback data lives, you have some pluses and minuses when it comes time to flash back some object (table or database). For the flashback that lives in UNDO, your flashback time is related to undo retention time. If the relevant undo segment where a table’s transactions were recorded is overwritten, then “flashback table table_name to timestamp (or SCN) whenever” is no longer an option, and you have to resort to other means of recovery. The major plus is the speed in which the recovery is performed; the major minus is the time limitation.

Within flashback, there is also some duplication of recovery source. If flashback database is enabled, there is no (technical) reason why you couldn’t flashback database to a time far enough in the past and extract a dropped or altered (data-wise, that is) table. Your limit here is based on the oldest SCN you can flashback to, which you can get from the v$flashback_database_log data dictionary view. One limitation of flashback database is that the flashback retention value (time) is not a guaranteed time you can get back to. If the fast recovery area (was flash recovery area in older versions of Oracle) needs space, well, you get what you can get out of the flashback logs. And although there is no technical reason preventing flashback recovery, there may be other reasons which can be flashback show stoppers (e.g., business users would rather have the database open for use while you go do a database or tablespace point in time recovery on another server).

Does flashback logging need to be enabled to be able to flashback the database? The answer is no. If you use a guaranteed restore point, and all you care about is being able to flashback to a specific time (i.e., the restore point), then flashback logging is not needed. A use case for “flashback without logging” is performing an application upgrade. Do the upgrade, and if you don’t like the results, then flashback database to the restore point and you’re back to where you were before the upgrade. Also, the database must be running in archivelog mode.

In addition to RMAN and flashback technologies, another related and quite relevant feature is Data Pump. Prior to Oracle 11gR2, the automatic tablespace point-in-time-recovery (TSPITR) process included export and import operations. In R2, the old/traditional export/import process has been replaced with Data Pump export and import. Data Pump also comes into play during flashback database. If you are trying to recover a dropped table or loss of data, once you’ve flashed the database far enough back in time, and assuming you want to return to the future, then while in the past you can Data Pump export the relevant object/data out to the file system. After the export, recover the database (which is why you need to be in archivelog mode). Now that you are back in the future, you can import the table or data into the database.

This also works for having dropped a user by accident (or had a change of mind). Flashback the database to the relevant SCN, and the dropped user is present. Recover to a time just before the error and then keep the database as is, or do the Data Pump export/import drill and recover the database back to the starting point.

Overall, your flashback recovery tools are flashback transaction backout, flashback table, flashback drop and flashback database. Your flashback tools for error analysis are flashback query, flashback versions query, and flashback transaction query. Flashback recovery can be performed in SQL*Plus and in RMAN. Further, flashback operations can be performed in Enterprise Manager via Availability > Perform Recovery > User Directed Recovery section. Within this area, Oracle offers the user directed recovery choices of database, tables, archived logs, tablespaces, datafiles and transactions. Out of this list, the flashback related items are limited to database , tables, and transactions. (The flashback database option is available for faster recovery. You can also recover to a point in time prior to the flashback log retention time. In that case, regular restore and recovery will be used.)

The tighter integration between traditional recovery and flashback recovery (along with Data Pump) practically requires you to know more about recovery than what was available in older versions of Oracle (pretty much just RMAN and possibly an export dump file “just in case” logical backup). One other item of note is that as much as “flashback this” and “flashback that” is advertised by Oracle, did you know that flashback table, database, transaction, and transaction query – and as mentioned before – tablespace point-in-time recovery are all licensable at the Enterprise Edition level. Going back to the title of this article, Flashback versus Recovery, it’s probably more accurate to view this as Recovery using Flashback since they work together more than they work apart.

» See All Articles by Columnist Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles