1 / 33

Module 3 Database Management

Module 3 Database Management. Section 5 Database Backup and Recovery. Introduction to Backup and Recovery. Backup is the process of making some kind of copies of parts of a database, or an entire database

jalia
Download Presentation

Module 3 Database Management

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. Module 3 Database Management ITEC 450 Section 5 Database Backup and Recovery

  2. Introduction to Backup and Recovery • Backup is the process of making some kind of copies of parts of a database, or an entire database • Recovery is the process of rebuilding a database after some part of a database has been lost • Restoration is the process of copying files from a backup • Recovery is the process of executing procedures in Oracle Database to update the recovered backup files to an up to date state ITEC 450

  3. What is Backup? ITEC 450

  4. What is Restoration? ITEC 450

  5. Categories of Database Failures • Instance failures – service interruption, no data loss • DBMS internal exception • Operating system failure • Hardware-related failures except storage media: CPU, Memory board, disk controller • Human error – hardest to prevent, data issue • Programs are run at the wrong time, wrong order, or wrong input • Media failure – data unavailable, recovery needed • Disk damage, file system failures, data file deletion • Tape degradation or damage ITEC 450

  6. Database Backup Choices Backup strategies are driven by the recovery requirements. • Full and incremental backups • Full – a complete copy at the time the backup was run. • Incremental (differential) – the backup of changes since the last full or incremental backup • Consistent and inconsistent backups • Consistent – the data in the backup is from a same point in time • Inconsistent – the backup is done while the database is open and there are changes • Physical and logical backups • Physical – copy physical data files to a backup device • Logical – export of (selected) data from database to a file or a backup device ITEC 450

  7. Backup Guidelines The DBA must assure a recoverable environment. • Make at least two copies of backup to avoid a single point of failure such as a damaged tape • Coordinate local backup strategy with disaster recovery strategy • Consider backups to disk, and then migrating to tape • Be sure to include the system catalog objects in the backup • Verify the correctness of the backup once a while • Database data and application metadata should be backed up at the same time. ITEC 450

  8. Backup Levels and Coverage • Backup levels • Level 0 (full) – a full backup of the database: all physical files associate the database • Level 1 (cumulative) – a backup of all of the data that has changed since the last level 0 backup • Level 2 (incremental) – a backup of all of the data that has changed since the last backup of any level (0, 1 or 2) • Backup coverage • Log archiving – it is required for up-to-time recovery • System catalog – containing backup and recovery information • Database object definition – It’s nice to have it over time to track what changed, and be able to recreate objects if necessary ITEC 450

  9. Recovery Steps • Identify the failure • Analyze the situation • Determine what needs to be recovered • Locate the required image copy backup • Restore the image copy backup • Roll forward through the database log ITEC 450

  10. Recover to Current • Recovery to current usually responds to a media failure, such as accidentally deleted, or disk crashed. • Restored database can be from full, cumulative, and/or incremental. ITEC 450

  11. Point-In-Time Recovery The PIT recovery responds non-media failures, such as application-level problem or user error. It will restore the whole database back to the time before the error. All changes between the time of the error and current will be rolled back. Recovery description: • Restore whole database backup • Apply the database change logs from the time of backup to the time just before the error • Open the database as the moment before the error ITEC 450

  12. What is Recovery? • Redo logs and archive logs consist of records of all transactions made to a database • Controlfiles contain pointers to datafiles, dictating where datafiles should be in relation to redo log entries • If a datafile is restored from a backup, then the controlfile will be ahead of the datafile in time • Restoration of recovered backup is a simple process of applying redo log entries to the datafile, until the datafile “catches up” to the time indicated by controlfile • Archive logs are copies of old redo log files, copied just before redo log files are reused • Restoration can utilize entries in both redo log files and archive log files to complete a recovery ITEC 450

  13. Transaction Recovery Transaction recovery addresses an error from a specific transaction. respond non-media failures, such as application-level problem or user error. Recovery options are: • PIT recovery – remove all changes including the error and other transactions • UNDO recovery – remove only the effects of the bad transaction • REDO recovery – PIT recovery and then redo the good transactions only ITEC 450

  14. Alternatives to Backup and Recovery Other high-availability alternatives can be used: • Standby database or database mirroring – an identical copy of an online production database that is close to being up-to-date. • Data replication or transactional replication – storing and maintaining redundant data in a separate database • Hardware redundancy with disk replication locally or remotely – allocating a secondary device that contains a duplicate copy of the primary device ITEC 450

  15. Disaster Planning Disaster recovery planning, also called contingency planning is the process for: • Any unplanned, extended loss of critical business applications due to lack of computer processing capabilities for more than a 48-hour period. • Any event that has a small chance of transpiring, a high-level of uncertainty, and a potentially devastating outcome. • Examples: floods, earthquakes, hurricanes, fires, terrorist attacks ITEC 450

  16. Disaster Recovery Guidelines The goals are to minimize downtime and loss of data. • Analyze business risks, and not technical needs and issues • Rank applications into groups • Plan for enterprise wide business functions and operational activities • Build an off-site location • Establish a written plan and distribute to all key personnel • Testing disaster plans ITEC 450

  17. Database Planning for Disaster Recovery Database strategies for disaster recovery: • Tape backups, and ship them to the remote site • Storage management software to make point-in-time copies of entire disk packs • Remote mirroring of data to the alternate site over the network • Standby or database mirroring at a different physical site from the primary database location ITEC 450

  18. Module 3 Database Management ITEC 450 Section 6 Oracle Backup and Recovery

  19. Backup and Recovery Concepts • Archivelog and noarchivelog modes • Archivelog – Oracle saves the filled redo logs as archivelogs. You may recover the database to any point in time. • Noarchivelog – Oracle overwrites the filled redo logs. You can’t recover the database changes since the last cold backup took place. • Restoring and Recovery • Restoring a database means copying the physical files from a backup medium. • Recovery is the process of updating database files restored from a backup with the changes made to the database after the backup, typically using redo log files. ITEC 450

  20. Consistent and Inconsistent Backups • Consistent Backups (closed, cold) • To make a consistent backup, the database is shutdown cleanly and remain closed for the duration of the backup. • All committed changes are written to the datafiles, so the datafiles are in a transaction-consistent sate. • When you restore your datafiles from a consistent backup, you can open the database immediately. • Inconsistent Backups (open, online, hot) • If a database is in ARCHIVELOG mode, then you can make inconsistent backups that are recoverable using archived redo log files. • The redo logs must be archived and then backed up with the datafiles to ensure recoverability. • An inconsistent backup is robust a form of backup as a consistent backup. It is common for production environments. ITEC 450

  21. Methods of Backup and Recovery • Two basic methods of backup and recovery: • Cold backups • Hot backups ITEC 450

  22. Tools for Backup and Recovery • Tools used for backup and recovery of an Oracle database are as follows: • Export and Import Utilities • Backup Mode Tablespace Copies • RMAN (Recovery Manager) • Oracle Enterprise Manager and the Database Control ITEC 450

  23. What is a Cold Backup? ITEC 450

  24. Cold Backups • For a cold backup, shut down the database completely and then copy all the files • All datafiles • All redo log files • All archive log files • All controlfiles • Optionally you can also back up parameter files and any networking configuration files • Restore at least all the datafiles and controlfiles • Optionally use more current redo log files, archive log files, and controlfiles—allowing a recovery by applying redo log entries to datafiles ITEC 450

  25. What is a Hot Backup? • Hot backup: performed when DB is online, active, and available for use • Many tools and methods for performing hot backups • Takes a snapshot of a database one file or type of file at a time • Not necessarily consistent across all files in backup • Made of pieces of a DB, where those files making up a complete DB backup are not recoverable to a working database as a group • Individual files can be slotted into a running DB, and can be recovered individually, or as a group ITEC 450

  26. Setting the Database in Archive Log Mode • In archive log mode, the database will create archive logs for you • Archive logs are files that are copied from redo logs when a redo log file is switched out for recycling • Redo logs contain entries of all transactional activity in a database as transactions occur • Redo logs are recycled • If a redo log is recycled, unless the redo log is copied to an archive log, all entries in that redo log group are effectively lost • A database must be in archive log mode to duplicate redo logs to archive logs ITEC 450

  27. Setting the Database in Archive Log Mode ARCHIVE LOG LIST; ARCHIVE LOG STOP; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ITEC 450

  28. Consistent Backups Using Exports • Export can be used to create a consistent backup • Export can scan through all types of data, including tables, redo and archive logs, and undo data • Allows for a consistent backup (export) based on a snapshot of data • In other words, regardless of any changes occurring to DB, during an export, the export utility will read datafiles and undo space to get a consistent backup • Examples: exp system/<password>@oraclass file=classmate.dat owner=classmate consistent=Y exp classmate/classpass@oraclass file=classmatetables.dat tables=(client,customer) ITEC 450

  29. Tablespace Backups • In a tablespace backup, you switch a tablespace into a special mode called backup mode • This allows a physical operating system-level copy of that tablespace • In Oracle 10g, allows a backup mode setting to be applied to all tablespaces at once • Problem with backup mode is a performance issue • Forces all change activity for that tablespace to be copied to the redo logs • If there are enough redo entries produced to recycle a redo log group, then archive log files will be used too • The longer a tablespace remains in backup mode, the more performance is affected, the longer any potential recovery is, and more redo log data is produced ITEC 450

  30. Other Approaches to Backup and Recovery ITEC 450

  31. Configuration of Backup and Recovery • Configure a flash recovery area – storing most backup related files • Run database in ARCHIVELOG mode • It is required for online backup • It enables recovery options such as compete and point-in-time media recovery • RMAN is the backup and recovery tool for Oracle database. ITEC 450

  32. Oracle Recovery Strategy • Response to media failure – recovery to current • Response to non-media failure – PIT recovery, flash back • Instance failure – Oracle performs instance recovery automatically: all you need to do is restart the database. • A power outage causes the server to crash • The server becomes unavailable because of hardware problems • The operating system crashes • One of the Oracle background processes fails. • You issue a SHUTDOWN ABORT statement ITEC 450

  33. Wrap Up • Assignment 8-1 • Using a specific DBMS example to present database security management • Hands-on practice to understand Oracle security: create a user, try to login and create a table • Detailed steps are on the course website • Assignment 3-1-3: Lab3 – Backup and Recovery • Reference to “assginment3-1-3 help.pdf”, otherwise take you a lot of effort to read Oracle documents • Hands-on practice: Cold backup • Hands-on practice: Hot backup • Challenges and technical ITEC 450

More Related