Oracle 9i Backup

1
Oracle 9i Backup

• ARCHIVELOG vs. NOARCHIVELOG mode
• User-managed backups Logical vs. Physical
• Export utility
• Whole and Partial backups
• Hot and cold backups

2
Noarchivelog Mode vs. Archivelog Mode
Source http//www.adp-gmbh.ch/ora/concepts/backup
_recovery/archive_vs_noarchive_log.html
3
ARCHIVELOG mode

• Oracle copies filled online redo logs to disk
  rather than writing over them, creating archived
  redo logs.SQLgt select log_mode from vdatabase
  
• Ways to set or change this mode
• Specify the mode at database creation using dbca
• Use the ALTER DATABASE ARCHIVELOGstatement
• 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

4
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

5
Physical Backups

• (This information is from the following
  documentation http//download-west.oracle.com/doc
  s/cd/B10501_01/server.920/a96572/intro.htm433631
  )

6
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.

7
The Export Utility

• Advantages
• 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)
• Disadvantages
• 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 cant
  apply redo to tables restored from export)
• Has to have database fully open

More on using the Export and Import utilities
http//download-west.oracle.com/docs/cd/B10501_01/
server.920/a96652/ch01.htm1004671
8
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

9
Hot (online) and cold (offline) backups

• Hot
• Database must be open
• put the tablespaces in backup mode by issuing
• SQLgt ALTER TABLESPACE BEGIN BACKUP
• ?Oracle stops recording checkpoints to the
  tablespace's datafiles.
• ?copy datafiles in that tablespace on OS level
• SQLgt ALTER TABLESPACE ... END BACKUP (or ALTER
  DATABASE END BACKUP)
• Cold especially important when the db schema
  changes
• performed while the tablespace or datafile is
  offline
• take tablespaces offline
• SQLgt ALTER TABLESPACE tablespacename OFFLINE

10
Recommendations

• 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

11
Oracle 9i Recovery

• User-managed recovery
• Recovery Manager (RMAN)
• Recommendations
• Recovery Resources

12
User-managed Recovery

• Files used in recovery
• Datafiles
• Control files
• Archived redo logs
• Server parameter files

13
Record Keeping

• Record locations for
• datafiles VDATAFILE
• control files VCONTROLFILE
• online redo logs VLOGFILE
• archived redo logs VPARAMATER
• 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

14
Datafile Recovery

• Files to recover
• VRECOVER_FILE
• File location
• VDATAFILE VTABLESPACE
• 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

15
Control File Recovery

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

16
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 VRECOVERY_LOG
• Keep copies of parameter files
• Restore from backup if available
• Use client file if server file not available

17
RMAN User-Managed Procedures Comparison
18
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

19
RMAN

• RMAN Architecture
• RMAN executable
• Target database(s)
• Recovery catalog database - optional
• Media Management software
• Running RMAN
• From command line rman
• Oracle Enterprise Manager (OEM)

20
Recommendations

• RMAN
• Dont store the RMAN database on the same disk
  with the database being backed up
• Dont use the default accounts default password
• Make sure to completely understand classical
  backup recovery procedures. RMANs methodology
  is not different.
• Synchronize RMAN recovery catalog when the
  database file layout changes
• Monitor VLONGOPS for long running backup
  sessions
• Generic
• Once you have established your backup plan
  scripts, test them and document your recovery.

21
Backup and Recovery Resources

• Oracle9i Backup and Recovery Concepts
• http//download-west.oracle.com/docs/cd/B10501_01/
  server.920/a96519/toc.htm
• Oracle9i User-Managed Backup and Recovery Guide
• http//download-west.oracle.com/docs/cd/B10501_01/
  server.920/a96572/toc.htm
• Oracle9i Recovery Manager User's Guide
• http//download-west.oracle.com/docs/cd/B10501_01/
  server.920/a96566/toc.htm
• Oracle9i DBA Handbook
• Chapter 11 Backup and Recovery Options
• Chapter 12 Using Recovery Manager (RMAN)