Transcript Oracle backup and recovery strategy - hep-proj

CERN/IT/DB
Oracle backup and
recovery strategy
Catherine Delamare /IT DB
CERN/IT/DB
AGENDA
Why 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 fails
Oracle never makes a mistake
Users never make mistakes
Programmers never make mistakes
Physicists never make mistakes
Database 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
1
2
3
4
5
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 down
Loss 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
hours
The 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 BACKUP
Copy/Backup all datafiles in the
tablespace
ALTER TABLESPACE tsp END BACKUP
ALTER SYSTEM SWITCH LOGFILE
Backup archived log files
ALTER 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 types
Export writes object type definitions
and all associated data to the dump file
Import then re-creates these items
from the dump file
The definition statements for derived
types are exported, same applies to
OIDs
CERN/IT/DB
EXPORT (logical)
Database must be up and running
Export 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 items
Portable:
 can be used to move data from one
machine to another
 useful in a heterogeneous environment
Unlike 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 examples
Export 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.dmp
LOG=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-0710: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 users
It 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?