Difference between revisions of "Flashback Database"
Jump to navigation
Jump to search
Line 158: | Line 158: | ||
** ''v$flashback_database_stat'' - Monitor logging | ** ''v$flashback_database_stat'' - Monitor logging | ||
** ''v$flashback_database_log'' - Monitor flashback retention target | ** ''v$flashback_database_log'' - Monitor flashback retention target | ||
− | |||
== See also == | == See also == |
Latest revision as of 07:23, 7 March 2011
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