Transcript Flashback Transaction Query

Session id: 40109
Oracle Database 10g Time
Navigation:
Human-Error Correction
Tammy Bednar
Sr. Product Manager
Oracle Corporation
Magnus Lubeck
DBA/Systems Analyst
CERN, IT-DB Group
Time Navigation
and Human
Error
CorrectionCorrection
Correction
noitcerroC
Human Error
 Estimated to be the biggest single cause of downtime
 Need to quickly determine what happened and fix it
–
–
Localized damage
 Needs surgical detection and repair
 Example – removed wrong person named ‘Smith’
Widespread damage
 Requires drastic action to avoid long downtime
 Example – batch job deletes this month’s orders
Human
Errors
Other
Downtime
 Analysis and correction using traditional recovery is slow and
complex
–
Restore database to point in time and extract data
 Oracle Database 10g is a breakthrough release for human error
correction
Flashback Time Navigation

Flashback Query
–
Query all data at point in time
Select * from Emp AS OF ‘2:00 P.M.’ where …

Tx 3
Flashback Versions Query
–
See all versions of a row between
two times
–
See transactions that changed the
row
Select * from Emp VERSIONS BETWEEN
‘2:00 PM’ and ‘3:00 PM’ where …
Tx 2
Tx 1

Flashback Transaction Query
–
See all changes made by a
transaction
Select * from DBA_TRANSACTION_QUERY
where xid = ‘000200030000002D’;
Flashback Error Correction
Database
Customer
 Recovery at all levels
 Database Level
–
Flashback Database restores
the whole database to time
 Uses Flashback Logs
 Table Level
–
Order
–
Flashback Table restores
rows in a set of tables to time
 Uses UNDO in database
Flashback Drop restores a
dropped table or a index
 Recycle bin for DROPs
 Row Level
–
Flashback Query restores
rows to time
Flashback Database
 A new strategy for point in time recovery
Disk Write
New Block
Version
Data Files
Old Block
Version
Flashback
Log
 Eliminate the need to restore a whole
database backup
 Integrated seamlessly with RMAN
– Think of it as a continuous backup
– Restores just changed blocks
Replay log to restore DB to time
 It’s fast - recover in minutes, not hours
 It’s easy - single command restore
Flashback Database to ‘2:05 PM’
–
“Rewind” button for the Database
Flashback Drop
Emp
Drop
table
emp;
Recycle bin
Flashback
Table emp
before
drop;
Mistake was
made
Emp
 Quickly recover dropped objects
Provides self-service recovery
 Eliminate the need for TSPITR
 Virtual Recycle Bin
– Objects remain in the recycle bin until
you permanently drop them with the
PURGE command or recover them
with the Flashback Table command.
– Objects will remain in the recycle bin
until there is no room in the
tablespace for new rows or updates to
existing rows or until the tablespace
needs to be extended
– Objects are purged in the order they
were dropped.
Flashback Table
 Recover a table or tables to a specific point in time without restoring a
backup
 Provides a way for users to easily and quickly recover from accidental
modifications without DBA involvement
 In-place and online recovery of a table to a point in time in the past
 Eliminate traditional restores and clone instances to recover a table or
tables to a specific point in time
 Data in the tables and all associated objects (indexes, constraints,
triggers, etc.) are restored
Flashback Versions Query



Provides a way to audit the rows of a table and
retrieve information about the transactions that
changed the rows.
Retrieve all committed versions of the rows that exist
or ever existed between the time the query was
issued and a point in time in the past
Use the transaction ID to perform transaction mining
using LogMiner or Flashback Transaction Query to
obtain additional information about the transaction.
Flashback Transaction Query
 Provides a way for you to view changes made to the
database at the transaction level
 When used in conjunction with Flashback Versions
Query, it allows you to easily recover from user or
application errors.
 Benefits
–
–
–
Increase online diagnosability of problems in your database
Perform analysis and audits of transactions
Fast recovery at the transaction level
Database Mission
Determine what transaction removed Order ID
2453
Recover the corruption fast!
Order ID 2453
& line item
data
The Order ID
data is removed
from the database
Using the
‘VERSIONS
BETWEEN’
syntax, you can
find the operations
and transactions
executed for
Order 2453
After manually
executing the SQL
statements,
Order 2453 & its line
items have been
inserted back into the
database!
Flashback
Transaction Query
Provides the SQL
statements to ‘undo’
the offending
operation
CERN

Magnus Lubeck, DBA/Systems
analyst

CERN is the world biggest particle
physics research center. In
operation for 50 years!

In our physics research we collect
enormous amounts of data, of which
some is stored in ORDBMS

Oracle 10G beta testing and
evaluation is an important activity for
CERN to support the physics
community by understanding and
request new features.

10 people in our Oracle beta test
team, focusing on different areas
6 miles
Testbed setup
 General purpose for
–
–
RAC testing
Oracle 10G testing
 The setup
–
–
–
–
Five dual CPU servers (four connected to SAN)
Gbit interconnect
Plenty of disk
A set up workstations to run applications
 Not a setup for Benchmarks, but for proof of concept
SAN
Testing Flashback Features
 The Flashback features is a subset of the
beta testing performed at CERN
 The Human Error Correction testing, in
order of appearance – the one hour
approach
–
–
–
–
–
Install Sofware + DB instance
Setting up the recovery area
Flashback table
Flashback transaction query
Flashback database
CERN – Testing FB features
Preparations
Testing
Summary of CERN Flashback testing
 Flashback Table/Drop
–
–
Easy to get back dropped
tables
Nice to be able to query
dropped tables
 Flashback Transaction
Query
–
–
Diagnosability
Accountability
 Flashback database
–
Fast recovery
 Flash Recovery Area
–
Organizes & manages
backups
 Future use of FB features
Revolution in Recovery
 Flashback Revolutionizes Recovery
–
–
Operates on just the changed data
Time to correct error equals time to make error
 Minutes instead of hours
Correction Time = Error Time + f(DB_SIZE)
 Flashback is Easy
–
Single command instead of complex procedure
Flashback for All Users
END USER
• Flashback Query
• Flashback Versions Query
DEVELOPER
• Flashback Versions Query
• Flashback Transaction Query
• Flashback Table
DATABASE ADMIN
• Flashback Database
• Flashback Drop
SYSTEM ADMIN
• Data Guard
Next Steps
High Availability Sessions from Oracle
Tuesday in Moscone Room 304
Wednesday in Moscone Room 304
11:00 AM
8:30 AM
How Oracle Database 10G
Revolutionizes Availability and
Enables the Grid
Oracle Database 10G - RMAN and ATA
Storage in Action
11:00 AM
3:30 PM
Oracle Recovery Manager (RMAN)
10G: Reloaded
Oracle Data Guard: Maximum Data
Protection at Minimum Cost
1:00 PM
5:00 PM
Proven Techniques for Maximizing
Availability
Oracle Database 10G Time
Navigation: Human-Error Correction
4:30 PM
Data Guard SQL Apply: Back to the
Future
For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/
Next Steps
High Availability Sessions from Oracle
Thursday
Database HA Demos All Four Days
In The Oracle Demo Campground
8:30 AM -- Moscone Room 304
Oracle Database 10G Data
Warehouse Backup and Recovery:
Automatic, Simple, Reliable
8:30 AM -- Moscone Room 104
Building RAC Clusters over
InfiniBand
Real Application Clusters
Data Guard
Database Backup & Recovery
Flashback Recovery
LogMiner, Online Redefinition, and
Cross Platform Transportable
Tablespaces
For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/
Reminder –
please complete the
OracleWorld online session
survey
Thank you.
QUESTIONS
ANSWERS