Flashback is a technology introduced in Oracle Database 10g to provide fixes for user errors. For example, one of the most common issues it can solve is when a DELETE operation was executed without a proper WHERE clause. Another case: a user has dropped a table but after some time that table is required. And the worst-case error: the data of a complete database has been logically corrupted. There are several use cases for Flashback technology, all of them focused on recovering objects and data or simply reverting data from the past. Flashback technology is not a replacement for other recovery methods such as RMAN hot backups, cold backups or datapump export/import; Flashback technology is a complement. While RMAN is the main tool to recover and restore physical data, Flashback technology is used for logical corruptions. For instance, it cannot be used to restore a datafile, while RMAN is the perfect tool for that purpose. Also, be careful when NOLOGGING operations are used; Flashback Database cannot restore changes through NOLOGGING.
Flashback Technology includes several "Flashback Operations", among them Flashback Drop, Flashback Table, Flashback Query, Flashback Version, Flashback Transaction and Flashback Database. They use different data sources to restore/revert user data from the past. The following table shows which data source is used for which Flashback operation:
Flashback Operation Data Source
Flashback Database Flashback LogsFlashback Drop Recycle binFlashback Table Undo DataFlashback Query Undo DataFlashback Version Undo DataFlashback Transaction Undo Data
In this article, we will focus on Flashback Database, a feature that is able to "flash back" a complete database to a point in the past. Flashback Database has the following use cases:
How Flashback Database works:
When blocks are modified in the Buffer Cache, some of the before-the-change block images are stored in the Flashback Buffer and subsequently stored physically in the Flashback Logs by the RVWR process. All blocks are captured: index blocks, table blocks, undo blocks, segment headers, etc. When a Flashback Database operation is performed, Oracle uses the target time and checks out its Flashback Logs to find which Flashback Logs have the required block images with the SCN right before the target time. Then Oracle restores the appropriate data blocks from Flashback Logs to the Datafiles, applies redo records to reach the exact target time, and when the Database is opened with resetlogs, the changes that were not committed are rolled back using undo data to finally have a consistent database ready to be used.
Flashback Database Enhancements:
Flashback Database has had several enhancements since it was introduced, with the biggest enhancements in 12.1 and 12.2. In Oracle Database 12.1 Flashback Database supported Container Databases (CDBs) supporting the Multitenant Architecture, however Flashback Database at the PDB Level was not possible. In Oracle Database 12cR2 Flashback Database added support at the PDB level. This was enabled thanks to another good feature introduced in Oracle Database 12.2 called "Local Undo". Local Undo allows you to create an undo tablespace in each Pluggable Database and use it to store locally undo data for that PDB specifically. Local Undo must be enabled at the CDB level. However, if the CDB is not running in Local Undo mode, Flashback Pluggable Database can also be used, but the mechanism used is totally different. In a Shared Undo mode, Flashback Pluggable Database needs an auxiliary instance in which the required tablespaces will be restored and recovered to perform the Flashback Database operation and a switch is then performed between the current tablespaces and the new restored-and-recovered tablespaces in the required Pluggable Database.
NOTE: All the examples in this article were created using Oracle Public Cloud:
Oracle Database 12c EE Extreme Perf Release 220.127.116.11.0 - 64bit ProductionPL/SQL Release 18.104.22.168.0 - ProductionCORE 22.214.171.124.0 ProductionTNS for Linux: Version 126.96.36.199.0 - ProductionNLSRTL Version 188.8.131.52.0 – Production
Local Undo is used in this example:
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUEFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE-------------------- ---------------LOCAL_UNDO_ENABLED TRUE
To read more about Local Undo and Shared Undo the following articles are recommended: Oracle DB 12.2 Local Undo: PDB undo tablespace creation, How to Enable and Disable Local Undo in Oracle 12.2.
Flashback cannot be enabled at the PDB level in 12.1 and 184.108.40.206. Flashback must be enabled at the CDB level. Before you can Enable Flashback in your CDB you have to ensure that enough space is available to store the Flashback Logs. Oracle recommends using the following generic formula to setup your Fast recovery area space:
Target FRA = (Current FRA)+[DB_FLASHBACK_RETENTION_TARGET x 60 x Peak Redo Rate (MB/sec)]
After setup the FRA space properly, Flashback may be enabled:
SQL> alter database flashback;
Creating a table and some rows
To test the result of Flashback Database operation, I will create a table with some rows in it; that data will be used to flashback the database and verify that the database was thereby successfully reverted to a past time.
SQL> alter session set container=nuvolapdb2;
SQL> create table deiby.piece (piece_name varchar2(20));
SQL> insert into deiby.piece values ('King');
SQL> insert into deiby.piece values ('Queen');
SQL> insert into deiby.piece values ('Rook');
SQL> insert into deiby.piece values ('Bishop');
SQL> insert into deiby.piece values ('Knight');
SQL> insert into deiby.piece values ('Pawn');
SQL> select * from deiby.piece;
6 rows selected.
Restore Point creation
To perform Flashback Database a restore point, a guaranteed restore point, an SCN or a timestamp is required. In this example a normal restore point is used.
SQL> create restore point before_resetlogs for pluggable database Nuvola2;
Restore point created.
SQL> SELECT name, pdb_restore_point, scn, time FROM V$RESTORE_POINTNAME PDB SCN TIME----------------- --- ---------- -------------------------------BEFORE_RESETLOGS YES 3864200 09-JAN-17 08.12.56.000000000 PM
Truncating and dropping the table
Now let's assume a user error: a DBA, developer, or end user truncates a table and then drops it. This is a simple example, but you can make this "logical error" as complex as you want so long as a physical error is not involved and a NOLOGGING is not used.
Truncating the table:
SQL> truncate table deiby.piece;
Drop the table with purge:
SQL> drop table deiby.piece purge;
Open the database with resetlogs:
To make it more interesting, I will simulate a recovery-until-time operation in order to perform a resetlogs operation:
RMAN> recover pluggable database nuvolapdb2 until scn 3864712;Starting recover at 09-JAN-17current log archivedusing channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:00
Finished recover at 09-JAN-17
Opening the Pluggable Database with resetlogs:
RMAN> alter pluggable database nuvolapdb2 open resetlogs;
We can verify that indeed a new incarnation was created for the PDB:
Flashback the database
Now it's time for the magic, the new feature introduced in Oracle Database 12.2 called "Flashback Pluggable Database". To use Flashback Database at Pluggable Database level, the PDB must first be closed.
SQL> alter pluggable database nuvolapdb2 close;
Pluggable database altered.
SQL> select con_id, db_incarnation# db_inc#, pdb_incarnation# pdb_inc#, status,incarnation_scn from v$pdb_incarnation where con_id=4;
CON_ID DB_INC# PDB_INC# STATUS INCARNATION_SCN---------- ---------- ---------- ------- --------------- 4 1 5 CURRENT 3864712 4 1 0 PARENT 1
Then Flashback PDB may be used:
SQL> flashback pluggable database nuvolapdb2 to restore point before_resetlogs;
After a Flashback PDB operation, the PDB must be opened with resetlogs:
SQL> alter pluggable database nuvolapdb2 open resetlogs;
Verifying the data
Once the Flashback PDB has completed successfully, the data that existed before the truncate, drop and resetlogs (and even more if you want) can be queried:
6 rows selected.
A quick look at the incarnations will show that a new incarnation was created for the PDB (Incarnation #6) and the former Incarnation was made orphan (Incarnation #5).
SQL> select con_id, db_incarnation# db_inc#, pdb_incarnation# pdb_inc#, status,INCARNATION_SCN from v$pdb_incarnation where con_id=4;
CON_ID DB_INC# PDB_INC# STATUS INCARNATION_SCN---------- ---------- ---------- ------- --------------- 4 1 6 CURRENT 3864201 4 1 0 PARENT 1 4 1 5 ORPHAN 3864712
Flashback Database has several use cases and is a very useful feature that DBAs should keep “in their pocket” and ready to use when they need to revert a database to a time in the past. It allows you to test upgrades, activate a physical standby, undo user errors, and test applications—all without worry. I’m sure that Oracle will keep improving this feature; perhaps in the next version of Oracle we will gain the ability to enable Flashback in PDB Level and several others functions. For now, the enhancements made by Oracle in 12.1 and 12.2 are enough to work with non-CDB, CDBs and PDBs.
About the Author: Deiby Gómez is an Oracle ACE Director from Guatemala, he has the certifications Oracle Certified Master 11g and Oracle Certified Master 12c. Deiby Gómez currently works for Nuvola Consulting Group, a Guatemalan company that provides Consulting Services for Oracle Products. He is the winner of “SELECT Journal Editor's Choice Award 2016”. Deiby has been Speaker in Collaborate in Las Vega USA, Oracle Open World in San Francisco USA and in Sao Paolo Brazil. Twitter | LinkedIn