Oracle10g Flashback Technology

1
Oracle10g Flashback Technology

• New Features for recoverability

2
Objectives

• At the end of this module the student will
  understand the following tasks and concepts.
• Understand the new Flashback Technologies in
  Oracle10g.
• Understand how to use Flashback Technology.

3
Overview

• Flashback Query
• Flashback Table
• Flashback Drop
• Flashback Database

4
Flashback Query

• Allows you to access data from the past
• Flashback Query
• Query data as it was in the past
• Flashback Version Query
• View all versions of data as it existed in the
  past
• Flashback Transaction Query
• View transactional data history
• Uses UNDO tablespace
• You can only recover to UNDO_RETENTION period

5
Flashback Query

• Query data at a time in the past (as far back as
  UNDO_RETENTION)
• Select
• SELECT FROM scott.emp AS OF TIMESTAMP
• TO_TIMESTAMP('2004-05-16 080000',
  'YYYY-MM-DD HHMISS')
• WHERE ename 'SCOTT'
• Populate
• INSERT INTO scott.emp
• (SELECT FROM scott.emp AS OF TIMESTAMP
• TO_TIMESTAMP('2004-05-16 080000',
  'YYYY-MM-DD HHMISS')
• WHERE ename 'SCOTT')

6
Flashback Version Query

• Used to retrieve version information
• SELECT SUBSTR(versions_startscn,1,10) AS
  "StartSCN",
• SUBSTR(versions_endscn,1,10) AS "EndSCN",
• SUBSTR(versions_starttime,1,25) AS
  "StartTime",
• SUBSTR(versions_endtime,1,25) AS
  "EndTime",
• SUBSTR(versions_xid,1,20) AS "XID",
• SUBSTR(versions_operation,1,20) AS
  "Operation",
• ename, sal
• FROM scott.emp
• VERSIONS BETWEEN TIMESTAMP
• TO_TIMESTAMP('2004-05-16 110000',
  'YYYY-MM-DD HH24MISS')
• AND TO_TIMESTAMP('2004-05-16 112000',
  'YYYY-MM-DD HH24MISS')
• WHERE ename'MILLER'

7
Flashback Version Query

• Output
• StartS EndSCN StartTime EndTime
  XID O ENAME
  SAL
• ------ ------ -------------------------
  ------------------------- ---------------- -
  ---------- ----------
• 462855 16-MAY-04 11.18.03 AM
  0300260084060000 U MILLER
  1500
• 462680 462855 16-MAY-04 11.13.55 AM 16-MAY-04
  11.18.03 AM 010022006A020000 U MILLER
  1400
• 462680 16-MAY-04
  11.13.55 AM MILLER
  1300

8
Flashback Transaction Query

• Similar to version except by transaction and not
  data
• SELECT xid,
• SUBSTR(logon_user,1,20) AS "User",
• SUBSTR(operation,1,8) AS "Oper",
• SUBSTR(undo_sql,1,40) AS "UNDO SQL"
• FROM flashback_transaction_query
• WHERE xid IN
• (SELECT versions_xid
• FROM scott.emp
• VERSIONS BETWEEN TIMESTAMP
• TO_TIMESTAMP('2004-05-16 110000',
  'YYYY-MM-DD HH24MISS')
• AND TO_TIMESTAMP('2004-05-16 115500',
  'YYYY-MM-DD HH24MISS')
• WHERE ename'MILLER')

9
Flashback Transaction Query

• Output
• XID User Oper
  UNDO SQL
• ---------------- -------------------- --------
  ----------------------------------------
• 010022006A020000 SYSTEM UPDATE
  update "SCOTT"."EMP" set "SAL" '1300'
• 010022006A020000 SYSTEM BEGIN
• 0300260084060000 SYSTEM UPDATE
  update "SCOTT"."EMP" set "SAL" '1400'
• 0300260084060000 SYSTEM BEGIN
• 0B002C0018000000 SYSTEM UPDATE
  update "SCOTT"."EMP" set "SAL" '1500'
• 0B002C0018000000 SYSTEM BEGIN

10
Flashback Table

• Restores a table as it was at some time in the
  past
• Uses UNDO data
• SQLgt FLASHBACK TABLE scott.emp TO TIMESTAMP
• TO_TIMESTAMP('2004-05-16 110000',
  'YYYY-MM-DD HH24MISS')
• 2
• Flashback complete.

11
Flashback Drop

• Dropped Objects are saved in the Recycle Bin
• Tables can be restored to before the drop
• SQLgt FLASHBACK TABLE emp TO BEFORE DROP
• Recycle bin must be manually cleaned out via the
  PURGE command

12
Flashback DropPURGE Command
Purge Command Effect
PURGE TABLE table Purges the listed table from the recycle area.
PURGE TABLESPACE tablespace_name Purges all of the dropped tables for the listed tablespace.
PURGE TABLESPACE tablespace_name USER user This will purge all of the dropped tables for a particular user in a tablespace.
PURGE RECYCLEBIN Purges the recyclebin for the current user.
PURGE DBA_RECYCLEBIN A user with SYSDBA privileges can purge all of the recyclebins for all of the users.
13
Flashback Database

• Returns an entire database to a point in the past
• Runs from within RMAN
• Requires Flash Recovery Area
• Requires full online RMAN backup flashback logs
• 1X 3X the size of data file storage, depending
  on retention period

14
Flashback DatabaseFlashback Command
Flashback Command Effect
FLASHBACK DATABASE TO SCN scn Flash back to a specific SCN.
FLASHBACK DATABASE TO SEQUENCE sequence Flash back to a specific log sequence.
FLASHBACK DATABASE TO TIME (SYSDATE-1/24) Flash back to a specific point in the past.
FLASHBACK DATABASE TO TIME timestamp('2004-05-16 140000') Flash back to a specific point in time.
15
Review

• What parameter controls the period of time you
  can flash back to?
• What area can be used to restore objects after
  they are dropped?
• What is the minimum size of the Flash Recovery
  Area relative to the database size?
• Name two options for using Flashback Database?

16
Summary

• Flashback Query
• Flashback Table
• Flashback Drop
• Flashback Database