ORACLE FLASHBACK

Table of Contents

• 1 Prerequisites
• 2 Configuration steps
• 3 Estimating log store space
• 4 Recover from flashback logs
• 5 Views
• 6 Recyclebin
  • 6.1 Show recyclebin
  • 6.2 Recover table from recyclebin
  • 6.3 Droping table without recyclebin
  • 6.4 Purging recyclebin
• 7 ERRORs

1 Prerequisites

• database ARCHIVELOG mode
• UNDO_MANAGEMENT=auto

2 Configuration steps

1. setting param DB_RECOVERY_FILE_DEST which point to flashback logs location
2. setting param DB_RECOVERY_FILE_DEST_SIZE which specify summary size of all logs
3. setting param DB_FLASHBACK_RETENTION_TARGET which specify logs store time in miutes
4. startup database in mount mode
5. turn on flashback
   • sql> ALTER DATABASE FLASHBACK ON;
6. check:
   • sql> SELECT log_mode,flashback_on FROM v$database;
7. open database

3 Estimating log store space

1. setting DB_FLASHBACK_RETENTION_TARGET
2. sql> SELECT estimated_flashback_size FROM v$flashback_database_log;

4 Recover from flashback logs

1. startup database in mount mode
2. sql> FLASHBACK DATABASE TO…
   • TIMESTAMP SYSDATE-1]
   • TO TIMESTAMP to_timestamp('2009-09-28 11:00','yyyy-mm-dd hh24:mi')
   • TO SCN [scn#];
3. sql> ALTER DATABASE OPEN READ ONLY;
   • at READ-ONLY mode it is possible to verify data
   • if data are not correct, so repeat steps from 1 to 3
4. turn off database
5. startup database in mount mode
6. sql> ALTER DATABASE OPEN RESETLOGS;

5 Views

• sql> SELECT oldest_flashback_scn,oldest_flashback_time,flashback_size FROM v$flashback_database_log;

  oldest_flashback_scn, oldest_flashback_time	oldest timestamp when data can be recovered
  	(To verify this operation, try delete oldest flashback log then put above SELECT command and oldest point was changed)
  estimated_flashback_size	the neccessary space to ensure DB_FLASHBACK_RETENTION_TARGET
  flashback_size

• sql> SELECT * FROM v$flash_recovery_area_usage;
• sql> SELECT * FROM v$recovery_file_dest;
• sql> SELECT SCN_TO_TIMESTAMP(10213123) FROM DUAL;

6 Recyclebin

6.1 Show recyclebin

• sql> SHOW RECYCLEBIN

6.2 Recover table from recyclebin

• sql> DROP TABLE [table_name]
• sql> FLASHBACK TABLE [table_name] TO BEFORE DROP;

6.3 Droping table without recyclebin

• sql> DROP TABLE [table_name] PURGE;

6.4 Purging recyclebin

• sql> PURGE TABLE [table_name];
• sql> PURGE TABLESPACE [ts_name];
• sql> PURGE TABLESPACE [ts_name] USER [user];
• sql> PURGE RECYCLEBIN
• sql> PURGE DBA_RECYCLEBIN;

7 ERRORs

[ symptom ]

• ORA-38729: Not enough flashback database log data to do FLASHBACK

[ solution ]

• timestamp older then oldest_flashback_time