oracle backup and recovery strategy
Download
Skip this Video
Download Presentation
Oracle backup and recovery strategy

Loading in 2 Seconds...

play fullscreen
1 / 32

Oracle backup and recovery strategy - PowerPoint PPT Presentation


  • 255 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle backup and recovery strategy' - Patman


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
oracle backup and recovery strategy

Oracle backup and recovery strategy

Catherine Delamare /IT DB

agenda
AGENDA
  • Why plan backups?
  • Overview of backups
  • Backup implementation in IT/DB
  • Export/Import
  • Recovery
why plan backups1
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
why plan backups2
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!
overview of backups
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
archivelog mode or not 1 3
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
archivelog mode or not 2 3
ARCHIVELOG Mode or not? (2/3)

Sunday

Friday

BACKUP

CRASH

1

2

3

4

5

archivelog mode or not 3 3
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
offline backup cold
Offline backup (COLD)
  • Database must be down
  • Backup of all datafiles, redo log files and control files
  • Disk space needed
benefits of offline backup
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)
drawbacks of offline backup
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!
online hot backup
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
benefits of online hot backup
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
drawbacks of online hot backup
Drawbacks of online (hot) backup
  • Setup and administration is more complex
  • Leading cause of recovery problems
online backup procedure
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’
backups implementation 1 3
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
backups implementation 2 3
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
backups implementation 3 3
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
oracle export import
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
export import cont d
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
export logical
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
benefits of export
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
drawbacks of export
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
export import examples
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
recovery considerations
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)
recovery in noarchivelog mode
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
recovery in archivelog mode
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
benefits of recovery
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
implications of recovery
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).
summary
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?
ad