Menu Bar

Tuesday, November 20, 2012

FLASHBACK Technology in Oracle Databases (Part 1)


"If it helps, I can travel in time as well" - The Tenth Doctor, referring to the TARDIS
Today we're talking about Time Travel in Oracle Databases.

I almost made a reference in the title and quote of this post about Mr. Peabody and the Way Back Machine but that would have perhaps shown my age - besides, quoting The Doctor is way cooler.

The Doctor and the Tardis

I recently found myself in a situation where I needed to go back in time. I was rolling forward a copy of a database to a particular point in time from a backup. I needed to stop at that particular point in time because the project manager wanted me to run row counts on the 1500 odd tables in the "before" and "after" databases and I had taken the "before" at that specific point in time.

During my recovery operation I fat fingered the UNTIL TIME clause and went past my stopping point.

After the expected cursing had died down I started wishing I had a TARDIS of my very own. Apart from the coolness factor of being able to zip back and forth through time at will, being able to go back about 15 minutes and fix my mistake would have been a miracle in and of itself.

It was then that I remembered FLASHBACK and it became my own TARDIS - Time and Relational Data in Schemas.

What is Flashback Query

First introduced in Oracle 9i, Flashback Query enables as to view the database as it existed at a point in time in the recent past. In Oracle 9i and onwards, this is implemented via the DBMS_FLASHBACK package.

This functionality was enhanced in Oracle 9i Release 2 with the addition of the "AS OF TIMESTAMP" clause to the SELECT statement.

Prerequisites and Limitations

There are some prerequisites to use Flashback Query

  • Initialization parameter requirement: undo_management = auto
  • Initialization parameter requirement: undo_retention = nnnn (maximum number of seconds to be able to query into the past)
  • Initialization parameter requirement: undo_tablespace = <undo_tablespace_name>
  • FLASHBACK (specific tables) or FLASHBACK ANY TABLE system privilege
  • EXECUTE privilege on DBMS_FLASHBACK

There are some restrictions that you should be aware of as well:

  • Prior to 11g, DDL that alters the structure of a table invalidates old UNDO data, rendering Flashback Query inoperative. Post 11g, most DDL is supported.
  • Timestamps map to an SCN (System Change Number) Value. These are internally updated every 5 minutes. If operating within that window, query results may be inaccurate if using timestamps (use SCN's for increased accuracy).
  • You must DISABLE/ENABLE the flashback mode before changing time windows.
  • Only data state is affected. Any table that has had a DDL change will reflect the most recent state of the table.
  • You cannot use Flashback Query on V$ views, but you can use it on catalog tables like USER_TABLES.
  • The SYS user cannot use DBMS_FLASHBACK but can use AS OF TIMESTAMP in a SELECT.
  • DML or DDL operations cannot be performed in Flashback mode. Open a cursor, fetch the data, end the flashback session and manipulate the data or use INSERT INTO ... SELECT AS OF TIMESTAMP constructs.
  • The undo_retention parameter is a target - it is not a guarantee of data availability.

Demo Time

The first thing we would need to do is to make sure that the user we're going to use has the correct permissions so we'd want to execute the following script:


create user demo identified by demo default tablespace users;
grant create session, flashback any table, create table to demo;
grant execute on dbms_flashback to demo;
alter user demo quota unlimited on users;

Next, we will check our parameters to make sure that automatic undo is turned on and that we're archiving:


SQL> connect / as sysdba
Connected.
SQL> show parameter undo

NAME                         TYPE        VALUE
---------------------------- ----------- ---------------
undo_management              string      AUTO
undo_retention               integer     3600
undo_tablespace              string      UNDOTBS1

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED                              

After that, let's create a table and load a little data and validate what we have. At the end of the inserts, we'll commit the changes, grab our current SCN and our current date and time.


SQL> conn demo/demo
Connected.

SQL> create table prez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into prez values ('Barack Obama', '2008');

1 row created.

SQL> insert into prez values ('George W. Bush', '2000');

1 row created.

SQL> insert into prez values ('Bill Clinton', '1992');

1 row created.

SQL> insert into prez values ('George H. W. Bush', '1988');

1 row created.

SQL> insert into prez values ('Ronald Reagan', '1980');

1 row created.

SQL> select * from Prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> commit;

Commit complete.

SQL> alter session set nls_date_Format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> set serveroutput on
SQL> var scn number;
SQL> exec :scn := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE 
-------------------
2012-11-20:22:49:06

Next, we'll run an update where we "accidentally" update all the rows in the table to an incorrect value. We'll also grab a timestamp here.


SQL> update prez set elected = '2013';

5 rows updated.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2012-11-20:22:49:32

With that done, it's now time to check the table out with current values and values as they were at a specific SCN. The first way uses the DBMS_FLASHBACK package to view the data as it was at that first SCN we assigned to the variable :scn. Then, we do it again using SELECT ... AS OF SCN.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_system_change_number(:scn);

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> select * from prez as of scn :scn;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

For the next demo, we'll do effectively the same thing, using Timestamps instead of SCN's. I made sure to wait at least 5 minutes so that the internal SCN to TIMESTAMP mapping was updated correctly and we went and did round 2.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_time(to_date('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:32','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

Finally, there's another clause we can add to the SELECT statement to show what versions of a row may exist. You can specify SELECT ... VERSIONS BETWEEN.

We start by updating one row of the table, getting the new times and SCN's and then running the queries.


SQL> update prez set elected = '2008' where name = 'Barack Obama';

1 row updated.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                                               
2012-11-20:23:11:54                                               

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801                                              

PL/SQL procedure successfully completed.

SQL> var scn2 number;
SQL> exec :scn2 := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn2));
SCN = 820456                                         

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  versions between scn 818801 and 820456 
  3  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

SQL> select sysdate from dual;

SYSDATE                                               
-------------------                                               
2012-11-20:23:19:24                                               

SQL> select * from prez 
  2  versions between timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS')
  4  and to_timestamp('2012-11-20:23:20:00','YYYY-MM-DD:HH24:MI:SS')
  5  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

Conclusion

One of the important things to note here is that while you're in Flashback mode, no DML is allowed. One way to get around that is to use SELECT ... AS OF to create a temporary copy of the data you need and then work from there to correct it.

The following would work:


create table temp_prez as select * from prez as of scn :scn;

From there you'd be able to compare current and old data and update accordingly as needed.

For further information on Flashback Technology in Oracle Databases, be sure to check the Oracle Documentation.

Next Time

Next time, we'll dig a little deeper into the features added in Oracle 10g and later, specifically Flashback Table, Flashback Drop and Flashback Database.

2 comments:

  1. Do "AS OF SCN" queries work remotely?

    ReplyDelete
    Replies
    1. What do you mean remotely ? Over a database link ? Or from a remote client connected to the machine as opposed to being directly logged into the machine ?

      Delete