oracle 9i backup l.
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle 9i Backup PowerPoint Presentation
Download Presentation
Oracle 9i Backup

Loading in 2 Seconds...

play fullscreen
1 / 21

Oracle 9i Backup - PowerPoint PPT Presentation

  • Uploaded on

Oracle 9i Backup. ARCHIVELOG vs. NOARCHIVELOG mode User-managed backups: Logical vs. Physical Export utility Whole and Partial backups Hot and cold backups. Noarchivelog Mode vs. Archivelog Mode. Source:

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 9i Backup' - oshin

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 9i backup

Oracle 9i Backup


User-managed backups: Logical vs. Physical

Export utility

Whole and Partial backups

Hot and cold backups

noarchivelog mode vs archivelog mode
Noarchivelog Mode vs. Archivelog Mode


archivelog mode
  • Oracle copies filled online redo logs to disk rather than writing over them, creating archived redo logs.SQL> select log_mode from v$database;
  • Ways to set or change this mode:
    • Specify the mode at database creation using dbca
    • Use the ALTER DATABASE ARCHIVELOG;statement
    • To enable automatic archiving without shutting down the current instance, use ALTER SYSTEM ARCHIVE LOG START;
    • set the initialization parameter LOG_ARCHIVE_START to TRUE
user managed backups
User-managed backups
  • Logical backup
    • use the Export utility to make backups of logical objects (i.e., tables, views, and stored procedures)
    • use the Import utility to restore these logical objects
  • Physical backup
    • a backup of an Oracle database file or archived redo log located on the operating system
    • Can use OS utilities or RMAN to create and restore these files
physical backups
Physical Backups

(This information is from the following documentation: )

logical backups
Logical Backups
  • The Export utility: a simple way to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
the export utility
The Export Utility


  • An Export file: an Oracle binary dump file (typically located on disk or tape)
  • Files can be transferred using FTP or physically transported to a different site
  • Use the Import utility to transfer data between databases that are on systems not connected through a network
  • Files can also be used as backups in addition to normal backup procedures
  • Can display contents of an export file without actually performing an import (use Import SHOW parameter)


  • Export dump files can only be read by the Oracle Import utility
  • The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file
  • Like snapshots of the database (therefore can’t apply redo to tables restored from export)
  • Has to have database fully open

More on using the Export and Import utilities:

whole and partial backups
Whole and Partial backups
  • Whole (full) database backup
    • A backup of the control file and all datafiles that belong to a database
    • Is either consistent or inconsistent(an inconsistent backup: some of the files in the backup contain changes that were made after the files were checkpointed. An inconsistent backup needs recovery before it can be made consistent).
    • Can be done hot or cold
  • Partial database backup
    • any OS backup short of a full backup, taken while the database is open or shut down.
    • Examples of partial database backups:
      • a backup of all datafiles for an individual tablespace
      • a backup of a single datafile
      • a backup of a control file
hot online and cold offline backups
Hot (online) and cold (offline) backups
  • Hot:
    • Database must be open
    • put the tablespaces in backup mode by issuing:


Oracle stops recording checkpoints to the tablespace's datafiles.

copy datafiles in that tablespace on OS level


  • Cold: *especially important when the db schema changes
    • performed while the tablespace or datafile is offline
    • take tablespaces offline:


  • Use archivelog mode
  • Perform regular full backups (not after unusual circumstances)
  • Consider time needed to perform backups and time to recover (the older your last backup is, the more redo log files will have to be appliedtakes longer to recover)
  • Perform scheduled cold backups (depends on database use) For example: before an upgrade or a mass load, after schema changes (new tablespace/datafile), after recovering, before using “resetlogs”
  • Take more frequent hot backups if the amount of downtime for cold backups must be limited
  • Backup archived log files on disks separate from the online redo logs
oracle 9i recovery

Oracle 9i Recovery

User-managed recovery

Recovery Manager (RMAN)


Recovery Resources

user managed recovery
User-managed Recovery
  • Files used in recovery
    • Datafiles
    • Control files
    • Archived redo logs
    • Server parameter files
record keeping
Record Keeping
  • Record locations for:
    • datafiles: V$DATAFILE
    • control files: V$CONTROLFILE
    • online redo logs: V$LOGFILE
    • archived redo logs: V$PARAMATER
      • use LIKE log_archive_dest%
  • Record backup file locations:
    • Correlate backup files with the original files
      • name backup files using same relative filename as the primary file
datafile recovery
Datafile Recovery
  • Files to recover:
  • File location:
  • Recovery:
    • Restore datafile from backup if available
    • If not available, can re-create when
      • all archived log files available
      • control file contains name of damaged file
control file recovery
Control File Recovery


  • Losing member of multiplexed control file
    • Restoring to default location
      • Copy one of the other control files
    • Restoring to non-default location
      • Copy one of the other control files
      • Alter the CONTROL_FILES initialization parameter
  • Losing all members of multiplexed control files
    • Restore from backup using RESETLOGS option
  • Losing all current & backup control files
    • Create new control file if all online redo log files are intact
archived redo logs parameter files
Archived Redo Logs & Parameter Files
  • Archived redo logs will be needed during recovery
    • All logs generated between last backup and recovery time
    • Logs will need to be on disk during recovery
    • Determine needed logs: V$RECOVERY_LOG
  • Keep copies of parameter files
    • Restore from backup if available
    • Use client file if server file not available
recovery manager
Recovery Manager
  • Recommended by Oracle for 8i or higher databases
  • Do not need to place online tablespaces in backup mode
  • Performs incremental backups
  • Omits never-used blocks from datafile backups
  • Uses the repository to report on crucial information
  • Stores RMAN scripts in the recovery catalog
  • Easily creates duplicate of production database for testing purposes
  • Easily create a back up or standby database
  • Performs checks to determine whether backups on disk or in the media catalog are still available
  • Performs automatic parallelization of backup and restore operations
  • Tests whether files can be backed up or restored without actually performing the backup or restore
  • Performs archived log failover automatically for corrupt or missing logs
  • RMAN Architecture
    • RMAN executable
    • Target database(s)
    • Recovery catalog database - optional
    • Media Management software
  • Running RMAN
    • From command line: rman
    • Oracle Enterprise Manager (OEM)


  • Don’t store the RMAN database on the same disk with the database being backed up
  • Don’t use the default account’s default password
  • Make sure to completely understand classical backup & recovery procedures. RMAN’s methodology is not different.
  • Synchronize RMAN recovery catalog when the database file layout changes
  • Monitor V$LONGOPS for long running backup sessions


  • Once you have established your backup plan & scripts, test them and document your recovery.
backup and recovery resources
Backup and Recovery Resources
  • Oracle9i Backup and Recovery Concepts

  • Oracle9i User-Managed Backup and Recovery Guide

  • Oracle9i Recovery Manager User's Guide

  • Oracle9i DBA Handbook
    • Chapter 11: Backup and Recovery Options
    • Chapter 12: Using Recovery Manager (RMAN)