Flashback Database

Flashback Database allows the ability to simply rewind the entire database to a previous point in time without affecting the integrity of the data.

Flashback Database needs Flashback Database log. 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. Flashback Database reduces the time required to recover the database to a point in time. The flashback logs are created in an area known as the Flash Recovery Area.

Steps:

1.Configure the Flashback recovery area.

SQL>Alter system set db_recovery_file_dest= D:oracleproduct10.2.0flash_recovery_area scope=spfile;

SQL>Alter system set db_recovery_file_dest _size=2G scope=spfile;

2.Shutdown the database.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3.Startup the database in Mount stage & Enable the flashback feature.

SQL> startup mount
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 1289064 bytes
Variable Size 92275864 bytes
Database Buffers 192937984 bytes
Redo Buffers 7098368 bytes
Database mounted.

SQL> Alter database flashback on;
Database altered.

SQL>Select flashback_on from v$database;
(Verify the flashback on or not)

SQL> alter database open;
Database altered.

4.Now we see one test case.

SQL> create user raja
2 identified by raja
3 default tablespace users
4 quota unlimited on users
SQL> /
User created.

SQL> grant connect,resource to raja;
Grant succeeded.

SQL> create table raja.test_objects as select * from scott.employee nologging;
Table created.

SQL> select count(*) from raja.test_objects;

COUNT(*)
———-
40755

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
——————– ——— —————- ————–
ESTIMATED_FLASHBACK_SIZE
————————
539855 12-AUG-08 1440 8192000
0

539855 —SCN Number

Now I have truncated the raja.test_objects table.

SQL> truncate table raja.test_objects;

Table truncated.

SQL> select count(*) from raja.test_objects;

COUNT(*)
———-
0

Now I have plan to revert back the database.

Shutdown & startup the database in mount stage.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1289064 bytes
Variable Size 92275864 bytes
Database Buffers 192937984 bytes
Redo Buffers 7098368 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO SCN 539855;
Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

SQL> select count(*) from raja.test_objects;

COUNT(*)
———-
40755

Now we should take the cold backup. (Previous Physical backup are all invalid)

Important points:

• Database must be in Archive log mode.
• Flashback database users have a SYSDBA privilege.
• We cannot the revert back the database to a time more than DB_FLASHBACK_RETENTION_TARGET

Restriction to use this feature:

• Control file restored/recreated.
• Incomplete recovery use the resetlogs option
• Tablespace/data file dropped or shrink

I Hope this article helped you to understand the Flashback Database.