IT420: Database Management and Organization

1. IT420: Database Management and Organization Crash Recovery 12 April 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. Goals • Finish database security • Database Recovery • Problems • Solutions • DBA Tasks Kroenke, Database Processing

3. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

4. Database Security • Database security - only authorized users can perform authorized activities • Developing database security • Determine users’ rights and responsibilities • Enforce security requirements using security features from both DBMS and application programs Kroenke, Database Processing

5. Application Security • If DBMS security features are inadequate, additional security code could be written in application program • Example In Project 2? Kroenke, Database Processing

6. SQL Injection Attack • SQL injection attack occurs when data from the user is used to modify a SQL statement • Example: • User input: 71111 OR TRUE SELECT * FROM EMPLOYEE WHERE EMPLOYEE.ID = 71111 OR TRUE; • Result: every row of the EMPLOYEE table will be returned • Check user input ! Kroenke, Database Processing

7. Database Security Review • Purpose:only authorized users can perform authorized activities • Methods: • Run DBMS behind a firewall • Apply the latest OS and DBMS service packs and fixes • Use the least functionality possible • Protect the computer that runs the DBMS • Manage accounts and passwords • Write additional security code in application programs Kroenke, Database Processing

8. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Facilitate database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

9. Review: ACID Properties • Atomicity: All actions happen, or none happen • Consistency • Isolation • Durability: If a transaction commits, its effects persist • The Recovery Manager guarantees Atomicity & Durability. Kroenke, Database Processing

10. Motivation • Atomicity: • Transactions may abort (“Rollback”) – need restore previous state • Durability: • DBMS might stop running – need preserve state Desired behavior after system restarts: T1, T2 & T3 should be durable. T4 & T5 should be aborted (effects not seen). Kroenke, Database Processing

11. Basic Idea: Logging • Periodically save the database to disk • Keep a transaction log - small • Saved on another disk • Contains records of the data changes in chronological order, for all transactions Kroenke, Database Processing

12. Transaction Log Kroenke, Database Processing

13. Write-Ahead Logging (WAL) • Must write to log before writing to the database  atomicity • Must write all log records for a transaction before commit  durability Kroenke, Database Processing

14. Class Exercise • Write transaction log entries for the following schedule: • T1: R(Ch) R(Sa), W(Sa), Abort • Ch = 100Sa=1000, Sa=500 • T2: R(Ch) R(Sa), W(Sa), Commit • Ch = 100Sa=1000, Sa=1100 Kroenke, Database Processing

15. Rollback/Rollforward • When there is a failure: • Analyze: figure out which transactions commited, which failed • REDO (Rollforward): restore database using saved data and re-execute all changes since the last save • UNDO (Rollback): undo the changes made by failed transaction to the database Kroenke, Database Processing

16. Rollforward After-image: a copy of every database record (or page) after it was changed Kroenke, Database Processing

17. Rollback Before-image: a copy of every database record (or page) before it was changed. Also used for simple transaction abort Kroenke, Database Processing

18. Checkpoint • A checkpoint is a point of synchronization between the database and the transaction log • DBMS refuses new requests, finishes processing outstanding requests, and writes its buffers to disk • The DBMS waits until the writing is successfully completed  the log and the database are synchronized • Checkpoints speed up database recovery process • Database can be recovered using after-images since the last checkpoint • Checkpoint can be done several times per hour • Most DBMS products automatically checkpoint themselves Kroenke, Database Processing

19. Database Recovery:A Processing Problem Occurs Kroenke, Database Processing

20. Database Recovery: Recovery Processing Kroenke, Database Processing

21. Class Exercise • T1: R(Ch) R(Sa), W(Sa), Abort • Ch = 100Sa=1000, Sa=500 • T2: R(Ch) R(Sa), W(Sa), Commit • Ch = 100Sa=1000, Sa=1100 • Process crash • Analysis • REDO • UNDO Kroenke, Database Processing

22. DBA Tasks for Recovery • Ensure database saves and logs are generated • Periodically backup database • Periodically backup log • On disk • On tapes Kroenke, Database Processing

23. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

24. Managing the DBMS • Generate database application performance reports • Investigate user performance complaints • Assess need for changes in database structure or application design • Modify database structure • Evaluate and implement new DBMS features • Tune the DBMS Kroenke, Database Processing

25. Maintaining the Data Repository • DBA is responsible for maintaining the data repository • Data repositories are collections of metadata about users, databases, and its applications • The repository may be • Virtual as it is composed of metadata from many different sources: DBMS, code libraries, Web page generation and editing tools, etc. • An integrated product • The best repositories are active and they are part of the system development process Kroenke, Database Processing

26. DBA Tasks - Conclusions • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing