Flashback

Flashback Database

Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

RVWR Background Process

When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.

Enabling Flashback Databae:

  1. Make sure the database is in archive mode and FLASHBACK_ON Yes

SQL>SELECT flashback_on, log_mode

FROM v$database;

  1. Configure the recovery area(if necessary) by setting the two parameters:

- db_recovery_file_dest

- db_recovery_file_dest_size

  1. Open the database in MONT mode and turn on the flashback feture:

SQL> STARTUP MOUNT;

SQL>ALTER DATABASE ARCHIVELOG; [If not in archive mode]

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

  1. SQL> create table test_flashback(name varchar(30));

  2. SQL> insert into test_flashback values('*******TEST BEFORE*******');

  3. SQL> commit;

  4. SQL> select to_char(sysdate,'dd-mm-yy hh24:mi:ss') from dual;

  5. SQL> SELECT current_scn FROM v$database;

  6. SQL> insert into test_flashback values('*******TEST AFTER*******');

  7. SQL> commit;

  8. SQL> select * from test_flashback;

  9. SQL> drop table test_flashback;

  10. SQL> shutdown immediate;

  11. SQL> startup mount;

  12. SQL> FLASHBACK DATABASE to timestamp to_timestamp('16-07-2008 13:59:45', 'DD-MM-YYYY HH24:MI:SS');

OR

SQL> FLASHBACK DATABASE TO SCN 3726625;

  1. SELECT current_scn FROM v$database;

  2. SQL> ALTER DATABASE OPEN RESETLOGS;

  3. SQL> SELECT * FROM test_flashback;

Another Example:

  1. SQL> conn moon/moon#1

  2. SQL> create table test_flash(id number);

  3. SQL> commit;

  4. SQL> drop table test_flash;

  5. SQL> flashback table test_flash to before drop;

  6. SQL> select * from test_flash;