Differences

This shows you the differences between two versions of the page.

--- flashback [2018/12/08 12:49] – created 0.0.0.0
+++ flashback [2019/01/30 11:32] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Flashback ======
+==== Enable Flashback while database is open (11g+) ====
+<code>
+SYS@EUDSTGP2> show parameter recover
-=====Enable Flashback while database is open (11g+)=====
+NAME                                 TYPE        VALUE
-<code>0@@</code>
+------------------------------------ ----------- ------------------------------
-=====Enable flashback=====
+db_recovery_file_dest                string      /oracle/EUDSTGP2/fra
+db_recovery_file_dest_size           big integer 4122M
+db_unrecoverable_scn_tracking        boolean     TRUE
+recovery_parallelism                 integer     0
+SYS@EUDSTGP2> show parameter flash
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+db_flash_cache_file                  string
+db_flash_cache_size                  big integer 0
+db_flashback_retention_target        integer     1440
+SYS@EUDSTGP2> select flashback_on, status from v$database, v$instance;
+FLASHBACK_ON       STATUS
+------------------ ------------
+NO                 OPEN
+SYS@EUDSTGP2> alter database flashback on;
+Database altered.
+SYS@EUDSTGP2> select flashback_on, status from v$database, v$instance;
+FLASHBACK_ON       STATUS
+------------------ ------------
+YES                OPEN
+SYS@EUDSTGP2>
+</code>
+==== Enable flashback ====
+<code>
  exec dbms_flashback.enable;
-=====Disable flashback=====
+</code>
+==== Disable flashback ====
+<code>
  exec dbms_flashback.disable;
-=====Put a session back in time=====
+</code>
+==== Put a session back in time ====
 Note: undo_management must be auto<br>
 Note: Take a look at undo_retention to get an idea of how far back you might be able to go.
+<code>
  exec dbms_flashback.enable_at_time(to_date('24-AUG-2012 12:00:00', 'DD-MON-YYYY HH24:MI:SS'));
+</code>
-=====Set the database recovery directory and size=====
+==== Set the database recovery directory and size ====
+<code>
  alter system set db_recovery_file_dest='<path>' scope=both;
  alter system set db_recovery_file_dest_size=<size> scope=both;
+</code>
-=====Use flashback to recover data deleted from a table=====
+==== Use flashback to recover data deleted from a table ====
-<code>1@@</code>
+<code>
+create table as_it_was_before
+as select *
+from ruined_table
+as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );
+</code>
-=====Enable flashback on Standby database=====
+==== Enable flashback on Standby database ====
+<code>
  select flashback_on from v$database;
  alter system set db_recovery_file_dest_size=50G scope=both;
@@ Line 29: / Line 76: @@
  alter system set db_flashback_retention_target=2880 scope=both;
  alter database recover managed standby database cancel;
+</code>
+==== How far back can we flashback the database? ====
+<code>
+col oldest_flashback_scn for 99999999999999999
+select\toldest_flashback_scn scn
+,\tto_char(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_time
+from\tv$flashback_database_log
+/
+</code>
-=====How far back can we flashback the database?=====
-<code>2@@</code>