Synopsis. Oracle 10g offers significant
enhancements that help insure the high availability of any Oracle database, as
well as improvements in the database disaster recovery arena. This article –
part three of a series – concentrates on the expanded capabilities of the
logical Flashback command set.
The previous
article in this series discussed how two new Oracle 10g features – the Flash
Recovery Area and the Flashback Database command — expand a DBA’s
flexibility during a point-in-time incomplete recovery of a database. However,
when those features are used in conjunction with the new logical FLASHBACK command
set enhancements, a DBA now has an extensive set of tools for recovering data
with more granularity than ever before.
A Quick Review: Flashback Query
Oracle 9i provided the ability to “flash back” to a prior
view of the database via queries performed against specific logical entities.
For example, if a user had accidentally added, modified or deleted a large
number of rows erroneously, it was now possible to view the state of the
logical entity just before the operation had taken place. Of course, this
capability is limited by the amount of UNDO data retained in the database’s
UNDO segments, and that is bounded by the time frame specified by the
UNDO_RETENTION initialization parameter.
For example, let’s assume that late one Friday afternoon a
junior DBA, in his zest to perform emergency maintenance requested by a
developer against the DEPARTMENTS
table, inadvertently set all of the values for the Department Managers to NULL
values. If the senior DBA knew the approximate time at which this had occurred,
then she could issue the following query to return the state of that table at
that time:
SELECT *
FROM hr.departments
AS OF TIMESTAMP
TO_TIMESTAMP(’12/05/2004 11:55:00′, ‘MM/DD/YYYY HH24:MI:SS’);
Moreover, to restore the data back to its prior state, she
could issue this UPDATE statement:
UPDATE hr.departments D1
SET D1.manager_id = (
SELECT manager_id
FROM hr.departments
AS OF TIMESTAMP
TO_TIMESTAMP(’12/05/2004 11:55:00′, ‘MM/DD/YYYY HH24:MI:SS’) D
WHERE manager_id IS NOT NULL
AND d1.department_id = D.department_id
);COMMIT;
(See Listing
3.1 for a complete set of DML statements that simulate this scenario.)
The ability to flash back to a different version of the data
is an obvious time-saver in this situation, as it prevents having to resort to
other brute-force methods to restore data integrity. However, what if more
than one table’s data had been affected by this transaction? For example,
what will happen if a user error causes data to be modified in another table
based on a change in values in the DEPARTMENTS table, perhaps through a
trigger?
Or – even worse – what if significant amounts of data had
been deleted instead of just being updated erroneously? Or what if the
junior DBA had dropped a whole table instead of just truncating
the data stored within? Now the restoration and recovery of these data requires
some difficult decisions for the DBA:
-
She could perform an incomplete recovery of the database
using the traditional method of restoring backups of all datafiles and then
applying the appropriate archived redo logs to roll the database forward until
the database reached a point in time just prior to the time that the user error
occurred. -
Alternatively, if the new Oracle 10g Flashback Database
feature had been enabled, she could use that new method to “flash” the database
back to a particular point in time. (See the prior
article in this series for complete details on this method.) -
Another option would be to perform a brute-force recovery
via manual means, perhaps using database exports (providing, of course, that
the exports were current enough). However, unless the DBA is intimately aware
of the inter-relation of the affected data, this may not be practical, and
could even be more destructive.
Unfortunately, in all these cases, the state of the data
that has been added, modified or deleted after the recovery point in
time would most likely also be lost. Moreover, that probably means that her
users are going to spend their weekend re-entering significant amounts of lost
data.
Wouldn’t it be great if Oracle provided a way to reverse the
effects of a particular DML statement completely, or (even better!) a dropped
table? Here is the good news: Oracle 10g has significantly improved the
existing set of logical FLASHBACK features to handle many of these
not-quite-a-disaster data recovery operations.
Flashback Version Query
Flashback Version Query improves upon the existing Flashback
Query feature: It allows a DBA to see all the different versions of a
particular row within a specified time frame, as long as those versions are
still available within the UNDO tablespace’s rollback segments. This time frame
can be defined based on either a beginning and an ending timestamp value,
or based on a range of starting and ending System Change Numbers (SCNs).
Flashback Version Query: An Illustration. I will use
the Human Resources demonstration schema to illustrate the new capabilities of
Flashback Version Query. First, I will establish a new baseline of “correct”
data by adding four new Job Titles, a new Department, and five new Employees to
the database via a series of DML statements (see Listing 3.2).
Next, I will issue a series of DML statements to simulate a
set of “mistaken” operations against the database (see Listing 3.3).
Note that the changes against the HR.EMPLOYEES table within these transactions
also mean that new rows will be added automatically to the HR.JOB_HISTORY table
via the NNNN trigger.
Now, I will use the query in Listing 3.4 to
show what Flashback Version Query can tell me about the versions of the rows in
the database as a result of these DML statements. Here is a sample of the
results:
Current FLASHBACK VERSION Results For Selected EmployeesVsn Vsn
Start End
XID SCN SCN Operatio Last Name Dept Salary
—————- ——— ——— ——– ———— —– ———-
04002900E7000000 2150721 Insert Campbell 280 110000.00
04000C00E7000000 2150749 Update Asimov 280 5250.00
04002900E7000000 2150721 2150749 Insert Asimov 280 5000.00
04000C00E7000000 2150749 Update Heinlein 280 26250.00
04002900E7000000 2150721 2150749 Insert Heinlein 280 25000.00
04000C00E7000000 2150749 Update Bradbury 280 50925.00
04002900E7000000 2150721 2150749 Insert Bradbury 280 48500.00
04001500E7000000 2150751 Update Ellison 270 34125.00
04000C00E7000000 2150749 2150751 Update Ellison 280 34125.00
04002900E7000000 2150721 2150749 Insert Ellison 280 32500.00
04001B00E7000000 2150753 Delete Brin 280 39375.00
04000C00E7000000 2150749 2150753 Update Brin 280 39375.00
04001E00E7000000 2150747 2150749 Insert Brin 280 37500.00
A New PseudoColumn: ORA_ROWSCN. While the ROWID pseudocolumn
uniquely identifies a row’s block location within the database, the new pseudocolumn
ORA_ROWSCN identifies the System Change Number (SCN) of the most recently
committed change to a row. This pseudocolumn can therefore be used to
establish the upper limit of SCNs that I might want to search through. See Listing 3.4 for a
sample query that utilized this new pseudocolumn.
Another intriguing potential use of ORA_ROWSCN is the
capability to control transaction concurrency within applications. For
example, if one user retrieves a row from HR.EMPLOYEES for eventual update, but
has not yet applied the change, the value of ORA_ROWSCN will remain unchanged
until those modifications are committed to the database. In the meantime, if
another user modifies that same row and commits the changes, I can have my
application check if the value of ORA_ROWSCN is still equal to its original
value.
If the value has changed, I can notify the original user
that the row has changed since it was originally retrieved for update, and
request whether to continue the transaction or roll it back. Prior to ORA_ROWSCN,
my application would have to check every value of every column
for that row to determine if any value had changed. Alternatively, I add a
separate numeric column that would be incremented whenever a change to a row
was committed for each table that needs this level of transaction control.
However, ORA_ROWSCN makes accurate transaction control almost trivial.
Flashback Transaction Query
Like its cousin Flashback Version Query, Flashback
Transaction Query gives me even more flexibility: It allows me to see all
changed rows within a particular set of transactions that occurred within a
range of timestamps or SCNs.
Flashback Transaction Query uses the FLASHBACK_TRANSACTION_QUERY view as a window into
the database’s UNDO segments. I can use this view’s transaction ID column (XID)
to identify what changes have been recorded during a specific transaction.
Reminiscent of Oracle’s LogMiner toolset, Flashback Transaction Query can
display the actual DML statements to issue, to reverse the original
transaction.
Listing
3.5 shows how to utilize a Flashback Version Query SELECT statement to
drive the retrieval of all transactions that have occurred during a specific
range off SCNs. Here is the result of that query:
Current FLASHBACK_TRANSACTION_QUERY Contents For Selected EmployeesUser Table Commit
XID# Operation Logon Owner Table Name SCN
—————- ———- —— —— ———— ———
UNDO SQL
——————————————————————————–
04000C00E7000000 UPDATE SYS HR EMPLOYEES 2150749
update “HR”.”EMPLOYEES” set “SALARY” = ‘37500’ where ROWID = ‘AAAGMsAAEAAAABWAAA’;04000C00E7000000 UPDATE SYS HR EMPLOYEES 2150749
update “HR”.”EMPLOYEES” set “SALARY” = ‘32500’ where ROWID = ‘AAAGMsAAEAAAABYAAE’;04000C00E7000000 UPDATE SYS HR EMPLOYEES 2150749
update “HR”.”EMPLOYEES” set “SALARY” = ‘48500’ where ROWID = ‘AAAGMsAAEAAAABYAAD’;04000C00E7000000 UPDATE SYS HR EMPLOYEES 2150749
update “HR”.”EMPLOYEES” set “SALARY” = ‘25000’ where ROWID = ‘AAAGMsAAEAAAABYAAC’;04000C00E7000000 UPDATE SYS HR EMPLOYEES 2150749
update “HR”.”EMPLOYEES” set “SALARY” = ‘5000’ where ROWID = ‘AAAGMsAAEAAAABYAAB’;04000C00E7000000 BEGIN SYS 2150749
04001500E7000000 INSERT SYS HR JOB_HISTORY 2150751
delete from “HR”.”JOB_HISTORY” where ROWID = ‘AAAGMwAAEAAAABtAAG’;04001500E7000000 UPDATE SYS HR EMPLOYEES 2150751
update “HR”.”EMPLOYEES” set “DEPARTMENT_ID” = ‘280’ where ROWID = ‘AAAGMsAAEAAAABYAAE’;04001500E7000000 BEGIN SYS 2150751
04001B00E7000000 DELETE SYS HR EMPLOYEES 2150753
insert into “HR”.”EMPLOYEES”(“EMPLOYEE_ID”,”FIRST_NAME”,”LAST_NAME”,”EMAIL”,
“PHONE_NUMBER”,”HIRE_DATE”,”JOB_ID”,”SALARY”,”COMMISSION_PCT”,”MANAGER_ID”,
“DEPARTMENT_ID”) values (‘906′,’David’,’Brin’,’dbrin@astounding.com’,
‘212-555-1616′,TO_DATE(’10/31/1987 00:00:00’, ‘mm/dd/yyyy hh24:mi:ss’),
‘WRITER-3′,’39375′,NULL,’901′,’280’);04001B00E7000000 BEGIN SYS 2150753
04001E00E7000000 INSERT SYS HR EMPLOYEES 2150747
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABWAAA’;04001E00E7000000 BEGIN SYS 2150747
04002900E7000000 INSERT SYS HR EMPLOYEES 2150721
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABYAAE’;04002900E7000000 INSERT SYS HR EMPLOYEES 2150721
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABYAAD’;04002900E7000000 INSERT SYS HR EMPLOYEES 2150721
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABYAAC’;04002900E7000000 INSERT SYS HR EMPLOYEES 2150721
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABYAAB’;04002900E7000000 INSERT SYS HR EMPLOYEES 2150721
delete from “HR”.”EMPLOYEES” where ROWID = ‘AAAGMsAAEAAAABYAAA’;04002900E7000000 BEGIN SYS 2150721
19 rows selected.
Using SCNs vs. TIMESTAMPs. As you might expect, using
an SCN to identify a transaction or range of row versions is more accurate than
using a TIMESTAMP. Oracle recommends using SCNs over TIMESTAMPs when an
extremely accurate Logical Flashback operation needs to be performed; in fact,
the documentation states that a TIMESTAMP can be as much as three minutes
ahead in time than an SCN.
Effect of UNDO_RETENTION Setting. The length of time
the row versions are available obviously depends on the setting of the UNDO_RETENTION
initialization parameter. By default, this setting is 900 seconds (15 minutes);
in some cases, I have set UNDO_RETENTION as high as
10800 (3 hours) for some databases that I knew needed longer UNDO retention
durations. For the sake of these examples, I have set it to 1800 (30 minutes)
in my demonstration database, so that I can more easily illustrate these two new
features without recreating examples every 15 minutes.
