Since version 9i there is the term “Flashback” in the Oracle Technology. However it is often hard to harmonize the different terms and corresponding technologies.
Let’s start with the definitions: Flashback means, at least in the Oracle terminology, that a past state shall be restored. Might be you want to see the state of a data set one hour ago, the table before it was deleted or the change from last month. And here we are at the actual question: What have I got to do to meet requirements like these?
Let’s start with the feature, that probably comes to your mind first: Flashback Database.
For what do we need it?
With Flashback Database it is possible, to roll back the entire database, that means to set back the entire data set to a former state.
What do we need for that?
Flashback Database is only available in the Enterprise Edition. Even though some claim different: First you don’t need to do anything, except that the database has to be in Archivelog Mode. With this you can create a Guaranteed Restore Point and reset the database after changes to this point.
SQL> CREATE RESTORE POINT before_batch GUARANTEE;
This is helpful e.g. if a fallback shall be available through a batch run or when applying a new application release. That means the Restore Point is set prior to the batch run and if you notice any failures after the run, the database is simply set back to that. If the batch was executed properly the restore point can be deleted – and you should by no means forget that, because then the Fast Recovery Area would fill up in a short period of time and the database would stop.
Why that?
At Flashback Database Flashback logs are written. That means, if a database block changes for the first time, a copy of that is written into the Fast Recovery Area. When setting back the database the blocks are simply exchanged and where applicable a short recovery run with information from the archived redo log file. This is necessary as you can set the Guaranteed Restore Point in a running database.
This is a limited utilization, as you need to know before, that you might want to roll back. If you want to set back your database to a former state at any time, you have to enable the Flashback Database constantly. Flashback Database like a guaranteed restore point can be enabled while the database is up and running. There is no need to stop the database and mount it but the Archiver is still required of course.
SQL> ALTER DATABASE FLASHBACK;
Afterwards the current state can be queried by the view v$database:
SQL> SELECT flashback_on, log_mode FROM v$database; FLASHBACK_ON LOG_MODE ------------------ ------------ YES ARCHIVELOG
Now the database can be set back to any point at any time. Really any point?
By the parameter db_flashback_retention_target you indicate in minutes (!) how far into the past you want to roll back. Realistic would be between one and three days here, depending on how much storage you allocate to the Fast Recovery Area.
The database can be set back as far into the past as the oldest Flashback log reaches. Every newer point is reached by a newer log and the meanwhile incurred archived Redolog files.
Flashback Logs or Storage Snapshots?
Many storage manufacturers nowadays offer snapshot functions, that means the altered blocks (no matter if database or other files) are stored in a snapshot area for a certain time. By this it is also possible to roll back the database to a former state. In contrast to Flashback Database it is often not possible to roll back the database to a former point, open it read only and afterwards roll forward to the previous state. This is interesting for example when using Dataguard, as you can roll back the Standby database, have a look at a table or copy it and then roll forward again. The availability remains during this time. In combination with dataguard please note that a “reinstate” of the primary database after a failover is only possible, when flashback database is enabled.
And one more hint: Caution with Dataguard! When copying with RMAN (duplicate for standby), the Flashback Database settings are not transferred, which means you have to enable them on the standby side once again.
Conclusion
Even though the Flashback logs need disc storage, the activation pays of in any case. And if you don’t want that, you can at least save a bunch of exports with the guaranteed restore points!
In my next blog I will go into the topic Flashback Table, for which three (!) different procedures are used.