Flashback Database

Flashback Database

Flashback Database is introduce with 10gr1, It is alternative for PITR (Point In Time Recovery).

It is superb feature, we can also called "rewind button" for database. becuase through this option we can rewind database to any point of time.

1. How to ENABLE Flashback Database option.

By Default it is DISABLE so we need to enable this option.

Requirement for the flashback database

1. Database must run in archivelog mode

check how to enable archivelog mode

2. FLASH_RECOVERY_AREA configured

check how to configure FRA

If above both requirement complete the do the following.

1. connect with SYS user and shutdown database with normal

SQL> conn / as sysdba

Connected.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

2. startup database with MOUNT mode

SQL> startup mount

3. Enable FLASHBACK DATABASE

SQL> alter database flashback on;
Database altered.

4. Open the database with normal operation

SQL> alter database open;
Database altered.

5. Check flashback database option enable for the database

SQL> select flashback_on from v$database;
FLA

---

YES

NOTE: FLASHBACK LOGS created in Flash recovery area

Up to this stage we only ENABLE the flashback database, now we need to configure one more parameter for How far can we bring the database back in time?

for that we need to configure below parameter

db_flashback_retention_target

NOTE: Parameter is dynamic, and default value is 1440 minutes means 24 hrs, parameter value is set in MINUTES

We can set FLASHBACK database option for particular tablespace also.

SQL> alter tablespace TEST1 flashback on;

SQL> alter tablespace TEST1 flashback off;

Estimating flashback database storage reqirement

We can get this information through below query after enable flashback database option.

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

How Flashback Database is important for DBA? lets check with below example

Nive (DBA) working in ABC bank as an DBA, suddenly one application user called and told he was by mistake DROP wrong production table with PURGE option.

Database version is 10gr1, so we can easily recovered dropped table through FLASHBACK TABLE but the problem is here user dropped table with PURGE option so we can't recovered dropped table without perform Point In Time Recover option.

Nive is happy becuase FLASHBACK DATABASE option is ENABLE.

So Miss Nive done the following steps

1. shutdown the database

2. flashback database before table was dropped

3. open database with read only

4. export dropped table

5. shutdown and startup database with mount mode

6. perform recover database

7. open database with normal operation without resetlogs

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542151

SQL> drop table SCOTT.EMP purge;
Table dropped.

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542182

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> flashback database to scn 542151;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> desc scott.emp

Name Null? Type

----------------------------------------- -------- ---------------------------
[output cut]

SQL> host exp system/oracle file=c:\emp.dmp tables=SCOTT.EMP
Export: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:42:03 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...

Current user changed to SCOTT. .

exporting table EMP 14 rows exported

Export terminated successfully without warnings.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> recover database

Media recovery complete.

SQL> alter database open;
Database altered.

SQL> desc scott.emp

ERROR:ORA-04043: object scott.emp does not exist

SQL> host imp system/oracle file=c:\emp.dmp fromuser=scott touser=scott
Import: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:43:10 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT. .

importing table "EMP" 14 rows imported

About to enable constraints...

Import terminated successfully without warnings.

SQL> desc scott.emp

Name Null? Type ----------------------------------------- -------- ---------------------------
[output cut]

Finally Nive able to recover dropped table :)

We can flashback database through SCN or TIMESTAMP

Read more:

Limitations of Flashback Database

Performance Tuning for Flashback Database

Monitoring Flashback Database

Example Flashback Database