Question: What is Oracle
flashback database? How can I use Oracle flashback database in an
OLTP shop?
Answer: Oracle flashback
database is an extension of the "rollback" functionality, allowing the
DBA to flashback a table to a specific date in history. With
Oracle flashback, the length
of the flashback recovery is determined by the storage dedicated to
Oracle UNDO and the settings for flashback database parameters.
Oracle flashback database is implemented via the
flashback database command.
Flashback database allows you to
quickly bring your database to a prior point in time by undoing all of
the changes that have taken place since that time. The Oracle database
flashback process is
fast, because you do not need to restore the backups.
Oracle flashback database can also be used in end-user
applications. This use of Oracle flashback database allows selected end-users to flashback their own data
tables to any point in time that they desire, without DBA intervention.
Some of the newer features of flashback database include:
-
Oracle's Flashback Database
-
Oracle's Flashback Standby
Database
-
Oracle's Flashback
Reinstantiation
-
Oracle's Flashback Drop
-
Oracle's Flashback Table
-
Oracle's Flashback Row History
-
Oracle's Flashback Transaction
History
More details on these key Oracle flashback database features is
available
HERE.
Perform Flashback
Database
After completing the functional and
technical testing, the database must be restored to the state just after
the upgrade is completed. The following procedure illustrates performing
the Flashback Database.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area
551165952 bytes
Fixed Size
2230232 bytes
Variable Size
373295144 bytes
Database Buffers
171966464 bytes
Redo Buffers
3674112 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 9294723;
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
Now, we query the dynamic view
v$session_longops view to
monitor the progress of the
Flashback Database. Another method is to monitor the instance's alert
log file.
$ cd /u01/app/oracle/diag/rdbms/t1c1/T1C1/trace
$ tail -f alert_T1C1.log
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 6 Seq
3 Reading mem 0
Mem#
0: +DATA/t1c1/onlinelog/group_6.266.846267799
Mem#
1: +FRA/t1c1/onlinelog/group_6.260.846267803
Incomplete Recovery applied until change 9319751
time 05/03/2014 07:23:44
Flashback Media Recovery Complete
Completed: flashback database to scn 9319750
Disable Flashback
Database
Now, we disable Flashback Database. The
disable command terminates the
Flashback background process RVWR and deletes existing Flashback logs
stored at Fast Recovery Area (FRA).
SQL> ALTER DATABASE FLASHBACK off;
Database altered.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
SQL>
See our additional notes on
Oracle flashback database.
-
-
-
-
-
-
-
-
-