Oracle backup and recovery strategy
1 / 32

Oracle backup and recovery strategy - PowerPoint PPT Presentation

  • 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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


  • 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)










    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


    • Copy/Backup all datafiles in the tablespace



    • Backup archived log files


    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

    • 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 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).


    • 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