technology INSIDE OCP
BY SUSHMA JAGANNATH
More Ways to Flash Back
Query history and recover from corruptions with Oracle Flashback technology.
racle Flashback technology
comprises a group of Oracle
Database 11g features with
which you can view data back
and forth in time and recover data
without using point-in-time media
recovery. These features are built in to
Oracle Database and exposed via a set
of simple SQL commands.
The Oracle Flashback technology uses Oracle’s Automatic Undo
Management system to obtain metadata
and historical data for transactions.
Using this information, it can roll back
a transaction and its dependent transactions while the database remains online.
Oracle Flashback technology can do
the following:
■ Query data as it existed in the past
■ Identify changes done by an erroneous query and request the undo SQL to
reverse those changes
■ Recover a table to a previous point
in time
■ Recover a database to a previous point
in time
This column focuses on some of the
Oracle Flashback features that help you
query historical data, perform change
analysis, and perform self-service repair
to recover from logical corruptions
while the database is online. It presents sample questions of the type you
may encounter when taking the Oracle
Database 11g Administration Workshop
II exam. Passing this exam is a requirement for achieving the Oracle Certified
Professional level of certification.
ORACLE FLASHBACK QUERY
By default, database operations use the
most-recent committed data available.
If you want to query the database as it
was at some time in the past, however,
you can do so with Oracle Flashback
Query, which queries all data as it
existed at a specific point in time or at
a specific user-specified system change
number (SCN). Users can set the date
and time, and then any SQL query they
execute will operate on the data as it
existed at that time.
John is a DBA at LMN Inc. and manages
a 200GB database. One of the developers
reports to John that he just accidentally
deleted 100,000 rows of data from the
SALES table and that the data needs to be
recovered. John needs to recover the data
as quickly as possible without any data
loss. Considering that the undo information
is available, which of the following is the
best method for recovering the lost data
and satisfying the recovery requirements?
A. Restore the entire database
B. Use the Import utility
C. Use Oracle Flashback Query
D. Use Oracle Flashback Drop
The correct answer is C. Oracle
Flashback Query uses undo segments,
provides a snapshot of the data at a
specific time, and enables the data to be
reinserted. John can issue the following
statement to restore the data:
INSERT INTO SALES
(SELECT FROM
SALES AS OF TIMESTAMP
TO_TIMESTAMP(’<required point in time>’,
’DD-MON-YY HH24:MI:SS’) MINUS
SELECT FROM SALES);
Answer A is incorrect because the
restoration of the entire database takes a
lot of time compared to reinserting the
data based on information provided by
Oracle Flashback Query. Answer B is
incorrect because there may be data loss
since the last export. Answer D is incorrect because Oracle Flashback Drop is
used to recover the entire table from an
erroneous DROP statement and not to
restore specific data in the table.
ORACLE FLASHBACK TRANSACTION QUERY
Oracle Flashback Transaction Query lets
you view changes made to the database
at the transaction level. This enables you
to diagnose problems in your database
and perform analysis and audits of
transactions. Given a transaction
ID, Oracle Flashback Transaction
Query retrieves all changes that were
performed by that transaction and the
corresponding undo SQL. The undo
SQL can then be used to quickly revert
changes across affected rows, to rectify a
bad transaction, or for testing purposes.
Which database transactions can you view
with Oracle Flashback Transaction Query?
A. All the transactions committed on the
same day
B. All the transactions from the time the
database was created
C. All the transactions for which you
have undo information retained in the
undo tablespace to satisfy the specified
time stamp or SCN
D. All the transactions committed within
the same session
The correct answer is C. As long as
you have the required undo information available in the undo tablespace
that corresponds to the required point
in time and SCN, you will be able to use
Oracle Flashback Transaction Query to
view the database transaction.
ORACLE FLASHBACK TABLE
When logical errors span a table or
a set of tables, traditional tablespace
point-in-time recovery requires restoration of the tablespace to an auxiliary
database instance; a media recovery
to a point in time before the logical
error; and finally, export or import of
the dictionary metadata for the recovered tablespace. However, with Oracle
Flashback Table, the DBA can recover
a table or a set of tables to a specified
