1 / 19

Database recovery contd …

Database recovery contd …. 10. 03 . 2011. RECAP. RECAP. Database Backup. Causes of failure: Transaction failure System/processor failure Media failure When secondary media (disk) fails, data may become unreadable.

hamish-hernandez
Download Presentation

Database recovery contd …

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. Database recovery contd… 10. 03 . 2011

  2. RECAP RECAP

  3. Database Backup • Causes of failure: • Transaction failure • System/processor failure • Media failure • When secondary media (disk) fails, data may become unreadable. • We typically rely on backing up the database to cheaper magnetic tape or other backup medium for a copy that can be restored. • Challenge: • When an DBMS is running, it is not possible to backup its files (data files, system logs, redo logs, etc.) as the resulting backup copy on tape may be inconsistent. • “old practice”: • Shut down the DBMS (and thus all applications), do a full backup, copy everything on to tape. Then start up again.

  4. Types of back up • Logical (Hot or Cold) • Export facility • Physical (with or without a Recovery manager) • File system and SQL statements back up • Incremental versus full back up Other than data, Other parts of the database to be backed up include the log files and any control files that monitor the state of the database

  5. Incremental • An Incremental backup will backup only those data changed or added since the last full backup. Sometimes called a delta backup. • Follows something like: • Weekend: Do a shutdown of the DBMS, and full backup of the database onto a fresh tape(s). • Nightly: Do an incremental backup onto different tapes for each night of the week.

  6. Please see Database Backup Examples from the notes • MS Access • Microsoft SQL Server • Oracle

  7. Discussion slides

  8. Scenario: • You are a new hiring at a company – you realize that there isn’t a database back up strategy in place. What do you do?

  9. Advisory note to your superior / ICT director may include/highlight: • Objectives of DB backup • Uses of a DB back up • Information of how to develop a back up and recovery strategy

  10. Advisory note to your superior / ICT director may include/highlight: • Objectives of DB backup • Protect the database from numerous types of failures • Increase Mean-Time-Between-Failures (MTBF) • Decrease Mean-Time-To-Recover (MTTR) • Minimize data loss

  11. A database backup will enable: • Archive historical data • Save table definitions (with or without data) to protect from user error failure • Move data between machines and databases or versions of a server (e.g. Oracle server) • Transport tablespaces between databases

  12. Normally in binary formats Data Base File *.dmp Exp Data Base Imp

  13. Steps in developing a back up and recovery strategy • Identify the business requirements • E.g. Evolutionary process • Identify technical requirements • Hardware, software, man power and time • Database configurations • Transaction volume • Frequency of backups • Define operational requirements • 7-day / 24-hour operations • Testing and validating backups

  14. Back up and recovery strategy considerations • Identify critical data from the rest • Assess tolerance for data loss - Recovery Point Objective (RPO) • Assess tolerance for downtime - Recovery Time Objective (RTO) • Determine backup retention policy • Assess data protection requirements What are example questions that you can raise for each consideration?

  15. Back up and recovery strategy considerations • Identify critical data from rest • Design recovery requirements around data criticality • Assess tolerance for data loss - Recovery Point Objective (RPO) • How frequently should backups be taken? • Point-in-time recovery required? • Assess tolerance for downtime - Recovery Time Objective (RTO) • Downtime: Problem identification + recovery planning + systems recovery • Tiered RTO per level of granularity, e.g. database, tablespace, table, row

  16. Backup and recovery strategy considerations • Determine backup retention policy • Onsite, offsite, long-term • Assess data protection requirements • Physical: Disasters, outages, failures, corruptions • Logical: Human errors, application errors

  17. Your homework! • Home work / self research • Form groups for research • In your group, research and report in the next class what the following DBMSs in the market address recovery • MS Access • Oracle • MS SQL (Server) • MySQL • PostgreSQL Due next week (a small write up + 5minutes presentation) After the written CAT

  18. Thanks

More Related