cern/it/db oracle backup and recovery strategy catherine delamare /it db
TRANSCRIPT
CERN/IT/DB
Oracle backup and recovery strategy
Catherine Delamare /IT DB
CERN/IT/DB
AGENDAWhy plan backups?
Overview of backups
Backup implementation in IT/DB
Export/Import
Recovery
CERN/IT/DB
Why plan backups?
CERN/IT/DB
Why plan backups?
Hardware never failsOracle never makes a mistakeUsers never make mistakesProgrammers never make mistakesPhysicists never make mistakesDatabase administrators (DBAs)
never make mistakes
CERN/IT/DB
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!
CERN/IT/DB
Why plan backups?
CERN/IT/DB
Overview of backups
Physical backups protect against global failure Cold backups : database needs to be
stopped Hot backups : database keeps on running
Logical backups (or “exports”) protect against deletion of (a) table(s) can be run by the user himself
CERN/IT/DB
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
CERN/IT/DB
ARCHIVELOG Mode or not? (2/3)
Sunday Friday
BACKUP CRASH
54321
CERN/IT/DB
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
CERN/IT/DB
Offline backup (COLD)
Database must be down
Backup of all datafiles, redo log files and control files
Disk space needed
CERN/IT/DB
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)
CERN/IT/DB
Drawbacks of offline backup
Database must be downLoss 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!
CERN/IT/DB
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
hoursThe unit of an online backup is a
tablespace
CERN/IT/DB
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
CERN/IT/DB
Drawbacks of online (hot) backup
Setup and administration is more complex
Leading cause of recovery problems
CERN/IT/DB
Online backup procedure
ALTER TABLESPACE tsp BEGIN BACKUPCopy/Backup all datafiles in the
tablespaceALTER TABLESPACE tsp END BACKUPALTER SYSTEM SWITCH LOGFILEBackup archived log filesALTER DATABASE BACKUP
CONTROLFILE to ‘filespec’
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
EXPORT/IMPORT(cont’d)
Export and Import support object typesExport writes object type definitions and
all associated data to the dump fileImport then re-creates these items from
the dump fileThe definition statements for derived
types are exported, same applies to OIDs
CERN/IT/DB
EXPORT (logical)
Database must be up and runningExport 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
CERN/IT/DB
Benefits of export
Easy to recover individual itemsPortable:
can be used to move data from one machine to another
useful in a heterogeneous environmentUnlike backup, export identifies
physical data block corruption since it performs a full table scan while exporting a table
CERN/IT/DB
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
CERN/IT/DB
Export/import examplesExport 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
CERN/IT/DB
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)
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
Implications of recovery
Database is not open to the usersIt 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).
CERN/IT/DB
SUMMARY
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
WHO WANTS TO TEST NEXT?