Flashback - Revision history

Stuart: /* Copy old data into a new table */

2016-01-06T17:20:30Z

‎Copy old data into a new table

2014-11-21T19:14:10Z

‎How far back can we flashback the database?

2013-02-07T15:36:13Z

2013-01-04T23:37:16Z

‎Enable flashback on Standby database

2012-11-11T12:18:13Z

‎Put a session back in time

2012-11-11T10:52:36Z

2012-11-11T10:12:22Z

‎Set the database recovery directory and size

2012-11-11T10:12:02Z

2012-11-11T09:44:20Z

‎Put a session back in time

2012-11-11T00:10:53Z

@@ Line 49: / Line 49: @@
   alter system set db_recovery_file_dest_size=<size> scope=both;
-===Copy old data into a new table===
+===Use flashback to recover data deleted from a table===
-*Create an empty copy of the source table
+<pre>
- create table old_table_data
+create table as_it_was_before
- tablespace ts_data
+as select *
- as select *
+from ruined_table
- from ruined_table
+as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );
- where rownum < 1;
+</pre>
-*Insert the flashback data into it
 ===Enable flashback on Standby database===
   select flashback_on from v$database;

@@ Line 73: / Line 73: @@
 ===How far back can we flashback the database?===
 <pre>
 select	oldest_flashback_scn scn
-,	to_char(oldest_flashback_time, 'hh24:mi:ss dd/mm/yyyy') oldest_time
+,	to_char(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_time
 from	v$flashback_database_log
+/
 </pre>

← Older revision		Revision as of 15:36, 7 February 2013
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;

← Older revision		Revision as of 23:37, 4 January 2013
Line 33:		Line 33:
	alter system set db_flashback_retention_target=2880 scope=both;		alter system set db_flashback_retention_target=2880 scope=both;
	alter database recover managed standby database cancel;		alter database recover managed standby database cancel;
		+
		+	===How far back can we flashback the database?===
		+	<pre>
		+	select oldest_flashback_scn scn
		+	, to_char(oldest_flashback_time, 'hh24:mi:ss dd/mm/yyyy') oldest_time
		+	from v$flashback_database_log
		+	/
		+	</pre>

@@ Line 4: / Line 4: @@
   exec dbms_flashback.disable;
 ===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.
   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===
   alter system set db_recovery_file_dest='<path>' scope=both;

← Older revision		Revision as of 10:52, 11 November 2012
Line 22:		Line 22:
	select * 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' );
		+	===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;