6 Flashback Copyright 2006 Oracle All rights reserved

Objectives Recycle bin Flashback DB Config. FB DB Monitor FB DB Guar. Res. Pt. After completing this lesson, you should be able to: • Query the recycle bin • Configure Flashback Database • Perform Flashback Database to a point in time • Monitor flashback log statistics • Enable and disable the Flashback Database feature • Use the Enterprise Manager Recovery Wizard to perform Flashback Database • Use guaranteed restore points with Flashback Database 6 -2 Copyright © 2006, Oracle. All rights reserved.

Flashback Technology: Review Object Level Uses Affects Database Truncate table; Undesired multitable changes made Database Flashback logs TRUE Table 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 Investigate several historical states of data Transaction Undo data FALSE Tx 6 -3 Scenario Examples Flashback Technology Copyright © 2006, Oracle. All rights reserved.

Flashback Drop and the Recycle Bin RECYCLEBIN=ON DROP TABLE employees; Mistake was made. 6

Recycle Bin BIN$zbjr. Bdpw==$0 BIN$zbjra 9 wy==$0 USER_OBJECTS EMPLOYEES_PK Recycle bin 4 DBA_FREE_SPACE BIN$zbjr. Bdpw==$0 EMPLOYEES 3 EMPLOYEES_PK 1 6 -5 BIN$zbjra 9 wy==$0 Objects are: – Renamed – Not moved 2 DROP TABLE employees; Copyright © 2006, 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>]; 6 -7 Copyright © 2006, Oracle. All rights reserved.

Recycle Bin: Automatic Space Reclamation Recycle bin 2 BIN$zbjr. Bdpw==$0 BIN$zbjra 9 wy==$0 DBA_FREE_SPACE

$Recycle Bin: Manual Space Reclamation PURGE {TABLE <table_name>|INDEX <index_name>} PURGE TABLESPACE <ts_name> [USER <user_name>]$

Recycle Bin: Manual Space Reclamation PURGE {TABLE <table_name>|INDEX <index_name>} PURGE TABLESPACE <ts_name> [USER <user_name>] PURGE [USER_|DBA_]RECYCLEBIN 6 -10 Copyright © 2006, Oracle. All rights reserved.

Bypassing the Recycle Bin DROP TABLE <table_name> [PURGE] ; DROP TABLESPACE <ts_name> [INCLUDING CONTENTS]

Querying the Recycle Bin SELECT owner, original_name, object_name, type, ts_name, droptime, related, space FROM dba_recyclebin WHERE can_undrop = 'YES'; SELECT original_name, object_name, type, ts_name, droptime, related, space FROM user_recyclebin WHERE can_undrop = 'YES'; SQL> SHOW RECYCLEBIN 6 -12 Copyright © 2006, Oracle. All rights reserved.

Querying Data from Dropped Tables USER_TABLES DROPPED TABLE_NAME NO SALES YES BIN$zbjr. Bdpw==$0 EMPLOYEES YES BIN$zbjra 9 wy==$0 EMPLOYEES_PK NO SALES_PK DROPPED INDEX_NAME USER_INDEXES SELECT. . . FROM "BIN$zbjr. Bdpw==$0" [AS OF. . . ] WHERE. . . 6 -13 Copyright © 2006, Oracle. All rights reserved. Recycle bin

Flashback Database: Review Recycle bin > Flashback DB Config. FB DB Monitor FB DB Guar. Res. Pt. The Flashback Database operation: • Works like a rewind button for the database • Can be used in cases of logical data corruptions made by users Users generate errors. 6 -14 The database is corrupted. You “press the rewind button. ” Copyright © 2006, Oracle. All rights reserved. The database is “rewound. ”

Flashback Database Architecture SGA Not every change! Flashback buffer Buffer cache Redo log buffer LGWR Every change Log block before images periodically. Flashback logs Redo logs RVWR 1 Back out changes to database using before images. Do forward media recovery. … 6 -15 2 … Copyright © 2006, Oracle. All rights reserved.

