1 / 32

Oracle backup and recovery strategy

Oracle backup and recovery strategy. Catherine Delamare /IT DB . AGENDA. Why plan backups? Overview of backups Backup implementation in IT/DB Export/Import Recovery. Why plan backups?. Why plan backups?. Hardware never fails Oracle never makes a mistake Users never make mistakes

Patman
Download Presentation

Oracle backup and recovery strategy

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. Oracle backup and recovery strategy Catherine Delamare /IT DB

  2. AGENDA • Why plan backups? • Overview of backups • Backup implementation in IT/DB • Export/Import • Recovery

  3. Why plan backups?

  4. Why plan backups? • Hardware never fails • Oracle never makes a mistake • Users never make mistakes • Programmers never make mistakes • Physicists never make mistakes • Database administrators (DBAs) never make mistakes

  5. Why plan backups? • The only insurance you have against failures causing loss of data: • Human errors • Hardware failures • Power failures • Software errors • DBA’s job security!

  6. Why plan backups?

  7. Overview of backups • Physical backups • protect against global failure • Cold backups : database needs to be stopped • Hot backups : database keeps on running • Logicalbackups (or “exports”) • protect against deletion of (a) table(s) • can be run by the user himself

  8. ARCHIVELOG Mode or not? (1/3) • Definition • All changes are kept in “archived logs” • The more activity, the more archived logs • Central db: 600 logs per week (30M each=18G transactions volume per week) • Advantages of ARCHIVELOG mode • Complete recovery up to the last committed transaction before the failure (“point-in-time” recovery) • Mandatory for mission-critical applications • Pre-requisite to run hot backups

  9. ARCHIVELOG Mode or not? (2/3) Sunday Friday BACKUP CRASH 1 2 3 4 5

  10. ARCHIVELOG Mode or not? (3/3) • ARCHIVELOG mode implications: • Additional overhead(even more overhead during online backup) • Oracle hangs if the archived disk fills up • Archived logs must be kept in a safe place • Tape facilities implied • More work for the DBA: • Space management • Log files tracking

  11. Offline backup (COLD) • Database must be down • Backup of all datafiles, redo log files and control files • Disk space needed

  12. Benefits of offline backup • Best guarantee for a restartable database (Oracle recommends a weekly cold backup) • Quick recovery (by replacing all datafiles, redo log files and control files by their backup version)

  13. Drawbacks of offline backup • Database must be down • Loss of data between backup and failure if not using ARCHIVELOG mode • Corruption may not be recognized! • Might not backup everything:Backup scripts must be often checked and tested!

  14. Online (hot) backup • Meaningful only if database is operating in ARCHIVELOG mode: if not, hot backups are useless for recovery • Database must be up and open • Do a favor to users, schedule it during off hours • The unit of an online backup is a tablespace

  15. Benefits of online (hot) backup • Database remains available • Control over what to backup and when • Not all tablespaces need to be backed up at the same time

  16. Drawbacks of online (hot) backup • Setup and administration is more complex • Leading cause of recovery problems

  17. Online backup procedure • ALTER TABLESPACE tsp BEGIN BACKUP • Copy/Backup all datafiles in the tablespace • ALTER TABLESPACE tsp END BACKUP • ALTER SYSTEM SWITCH LOGFILE • Backup archived log files • ALTER DATABASE BACKUP CONTROLFILE to ‘filespec’

  18. Backups implementation (1/3) • Most production databases run in ARCHIVELOG mode • Archived redo logs stored on two different tape devices • Home-grown scripts perform • depending on the service: • daily/weekly cold/hot backup + • daily full export

  19. Backups implementation (2/3) • On mission-critical systems : • Shutdown the oracle database • Detach the mirrors • Restart the database on one side => service interruption of the order of 1-6 minutes • Backup the frozen side • Reattach the mirrors • E.g.:central cluster, cryogenics, remedy,tape management and radio-frequency databases

  20. Backups implementation (3/3) • On the central database cluster.. • The 2 nodes synchronize a clean rundown of the common database • Node A detaches the database mirrors and restarts DB • Tells node B to restart as well • Performs a cold backup onto disks • Reattaches the mirrors • Service interruption: 6 minutes

  21. Oracle EXPORT/IMPORT • The Export and Import Oracle utilities generate a file with a logical copy of the data and application • Export and Import are useful to recover specific items lost due to user errors • Export and Import help migration to different releases of the Oracle RDBMS

  22. EXPORT/IMPORT(cont’d) • Export and Import support object types • Export writes object type definitions and all associated data to the dump file • Import then re-creates these items from the dump file • The definition statements for derived types are exported, same applies to OIDs

  23. EXPORT (logical) • Database must be up and running • Export reads the database using SQL • Export file contains create and insert statements • Export provides a Read consistent view of the database • Changes made after export begins are not included

  24. Benefits of export • Easy to recover individual items • Portable: • can be used to move data from one machine to another • useful in a heterogeneous environment • Unlike backup, export identifies physical data block corruption since it performs a full table scan while exporting a table

  25. Drawbacks of export • Does not provide point in time recovery • Export/Import are usually long processes.To improve speed: • import with ROWS=Y INDEX=N to restore data • then import with ROWS=N and INDEX=Y to build indexes

  26. Export/import examples • Export of a full database (from a DBA):EXP FULL=Y FILE=fullexp.dmp LOG=fullexp.log • Export of a specific schema:EXP USER/PASSWORD TABLES=(RUN1,RUN2) FILE=runs12.dmpLOG=runs12.log

  27. Recovery considerations • Media recovery: • Done in response to a RECOVERY command • Has to be done by a DBA after identifying the appropriate recovery action depending on the failure (datafile, tablespace, database)

  28. Recovery in NOARCHIVELOG mode • Only offline backup (or export) can be used • What to do: • Restore all copies of datafiles, control files and redo log files • startup the database • You are back in the status when the backup (export) were run

  29. Recovery in ARCHIVELOG mode • Only way to perform the point in time recovery • What to do: • Restore from backup the damaged Oracle files • Apply Redo log changes • Database recovery • Tablespace recovery • Datafile recovery

  30. Benefits of recovery • Database recovery can be complete or not: • recover database (complete) • recover database until time ‘2001-07-10:10:05:00’ • recover database until cancel • recovery of TABLESPACE or DATAFILE can only be complete

  31. Implications of recovery • Database is not open to the users • It can take a long time depending on the number of redo log files to be applied. • Must have ALL required files (datafiles, archived redo log files).

  32. SUMMARY • 5 real cases of Oracle recovery have been performed in the past 7 years (Successful…) • Many items have been recovered from export files to repair accidental deletion of items • WHO WANTS TO TEST NEXT?

More Related