Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 1, 2007

Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management

Filed under: Oracle 10g New Features,Oracle Concepts and Architecture — H.Tonguç Yılmaz @ 8:00 am

Flashback technology provides a set of features to view and rewind data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, perform change analysis, and perform self-service repair to recover from logical corruption while the database is online.

Flashback technology provides a SQL interface to quickly analyze and repair human errors. Flashback provides fine-grained analysis and repair for localized damage such as deleting the wrong customer order. Flashback technology also enables correction of more widespread damage, yet does it quickly to avoid long downtime. Flashback technology is unique to the Oracle Database and supports recovery at all levels including row, transaction, table, tablespace, and database.

Oracle Flashback Versions Query is an extension to SQL that can be used to retrieve the versions of rows in a given table that existed in a specific time interval. Oracle Flashback Versions Query returns a row for each version of the row that existed in the specified time interval. For any given table, a new row version is created each time the COMMIT statement is executed.

Oracle Flashback Table enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table or set of tables that has been erroneously modified by a user or application. In most cases, Flashback Table alleviates the need for administrators to perform more complicated point-in-time recovery operations. Even after a flashback, the data in the original table is not lost; it can later be reverted back to the original state.

With 9i this feature was marketed as “Oracle found the time machine” :) But with 9i to flashback was not so easy, needed DBA privileges etc. After 10g with SELECT statements we are not able to get only a picture but a movie of our data in time. Ok lets work on an example, seeing is necessary for believing, we are not marketing here :)


create table flashback_test ( c1 number, c2 date ) nologging ;

insert into flashback_test values ( 1, sysdate ) ;
commit ;
exec dbms_lock.sleep(15);

update  flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);

update  flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);

delete flashback_test ;
commit ;
exec dbms_lock.sleep(15);

select versions_starttime, versions_endtime, versions_xid, versions_operation, c1
from  flashback_test versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;

VERSIONS_STARTTIME                                VERSIONS_ENDTIME                                  VERSIONS_XID     VERSIONS_OPERATION         C1
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ----------
08/10/2007 08:18:29                               08/10/2007 08:18:44                               08002200A6040000 U                           2
08/10/2007 08:18:44                               08/10/2007 08:18:59                               03002300C7040000 U                           4
08/10/2007 08:18:59                                                                                 06002900AC040000 D                           4
                                                  08/10/2007 08:18:29                                                                            1

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete

UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."FLASHBACK_TEST"("C1","C2") values ('4',TO_DATE('08/10/2007', '

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('03002300C7040000') ; -- update 4

UNDO_SQL
--------------------------------------------------------------------------------
update "HR"."FLASHBACK_TEST" set "C1" = '2' where ROWID = 'AAAONoAAEAAAAGWAAA';

The maximum of there versions e can get are dependent on UNDO_RETENTION parameter of the database. TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP flashback functions are used in SQL or PL/SQL as needed. In this example we are looking for the sum of the salaries on the employees in time;


set serveroutput on
DECLARE
   l_scn NUMBER;
   l_timestamp TIMESTAMP;
BEGIN
   l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48);
   dbms_output.put_line('l_scn '||l_scn);
   l_timestamp := SCN_TO_TIMESTAMP(l_scn);
   dbms_output.put_line('l_timestamp '||l_timestamp);
END;
/

SELECT sum(salary) FROM employees
AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT sum(salary) FROM employees -- TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48)
AS OF TIMESTAMP SCN_TO_TIMESTAMP(1531264);

SQL>

l_scn 1531264
l_timestamp 04/01/2007 08:01:29,000000

PL/SQL procedure successfully completed

SUM(SALARY)
-----------
691400

SUM(SALARY)
-----------
691400

Another related subject is PURGE command and RECYCLEBIN with 10g, after 10g when you drop a table by default it is stored in recyclebin like windows. And you can restore the table with flashback command easily;


drop table RECYCLETEST purge ;
create table RECYCLETEST (c1 number) ;
select * from tab where tname like '%REC%';

-- lets drop the table, this must be seen as an accident :)
drop table recycletest;
select * from tab where tname like '%BIN%';
show recyclebin

-- ok lets restore back
FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
select * from tab where tname like '%REC%';

SQL>

Table dropped