Configuring Flashback Database 1. Configure the flash recovery area. 2. Set the retention target. Recycle bin Flashback DB > Config. FB DB Monitor FB DB Guar. Res. Pt. 3. Enable Flashback Database. SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT EXCLUSIVE; SQL> ALTER SYSTEM SET 2 DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH; SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN; 6 -16 Copyright © 2006, Oracle. All rights reserved.

Configuring Flashback Database Using EM Make sure that the database is in ARCHIVELOG mode.

Configuring Flashback Database Using EM Enable flashback logging and specify the flash recovery area.

Flashback Database: Examples RMAN> FLASHBACK DATABASE TO TIME = 2> "TO_DATE('2004 -05 -27 16: 00', 3> 'YYYY-MM-DD HH 24: MI: SS')"; RMAN> FLASHBACK DATABASE TO SCN=23565; RMAN> FLASHBACK DATABASE 2> TO SEQUENCE=223 THREAD=1; SQL> FLASHBACK DATABASE 2 TO TIMESTAMP(SYSDATE-1/24); SQL> FLASHBACK DATABASE TO SCN 53943; SQL> FLASHBACK DATABASE TO RESTORE POINT b 4_load; 6 -19 Copyright © 2006, Oracle. All rights reserved.

Performing Flashback Database Using EM Select object and operation type. 6 -20 Copyright ©

Performing Flashback Database Using EM Launching the Recovery Wizard: 6 -21 Copyright © 2006,

Performing Flashback Database Using EM 6 -22 Copyright © 2006, Oracle. All rights reserved.

$Excluding Tablespaces from Flashback Database ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF} SQL> SELECT name, flashback_on$

Excluding Tablespaces from Flashback Database ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF} SQL> SELECT name, flashback_on 2 FROM v$tablespace; • • 6 -23 Take the tablespace offline before you perform the Flashback Database recovery. Drop the tablespace or recover the offline files with traditional point-in-time recovery. Copyright © 2006, Oracle. All rights reserved.

Flashback Database Considerations • When the Flashback Database operation completes, open the database: – In read-only mode to verify that the correct target time or SCN was used – With a RESETLOGS operation to allow for DML • • The opposite of “flash back” is “recover. ” You cannot use Flashback Database in the following situations: – The control file has been restored or re-created. – A tablespace has been dropped. – A data file has been shrunk. • 6 -24 Use the TO BEFORE RESETLOGS to flash back to before the last RESETLOGS operation. Copyright © 2006, Oracle. All rights reserved.

Monitoring Flashback Database Recycle bin Flashback DB Config. FB DB > Monitor FB DB Guar. Res. Pt. To monitor the ability to meet your retention target: • View the flash recovery area disk quota: SQL> SELECT estimated_flashback_size, 2 flashback_size 3 FROM V$FLASHBACK_DATABASE_LOG; • Determine the current flashback window: SQL> SELECT oldest_flashback_scn, 2 oldest_flashback_time 3 FROM V$FLASHBACK_DATABASE_LOG; • Monitor logging in the Flashback Database logs: SQL> SELECT * 2 FROM V$FLASHBACK_DATABASE_STAT; 6 -26 Copyright © 2006, Oracle. All rights reserved.

Monitoring Flashback Database with EM 6 -28 Copyright © 2006, Oracle. All rights reserved.

Guaranteed Restore Points Recycle bin Flashback DB Config. FB DB Monitor FB DB > Guar. Res. Pt. A guaranteed restore point ensures that you can perform a FLASHBACK DATABASE command to that SCN at any time. SQL> CREATE RESTORE POINT before_load 2 GUARANTEE FLASHBACK DATABASE; 6 -29 Copyright © 2006, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Query the recycle bin • Configure Flashback Database • Perform Flashback Database to a point in time using Enterprise Manager or RMAN • Monitor flashback log statistics • Enable and disable the Flashback Database feature • Use guaranteed restore points with Flashback Database 6 -30 Copyright © 2006, Oracle. All rights reserved.

Practice Overview: Performing Flashback Database This practice covers the following topics: • Performing Flashback Database to undo unwanted transactions • Monitoring the Flashback Database retention • Determine the size of the flashback logs 6 -31 Copyright © 2006, Oracle. All rights reserved.