Oracle%20backup%20and%20recovery%20strategy

1
Oracle backup and recovery strategy

• Catherine Delamare /IT DB

2
AGENDA

• Why plan backups?
• Overview of backups
• Backup implementation in IT/DB
• Export/Import
• Recovery

3
Why plan backups?
4
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

5
Why plan backups?

• The only insurance you have against failures
  causing loss of data
• Human errors
• Hardware failures
• Power failures
• Software errors
• DBAs job security!

6
Why plan backups?
7
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

8
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 each18G
  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

9
ARCHIVELOG Mode or not? (2/3)
Sunday
Friday
BACKUP
CRASH
5
4
3
2
1
10
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

11
Offline backup (COLD)

• Database must be down
• Backup of all datafiles, redo log files and
  control files
• Disk space needed

12
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)

13
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 everythingBackup scripts must
  be often checked and tested!

14
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

15
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

16
Drawbacks of online (hot) backup

• Setup and administration is more complex
• Leading cause of recovery problems

17
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

18
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

19
Backups implementation (2/3)

• On mission-critical systems
• Shutdown the oracle database
• Detach the mirrors
• Restart the database on one side gt 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

20
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

21
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

22
EXPORT/IMPORT(contd)

• 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

23
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

24
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

25
Drawbacks of export

• Does not provide point in time recovery
• Export/Import are usually long processes.To
  improve speed
• import with ROWSY INDEXN to restore data
• then import with ROWSN and INDEXY to build
  indexes

26
Export/import examples

• Export of a full database (from a DBA)EXP
  FULLY FILEfullexp.dmp LOGfullexp.log
• Export of a specific schemaEXP USER/PASSWORD
  TABLES(RUN1,RUN2) FILEruns12.dmpLOGruns12.log

27
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)

28
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

29
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

30
Benefits of recovery

• Database recovery can be complete or not
• recover database (complete)
• recover database until time 2001-07-10100500
  
• recover database until cancel
• recovery of TABLESPACE or DATAFILE can only be
  complete

31
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).

32
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?