Introduction:

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 Logs
Flashback Drop                   Recycle bin
Flashback Table                  Undo Data
Flashback Query                 Undo Data
Flashback Version               Undo Data
Flashback 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:

  • Taking a database to an earlier SCN: This is really useful when a new version of an application needs to be tested and all the changes made for the testing discarded afterwards. In this case, a new environment (for testing or dev) must be created that contains the data in the production database at a specific time in the past.
  • Recovery through resetlogs: Flashback Database can revert (logically) a database to a specific date in the past, even if that specific date precedes that of a RESETLOGS operation.
  • Activating a Physical Standby Database: With Oracle Database 10g, Flashback Database can be used in a Physical Standby. The Physical Standby can be opened in read-write for testing purposes and when the activity completes, the database can be reverted to the time before the Physical Standby was activated.
  • Creating a Snapshot Standby: In 11g, Snapshot Standby was introduced. The concept is basically to automate all the steps involved in activating (opening in read-write) a Physical Standby in version 10g, then later make it Physical Standby again (with recovery). This "automated" conversion of a Physical Standby into a “Snapshot Standby” uses Flashback Database transparently to the DBA. 
  • Configuring Fast Start Failover: To configure Fast Start Failover in Data Guard Broker, Flashback Database is required.
  • Reinstating a Physical Standby: Data Guard broker uses Flashback Database to reinstate a former primary after Failover operations. Read more about reinstating a database in the following articles:  Role Operations with Snapshot Standby 12cRole Operations involving two 12c Standby Databases
  • Upgrade testing: A Physical Standby can be used to test an upgrade; in this case, the Physical Standby is opened in read-write and upgraded. Applications can be tested with the upgraded database and when the activity completes the Physical Standby can be reverted to the former version using Flashback Database. The Transient Logical Standby method for upgrades also involves Flashback Database.

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 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 – Production

Enabling Flashback:

Local Undo is used in this example:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE 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 12.2.0.1. 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; 

Database altered.

 

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;

Session altered.

SQL> create table deiby.piece (piece_name varchar2(20));

Table created.

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> commit;

Commit complete.

SQL> select * from deiby.piece;

PIECE_NAME
--------------------
King
Queen
Rook
Bishop
Knight
Pawn

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_POINT
NAME 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;

Table truncated. 

Drop the table with purge:

SQL> drop table deiby.piece purge;

Table dropped.

 

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-17
current log archived
using channel ORA_DISK_1

starting media recovery
media 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;

Statement processed

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;

Flashback complete. 

After a Flashback PDB operation, the PDB must be opened with resetlogs:

SQL>  alter pluggable database nuvolapdb2 open resetlogs;

Pluggable database altered.

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:

SQL> alter session set container=nuvolapdb2;

Session altered. 

SQL> select * from deiby.piece;

PIECE_NAME
--------------------
King
Queen
Rook
Bishop
Knight
Pawn

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

Conclusion:

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