Oracle 10 g Flashback Technology New Features for
Oracle 10 g 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 Oracle 10 g. 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 n Flashback Query n n Flashback Version Query n n View all versions of data as it existed in the past Flashback Transaction Query n ¢ Query data as it was in the past View transactional data history Uses UNDO tablespace n You can only recover to UNDO_RETENTION period
Flashback Query ¢ Query data at a time in the past (as far back as UNDO_RETENTION) n Select SELECT * FROM scott. emp AS OF TIMESTAMP TO_TIMESTAMP('2004 -05 -16 08: 00', 'YYYY-MM-DD HH: MI: SS') WHERE ename = 'SCOTT'; n Populate INSERT INTO scott. emp (SELECT * FROM scott. emp AS OF TIMESTAMP TO_TIMESTAMP('2004 -05 -16 08: 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 "Start. SCN", SUBSTR(versions_endscn, 1, 10) AS "End. SCN", SUBSTR(versions_starttime, 1, 25) AS "Start. Time", SUBSTR(versions_endtime, 1, 25) AS "End. Time", 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', 'YYYY-MM-DD HH 24: MI: SS') AND TO_TIMESTAMP('2004 -05 -16 11: 20: 00', 'YYYY-MM-DD HH 24: MI: SS') WHERE ename='MILLER';
Flashback Version Query ¢ Output Start. S End. SCN Start. Time End. Time 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 010022006 A 020000 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', 'YYYY-MM-DD HH 24: MI: SS') AND TO_TIMESTAMP('2004 -05 -16 11: 55: 00', 'YYYY-MM-DD HH 24: MI: SS') WHERE ename='MILLER');
Flashback Transaction Query ¢ Output XID --------010022006 A 020000 '1300' 010022006 A 020000 0300260084060000 '1400' 0300260084060000 0 B 002 C 0018000000 '1500' 0 B 002 C 0018000000 User ----------SYSTEM Oper -------UPDATE UNDO SQL --------------------update "SCOTT". "EMP" set "SAL" = 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', 'YYYY-MM-DD HH 24: 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 n n Requires full online RMAN backup + flashback logs 1 X – 3 X the size of data file storage, depending on retention period
Flashback Database 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'); 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
- Slides: 16