Hot Backups Summary

From: Mark Gurry <mag_at_scammell.ecos.tne.oz.au>
Date: Fri, 26 Nov 1993 01:41:31 GMT
Message-ID: <1993Nov26.014131.9433_at_scammell.ecos.tne.oz.au>


Summary of findings on Hot Backups.

   I had many responses from both the Internet and CompuServe on this    topic. It made for informative and interesting reading.

  1. There is conflicting information on Point in Time Recovery provided by Hot Backups. There definitely IS Point in Time recovery provided although nowhere is it written in as many words.

   I have 3 sources at my disposal which indicate it is NOT possible    which must refer to earlier Version 6 releases e.g. pre 6.0.30.

   The sources should be updated to reflect the current scenario.

   Source 1) Database Administration for ORACLE V6 training notes page 16-27

            "Recovery from such a backup can only be restored to the most
             recent state of the database, not some intermediate time."

   Source 2) Early Database Administrators Guide Version 6.0 page 15-23
            "Recovery from such a backup can only be restored to the most
             recent state of the database, not some intermediate time."

   Source 3) International Oracle User Week Conference Notes
             Article 36 - Backup and Recovery : Knowing Your Options
             Paper 36 Page 8
             "Disadvantages 
              -If it does not work, it cannot be used for point in time"

   These sources are NOT CORRECT for more recent releases of Version 6    although they probably are for earlie V6 releases.

2] Almost all people who responded agreed that the ultimate backup is a

   COLD BACKUP each night with the database re-started in ARCHIVELOG MODE    and a FULL DATABASE EXPORT nightly. The export and image copies are    copied to tape later from disk.Some sites have Databases that are    many Gig in size, and this perfect backup is not possible.

3] Hot Backup Peculiarities

   If you lose you Redo Logs between the BEGIN and END BACKUPS, you must    use Point in Time recovery, which worried me when you read the sources    listed in point 1]. You will lose the changes stored in the current redo.

   For the duration of the BEGIN BACKUP END BACKUP, if you are running    batch updates, significantly more data will be written to the Redos    and Archive Logs, because for each block modified, the entire block is    written to the Redos , not just the characters changed. There is a V7    parameter (LOG_BLOCKS_DURING_BACKUP) which apparently overcomes this    problem, bout it is currently disabled. Recovery from Hot Backups will    take longer obviously, because there are more archive logs to recover.

   One must have a close look at sites that are attempting to run 24 hours    to ensure they get their large batch updates through. If they are backing    up the tablespace at the same time as the updates are running, you are    probably using more time using Hot Backups, and it would run faster if    you did a Cold Backup and re-started the database in archivelog mode. Of    course, this doesn't apply to HUGE Databases e.g. Many many Gig.

4] Almost all of the huge sites that responded performed the following

  • Perform Hot Backups, backing up one or 2 tablespaces nightly.
  • Perform Exports of as much as they can nightly. Some have multiple exports running against the one database, others exporting all smaller tables nightly perhaps with one larger table, and alternate the large table exported one night to the next, others spoke of current and historical tables being stored with a simple view used to query the data by way of a union. Only the current table is exported nightly. The exports are important to detect table corruption. One guy who had a recent table corruption described those who don't export nightly as "LIVING ADVENTUROUSLY".

I hope these comments help somebody out there because the responses certainly set my mind at rest re possible pitfalls re HOT BACKUPS.

Mark Received on Fri Nov 26 1993 - 02:41:31 CET

Original text of this message