Performing Flashback in Oracle Database
Performing Flashback in Oracle Database
Performing Flashback in Oracle Database
Flashback supports recovery at all levels including the row, transaction, table, and the entire database.
Flashback provides an ever growing set of features to view and rewind data back and forth in time,
namely:
The Flashback features offer the capability to query historical data, perform change analysis, and perform
self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback
Technology, you can indeed undo the past.
FLASHBACK DATABASE
Purpose
Use the FLASHBACK DATABASE command to rewind the database to a target time, SCN, or log
sequence number.
This command works by undoing changes made by Oracle Database to the data files that exist
when you run the command. Flashback can fix logical failures, but not physical failures. Thus, you
cannot use the command to recover from disk failures or the accidental deletion of data files.
FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time
recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of
changes made to the database since the desired flashback time. On the other hand, the time
needed to do a traditional point-in-time recovery from restored backups depends on the size of the
database.
Prerequisites
You can run this command from the RMAN prompt or from within a RUN command.
RMAN must be connected as TARGET to a database, which must be Oracle Database 10g or later.
The target database must be mounted with a current control file, that is, the control file cannot be a
backup or re-created. The database must run in ARCHIVELOG mode.
You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of
a control file. If the database control file is restored from backup or re-created, then all existing
flashback log information is discarded.
The fast recovery area must be configured to enable flashback logging. Flashback logs are stored as
Oracle-managed files in the fast recovery area and cannot be created if no fast recovery area is
configured. You must have enabled the flashback logging before the target time for flashback using
the SQL statement ALTER DATABASE ... FLASHBACK ON. Query V$DATABASE.FLASHBACK_ON to
see whether flashback logging has been enabled.
The database must contain no online tablespaces for which flashback functionality was disabled with
the SQL statement ALTER TABLESPACE ... FLASHBACK OFF.
Usage Notes
A Flashback Database operation applies to the whole database. You cannot flash back individual
tablespaces. A Flashback Database operation is similar to a database point-in-time recovery
(DBPITR) performed with RECOVER, but RMAN uses flashback logs to undo changes to a point
before the target time or SCN. RMAN automatically restores from backup any archived redo log files
that are needed and recovers the database to make it consistent. RMAN never flashes back data for
temporary tablespaces.
The earliest SCN that can be used for a Flashback Database operation depends on the setting of
the DB_FLASHBACK_RETENTION_TARGET initialization parameter, and on the actual retention of
flashback logs permitted by available disk. View the current database SCN in
V$DATABASE.CURRENT_SCN.
Oracle Flashback
Oracle flashback allows you to move database back in time. You can use flashback
technology to move entire database or a particular table inside database.
Note: only for flashback database activity, you must enable flashback
database. For all other flashback activities, you do not need to enable flashback
database
Flashback Table Before Drop
You can flashback a dropped table from recyclebin using flashback table command
SHOW RECYCLEBIN;
or
You can even rename table while flashing it back from recyclebin
Flashback Table
You can flashback table to a particular SCN or time in the past. Before you can
flashback table, you must enable row movement
Flashback Database
Create a user FLASH_USR and try to connect the database with same user
SQL> create user flash_usr identified by flash_usr;
SQL> grant connect, resource to flash_usr;
Assume that the user has been created by mistake and you want to flashback database
to the SCN just before the user creation. Shutdown DB and startup mount
SQL> shut immediate;
Flashback database to SCN before user creation and open database with resetlogs
SQL> Flashback database to scn 2703232;
Restore point:
Restore point is nothing but a name associated with a timestamp or an SCN of the
database. One can create either a normal restore point or a guaranteed restore point.
The difference between the two is that guaranteed restore point allows you to flashback
to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET
initialization parameter i.e. it is always available (assuming you have enough space in
the flash recovery area).
After you have created or migrated a fresh database, first thing to do is to create a
guaranteed restore point so you can flashback to it each time before you start a new
workload. The steps are as under:
1. $> su – oracle
2. $> sqlplus / as sysdba;
3. Find out if ARCHIVELOG is enabled
SQL> select log_mode from v$database;
If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step
8 below.
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> alter database archivelog;
7. SQL> alter database open;
8. SQL> create restore point CLEAN_DB guarantee flashback database;
where CLEAN_DB is the name given to the guaranteed restore point.
Verify the information about the newly created restore point. Also, note down the SCN#
for reference and we will refer to it as "reference SCN#"
Now, in order to restore your database to the guaranteed restore point, follow the steps
below:
1. $> su – oracle
2. $> sqlplus / as sysdba;
3. SQL> select current_scn from v$database;
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> select * from v$restore_point;
7. SQL> flashback database to restore point CLEAN_DB;
8. SQL> alter database open resetlogs;
9. SQL> select current_scn from v$database;