1 / 26

IT420: Database Management and Organization

IT420: Database Management and Organization. Crash Recovery 12 April 2006 Adina Cr ă iniceanu www.cs.usna.edu/~adina. Goals. Finish database security Database Recovery Problems Solutions DBA Tasks. DBA Tasks. Managing database structure Controlling concurrent processing

cecil
Download Presentation

IT420: Database Management and Organization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related