Oracle Flashback Technologies COUG Presentation – Feb 2010

1. Oracle Flashback Technologies COUG Presentation – Feb 2010 Feb 25, 2010 Ray Smith raymond.smith@cgi.com

2. Agenda: • What are my objectives today? • Introduce you to some flashback technologies • Raise your awareness of what’s available • I won’t go over everything, 1 hour, not 5 days.. • Maybe light a spark so you’ll go play too. • Fly through the slides as quickly as possible. • Go break my system and fix it… lots of times

3. Flashback technologies • Flashback Query • Flashback Table • Flashback Drop • Flashback Versions Query • Flashback Transaction Query • Flashback Database • Flashback Data Archive

4. Flashback technologies • Flashback Query • SQL> exec dbms_flashback package.. 9i R1. • Scary code: DECLARE CURSOR emp_cur IS SELECT * FROM EMPLOYEE; v_rec emp_cur%rowtype; BEGIN DBMS_FLASHBACK.ENABLE_AT_TIME ('25-FEB-10 08:10:58'); open emp_cur; DBMS_FLASHBACK.DISABLE; LOOP fetch emp_cur into v_rec; EXIT WHEN emp_cur%NOTFOUND; INSERT INTO EMPLOYEE_TEMP VALUES (v_rec.emp_id, v_rec.name, v_rec.age ); END LOOP; close emp_cur; COMMIT; END; • SQL> SELECT .. FROM.. AS OF…. WHERE

5. Flashback technologies • Flashback Table • SQL> flashback table … to timestamp … • SQL> flashback table … to SCN … • Flashback Drop • Use of Recyclebin • flashback table … to before drop;

6. Flashback technologies • Flashback Versions Query • Ability to capture changes to data over a given time. • VERSIONS_STARTTIME (start timestamp of version); • VERSIONS_STARTSCN (start SCN of version); • VERSIONS_ENDTIME (end timestamp of version); • VERSIONS_ENDSCN (end SCN of version); • VERSIONS_XID (transaction ID of version); and • VERSIONS_OPERATION (DML operation of version).

7. Flashback technologies Flashback Transaction Query Who run that? What was it? What is the UNDO code? Use of FLASHBACK_TRANSACTION_QUERY SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> select * from FLASHBACK_TRANSACTION_QUERY where xid= HEXTORAW('0003001A0000010D');

8. Flashback technologies Flashback Database – through RESETLOGS (10.2) Create a flash recovery area Restart database ( mount exclusive ) SQL> ALTER DATASE FLASHBACK ON; SQL> ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>; SQL> ALTER DATABASE OPEN; Restore Points create restore point PREQA_TESTING; create restore point PREQA_TESTING guarantee flashback database; drop restore point PREQA_TESTING;

9. Flashback technologies Flashback Data Archive SQL> create FLASHBACK ARCHIVE default <flasharea> tablespace <flash_tablespace> quota 200M retention 1 MONTH; SQL> alter table <table> flashback archive <flasharea>;

10. Why are DBA’s avoiding using Flashback options? Complicated to setup – I think.. Too confusing.. I don’t have time to figure it out It slows down my system.. I think It’s new, so probably not so reliable

11. Why DBA’s should get to know it… • It’s actually quite good. • It can seriously save your bacon. • Fast recoveries

12. How do you configure Flashback? • It Depends.. • Flashback Query – no setup required – Just undo • Flashback Table – no setup required – Just undo • Flashback Drop – Recyclebin – on by default. • Flashback Versions - no setup required – Just undo • Flashback Database – Requires a flash recovery area / feature to be switched on. • Flashback Transaction – may require supplimental data • Flashback Data Archives – needs flashback tablespace

13. How to Flashback Database? FLASHBACK DATABASE TO [BEFORE] SCN <system_change_number> FLASHBACK DATABASE TO [BEFORE] TIMESTAMP <system_timestamp_value> FLASHBACK DATABASE TO [BEFORE] RESTORE POINT <restore_point_name>

14. Live Demo

15. Summary • Start playing with flashback. • Potential issues • ERROR at line 1: ORA-08185: Flashback not supported for user SYS