"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.
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 STARTEDAfter 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:32With 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 1980For 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
ConclusionOne 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.
