Chapter 13 Performing Flashback Recovery

1. Chapter 13Performing Flashback Recovery

2. Background • One of the authors had extensive material on Flashback due to articles authored for OTN

3. Objectives • Flashback database • Undrop a table • Flash back a table33

4. Three Flavors of Flashback • Flashing back a database • Undropping a table • Flashing back a table

5. Checking the Flashback Status of a Database • Simple check to verify the status of flashback SQL> select flashback_on from v$database;

6. Enabling Flashback on a Database 1. Make sure the FRA is defined in the database 2. Make sure the database is in archivelog mode 3. Make sure the database is in mounted 4. Enable flashback for the database by issuing the following SQL statement: SQL> alter database flashback on;

7. Disabling Flashback on a Database SQL> alter database flashback off; SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO

8. Flashing Back a Database from RMAN • A specific point in time, specified by date and time • A specific SCN number • The last resetlogs operation • A named restore point

9. Flashing Back a Database from RMAN • Pre-steps • Set database into mount mode • Shutdown immediate • Startup mount • Post-steps • Alter database open resetlogs

10. Flashing Back to a Specific SCN • Perform the pre-steps • Check the current SCN • You can only flashback to a point in time prior to the scn RMAN> flashback database to scn <scn value> • Perform post-steps

11. Flashing Back to a Specific Time • Perform pre-steps RMAN> flashback database to time ‘<date>’; • Perform post-steps

12. Flashing Back to a Restore Point • Perform pre-steps RMAN> flashback database to restore point <restore point>; • Perform post steps

13. Flashing Back to Before the Last resetlogs Operation • Perform pre-steps RMAN> flashback database to before resetlogs; • Perform post-steps

14. Flashing Back a Database from SQL • You can also issue flashback from SQL*Plus • A specific point in time, specified by date and time • A specific SCN • A named restore point • Syntax the same, except you issue the commands from SQL*Plus

15. Finding Out How Far Back into the Past You Can Flash Back SQL> select * from v$flashback_database_log; • OLDEST_FLASHBACK_SCN SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

16. Estimating the Amount of Flashback Logs Generated at Various Times SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss'; SQL> select * from v$flashback_database_stat order by begin_time; • ESTIMATED_FLASHBACK_SIZE

17. Estimating the Space Occupied by Flashback Logs in the Flash Recovery Area SQL> select * from v$flashback_database_log; • ESTIMATED_FLASHBACK_SIZE

18. Creating Normal Restore Points • Restore point is a pointer to an SCN number at the time the restore point was created SQL> create restore point rp1;

19. Creating Guaranteed Restore Points • Guarantee that you can flash back and all logs are available SQL> create restore point rp2 guarantee flashback database; • Make sure you drop the restore point, otherwise the logs are kept indefinitely

20. Listing Restore Points SQL> col time format a32 SQL> col name format a10 SQL> select * from v$restore_point order by 2,1;

21. Dropping Restore Points SQL> drop restore point rp2;

22. Recovering a Dropped Table • Logon to schema that owns table SQL> show recyclebin SQL> flashback table accounts to before drop; • To permanently drop: SQL> drop table accounts purge;

23. Undropping a Table When Another Exists with the Same Name • Drop the existing table so there will be no conflict for the name of the table undropped • Undrop the table but reinstate it to a different name SQL> flashback table accounts to before drop rename to new_accounts;

24. Undropping a Specific Table from Two Dropped Tables with the Same Name SQL> show recyclebin SQL> flashback table "BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0" to before drop;

25. Checking the Contents of the Recycle Bin SQL> show recyclebin SQL> select * from user_recyclebin; SQL> select * from dba_recyclebin;

26. Restoring Dependent Objects of an Undropped Table SQL> col type format a5 SQL> col original_name format a15 SQL> select original_name, object_name, type, can_undrop from user_recyclebin;

27. Turning Off the Recycle Bin SQL> alter session set recyclebin = off; SQL> alter system set recyclebin = off;

28. Clearing the Recycle Bin SQL> purge recyclebin; SQL> purge dba_recyclebin; SQL> begin 2 execute immediate 'purge recyclebin'; 3 end; 4 /

29. Querying the History of a Table Row (Flashback Query) select acc_status, versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation from accounts versions between scn minvalue and maxvalue where accno = 3760 order by 3;

30. Flashing Back a Specific Table SQL> select row_movement from user_tables where table_name = 'ACCOUNTS'; • If not enabled: SQL> alter table accounts enable row movement; SQL> flashback table accounts to timestamp to_date ('23-JAN-07 18.23.00', 'dd-MON-YY hh24.mi.ss');