1 / 27

Flashback

Flashback. Recycle bin Flashback DB Config. FB DB Monitor FB DB Guar. Res. Pt. Objectives. 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

tressaj
Download Presentation

Flashback

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Flashback

  2. Recycle bin Flashback DB Config. FB DB Monitor FB DB Guar. Res. Pt. Objectives • 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

  3. Flashback Technology: Review

  4. Flashback Drop and the Recycle Bin RECYCLEBIN=ON DROP TABLE employees; FLASHBACK TABLEemployeesTO BEFORE DROP; Mistake wasmade.

  5. 4 3 1 2 Recycle Bin BIN$zbjrBdpw==$0 EMPLOYEESBIN$zbjra9wy==$0 EMPLOYEES_PK Recyclebin USER_OBJECTS DBA_FREE_SPACE BIN$zbjrBdpw==$0 EMPLOYEES EMPLOYEES_PK BIN$zbjra9wy==$0 • Objects are: • Renamed • Not moved DROP TABLE employees;

  6. 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>];

  7. Recycle Bin: Automatic Space Reclamation Recycle bin 2 BIN$zbjrBdpw==$0 BIN$zbjra9wy==$0 BIN$zbjrBdpw==$0 BIN$zbjra9wy==$0 DBA_FREE_SPACE - RECYCLEBIN 1 Autoextend 3

  8. Recycle Bin: Manual Space Reclamation PURGE {TABLE <table_name>|INDEX <index_name>} PURGE TABLESPACE <ts_name> [USER <user_name>] PURGE [USER_|DBA_]RECYCLEBIN

  9. Bypassing the Recycle Bin DROP TABLE <table_name> [PURGE]; DROP TABLESPACE <ts_name> [INCLUDING CONTENTS] ; DROP USER <user_name> [CASCADE] ;

  10. 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

  11. Querying Data fromDropped Tables USER_TABLES DROPPED TABLE_NAME NO SALES Recyclebin YES BIN$zbjrBdpw==$0 EMPLOYEESBIN$zbjra9wy==$0 EMPLOYEES_PK YES NO SALES_PK DROPPED INDEX_NAME USER_INDEXES SELECT ... FROM "BIN$zbjrBdpw==$0"[AS OF ...] WHERE ...

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

  13. Flashback Database Architecture SGA Not everychange! Redo logbuffer Flashbackbuffer Buffer cache LGWR Everychange Log blockbeforeimagesperiodically. Redologs Flashbacklogs RVWR Back out changesto database using before images. Do forward media recovery. 1 2 … …

  14. Recycle bin Flashback DB > Config. FB DB Monitor FB DB Guar. Res. Pt. Configuring FlashbackDatabase 1. Configure the flash recovery area. 2. Set the retentiontarget. 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;

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

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

  17. Flashback Database: Examples RMAN> FLASHBACK DATABASE TO TIME = 2> "TO_DATE('2004-05-27 16:00:00', 3> 'YYYY-MM-DD HH24: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 b4_load;

  18. Performing Flashback Database Using EM • Select object and • operation type.

  19. Performing Flashback Database Using EM • Launching the Recovery Wizard:

  20. Performing Flashback Database Using EM

  21. Excluding Tablespaces from Flashback Database • 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. ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF} SQL> SELECT name, flashback_on 2 FROM v$tablespace;

  22. 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. • Use the TOBEFORERESETLOGS to flash back to before the last RESETLOGS operation.

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

  24. Monitoring Flashback Database with EM

  25. Recycle bin Flashback DB Config. FB DB Monitor FB DB > Guar. Res. Pt. Guaranteed Restore Points • A guaranteed restore point ensures that you can perform a FLASHBACKDATABASE command to that SCN at any time. SQL> CREATE RESTORE POINT before_load 2 GUARANTEE FLASHBACK DATABASE;

  26. 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

  27. 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

More Related