Flashback Database: A Primer

Introduction

Oracle 10g’s brilliant alternative to database point in time recovery is the the Flashback Database feature. With this feature in place you can do almost everything that you can with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily entails.I recently had a first hand opportunity to see the power of this feature, when I ran a scriptfile to drop tables and unwittingly dropped one of the tables containing sensitive information belonging to my employer Creative Infotech. I later recovered the table and was amazed at seeing how easy it had become to get back dropped objects in Oracle 10g, especially Release 2. Below is a simplified version of what I did

Unlike other flashback features, which depend on undo data for reconstructing your lost data, Flashback Database uses flashback logs to access past versions of changed blocks and allied with some more information mined from the archive logs, you can easily revert your database to a point in time in the past. Whilst the end product is very much like a point in time recovery, Flashback database is much faster and less disruptive, because you do not restore from backups and flashback logs are maintained on the disk itself. Setting it up at the basic level is pretty simple. It all starts being in ARCHIVELOG mode. I give an example below of how I set it up on my system.

Setting up Archiving

system@ORCL-SQL>>alter system set log_archive_dest_1='location=c:\test_flash_arcc','mandatory';

System altered.

system@ORCL-SQL>>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL-SQL>>startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
system@ORCL-SQL>>alter database archivelog;

Database altered.

system@ORCL-SQL>>alter database open;

Database altered.

sys@ORCL-SQL>>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\test_flash_arcc
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13

Once archiving is set up, you need to configure the flash recovery area and its size. For this you need to set two initialization parameters:

• DB_RECOVERY_FILE_DEST_SIZE
• DB_RECOVERY_FILE_DEST

The first parameter specifies the disk quota or maximum amount of space allocable for the flash recovery area files in the flash recovery area. The second parameter points to the flash recovery area. DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST. In a RAC database, all instances must have the same values for these parameters and flash recovery area must be stored in a clustered file system or ASM.

Configuring initialization parameters:

system@ORCL-SQL>>alter system set db_recovery_file_dest_size=4g;

System altered.

system@ORCL-SQL>>alter system set db_recovery_file_dest='e:\ora_flashback';

Note: In case you have already configured a flashback area while creating the database, or even subsequently, then you do need to set the destination parameter again, as Oracle already has a place to write backup and flashback information. In my case, I preferred to set my own flashback area.

How big the flashback area ought to be is contingent on quite a few factors, such as size of the datafiles, redologs, controlfiles. You have also to be aware of the mean frequency and number of your block changes, whether you store backups only on disk, or on disk and tape, and whether you use a redundancy-based retention policy, or a recovery window-based retention policy etc.
But, once you have all these figured out, the mechanics of enabling flashback is pretty simple:

Enabling Flashback:

system@ORCL-SQL>>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
system@ORCL-SQL>>startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.


system@ORCL-SQL>>alter system set db_flashback_retention_target=4320;--flashback to be retained for
                                   --three days
System altered.

sys@ORCL-SQL>>alter database flashback on;

Database altered.

sys@ORCL-SQL>>alter database open;

Database altered.

So, that enables flashback for our database. We can query v$flashback_database_log to see what's going on

system@ORCL-SQL>>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
              539853 09-MAY-07             4320        8192000                        0

We see that oldest SCN that we can flashback to is SCN 539853. In other words this is our baseline SCN. The entire technology of flashback database is being implemented from this SCN. We can easily convert the SCN to a timestamp, if we are interested in seeing the time from which flashback database stands enabled.

system@ORCL-SQL>>select scn_to_timestamp(539853) from dual;

SCN_TO_TIMESTAMP(539853)
---------------------------------------------------------------------------
09-MAY-07 04.38.30.000000000 PM

Flashing Back

Its time to put flashback database in action. For this, I am going to create a table s and then truncate it. But before truncating the table I need to find out the scn and/ or timestamp to which I will revert back to, after truncating my table.

sys@ORCL-SQL>>create table s as select * from tab;

Table created.

sys@ORCL-SQL>>select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
     591023 10-MAY-07 11.53.52.000000000 AM

sys@ORCL-SQL>>truncate table s;

Table truncated.

sys@ORCL-SQL>>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL-SQL>>startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
sys@ORCL-SQL>>flashback database to scn 591023;

Flashback complete.

sys@ORCL-SQL>>alter database open resetlogs;

Database altered.

sys@ORCL-SQL>>select * from s where rownum=1;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ICOL$                          TABLE            4

sys@ORCL-SQL>>select count(*) from s;

  COUNT(*)
----------
      3339

sys@ORCL-SQL>>

So, that's it. Its that simple. On the other hand, if you aren't satisfied with what you have got after flashing back, you can simply undo the results of the entire flashback operation by issuing RECOVER DATABASE command. This will perform a complete recovery by applying all of the changes from the archived logs and restoring the status of the database to current.Or, in case you feel you haven't flashed back far enough, you can do so by running the FLASHBACK DATABASE command once again to go back further.