9 Flashback Database Copyright 2004 Oracle All rights

Objectives After completing this lesson, you should be able to: • Describe Flashback Database architecture • Enable and disable the Flashback Database • Monitor the Flashback Database • Use the Enterprise Manager Recovery Wizard 9 -2 Copyright © 2004, Oracle. All rights reserved.

Flashback Any Error • • • 9 -3 Flashback Database brings the database to a prior point in time by undoing all changes made since that time. Flashback Table recovers a table to a point in time in the past without restoring a backup. Flashback Drop restores accidentally dropped tables. Copyright © 2004, Oracle. All rights reserved.

Flashback Technology Benefits • • Flashback technology is a revolutionary advance in recovery Traditional recovery techniques are slow – Entire database or file has to be restored, not just the incorrect data – Every change in the database log must be examined • Flashback is fast – Changes are indexed by row and by transaction – Only the changed data is restored • Flashback commands are easy – No complex multi-step procedures 9 -4 Copyright © 2004, Oracle. All rights reserved.

When to Use Flashback Technology Object Level Scenario Flashback Technology Database Drop User Flashback Database Truncate Table Flashback Database Batch job: partial changes Flashback Database Drop Table Flashback Drop Update with wrong WHERE clause Flashback Table Comparing current data against the data at some time in the past Flashback Query Batch Job runs twice, but not really sure of the objects affected Flashback Query Table Tx 9 -5 Copyright © 2004, Oracle. All rights reserved.

Flashback Database Overview 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 9 -6 Corrupted database Press the rewind button Copyright © 2004, Oracle. All rights reserved. Database is "rewound"

Flashback Database Reduces Restore Time Incomplete Recovery Restore files Generate logs User error Backup Flashback Database Flashback logs Backup 9 -7 User error Apply logs forward Repaired database Apply Flashback logs backward Copyright © 2004, Oracle. All rights reserved. Repaired database

Flashback Database Architecture SGA 16 Mb Flashback Buffer Not every change! Buffer Cache Redo Log Buffer Apply direction Log block before images periodically Write direction Redo Logs RVWR Back out changes 1 to database without restore Forward media 2 recovery … 9 -8 LGWR Every change Write direction Flashback Logs 8 Mb Apply direction … Copyright © 2004, Oracle. All rights reserved.

Configuring Flashback Database 1. Configure the flash recovery area 2. Set the retention target 3. Enable Flashback Database SQL> ALTER SYSTEM SET 2 DB_FLASHBACK_RETENTION_TARGET=2880 3 SCOPE=BOTH; SQL> ALTER DATABASE FLASHBACK ON; 9 -9 Copyright © 2004, Oracle. All rights reserved.

Configure Flashback Database with EM Make sure the database is in ARCHIVELOG mode. 9

Configure Flashback Database with EM Enable Flashback logging and specify flash recovery area 9

Monitoring Flashback Database • Adjust 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; 9 -12 Copyright © 2004, Oracle. All rights reserved.

Monitoring Flashback Database with EM 9 -14 Copyright © 2004, Oracle. All rights reserved.

Monitoring Flash Recovery Area with EM 9 -15 Copyright © 2004, Oracle. All rights

Best Practices for the Database and Flash Recovery Area Use the flash recovery area for recovery-related files: • Simplifies location of database backups • Automatically manages the disk space allocated for recovery files • Does not require changes to existing scripts • Puts database backups, archive logs, and control file backups in the flash recovery area 9 -16 Copyright © 2004, Oracle. All rights reserved.

Backing Up the Flash Recovery Area RMAN> BACKUP RECOVERY FILES; 9 -17 Copyright ©

Flash Recovery Area Space Usage • • • 9 -18 Configure the retention policy to the minimum value appropriate for your database Backup the archive log files regularly and delete the files upon completion of the backup Use the RMAN REPORT OBSOLETE and DELETE OBSOLETE commands to remove unneeded backups and file copies Copyright © 2004, Oracle. All rights reserved.

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; 9 -19 Copyright © 2004, Oracle. All rights reserved.

Flashback Database with EM Select object and operation type 9 -20 Copyright © 2004,

Flashback Database with EM Launching Recovery Wizard 9 -21 Copyright © 2004, Oracle. All

Flashback Database Using EM 9 -22 Copyright © 2004, Oracle. All rights reserved.

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

Excluding Tablespaces from Flashback Database ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF} SQL> SELECT tablespace_name, flashback_on 2 FROM v$tablespace; • • 9 -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 © 2004, 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 updates • • The opposite of flashback is recover You cannot use Flashback Database in the following situations: – – 9 -24 The control file has been restored or re-created. A tablespace has been dropped. A data file has been shrunk. You want to flashback before RESETLOGS operation. Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Describe Flashback Database architecture • Enable and disable Flashback Database • Monitor Flashback Database • Use the Enterprise Manager Recovery Wizard 9 -25 Copyright © 2004, Oracle. All rights reserved.

Practice 9 Overview: Flashback Database These practices cover the following topics: • Configuration of Flashback Database • Using the Flashback Database RMAN interface • Using Flashback Database through the EM Database Control Console • Managing and monitoring Flashback Database operations 9 -26 Copyright © 2004, Oracle. All rights reserved.