Flashback Database
Jump to navigation
Jump to search
What is Flashback Database?
The Flashback Database allows to return the database to its state in the past. It is much faster than the RMAN's point-in-time recovery because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.
Configuration
- Verify that the db_recovery_file_dest is set. This is the location of the flashback recovery area where the flash recovery files will be stored. Verify that there is sufficient space in the db_recovery_file_dest_size as well. Lastly, set an appropriate retention time (in minutes) in the db_flashback_retention_target.
SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------- db_recovery_file_dest string +DG2 db_recovery_file_dest_size big integer 100G SQL> show parameter db_flashback NAME TYPE VALUE ------------------------------------ ----------- ------------- db_flashback_retention_target integer 2880
- Turn the flashback ON.
[Sample here is for RAC database.]
-- Execute this on one of the nodes. SQL> alter system set cluster_database=false scope=spfile; System altered. -- Stop database. $ srvctl stop database -d DB11G -- Startup in MOUNT mode. SQL> startup mount ORACLE instance started. Total System Global Area 1.3700E+10 bytes Fixed Size 2113448 bytes Variable Size 7385194584 bytes Database Buffers 6308233216 bytes Redo Buffers 4440064 bytes Database mounted. -- Turn flashback ON. SQL> alter database flashback on; Database altered. -- Put cluster_database back to TRUE. SQL> alter system set cluster_database=true scope=spfile; System altered. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. -- Restart database. $ srvctl start database -d DB11G
- Verify that flashback is ON.
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
- Test flashback database.
SQL> create restore point before_flashback;
Restore point created.
SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
1.0376E+13 1 NO 0
10-APR-10 05.10.40.000000000 PM
NO
BEFORE_FLASHBACK
-- Create a test table.
SQL> create table a (a number);
Table created.
-- Verify that table is created.
SQL> select * from tab where tname='A';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
-- To flashback database, it needs to startup to MOUNT
$ srvctl stop database -d DB11G
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.3700E+10 bytes
Fixed Size 2113448 bytes
Variable Size 7385194584 bytes
Database Buffers 6308233216 bytes
Redo Buffers 4440064 bytes
Database mounted.
-- Let's flashback to a RESTORE POINT created earlier.
SQL> flashback database to restore point BEFORE_FLASHBACK;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
-- Verify that the table created after the restore point is no longer there.
SQL> select * from tab where tname='A';
no rows selected
- The V$FLASH_RECOVERY_AREA_USAGE view shows what types of files are and their percentages of the space usage in the Flash Recovery Area.
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3
To determine the actual space usage, simply query the view V$RECOVERY_FILE_DEST.
SQL> select * from V$RECOVERY_FILE_DEST; NAME ---------------------------------------------------------- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- --------------- +DG2 2147483648 1502122496 22201856 14
- Other views
- v$flashback_database_stat - Monitor logging
- v$flashback_database_log - Monitor flashback retention target
