FLASHBACK DEPEND ON UNDO DATA
-conditions:
UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=900 #(in seconds 900 equal 15min, if value is extended UNDO tablespace will be increased also)
only for DML because FLASHBACK use data directory, but DDL cause ORA-01466
only for DELETE because TRUNCATE are not write in UNDO tablespace
recover delete rows:
INSERT INTO [table] (SELECT * FROM [table] AS OF TIMESTAMP TO_TIMESTAMP(’2008-01-01 10:00:00′,’YYYY-MM-DD HH24:MI:SS’) MINUS SELECT * FROM [table];
recover table:
FLASHBACK TABLE [table] TO TIMESTAMP TO_TIMESTAMP(’2008-01-01 10:00:00′,’YYYY-MM-DD HH24:MI:SS’);
SELECT versions_xid FROM [table] VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP ( '2008-01-01 08:00:00′,’YYYY-MM-DD HH24:MI:SS’) and to_timestamp ( '2008-01-01 08:10:00′,’YYYY-MM-DD HH24:MI:SS’);
SLECT * FROM flashback_transaction_query WHERE xid = HEXTORAW ('[above’]);
FLASHBACK DEPEND ON FLASHBACK LOGS
– conditions:
SELECT log_mode,flashback_on FROM V$DATABASE;
ARCHIVELOG mode is necessary, in parameter LOG_ARCHIVE_DEST_n
DB_RECOVERY_FILE_DEST #init param for flashback logs space
DB_RECOVERY_FILE_DEST_SIZE #init param for flasback logs size
DB_FLASHACK_RETENTION_TARGET #init param for flashback log time store
STARTUP MOUNT
ALTER DATABASE FLASHBACK ON
recovery:
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1 | TO TIMESTAMP to_timestamp(’2009-09-28 11:00′,’yyyy-mm-dd hh24:mi’) | TO SCN [scn#];
* ALTER DATABASE OPEN READ ONLY;
* check data by SELECT command
* SHUTDOWN IMMEDIATE;
* STARTUP MOUNT;
* ALTER DATABASE OPEN RESETLOGS;
futhermore:
SELECT estimated_flashback_size FROM v$flashback_database_log; #estimate size for flashback logs in Bytes
DB_FLASHBACK_RETENTION_TARGET #estimate depends on present load
SELECT oldest_flashback_scn, oldest_flashback_time FROM v$flashback_database_log; #oldest point which recovery is possible (if you want to chec, delete oldest flashback log and oldest point will change)
SELECT * FROM v$flash_recovery_area_usage;
SELECT * FROM v$recovery_file_dest;
SELECT SCN_TO_TIMESTAMP(10213123) FROM DUAL;
RECYCLEBIN
– delete table:
DROP TABLE [table_name]
– check „recyclebin”
SHOW RECYCLEBIN
– recover
FLASHBACK TABLE [table_name] TO BEFORE DROP;
– delete without „recyclebin”
DROP TABLE [table_name] PURGE;
– purge „recyclebin”
PURGE TABLE [table_name];
PURGE TABLESPACE [ts_name];
PURGE TABLESPACE [ts_name] USER [user];
PURGE RECYCLEBIN
PURGE DBA_RECYCLEBIN;
