Transcript Intro to the Class

Oracle10g Flashback
Technology
New Features for recoverability
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.
Overview




Flashback Query
Flashback Table
Flashback Drop
Flashback Database
Flashback Query

Allows you to access data from the past

Flashback Query


Flashback Version Query


View all versions of data as it existed in the past
Flashback Transaction Query


Query data as it was in the past
View transactional data history
Uses UNDO tablespace

You can only recover to UNDO_RETENTION period
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 08:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE ename = 'SCOTT';

Populate
INSERT INTO scott.emp
(SELECT * FROM scott.emp AS OF TIMESTAMP
TO_TIMESTAMP('2004-05-16 08:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE ename = 'SCOTT');
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 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2004-05-16 11:20:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE ename='MILLER';
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
462680
16-MAY-04 11.13.55 AM
MILLER
1300
1400
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 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2004-05-16 11:55:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE ename='MILLER');
Flashback Transaction Query

Output
XID
---------------010022006A020000
'1300'
010022006A020000
0300260084060000
'1400'
0300260084060000
0B002C0018000000
'1500'
0B002C0018000000
User
-------------------SYSTEM
Oper
-------UPDATE
UNDO SQL
---------------------------------------update "SCOTT"."EMP" set "SAL" =
SYSTEM
SYSTEM
BEGIN
UPDATE
update "SCOTT"."EMP" set "SAL" =
SYSTEM
SYSTEM
BEGIN
UPDATE
update "SCOTT"."EMP" set "SAL" =
SYSTEM
BEGIN
Flashback Table


Restores a table as it was at some time in
the past
Uses UNDO data
SQL> FLASHBACK TABLE scott.emp TO TIMESTAMP
TO_TIMESTAMP('2004-05-16 11:00:00', 'YYYY-MM-DD HH24:MI:SS');
2
Flashback complete.
Flashback Drop


Dropped Objects are saved in the Recycle
Bin
Tables can be restored to before the drop
SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Recycle bin must be manually cleaned out via
the PURGE command
Flashback Drop
PURGE 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.
Purges the recyclebin for the current
user.
PURGE RECYCLEBIN
PURGE DBA_RECYCLEBIN
A user with SYSDBA privileges can
purge all of the recyclebins for all of
the users.
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
Flashback Database
Flashback Command
Flashback Command
Effect
FLASHBACK DATABASE TO SCN
scn#
FLASHBACK DATABASE TO
SEQUENCE sequence#
FLASHBACK DATABASE TO TIME
(SYSDATE-1/24);
FLASHBACK DATABASE TO TIME
timestamp('2004-05-16 14:00:00');
Flash back to a specific SCN.
Flash back to a specific log sequence.
Flash back to a specific point in the
past.
Flash back to a specific point in time.
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?
Summary




Flashback Query
Flashback Table
Flashback Drop
Flashback Database