1 / 34

Chapter 16

Chapter 16. User-Managed Backup and Recovery. Introduction to User Managed Backup and Recovery. Backup and recover is one of the most critical skills a DBA must be proficient with. Oracle provides two general types of B&R tools: Recovery Manager (RMAN) User-managed

hiram-williams
Télécharger la présentation

Chapter 16

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. Chapter 16 User-Managed Backup and Recovery

  2. Introduction to User Managed Backup and Recovery • Backup and recover is one of the most critical skills a DBA must be proficient with. • Oracle provides two general types of B&R tools: • Recovery Manager (RMAN) • User-managed • RMAN is Oracle’s flagship B&R tool; automates most of the B&R tasks. • However, understanding how user managed backups work lays the foundation for understanding RMAN and B&R internals. • User managed backups require that the DBA manually perform tasks for B&R. • Focus of this chapter is user-managed B&R

  3. Why Learn about User-Managed B&R? • You still find shops using user-managed B&R techniques. Therefore, you’re required to be knowledgeable about this technology. • Manually executing a user-managed backup, restore, and recovery solidifies your knowledge of the Oracle B&R architecture. This helps immensely when you’re troubleshooting issues with any B&R tool and lays the foundation of core knowledge for key Oracle tools such as RMAN and Data Guard. • You’ll more fully appreciate RMAN and the value of its features. • Nightmarish database-recovery stories recounted by the old DBAs will now make sense.

  4. Making a Cold Backup of a Noarchivelog-Mode Database • Determine where to copy the backup files and how much space is required. • Determine the locations and names of the database files to copy. • Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause. • Copy the files (identified in step 2) to the backup location (determined in step 1). • Restart your database.

  5. Restoring a Cold Backup in Noarchivelog Mode with Online-Redo Logs • Shut down the instance. • Copy the datafiles, online-redo logs, temporary files, and control files back from the backup. • Start up your database.

  6. Restoring a Cold Backup in Noarchivelog Mode Without Online-Redo Logs • Shut down the instance. • Copy the control files and datafiles back from the backup. • Start up the database in mount mode. • Open the database with the OPEN RESETLOGS clause.

  7. Archivelog Architectural Decisions • Where to place the archive-redo logs, and whether to use the fast-recovery area (formerly known as the flash-recovery area) to store the archive-redo logs • How to name the archive-redo logs • How much space should be allocated to the archive-redo log location • How often to back up the archive-redo logs • When it’s okay to permanently remove archive-redo logs from disk • Whether multiple archive-redo log locations should be enabled • If this is a production database, when to schedule the small amount of downtime that’s required

  8. Setting the Archive Location to a User-Defined Disk Location (non-FRA) • Set it to a location that has plenty of space to hold the amount required before you can backup and remove the archive logs. • If the archive redo log file destination fills up, this will hang your database. SQL> alter system set log_archive_dest_1='location=/ora02/oraarch/O11R2' scope=both; SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

  9. Using the FRA for Archive Log Files • Many DBAs prefer to use the FRA to house archive redo log files. • You need to set two parameters to enable the FRA. • DB_RECOVERY_FILE_DEST_SIZE specifies the maximum space to be used for all files that are stored the FRA. • DB_RECOVERY_FILE_DEST specifies the base directory for the FRA. • Make sure the location that you set the FRA to has enough space to contain the archive log files (and any other log files).

  10. Thinking Unoraclethodox FRA Thoughts • Author of the book usually doesn’t use a FRA for archive redo logs or RMAN backup files. • However many DBAs do prefer to use the FRA. • Make sure you carefully think about how you use the FRA and how it meets your requirements for availability. • Both opinions are valid (to FRA or not to FRA).

  11. Enabling ArchivelogMode $ sqlplus / as sysdba SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; • Check it via: SQL> archive log list;

  12. Disabling ArchivelogMode • Sometimes you may want to disable archiving because you’ve enabled it in a test environment and now want to disable it. • Or perhaps you’re doing a large load of data in an environment where you don’t have the requirement to recover data that might be lost in the event a media failure occurs after the data has been loaded. $ sqlplus / as sysdba SQL> shutdown immediate; SQL> startup mount; SQL> alter database noarchivelog; SQL> alter database open;

  13. Reacting to a Lack of Disk Space in Your Archive Log Destination • The archiver background process writes archive-redo logs to a location that you specify. • If for any reason the archiver process can’t write to the archive location, your database hangs. Any users attempting to connect receive this error: ORA-00257: archiver error. Connect internal only, until freed. • Actions you can take to free up space: • Move archive files to a different location. • Compress old files in the archive-redo log location. • Permanently remove old files. • Switch the archive-redo log destination to a different location.

  14. Backing Up Archive-Redo Log Files • You need all archive-redo logs generated since the begin time of the last good backup to ensure that you can completely recover your database. • Only after you’re sure you have a good backup of your database should you consider removing archive-redo logs that were generated prior to the backup. • Strategies for user managed backup of archive redo log files: • Periodically copying archive-redo logs to an alternate location and then removing them from the primary destination • Copying the archive-redo logs to tape and then deleting them from disk • Using two archive-redo log locations • Using Data Guard for a robust disaster-recovery solution

  15. Making a Cold Backup of an Archivelog-Mode Database • Determine where to copy the backup files and how much space is required. • Determine the locations and names of the database files to copy. • Shut down the database with IMMEDIATE, TRANSACTIONAL, or NORMAL . • Copy the files (identified in step 2) to the backup location (determined in step 1). • Restart your database.

  16. Steps for Hot Backups • Ensure that the database is in archivelog mode. • Determine where to copy the backup files. • Determine which files need to be backed up. • Note the maximum sequence number of the online-redo logs. • Alter the database/tablespace into backup mode. • Copy the datafiles with an OS utility. • Alter the database/tablespace out of backup mode. • Archive the current online-redo log, and note the maximum sequence number of the online-redo logs. • Back up the control file. • Back up any archive-redo logs generated during the backup.

  17. Understanding the Split-Block Issue • When performing a Hot Backup, before datafiles can be copied with an OS utility, the tablespace that contains the datafiles to be copied must be placed in backup mode. • The issue is that Oracle is actively writing to datafiles while the datafiles are being copied. • This means the datafiles are most likely corrupted during the copy. • This is know as the fractured block or split block issue.

  18. Understanding the Split-Block Issue

  19. Oracle Normal Mode of Writing Redo

  20. Oracle Writing Full Blocks to Redo Stream when Datafile in Backup Mode

  21. Restore and Recovery from Datafiles Backed up via Hot Backups

  22. Understanding the Need for Redo Generated During Backup

  23. Understanding that Datafiles Are Updated • Common misconception is that datafiles are not written to by database writer during a user-managed Hot Backup. • Datafiles are written to. • Easy to prove.

  24. Restoring and Recovering with the Database Offline • Place your database in mount mode • Restore the datafile from the backup • Issue the appropriate RESTORE statement • Alter your database open

  25. Restoring and Recovering with a Database Online • Take the datafile offline • Restore the datafile from a backup • Recover the datafile • Alter the datafile online

  26. Restoring One Damaged Control File When Multiplexed • Shutdown the database • Copy an existing good control file to the location of the bad one • Startup your database

  27. Restoring When All Control Files Are Damaged • Shut down the database. • Restore a control file from the backup. • Restore all datafiles from the backup. • Start the database in mount mode, and initiate database recovery using the RECOVER DATABASE USING BACKUP CONTROLFILE clause. • For a complete recovery, manually apply the redo contained in the online-redo logs. • Open the database with the RESETLOGS clause.

  28. Reasons for Performing an Incomplete Recovery of an Archivelog-Mode Database • You attempt to perform a complete recovery but are missing the required archive-redo logs or unarchived online-redo log information. • You want to restore the database back to a point in time in the past just prior to an erroneous user error (such as deleted data, dropped table, and so on). • You have a testing environment in which you have a baseline copy of the database. After the testing is finished, you want to reset the database back to baseline for another round of new testing.

  29. Complete vs. Incomplete Recovery • Complete recovery means that you can recover all committed transactions in the database prior to the media failure. • Incomplete recovery means that cannot recover all committed transactions in the database. • Complete recovery can be performed on a single datafile (if the media failure is isolated to a single datafile). • Incomplete recovery must always be performed on all datafiles (that are online) in the database when the recovery process is started.

  30. Types of Incomplete Recoveries • Cancel based • SCN based • Time based • Examples: SQL> recover database until cancel; SQL> recover database until change 12345; SQL> recover database until time '2010-10-21:02:00:00';

  31. Steps for Incomplete Recovery • Shut down the database. • Restore all the datafiles from the backup. • Start the database in mount mode. • Apply redo (roll forward) to the desired point, and halt the recovery process (use cancel-, SCN-, or time-based recovery). • Open the database with the RESETLOGS clause.

  32. Flashing Back a Table • FLASHBACK TABLE TO BEFORE DROP  quickly undrops a previously dropped table. This feature uses the recycle bin. • FLASHBACK TABLE flashes back to a recent point in time to revert the effects of undesired Data Manipulation Language (DML) statements. You can flash back to an SCN, timestamp, or restore point.

  33. Flashing Back a Database • Alternate way of performing an incomplete recovery. • The database must be in archivelog mode. • You must be using an FRA. • The Flashback Database feature must be enabled.

  34. Summary • User managed backups lay the foundation for understand backup and recovery internals. • DBAs should be aware of how to backup and restore using cold and hot backups. • Once you understand user managed backups you will be in a much better position to learn and implement backups using RMAN.

More Related