Oracle 11g Flashback examples

We review briefly in this post flashback query functionality provided by the Oracle DB Manager since version 9i.

Basically it is a type of sql that accesses data that existed in the database at an earlier time, but at the time running the sql may not exist or have been altered. To do this, Oracle uses the data that are available during a time segment UNDO. This segment, as is known, the data stored before a series of modifications. It is used to ensure consistency in the reading of a consultation prior to the confirmation of the changes (commit) and can be used in a possible recovery (rollback).

The ruling allows us to see flashback query data in the table that have been deleted or modified.Running a flashback queryto access data from a picture of consistent data at a certain point, this time specifying to the system or the system change number (SCN). The database must be set to Automatic Undo Management work (AUM). For this review the following parameters:

UNDO_MANAGEMENT = auto

undo_tablespace = UNDOTBS001 (tablespace that hosts the segment undo)

UNDO_RETENTION = 3600 (time in seconds that we retained the data in the segment undo)

Must be taken into account with respect to the parameter if the UNDO_RETENTION UNDO tablespace is not large enough to keep all transactions that time, the database manager is going to override.Also, consider that in order to execute the command flashback query must have permissions on the package BDMS_FLASHBACK. To do this:

sys> grant execute on dbms_flashback to user1;

— query table data from specific point in time

1 2 3

SELECT * FROM matthiash AS OF TIMESTAMP TO_TIMESTAMP('2013-05-03 14:00:00', 'YYYY-MM-DD HH24:MI:SS');

— query table data from one hour ago

1 2 3

SELECT * FROM matthiash AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

— query all versions of one or more records

1 2 3 4 5 6 7

SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation,m_id FROM matthiash VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS')

— get the user name of the user who modified records

1 2 3 4 5 6 7 8 9 10

GRANT SELECT ANY TRANSACTION TO matthiash; SELECT xid, logon_user FROM flashback_transaction_query WHERE xid IN ( SELECT versions_xid FROM matthiash VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS') ) AND xid = HEXTORAW('08000800C6070000');

— flashback entire table to specific point in time

1 2 3 4

ALTER TABLE matthiash ENABLE ROW MOVEMENT FLASHBACK TABLE matthiash TO TIMESTAMP TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS');

— creating restore point and flashback table to restore point

1 2 3 4 5 6 7 8 9

CREATE RESTORE POINT before_table_update; SELECT NAME, SCN, TIME FROM V$RESTORE_POINT; UPDATE matthiash SET m_id=0; FLASHBACK TABLE matthiash.matthiash TO RESTORE POINT before_table_update;

— recover dropped table from recycle bin (note: does not work for all types of tables)

1 2 3 4 5 6 7

DROP TABLE matthiash; SELECT * FROM recyclebin; FLASHBACK TABLE matthiash TO BEFORE DROP; FLASHBACK TABLE "BIN$29FWNr5ICjbgQ68BEqzYLw==$0" TO BEFORE DROP RENAME TO matthiash;

— recover entire database to restore point, SCN or point in time

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE RESTORE POINT before_table_drop GUARANTEE FLASHBACK DATABASE; DROP TABLE matthiash PURGE; SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO RESTORE POINT BEFORE_TABLE_DROP; FLASHBACK DATABASE TO SCN 46963; FLASHBACK DATABASE TO TIME "TO_TIMESTAMP('2013-05-03 15:00:00', 'YYYY-MM-DD HH24:MI:SS')"; ALTER DATABASE OPEN RESETLOGS; Here is an example. Suppose you want to inquire about a table that contains bills cllowing and want to access the customer data we have previously deleted: sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema; fecha_sistema ------------------------- 12-09-2011 12:05 sys> delete from t_facturas WHERE cod_cliente = '00125 '; 4 rows deleted sys> commit;    Half an hour later by running: sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema fecha_sistema ------------------------- 12-09-2011 12:35 sys> exec dbms_flashback.enable_at_time (to_date ('12-09-2011 12:05, 'DD-MM-YYYY HH24: MI')); sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema fecha_sistema ------------------------- 12-09-2011 12:35 sys> exec dbms_flashback.enable_at_time (to_date ('12-09-2011 12:05, 'DD-MM-YYYY HH24: MI')); PL / SQL procedure successfully completed. sys> select * from WHERE t_facturas cod_cliente = '00125 '; ... ... ... ... ... ... ... & Hellip; 4 selected records. sys> execute dbms_flasback.disable; sys> select count (*) from t_facturas WHERE cod_cliente = '00125 '; count (*) ------ 0 Can be obtained by accessing the same change number SCN: sys> dbms_flashback.get_system_change_number select from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1307125 sys> exec dbms_flashback.enable_at_system_change_number (1307125); PL / SQL procedure successfully completed It is also possible to use the statement 'select ...as of ... ': sys> select * from WHERE t_facturas cod_cliente = '00125 'TO_TIMESTAMP as of timestamp ('12-09-2011 12:05', 'DD-MM-YYYY HH24: MI'); sys> select * from WHERE t_facturas cod_cliente = '00125 'as of scn 1307125; Keep in mind that while the session is in Flashback Query mode, we can only execute SELECT statements. The update statements (insert, delete and update) are not allowed. In the daily work with this option is useful to use temporary tables to work with the retrieved data: sys> create table t_facturas_antas (select * from WHERE t_facturas cod_cliente = '00125 'TO_TIMESTAMP as of timestamp ('12-09-2011 12:05,' DD-MM-YYYY HH24: MI '); Other options Flashback Query Below are some features that Oracle provides operations related to Flashback Query: Flashback Version Query: Access to historic changes to a table. Flashback Transaction Query: Access to historical changes in a particular transaction. Flashback Table: Access to data above, but for a single table. Flashback Drop: Retrieve a deleted table ('Recycle Bin'). Flashback Database: Lets leave the DB as it was in a past time.Similar to restore a backup, but with the time constraints of the processes flashback, but much faster to retrieve the backup copy. You must have enabled flashback mode and the flash recovery area. Some of these options require that the manager is 'Enterprise Edition'. It is, as mentioned, a very useful statement with multiple options "rewind" and that we can take more than a pinch. Select ratingPoorOkayGoodGreatAwesomeGive it 6/10Give it 7/10Give it 8/10Give it 9/10Give it 10/10 Poor Okay Good Great Awesome Give it 6/10 Give it 7/10