Difference between revisions of "Flashback"

From dbawiki
Jump to: navigation, search
(→‎Put a session back in time)
(→‎Copy old data into a new table)
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
===Enable Flashback while database is open (11g+)===
 +
<pre>
 +
SYS@EUDSTGP2> show parameter recover
 +
 +
NAME                                TYPE        VALUE
 +
------------------------------------ ----------- ------------------------------
 +
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>
 +
</pre>
 
===Enable flashback===
 
===Enable flashback===
 
  exec dbms_flashback.enable;
 
  exec dbms_flashback.enable;
Line 4: Line 41:
 
  exec dbms_flashback.disable;
 
  exec dbms_flashback.disable;
 
===Put a session back in time===
 
===Put a session back in time===
−
Note: undo_management must be auto
+
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.
 
Note: Take a look at undo_retention to get an idea of how far back you might be able to go.
 
  exec dbms_flashback.enable_at_time(to_date('24-AUG-2012 12:00:00', 'DD-MON-YYYY HH24:MI:SS'));
 
  exec dbms_flashback.enable_at_time(to_date('24-AUG-2012 12:00:00', 'DD-MON-YYYY HH24:MI:SS'));
  
−
===Copy old data into a new table===
+
===Set the database recovery directory and size===
−
*Create an empty copy of the source table
+
  alter system set db_recovery_file_dest='<path>' scope=both;
−
  create table old_table_data
+
  alter system set db_recovery_file_dest_size=<size> scope=both;
−
  tablespace ts_data
+
 
−
as select *
+
===Use flashback to recover data deleted from a table===
−
from ruined_table
+
<pre>
−
where rownum < 1;
+
create table as_it_was_before
−
*Insert the flashback data into it
+
as select *
−
insert into old_table_data
+
from ruined_table
−
select * from ruined_table
+
as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );
−
as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );
+
</pre>
 +
 
 +
===Enable flashback on Standby database===
 +
select flashback_on from v$database;
 +
alter system set db_recovery_file_dest_size=50G scope=both;
 +
alter system set db_recovery_file_dest=’/<directory>/’
 +
shutdown immediate;
 +
startup mount;
 +
alter database flashback on;
 +
show parameter db_flashback_retention_target;
 +
alter system set db_flashback_retention_target=2880 scope=both;
 +
alter database recover managed standby database cancel;
 +
 
 +
===How far back can we flashback the database?===
 +
<pre>
 +
col oldest_flashback_scn for 99999999999999999
 +
select oldest_flashback_scn scn
 +
, to_char(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_time
 +
from v$flashback_database_log
 +
/
 +
</pre>

Latest revision as of 17:20, 6 January 2016

Enable Flashback while database is open (11g+)[edit]

SYS@EUDSTGP2> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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>

Enable flashback[edit]

exec dbms_flashback.enable;

Disable flashback[edit]

exec dbms_flashback.disable;

Put a session back in time[edit]

Note: undo_management must be auto
Note: Take a look at undo_retention to get an idea of how far back you might be able to go. 

exec dbms_flashback.enable_at_time(to_date('24-AUG-2012 12:00:00', 'DD-MON-YYYY HH24:MI:SS'));

Set the database recovery directory and size[edit]

alter system set db_recovery_file_dest='<path>' scope=both;
alter system set db_recovery_file_dest_size=<size> scope=both;

Use flashback to recover data deleted from a table[edit]

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' );

Enable flashback on Standby database[edit]

select flashback_on from v$database;
alter system set db_recovery_file_dest_size=50G scope=both;
alter system set db_recovery_file_dest=’/<directory>/’
shutdown immediate;
startup mount;
alter database flashback on;
show parameter db_flashback_retention_target;
alter system set db_flashback_retention_target=2880 scope=both;
alter database recover managed standby database cancel;

How far back can we flashback the database?[edit]

col oldest_flashback_scn for 99999999999999999
select	oldest_flashback_scn scn
,	to_char(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_time
from	v$flashback_database_log
/
Retrieved from "https://mailnest.com/dbawiki/index.php?title=Flashback&oldid=2144"