Difference between revisions of "Flashback Database"
Jump to navigation
Jump to search
Line 3: | Line 3: | ||
== Configuration == | == 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 | + | * 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'''. |
<pre> | <pre> | ||
SQL> show parameter db_recovery_file | SQL> show parameter db_recovery_file | ||
Line 22: | Line 22: | ||
[Sample here is for RAC database.] | [Sample here is for RAC database.] | ||
<pre> | <pre> | ||
− | -- Execute this on one of the nodes | + | -- Execute this on one of the nodes. |
SQL> alter system set cluster_database=false scope=spfile; | SQL> alter system set cluster_database=false scope=spfile; | ||
Line 30: | Line 30: | ||
$ srvctl stop database -d DB11G | $ srvctl stop database -d DB11G | ||
− | -- Startup MOUNT | + | -- Startup in MOUNT mode. |
SQL> startup mount | SQL> startup mount | ||
ORACLE instance started. | ORACLE instance started. | ||
Line 105: | Line 105: | ||
$ srvctl stop database -d DB11G | $ srvctl stop database -d DB11G | ||
− | + | SQL> startup mount | |
ORACLE instance started. | ORACLE instance started. | ||
Line 115: | Line 115: | ||
Database mounted. | Database mounted. | ||
− | -- | + | -- Let's flashback to a RESTORE POINT created earlier. |
SQL> flashback database to restore point BEFORE_FLASHBACK; | SQL> flashback database to restore point BEFORE_FLASHBACK; | ||
Line 124: | Line 124: | ||
Database altered. | Database altered. | ||
− | -- Verify that table created after the restore point is no longer there. | + | -- Verify that the table created after the restore point is no longer there. |
SQL> select * from tab where tname='A'; | SQL> select * from tab where tname='A'; | ||
Revision as of 20:40, 6 March 2011
What is Flashback Database?
Coming soon
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