Database Backup and Recovery Keeping backups E g

Database Backup and Recovery

Keeping backups • E. g. , Daily backups for 7 days, then weekly backups for 8 weeks, then monthly backups for 12 months, then yearly backups. • Keep backups off-site • RAID reduces failures, but doesn’t give backups • Losing 1 day might be unacceptable! • Reconstruct instantaneous backups via log files.

Log Files • “Dear journal: today I updated phone# for…” • Captures all database transactions (changes) in order to roll a database forward after a tape recovery. • Oracle logs each : old and : new • Log files should be stored on a separate drive from the data files • Log files in Oracle can be multiplexed (mult copies); less common since RAID does this.

Log Modes • No Archive Log Mode – Also called circular logging – When the final redo log file in a set fills up, the first redo log file is overwritten – Lacks: guaranteed point in time recovery • Archive Log Mode – The on-line redo logs are archived and numbered before they overwritten – Allows point-in-time recovery of the database – Requires: more disks ($$)

Backup Modes • Hot backup – allows backup of the database while the database is running and available to users. – performance degrades during the backup period – takes longer than a cold backup • Cold backup – – requires database shutdown before backup begins physical files are backed up while shutdown database is unavailable to users during backup period faster than a hot backup; no timing issues

Oracle Backup Options • Off-line physical backup – is a cold backup technique – copies data files, log files, init files, and control files after shutdown • On-line physical backup – is a hot backup technique – temporarily sets tablespaces into backup mode • Logical Backup – is a hot backup technique – exports all or part of the database by creating SQL scripts necessary to recreate the objects

Off-line physical backup • • Shut down the database Backup the data files Backup the control files Backup the online redo log files or the archive log files • Backup the initialization and password files • Restart the database

On-line physical backup • Requires the DB to be in Archive Log Mode • For each tablespace – Set the tablespace into a backup state: read-only, but keep log – Backup the datafiles for that tablespace – Restore the tablespace to its normal state • Backup the archived redo log files – – – Stop the archiving process Note which files are in the archive log directory Restart the archiving process Backup the archived redo log files Delete the archived redo log files • Backup the control file using the “Alter database backup controlfile” command • Backup the initialization and password files

Logical Backup • Back up tables -- not disks/files per se • Essentially creates SQL scripts that will recreate database objects • Use Oracle Export Utility to create. dmp file • Use Oracle Import Utility to selectively import database objects from the. dmp file • Can perform – Complete database backup – User (schema) backup – Individual table backup • Used in conjunction with a recovery server to recover selected objects (not entire database)

Backup Types • Complete (Full) – copy all database and related files – delete the archive log files • Cumulative (Differential) – copy all archive log files – Essentially copies transactions since last full backup – clear the archive bit on the log files • Incremental – copy all archive log files that haven’t already been backed up – Essentially copies all transactions since last backup of any kind – clear the archive bit on the log files copied • Complete (Copy) – copy all target data – Don’t delete or clear the archive bit on an data files so as not to interfere with normal backup schedule

Oracle Files Overview Files open when instance is running 343 342 341 PW Password File Init. ora Initialization File Data files (for tablespace data) 1 2 Control Files A B C On-line redo log files Archive Log Files