{"id":36,"date":"2009-03-17T08:27:00","date_gmt":"2009-03-17T06:27:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2009\/03\/17\/oracle-flashback\/"},"modified":"2010-08-25T23:26:53","modified_gmt":"2010-08-25T21:26:53","slug":"oracle-flashback","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2009\/03\/17\/oracle-flashback\/","title":{"rendered":"ORACLE FLASHBACK"},"content":{"rendered":"
\n
FLASHBACK DEPEND ON UNDO DATA<\/span><\/p>\n
\n<\/div>\n
-conditions:<\/span><\/div>\n
UNDO_MANAGEMENT=AUTO<\/span>
<\/span><\/div>\n
UNDO_RETENTION=900 #(in seconds 900 equal 15min, if value is extended UNDO tablespace will be increased also)<\/div>\n
only for DML because FLASHBACK use data directory, but DDL cause ORA-01466<\/div>\n
only for DELETE because TRUNCATE are not write in UNDO tablespace<\/div>\n
recover delete rows:<\/span><\/div>\n

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];<\/span><\/p>\n

recover table:<\/span><\/div>\n
FLASHBACK TABLE [table] TO TIMESTAMP TO_TIMESTAMP(’2008-01-01 10:00:00′,’YYYY-MM-DD HH24:MI:SS’);<\/div>\n
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’);<\/div>\n
SLECT * FROM flashback_transaction_query WHERE xid = HEXTORAW ('[above’]);<\/div>\n
\n
FLASHBACK DEPEND ON FLASHBACK LOGS<\/span><\/p>\n
\n<\/div>\n
<\/p>\n
– conditions:<\/span><\/div>\n
SELECT log_mode,flashback_on FROM V$DATABASE;<\/div>\n
ARCHIVELOG mode is necessary, in parameter LOG_ARCHIVE_DEST_n<\/p>\n
DB_RECOVERY_FILE_DEST #init param for flashback logs space<\/p>\n<\/div>\n

<\/p>\n

DB_RECOVERY_FILE_DEST_SIZE #init param for flasback logs size
DB_FLASHACK_RETENTION_TARGET #init param for flashback log time store
STARTUP MOUNT<\/p>\n<\/div>\n<\/div>\n
ALTER DATABASE FLASHBACK ON<\/div>\n

recovery:<\/span><\/div>\n

STARTUP MOUNT<\/div>\n
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1 | TO TIMESTAMP to_timestamp(’2009-09-28 11:00′,’yyyy-mm-dd hh24:mi’) | TO SCN [scn#];<\/div>\n
* ALTER DATABASE OPEN READ ONLY;
* check data by SELECT command
* SHUTDOWN IMMEDIATE;
* STARTUP MOUNT;
* ALTER DATABASE OPEN RESETLOGS;<\/div>\n
futhermore:<\/span>
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<\/div>\n

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)
<\/span>SELECT * FROM v$flash_recovery_area_usage;
SELECT * FROM v$recovery_file_dest;<\/p>\n

SELECT SCN_TO_TIMESTAMP(10213123) FROM DUAL; <\/p>\n

\n
\n
\n
RECYCLEBIN<\/span><\/div>\n
\n

– 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;<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[80],"tags":[42],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-flashback"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":2,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":226,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/36\/revisions\/226"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}