Transcript Flashback Transaction Query

Using Flashback Technology
Copyright © 2007, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Query the recycle bin
• Restore dropped tables from the recycle bin
• Perform Flashback Query
• Use Flashback Version Query
• Use Flashback Transaction Query
• Use Flashback Transaction
11 - 2
Copyright © 2007, Oracle. All rights reserved.
Flashback Technology
Object
Level
Scenario Examples
Database
Table
Transaction
11 - 3
Flashback
Technology
Depends
On
Affects
Data
Truncate table; Undesired Database
multitable changes made
Flashback
logs
TRUE
Drop table
Drop
Recycle bin
TRUE
Update with the wrong
WHERE clause
Table
Undo data
TRUE
Compare current data
with data from the past
Query
Undo data
FALSE
Compare versions of a
row
Version
Undo data
FALSE
Keep historical
transaction data
Data Archive
Undo data
TRUE
Investigate and back out
suspect transactions
Transaction
Undo data
TRUE
Copyright © 2007, Oracle. All rights reserved.
Transactions and Undo
Original data
in
buffer cache
Undo “old” data
in undo tablespace
DML operations
11 - 4
Copyright © 2007, Oracle. All rights reserved.
Guaranteeing Undo Retention
Retention guarantee:
15 minutes
Undo data in
undo
tablespace
SELECT statements
running 15 minutes or less
are always satisfied.
11 - 5
A transaction that generates
more undo than what there
is space for will fail.
Copyright © 2007, Oracle. All rights reserved.
Preparing Your Database for Flashback
• Creating an undo tablespace
• Enabling Automatic Undo Management
• Specifying versus guaranteeing undo retention
Default database initialization parameters:
—
—
—
11 - 6
UNDO_MANAGEMENT='AUTO'
UNDO_TABLESPACE='UNDOTBS1'
UNDO_RETENTION=900
Copyright © 2007, Oracle. All rights reserved.
Flashback Drop and the Recycle Bin
RECYCLEBIN=ON
DROP TABLE employees;
FLASHBACK TABLE
employees
TO BEFORE DROP;
Mistake was
made.
11 - 8
Copyright © 2007, Oracle. All rights reserved.
Recycle Bin
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
USER_OBJECTS
EMPLOYEES
EMPLOYEES_PK
Recycle
bin
4
DBA_FREE_SPACE
BIN$zbjrBdpw==$0
EMPLOYEES
3
EMPLOYEES_PK
BIN$zbjra9wy==$0
Objects are:
– Renamed
– Not moved
1
11 - 9
2
DROP TABLE employees;
Copyright © 2007, Oracle. All rights reserved.
Restoring Tables from the Recycle Bin
• Restore dropped tables and dependent objects.
• If multiple recycle bin entries have the same original name:
– Use unique, system-generated names to restore a particular
version
– When using original names, the restored table is last in, first
out (LIFO)
• Rename the original name if that name is currently used.
FLASHBACK TABLE <table_name> TO BEFORE DROP
[RENAME TO <new_name>];
11 - 11
Copyright © 2007, Oracle. All rights reserved.
Recycle Bin: Automatic
Space Reclamation
Recycle bin
2
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
DBA_FREE_SPACE - RECYCLEBIN
1
Autoextend 3
11 - 12
Copyright © 2007, Oracle. All rights reserved.
Recycle Bin: Manual Space Reclamation
PURGE {TABLE <table_name>|INDEX <index_name>}
PURGE TABLESPACE <ts_name> [USER <user_name>]
PURGE [USER_|DBA_]RECYCLEBIN
11 - 13
Copyright © 2007, Oracle. All rights reserved.
Bypassing the Recycle Bin
DROP TABLE <table_name> [PURGE] ;
DROP TABLESPACE <ts_name>
[INCLUDING CONTENTS] ;
DROP USER <user_name> [CASCADE] ;
11 - 14
Copyright © 2007, Oracle. All rights reserved.
Querying the Recycle Bin
SELECT owner, original_name, object_name,
type, ts_name, droptime, related, space
FROM dba_recyclebin
WHERE can_undrop = 'YES';
SQL> SELECT original_name, object_name, ts_name, droptime
FROM user_recyclebin WHERE can_undrop = 'YES'; 2
ORIGINAL_NAME OBJECT_NAME
TS_NAM DROPTIME
------------- ----------------------- ------ ------------------EMPLOYEES2
BIN$NE4Rk64w...gbpQ==$0 USERS 2007-07-02:15:45:13
SQL> SHOW RECYCLEBIN
11 - 15
Copyright © 2007, Oracle. All rights reserved.
Querying Data from
Dropped Tables
DBA_TABLES
DROPPED
TABLE_NAME
NO
SALES
YES
BIN$zbjrBdpw==$0
EMPLOYEES
YES
BIN$zbjra9wy==$0
EMPLOYEES_PK
NO
SALES_PK
DROPPED
INDEX_NAME
DBA_INDEXES
SELECT ...
FROM "BIN$zbjrBdpw==$0" [AS OF ...]
WHERE ...
11 - 16
Copyright © 2007, Oracle. All rights reserved.
Recycle
bin
Using Flashback Technology to Query Data
• Flashback Query
– Query all data at a specified point in time.
• Flashback Version Query
– See all versions of a row between two times.
– See the transactions that changed the row.
• Flashback Transaction Query
– See all changes made
by a transaction.
Time
Tx3
Tx2
Tx1
11 - 17
Copyright © 2007, Oracle. All rights reserved.
Flashback
Flashback Query
Use to query all data at a specified point in time.
employees
Unwanted
updates
employees
T1
SELECT employee_id, salary FROM employees
AS OF TIMESTAMP <T1>
WHERE employee_id = 200
11 - 18
Copyright © 2007, Oracle. All rights reserved.
T2
Flashback Query: Example
employees
employees
employees
salary = 4,400
salary = 4,400
salary = 4,840
11:00
11:10
UPDATE employees
SET salary =
(SELECT salary FROM employees
AS OF TIMESTAMP TO_TIMESTAMP
('2005-05-04 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
WHERE employee_id = 200)
WHERE employee_id = 200
11 - 19
Copyright © 2007, Oracle. All rights reserved.
Flashback Version Query
Tx0
Tx1
employees
Tx2
employees
employees
200
t1
t2
SELECT versions_xid, salary FROM employees
VERSIONS BETWEEN TIMESTAMP <t1> and <t2>
WHERE employee_id = 200;
Tx0
11 - 20
Tx1
Tx2
Copyright © 2007, Oracle. All rights reserved.
Using Enterprise Manager to Perform
Flashback Version Query
11 - 21
Copyright © 2007, Oracle. All rights reserved.
Flashback Version Query:
Considerations
• The VERSIONS clause cannot be used to query:
–
–
–
–
External tables
Temporary tables
Fixed tables
Views
• The VERSIONS clause cannot span DDL commands.
• Segment shrink operations are filtered out.
11 - 22
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Query
FLASHBACK_TRANSACTION_QUERY
DBA
Erroneous
DML
Undo
SQL
User
11 - 23
Copyright © 2007, Oracle. All rights reserved.
Using Enterprise Manager to Perform
Flashback Transaction Query
11 - 24
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Query:
Considerations
• DDL commands are seen as dictionary updates.
• Flashback Transaction Query on a transaction underlying a
DDL command displays the data dictionary changes.
• Dropped objects appear as object numbers.
• Dropped users appear as user identifiers.
11 - 25
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction
•
•
•
•
•
•
Setting up Flashback Transaction prerequisites
Stepping through a possible workflow
Using the Flashback Transaction Wizard
Querying transactions with and without dependencies
Choosing back-out options and flashing back transactions
Reviewing the results
11 - 26
Copyright © 2007, Oracle. All rights reserved.
Prerequisites
11 - 27
Copyright © 2007, Oracle. All rights reserved.
Flashing Back a Transaction
• You can flash back a transaction with Enterprise Manager or
from the command line.
• EM uses the Flashback Transaction Wizard, which calls the
DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure
with the NOCASCADE option.
• If the PL/SQL call finishes successfully, it means that the
transaction does not have any dependencies and a single
transaction is backed out successfully.
11 - 28
Copyright © 2007, Oracle. All rights reserved.
Possible Workflow
1. Viewing data in a table
2. Discovering a logical problem
3. Using Flashback Transaction
1.
2.
3.
4.
Performing a query
Selecting a transaction
Flashing back a transaction (with no conflicts)
Choosing other back-out options (if conflicts exists)
4. Reviewing Flashback Transaction results
11 - 29
Copyright © 2007, Oracle. All rights reserved.
Viewing Data
11 - 30
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard
11 - 31
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard
11 - 32
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard
1
2
11 - 33
Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard
11 - 34
Copyright © 2007, Oracle. All rights reserved.
Choosing Other Back-out Options
11 - 35
Copyright © 2007, Oracle. All rights reserved.
Choosing Other Back-out Options
11 - 36
Copyright © 2007, Oracle. All rights reserved.
Final Steps Without EM
After choosing your back-out option, the dependency report is
generated in the DBA_FLASHBACK_TXN_STATE and
DBA_FLASHBACK_TXN_REPORT tables.
• Review the dependency report that shows all transactions
which were backed out.
• Commit the changes to make them permanent.
• Roll back to discard the changes.
11 - 37
Copyright © 2007, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Query the recycle bin
• Restore dropped tables from the recycle bin
• Perform Flashback Query
• Use Flashback Version Query
• Use Flashback Transaction Query
• Use Flashback Transaction
11 - 38
Copyright © 2007, Oracle. All rights reserved.
Practice 11 Overview:
Performing Flashback Database
This practice covers the following topics:
• Recycle bin activities
• Flashback Query
• Flashback Transaction
11 - 39
Copyright © 2007, Oracle. All rights reserved.