Introduction to Oracle 10g Flashback Database

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 do with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily entails.

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:	est_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:	est_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