flashback query in oracle 9i
This short article introduces flashback query, a new feature of Oracle 9i. Flashback query enables us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before we or any other users made permanent changes to it.
Flashback query works in tandem with another new feature of Oracle 9i; automatic undo management, whereby Oracle manages our rollback segments for us. In fact, the term "rollback segments" is confined to history (or applies only to manual undo management at least). From 9i, we know these segments as "undo segments" and under automatic management, Oracle will create as many or as few as are required to satisfy our transaction workloads. This is, of course, within system limits; these being the size of the undo tablespace and a new parameter named undo_retention. This parameter specifies the number of seconds that Oracle should retain undo data for us.
Flashback query enables us to query the undo segments directly, either by SCN (System Change Number) or by timestamp. The means and ease of doing this changed dramatically between Oracle 9.0 and 9.2 and we shall examine both of them in this article. It is unlikely, however, that any users of 9i Release 2 (9.2) will wish to use the 9.0 method and we shall see why below.
requirements
To be able to use flashback query, we require the following system elements:
- undo_management=auto (set in pfile/spfile);
- undo_retention=n (set in pfile/spfile, where n is a positive number of seconds);
- undo_tablespace=[undo tablespace name] (set in pfile/spfile);
- FLASHBACK or FLASHBACK ANY system privilege; and
- EXECUTE on DBMS_FLASHBACK.
sample data
For our sample data we will create a test table and copy a few rows and columns of ALL_TABLES as follows.
SQL> CREATE TABLE t 2 AS 3 SELECT owner, table_name, tablespace_name 4 FROM all_tables 5 WHERE ROWNUM <= 5;
Table created.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ SYSTEM SYS CLU$ SYSTEM SYS OBJ$ SYSTEM SYS FILE$ SYSTEM SYS COL$ SYSTEM 5 rows selected.
A small note on new tables, which applies to both releases of 9i, is that it might not be possible to begin flashback queries against them immediately. Oracle recommends that we wait for approximately five minutes, which equates to the intervals at which the SCN is mapped to a timestamp. The SCN itself is incremented with every commit. Attempting to flashback query a new table before then is likely to result in ORA-01466: unable to read data - table definition has changed.
9i release 1: dbms_flashback
Flashback queries in Oracle 9.0 use calls to DBMS_FLASHBACK to enable and disable the feature either side of a SQL statement or the opening of a PL/SQL cursor. We'll move straight onto an example to show how this works. In the following example, we will update our data and commit the changes. We will then enable flashback query to a point in time before this change and then run a query against the sample table. Finally we will disable flashback query to enable us to resume "normal" query mode.
Note that when we enable flashback query, we provide either a timestamp or SCN. We will use the SCN for the 9.0 examples and timestamps (another new feature of 9i) for the 9.2 examples later on. Because a timestamp is mapped to an SCN number every five minutes, the SCN offers a much finer level of precision for flashback. We will begin, therefore, by capturing the current SCN and permanently updating our sample data.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scn 2 FROM dual;
SCN
----------
592967
1 row selected.
SQL> UPDATE t SET tablespace_name = LOWER(tablespace_name);
5 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ system SYS CLU$ system SYS OBJ$ system SYS FILE$ system SYS COL$ system 5 rows selected.
Now we can enable flashback query to the point in time before we changed our data (i.e. using the earlier SCN). Once in "flashback query mode", any queries we run will return data consistent with the SCN we enabled. We can see this below.
SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(592967);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ SYSTEM SYS CLU$ SYSTEM SYS OBJ$ SYSTEM SYS FILE$ SYSTEM SYS COL$ SYSTEM 5 rows selected.
We can now see our data in its original state (i.e. before we changed the TABLESPACE_NAME to lower-case). To leave "flashback query mode", we must disable it using DBMS_FLASHBACK as follows. We can now see the data in its current, post-update form.
SQL> exec DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ system SYS CLU$ system SYS OBJ$ system SYS FILE$ system SYS COL$ system 5 rows selected.
There are a number of uses for this feature, such as data recovery, adhoc change-tracking or point-in-time queries, but of course, this is restricted to the period specified by undo_retention (with some variance depending on transaction loads). In fact, Oracle suggests that it is feasible to build flashback query capabilities into our applications. We might view this as a somewhat ambitious claim for the technology, but as a short-term recovery mechanism, it is very useful.
9i release 2: as of [scn|timestamp]
As we saw in the previous section, flashback query in 9i Release 1 is somewhat involved. Oracle 9i Release 2 makes flashback query significantly easier by building the capability into the SQL FROM clause itself. In 9.2, we can query our data AS OF TIMESTAMP or AS OF SCN directly. Flashback query does not need to be explicitly enabled and disabled via package calls; it is invoked directly by this syntax.
We will repeat the earlier example but this time using 9.2 flashback query. Furthermore, we'll use a timestamp (another new feature of Oracle 9i) instead of an SCN. Note that the initial ALTER SESSION is simply a convenience to enable a consistent timestamp format mask without having to supply it explicitly.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';
Session altered.
SQL> SELECT LOCALTIMESTAMP 2 FROM dual;
LOCALTIMESTAMP ------------------------ 22-NOV-2002 21:31:01.750 1 row selected.
SQL> UPDATE t SET table_name = LOWER(table_name);
5 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS seg$ system SYS clu$ system SYS obj$ system SYS file$ system SYS col$ system 5 rows selected.
Now we can invoke flashback query as of the timestamp prior to our update.
SQL> SELECT * 2 FROM t AS OF TIMESTAMP TO_TIMESTAMP('22-NOV-2002 21:31:01.750');
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ SYSTEM SYS CLU$ SYSTEM SYS OBJ$ SYSTEM SYS FILE$ SYSTEM SYS COL$ SYSTEM 5 rows selected.
This is much easier! The extended FROM clause is simple and intuitive to use and is more likely to encourage developers to use flashback query. A particularly good use for this is for resetting test data during development and unit-testing. It is also a good short-term recovery tool for rectifying minor mistakes.
a note on flashback precision
As noted earlier, flashback query timestamps are mapped to SCNs, but only once every five minutes. This makes flashback queries with timestamps subject to precision errors. We can see the effect of this in our 9.2 flashback query results above. Our flashback query correctly returns the original upper-case TABLE_NAME data but it also returns the original upper-case TABLESPACE_NAME data also. This tells us that the SCN that Oracle mapped to our timestamp is from a time before we ran the 9.0 example.
Using an SCN, however, we can be more precise with our flashback query. We will take a guess that the SCN after our 9.0 example update will be a few greater than before we began (there is no other activity on this test system). Using this, we will try a 9.2 flashback query AS OF SCN.
SQL> SELECT * FROM t AS OF SCN 592969;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ system SYS CLU$ system SYS OBJ$ system SYS FILE$ system SYS COL$ system 5 rows selected.
We can now see the data as it existed after the first update (9.0 example) but before the second update (9.2). Using the SCN enabled us to be far more precise with our flashback query.
recovering data
Finally for this article, the 9i Release 2 flashback query syntax makes it much easier to recover data. Using the AS OF syntax, we can either update the table from the flashback query source or we can delete the current data and insert the flashback data. In the following example, we'll remove the current data and replace it with the data as it existed after our 9.0 examples (i.e. lower-case TABLESPACE_NAME).
SQL> DELETE FROM t;
5 rows deleted.
SQL> INSERT INTO t 2 SELECT * FROM t AS OF SCN 592969;
5 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
OWNER TABLE_NAME TABLESPACE_NAME --------------- --------------- -------------------- SYS SEG$ system SYS CLU$ system SYS OBJ$ system SYS FILE$ system SYS COL$ system 5 rows selected.
Data changes between current and flashback data can be identified quite simply by set queries using MINUS/UNION. If we need to search for changes to specific columns, we can join the current and flashback datasets together (we can also use the 9i FULL OUTER JOIN for this purpose). In the 9i Release 1 variant of flashback query, this would only be possible in PL/SQL. The sequence would be: enable flashback -> open flashback cursor -> disable flashback -> open current cursor -> fetch and compare data. As stated throughout this article, this simplicity of flashback query in 9i Release 2 makes this complex variant largely redundant.
flashback version query in oracle 10g
Flashback query was introduced in Oracle 9i. It provides a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.
This article introduces the new flashback version query. It assumes that readers are familiar with flashback query concepts. For an overview, including the necessary privileges required, read this oracle-developer.net article.
sample data
For the examples in this article, we will use a scratch table and some dummy data. The table is created as follows.
SQL> CREATE TABLE fbt
2 ( x INTEGER
3 , y TIMESTAMP
4 , z VARCHAR2(30) );
Table created.
We can now populate the table and make several changes to the data. We will add a row, update it a couple of times and finally delete it. We will include a short pause between each DML operation, but capture the timestamp before and after the updates for use in later examples. All DML operations will be committed, because new versions of data are only recorded following a commit. We'll begin with a single-row insert.
SQL> EXEC DBMS_LOCK.SLEEP(10);
PL/SQL procedure successfully completed.
SQL> INSERT INTO fbt VALUES (1, LOCALTIMESTAMP, 'Initial population');
1 row created.
SQL> COMMIT;
Commit complete.
Note that we included a sleep before we added any data to the table. This is recommended by Oracle to avoid ORA-01466: unable to read data - table definition has changed (this will only be an issue if the table is subject to flashback queries as soon as it is created).
We can now update our sample data. Before we do, however, we'll capture the timestamp to use later in this article. We will also sleep for ten seconds. Again, our DML must be committed.
SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';
Session altered.
SQL> EXEC DBMS_LOCK.SLEEP(10);
PL/SQL procedure successfully completed.
SQL> SELECT LOCALTIMESTAMP AS lower_bound FROM dual;
LOWER_BOUND
------------------------
10-AUG-2005 18:01:07.109
1 row selected.
SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'First update';
1 row updated.
SQL> COMMIT;
Commit complete.
Next, we update the data a second time and capture the timestamp after the commit.
SQL> EXEC DBMS_LOCK.SLEEP(10);
PL/SQL procedure successfully completed.
SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'Second update';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT LOCALTIMESTAMP AS upper_bound FROM dual;
UPPER_BOUND
------------------------
10-AUG-2005 18:01:17.125
1 row selected.
Finally, we will delete the data and commit the change.
SQL> EXEC DBMS_LOCK.SLEEP(10);
PL/SQL procedure successfully completed.
SQL> DELETE FROM fbt WHERE x = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
Following the sample data population, we should have no data in the FBT table, which we can verify as follows.
SQL> SELECT * FROM fbt;
no rows selected
flashback version query
Despite the fact that there is no data in FBT, we can now run some flashback version queries against it. This will enable us to view the data as it evolved between commits. Flashback version query is invoked using the new VERSIONS BETWEEN extension to the FROM clause. It takes two forms as follows:
- VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or
- VERSIONS BETWEEN SCN [lower bound] AND [lower bound].
The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. For our first flashback version query, we will attempt to retrieve all available data.
SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';
Session altered.
SQL> SELECT x, y, z
2 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
3 ORDER BY
4 y;
X Y Z
---------- ------------------------- -----------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update
1 10-AUG-2005 18:01:17.125 Second update
1 10-AUG-2005 18:01:17.125 Second update
4 rows selected.
We have seemingly generated four rows of data from one source record. What we are seeing, however, is the evolution of this single record in terms of all the values it has held over time (depending on its presence in the undo segments). However, we can see that the second update entry appears twice, yet we have no way of identifying why from the above output. Flashback version query therefore includes several pseudo-columns to describe each version of our data, which we can now use to determine the actual operations and change times.
versions pseudo-columns
As stated above, Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows:
- VERSIONS_STARTTIME (start timestamp of version);
- VERSIONS_STARTSCN (start SCN of version);
- VERSIONS_ENDTIME (end timestamp of version);
- VERSIONS_ENDSCN (end SCN of version);
- VERSIONS_XID (transaction ID of version); and
- VERSIONS_OPERATION (DML operation of version).
We can now include some of these pseudo-columns in our flashback version query as follows. Note the SCN metadata is excluded as we are using timestamps for the examples.
SQL> SELECT z
2 , VERSIONS_STARTTIME
3 , VERSIONS_ENDTIME
4 , VERSIONS_XID
5 , VERSIONS_OPERATION
6 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
7 ORDER BY
8 VERSIONS_ENDTIME;
Z VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION
-------------------- ------------------------- ------------------------- ---------------- ------------------
Initial population 10-AUG-2005 18:00:53.000 10-AUG-2005 18:01:05.000 040026008A010000 I
First update 10-AUG-2005 18:01:05.000 10-AUG-2005 18:01:14.000 040029008A010000 U
Second update 10-AUG-2005 18:01:14.000 10-AUG-2005 18:01:26.000 040027008A010000 U
Second update 10-AUG-2005 18:01:26.000 040028008A010000 D
4 rows selected.
This explains why we were seeing the second update row twice. If we look at the VERSIONS_OPERATION column, we can see that the second appearance of the final update record is actually the delete operation against it (specified by 'D'). Without the versions metadata, the Y timestamp column was actually confusing us into thinking we had two versions of the same record at the same time.
The metadata also gives us an indication that the delete operation was the final version of this data. The end timestamp of the version is NULL which tells us that there is no superceding record.
Interestingly, if we had not included a sleep between creating the FBT table and adding the single record, it would be likely (based on observations) that all VERSIONS_* pseudo-columns (except the ENDTIME and ENDSCN) would be NULL for the insert record.
flashback transaction query
Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as "flashback transaction queries" and require the SELECT ANY TRANSACTION system privilege. The view definition is as follows.
SQL> desc flashback_transaction_query
Name Null? Type
----------------------------- -------- --------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
The VERSIONS_XID pseudo-column gives us the key to this view (the XID column) for specific versions of data. Rather than filter on XID, we will look at all records currently available for our FBT table, concentrating on the more interesting UNDO_SQL column.
SQL> SELECT xid
2 , operation
3 , undo_sql
4 FROM flashback_transaction_query
5 WHERE table_owner = USER
6 AND table_name = 'FBT'
7 ORDER BY
8 start_timestamp;
XID OPERATION UNDO_SQL
---------------- --------- ------------------------------------------------------------
040026008A010000 INSERT delete from "SCOTT"."FBT" where ROWID = 'AAANCeAAEAAAAuXAAA'
;
040029008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
00:57.000'), "Z" = 'Initial population' where ROWID = 'AAANC
eAAEAAAAuXAAA';
040027008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
01:07.000'), "Z" = 'First update' where ROWID = 'AAANCeAAEAA
AAuXAAA';
040028008A010000 DELETE insert into "SCOTT"."FBT"("X","Y","Z") values ('1',TO_TIMEST
AMP('10-AUG-2005 18:01:17.000'),'Second update');
4 rows selected.
The UNDO_SQL column shows us the reversal of every change we made to our sample record. Remember that we followed a sequence of INSERT-UPDATE-UPDATE-DELETE. The values in the OPERATION column show us this, reading from the top-down. The reversal of this sequence can be viewed by reading the UNDO_SQL column from the bottom-up. For recovery purposes this can be quite useful. Note that the user responsible for the change is also available.
versions between explicit ranges
So far, we have issued flashback version queries using the MINVALUE and MAXVALUE range boundaries. As noted earlier in the article, we can also supply specific timestamp or SCN ranges. Remember that we captured the timestamps before and after the updates of our data? We will use these to limit the data versions requested below. One restriction with using specific timestamps or SCNs is that they must be within the boundaries of the undo_retention parameter. Attempting to flashback to a version older than approximately query time-undo_retention will result in ORA-30052: invalid lower limit snapshot expression.
Note that in the following examples, the ALTER SESSION statement is included for convenience, to save having to supply a long format mask in the TO_TIMESTAMP calls.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';
Session altered.
SQL> SELECT x
2 , y
3 , z
4 , VERSIONS_OPERATION
5 FROM fbt VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('10-AUG-2005 18:01:07.109')
6 AND TO_TIMESTAMP('10-AUG-2005 18:01:17.125')
7 ORDER BY
8 VERSIONS_ENDTIME;
X Y Z VERSIONS_OPERATION
--- ------------------------- -------------------- ------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update U
1 10-AUG-2005 18:01:17.125 Second update U
3 rows selected.
Remember that we took the timestamp for the lower boundary before our first update. We can see this from the above version query. At this time, there was already a version of our data in existence (the initial insert). The VERSIONS_OPERATION pseudo-column is NULL for this record because the change had already occurred prior to the lower timestamp boundary. Our two updates occurred within the timestamp range, however, and the VERSIONS_OPERATION column shows us this. This is a useful tracking mechanism that enables us to distinguish between changes and existing data.
a note on timestamps and scns
As a final note, it is very simple to switch between SCNs and timestamps, should the need arise. Oracle 10g provides two conversion functions, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP for this purpose. Timestamps are mapped to SCNs with a precision of approximately 3 seconds (in 9i this was 5 minutes). Most readers will be aware of the SYSTIMESTAMP and LOCALTIMESTAMP functions to capture timestamps, but we can also capture SCNs using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function. We will complete this article with an example of each.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';
Session altered.
SQL> SELECT SYSTIMESTAMP
2 , LOCALTIMESTAMP
3 , DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scn
4 FROM dual;
SYSTIMESTAMP LOCALTIMESTAMP SCN
----------------------------------- ------------------------- ----------
10-AUG-05 18.08.04.078000 +00:00 10-AUG-2005 18:08:04.078 1408640
1 row selected.
SQL> SELECT SCN_TO_TIMESTAMP(1408640) AS ts
2 FROM dual;
TS
-------------------------
10-AUG-2005 18:08:03.000
1 row selected.
SQL> SELECT TIMESTAMP_TO_SCN('10-AUG-2005 18:08:03.000') AS scn
2 FROM dual;
SCN
----------
1408640
1 row selected.
No comments:
Post a Comment