SQL Gone Bad

1. SQL Gone Bad What to do when faulty SQL code corrupts your database Mary Louise Powers Tom Smull Lehigh University

2. Lehigh University technical environment • All SCT Banner modules installed • All Self Service products installed • AIX/Unix, Oracle 9i • Luminis / Portal

3. What can go wrong? • Batch updates to the system • An unnoticed semi-colon • “Fixes” to the system • People make mistakes

4. When it does go wrong • Symptoms • Scope (what / where / how many) • Causes • Possible Solutions / Resources • Effect repairs • Communicate / Document • Punish Offenders

5. What tools are available? • Most recent backup • Last night’s export • LogMiner • Flashback

6. LogMiner • Allows DBA to examine contents of archived redo logs • Especially: SQL_REDO, SQL_UNDO • Knowing when the error occurred is helpful • Knowing Oracle user who did it is also helpful

7. LogMiner script generation • Need access to current data dictionary • Which archive logs are to be searched? • Which tables are suspect? • Which user performed the erroneous update?

8. accept start_seq prompt 'Starting sequence: ' accept end_seq prompt ' Ending sequence: ' spool logminer_&start_seq select '-- exec dbms_logmnr_d.build(''ban6dictfile'',''/home/local/dba/logmnr'');' from sys.dual; select 'exec dbms_logmnr.add_logfile(LogFileName=>'''||name||''',Options=>dbms_logmnr.NEW);' from V_$ARCHIVED_LOG where SEQUENCE# = to_number('&start_seq'); select 'exec dbms_logmnr.add_logfile(LogFileName=>'''||name||''',Options=>dbms_logmnr.ADDFILE);' from V_$ARCHIVED_LOG where SEQUENCE# between (to_number('&start_seq') +1) and (to_number('&end_seq')) order by SEQUENCE#;

9. -- PART 2… select 'exec dbms_logmnr.start_logmnr(DictFileName=>''/~/dba/logmnr/ban6dictfile'');' from sys.dual; select 'spool LOGMNR.lst select to_char(timestamp,''YY-MM-DD HH24:MI:SS'') DATE_TIME, username, sql_redo /* your code goes here */ from sys.v_$logmnr_contents /* where seg_name IN (''SPBPERS''); */ spool off exec dbms_logmnr.end_logmnr;' from sys.dual; spool off

10. sys.v_$logmnr_contents (partial) Name Type ------------------------------- --------------  SCN NUMBER CSCN NUMBER  TIMESTAMP DATE COMMIT_TIMESTAMP DATE THREAD# NUMBER LOG_ID NUMBER . SEG_OWNER VARCHAR2(32)  SEG_NAME VARCHAR2(256) SEG_TYPE_NAME VARCHAR2(32) ROW_ID VARCHAR2(19)  SESSION# NUMBER  SERIAL# NUMBER  USERNAME VARCHAR2(30) OPERATION VARCHAR2(32) OPERATION_CODE NUMBER  SQL_REDO VARCHAR2(4000)  SQL_UNDO VARCHAR2(4000) . SQL_COLUMN_TYPE VARCHAR2(32) SQL_COLUMN_NAME VARCHAR2(32)

11. -- exec dbms_logmnr_d.build('ban6dictfile','/home/local/dba/logmnr'); exec dbms_logmnr.add_logfile(LogFileName=>'/u5/ORACLE/arch_ban6_1_136253.dbf', Options=>dbms_logmnr.NEW); exec dbms_logmnr.add_logfile(LogFileName=>'/u5/ORACLE/arch_ban6_1_136254.dbf', Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/u5/ORACLE/arch_ban6_1_136255.dbf', Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/u5/ORACLE/arch_ban6_1_136256.dbf', Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.add_logfile(LogFileName=>'/u5/ORACLE/arch_ban6_1_136257.dbf', Options=>dbms_logmnr.ADDFILE); exec dbms_logmnr.start_logmnr(DictFileName=>'/home/local/dba/logmnr/ban6dictfile'); spool LOGMNR.lst select to_char(timestamp,'YY-MM-DD HH24:MI:SS') DATE_TIME, username, sql_redo, sql_undo from sys.v_$logmnr_contents where seg_name = 'SPBPERS'; and username = 'SATURN' spool off exec dbms_logmnr.end_logmnr;

12. -- FINAL GENERATED RECOVERY CODE: update "SATURN"."SPBPERS" set "SPBPERS_ETHN_CODE" = 'H' where "SPBPERS_ETHN_CODE" IS NULL and ROWID = 'AAAGIeAADAABX+KAA'; update "SATURN"."SPBPERS" set "SPBPERS_ETHN_CODE" = 'X' where "SPBPERS_ETHN_CODE" IS NULL and ROWID = 'AAAGIeAADAABX+KAA'; update "SATURN"."SPBPERS" set "SPBPERS_ETHN_CODE" = 'W' where "SPBPERS_ETHN_CODE" IS NULL and ROWID = 'AAAGIeAADAABX+KAA'; update "SATURN"."SPBPERS" set "SPBPERS_ETHN_CODE" = 'H' where "SPBPERS_ETHN_CODE" IS NULL and ROWID = 'AAAGIeAADAABX+KAB';

13. Oracle Flashback • Lets you see data as it was in the past • Allows online processing • Need to know what was changed • Can only go back as far as Undo Retention time • Can restore accidental drop/delete • Useful to reset tables in testing mode

14. Requirements/Limitations • Oracle 9i or higher • Must use Auto Undo Management • Limited to undo retention time • User must have certain privileges • Flashback any/target table • Execute on dbms_flashback

15. Flashback vs LogMiner • Logminer lets you review transactions to see what happened. • Flashback requires you to know what happened. • Flashback is limited to the retention time – Logminer is limited by archivelog availability. • Flashback can provide a before/after view of data in real time.

16. Flashback Query – Oracle9i • Dbms_flashback package • exec dbms_flashback.enable_at_time(…); • select * from mytable; • Exec dbms_flashback.disable;

17. declare Type pers_cursor is REF CURSOR; pcur pers_cursor; prow spbpers%ROWTYPE; BEGIN -- make sure no transactions or flashback in progress commit; dbms_flashback.disable; -- set the database back to previous time dbms_flashback.enable_at_time(to_timestamp ('16-NOV-2005', 'DD-MON-YY')); open pcur for 'select * from spbpers where spbpers_ethn_code is not null'; -- move back to present by disabling flashback. dbms_flashback.disable; LOOP fetch pcur into prow; exit when pcur%NOTFOUND; update spbpers set spbpers_ethn_code = prow.spbpers_ethn_code where spbpers_pidm = prow.spbpers_pidm and spbpers_ethn_code is null; commit; END LOOP; END;

18. Flashback Query – Oracle9i • “as of” clause • Select * from mytable as of timestamp ‘…’; • Example: -- Restore rows erroneously deleted: insert into mytable (select * from mytable as of timestamp systimestamp - interval '15' minute minus select * from mytable);

19. Flashback Enhancements - 10g • Flashback table • Flashback database • Version(s) query

20. Flashback Table • Alternative to point in time recovery from backup. • Restores table to earlier point in time or “undrops” table. • Requires select, insert, delete, alter, and flashback privileges on table. • To “rewind” table, enable row movement. • Flashback table <tablename> to timestamp. • Recycle bin holds dropped tables.

21. Flashback table to earlierpoint in time alter table mytable enable row movement; flashback table mytable to SCN <scn#>; Or flashback table mytable to TIMESTAMP ‘<timestamp>’;

22. Flashback Dropped Table FLASHBACK TABLE TO BEFORE DROP ORIGINAL NAME RECYCLEBIN NAME DROP TIME ------------------- ------------------------------------------------------ ---------------------------- NAME_ADDR BIN$6u0c1Kv2wG7gM4C0PwLAbg==$0 2004-12-10:19:25:47 NAME_ADDR BIN$6R5+rrqmwPbgM4C0PwLA9g==$0 2004-11-17:19:30:19 MYTABLE BIN$BdpRTDoogGrgQ4C0PwKAag==$0 2005-11-18:11:20:36 SQL> select * from "BIN$BdpRTDoogGrgQ4C0PwKAag==$0"; ID NAME ---------- ----------------------------------- 1 Smull 2 Winwood 3 Smith SQL> flashback table "BIN$BdpRTDoogGrgQ4C0PwKAag==$0" to before drop; Flashback complete. SQL> select * from mytable; ID NAME ---------- ----------------------------------- 1 Smull 2 Winwood 3 Smith

23. Managing These Tools • Production vs Test Systems • Audit Trails • Templates

24. References • Oracle Database Backup and Recovery Advanced User's Guide • Oracle Database Backup and Recovery Basics • Oracle Database Recovery Manager Reference

25. Questions?

26. Presenters • Mary Louise Powers • mlp5@lehigh.edu • Tom Smull • tjs5@lehigh.edu