Table created

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Table dropped

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$6V3qqZH/Q1ascMRsn5uImg==$0 TABLE

Done

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Similar to windows you may want to free the recyclebin in time.

* For an index, table or tablespace you can use;

PURGE TABLE TEST; or PURGE TABLE “BIN$04LhcpndanfgMAAAAAANPw==$0";
purge index in_test1_01;
PURGE TABLESPACE USERS; or PURGE TABLESPACE USERS USER SCOTT;

* For the all objects belonging to the user ;

PURGE RECYCLEBIN;

* As a DBA all objects;

PURGE DBA_RECYCLEBIN;

Some important notes related to this subject are;

* When you flashback a table the triggers and indexes are not returned with their original names. This can be fixed by running an extra alter index or alter trigger statement.
* Not only you may want to flashback a table that is dropped, like Versions query a whole table can be flashbacked in time;

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

* If there are tables with same names in Recyclebin they can be restores with different names;

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

* Flashback technology includes the following features:
Oracle Flashback Query
Oracle Flashback Versions Query
Oracle Flashback Transaction Query
Oracle Flashback Table
Oracle Flashback Drop
Oracle Flashback Database — This provides a more efficient alternative to database point-in-time recovery, Check V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views also.
Oracle Flashback Restore Points
* You may want to check the Flashback General and Performance Tips

Oracle Flashback Technologies

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

References Used :
Oracle® Database Concepts 10g Release 2 (10.2)

About these ads

5 Comments »

  1. [...] 1- Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management 2- How shared pool works in Oracle and binding example 3- The Storage Hierarchy Summary in an Oracle Database [...]

    Pingback by Oracle Concepts and Architecture - Part 1 « H.Tonguç YILMAZ Oracle Blog — January 2, 2007 @ 6:18 am | Reply

  2. a summary comment;

    - for flashback database archivelog mode and flash recovery area configurations are mandatory,
    db_flashback_retention_target=2880 — default is 1440(this is minutes, 1 day)
    alter database flashback on;
    - with 9iR1 dbms_flashback_enable_at_time(timestamp); — time journey, from undo images, session level
    - with 9iR2 select … as of timestamp/scn .. — query level
    - with 10gR1 select … versions_xid .. version between scn minvalue and maxvalue — row level
    also select undo_sql from flashback_transaction_query where xid = hextoraw(”) — transaction level
    also flashback table .. to timestamp/scn ; — segment level, enable row movement needed
    also flashback table .. before drop ;
    also flashback database .. — will use flashback log files in the flash recovery area
    - with 10gR2 sql > flashback databse to before resetlogs;
    rman > reset database to incarnation 1;
    also flashback to a point in time before a resetlogs operations
    and also guarenteed restore points similar to savepoint of plsql(rollback to savepoint_a;)
    flashback database/table to restore point ..;
    v$respore_point

    http://sysdba.wordpress.com/2006/01/23/flashback-any-error-with-oracle-10g-release-2/

    - rvwr background process and flashback buffer cache area,
    - v$flashback_database_log – v$flashback_database_stat

    also for flashback recovery area need;

    - backup as copy database; rman will backup to flashback recovery area,
    - to swtich a database to asm using flashback recovery are and rman is the easiest way, first backup as copy database than swith to copy database and recover database. after this also online redo logs+control files and temp files need to be transfered,
    - backup recovery files/area backs up flashback recovery area, check advanced b&r guide for this strategy’s details;

    Comment by H.Tonguç Yılmaz — May 16, 2007 @ 4:04 am | Reply

  3. Alejandro Vargas’s Flashback Features Demos -

    http://static7.userland.com/oracle/gems/alejandroVargas/FlashbackTechnologies.pdf

    Comment by H.Tonguç Yılmaz — October 8, 2007 @ 6:21 pm | Reply

  4. [...] Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management [...]

    Pingback by 11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback « H.Tonguç Yılmaz - Oracle Blog — September 4, 2008 @ 9:40 pm | Reply

  5. Retrieve old versions of procedures with Flashback Query – http://uhesse.wordpress.com/2009/06/03/retrieve-old-versions-of-procedures-with-flashback-query/

    Comment by H.Tonguç Yılmaz — September 4, 2009 @ 11:35 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: