Complete understanding of Oracle Database Backup and
Recovery
Backup:
--- backup is a copy
of data which helps in restoring data in case of original data is lost
--- Backups should be taken
in deferent disks than your original data disks to avoid loss of data as well as backups in case of hard disk
failure.
Ground Rules for Backup and Recovery;
=================================
--- We must keep our
backup data into separate drive
--- Consistent
Backup
--- There should
not be any changes while taking the backup
--- Cold Backup
-
DB Downtime required
-
Incomplete recovery
-
Backup is consistent
-
You can only recover till the point where you had taken the backup
--- Database
Recovery
-
Restore - Copying data from a backup location.
- Recover
- Recover the database by applying logs or archive logs till the point in time
Types of Backup
===============
1. Logical backup -
Table backup
--- Oracle provides different
utilities to take logical backup
- Export / Import utility
- Datapump utility
2. Physical
backup - OS files (Datafile) backup
Ways to perform backup:
=====================
1. User Managed
---
Cold Database Backup
--- Hot Database Backup
Manual
Backup:
==============
---
Cold backup and Hot backup are called as manuall backup
---
Because backups are performed manually without using any backup tool
Manual
Recovery:
================
- When
backups are performed using manual backup then we use manual recovery
2. Recovery Manager RMAN
Backup states / Types:
==================
1. Consistent
Backup
--- Cold backup
---
No recovery needed
2.
Inconsistenet Backup
---
Hot backup / RMAN Backups
---
Recovery needed
Recovery:
========
1. Complete
Recovery
---
Recovering the database exactly till the point of failure
2. Incomplete
Recovery
---
It cannot recover the database till the point of failure
---
You can recover the database till the time you had taken the backup
What to back up?
===============
parameter file,
controle file and datafiles
What file needed in deferent stages of
database:
========================================
1. No mount -
Parameter files
2. Mount -
Control file
3. Open -
Datafiles
Cold Database Backup:
===================
1. Fully consistent
backup
2. Performed
when DB is shut down
3. Requires down
time
4. User cannot
access the database
5. No need to
backup redo logs or archive logs
6. A cold backup
is performed using OS level Copy command
7. In real time,
we rarely perform cold backup
Cold Database recovery:
====================
1. Simulate a
failure, All the database files have been deleted
---
Delete pfile / spfile, control files and datafiles
---
Copy pfile/spfile from cold backup location to dbs location - NO MOUNT STAGE
---
Copy control files from cold backup location to required location - MOUNT STAGE
---
Copy datafiles from cold backup location to required location - OPEN STAGE
2. All the
transaction happened after the backup was taken will not be there
3. You will
recover the database till the point where you have taken the backup
4. Cold Database
recovery is Incomplete recovery
Performing Cold Backup and Recovery:
================================
- Get the
location of all the files to be backed up.
---
Parameter file: $ORACLE_HOME/dba
---
select name from v$confilfile;
---
select name from v$datafile;
- shutdown the
Database
- Copy all the
files to a Cold backup location
- Start the
Database and create some dummy tables
- Simulate the
failure by removing all files at OS level
- shutdown the
database (Shut abort or kill the OS level instance process)
- Copy the
parameter file from the Cold backup location to original location
- Start the
instance in no mount state.
- Copy the
control files from Cold backup location to original location
---
you can query show parameter controlfile;
- Take the instance
to mount state
- Copy all the
datafiles from Cold backup location to original location
- Open the
Database
- Check for the
dummy table which are created?
---
dummy table will not be exists due to incomplete recovery
Hot Database Backup:
===================
- Inconsistent
backup
- Backup while
the database is up and running
- No down time
required
- DB Must be in
archive log mode to perform Hot Backup
- We must backup
all the Archive logs from the time of backup to recover the database
Hot Database recovery (All Datafiles were
lost):
========================================
- Get the location
of all the files to be backed up.
-
select name from v$datafile;
- Delete all the
data file from OS level
- shutdown the
database (Shut abort or kill the OS leve instance process)
- Copy/Restore all the data
files from the hot backup location (restoring from backup)
- Startup mount
- Recover
database;(This part if applying archive logs/redo logs)
- Alter database
open;
Performing Hot Backup and Recovery (All
Datafiles were lost):
====================================================
- DB must be up and
running
- No downtime
required
- Put the
Database in begin backup mode
-
alter database begin backup;
-
select distinct status from v$backup
- Copy all the file
at OS level to Hot backup location
- Put the Database
in end backup mode
-
alter database end backup;
-
select district status from v$backup;
- Switch the log
file
-
alter system switch logfile;
- Take the
backup of archive logs
Hot Database recovery (All Datafiles and
Control files were lost):
======================================================
- Get the
location of all the files to be backed up.
-
select name from v$confilfile;
-
select name from v$datafile;
- Delete all the
data files and confilfile from OS level
- shutdown the
database (Shut abort or kill the OS level instance process)
- Copy/Restore
all the data & Control files from the hot backup location (restoring from
backup)
- Startup mount
- recover
database using backup controlfile until cancel; (applying archive logs/redo
logs)
-
AUTO
- recover
database using backup controlfile until cancel;
-
CANCEL
- Alter database
open resetlogs;
(resetlogs will reset the
log sequence the start the archive log sequence starting from 1. Database new
in-carnation is started)
- login to
database check the archive log sequence
-
archive log list
Performing Hot Backup and Recovery (All
Datafiles and Control files were lost):
==================================================================
- DB must be up and
running
- No downtime
required
- Put the
Database in begin backup mode
-
alter database begin backup;
-
select distinct status from v$backup
- Copy all the file
at OS level to Hot backup location
- Put the
Database in end backup mode
-
alter database end backup;
-
select distict status from v$backup;
- Switch the log
file
-
alter system switch logfile;
- Take the backup of
archive logs
- Create a new
user and create a dummy table
- Simulate
failure of controlefile and datafile loss and recover the Database
- Check if new
user and dummy table exists or not
RMAN:
======
- Recovery Manager
- Oracle
Database backup and recovery automation
- It Introduced
in Oracle 8i version
- It performs
block level backup
- Parallelism
- Detecting
corruption in datafiles
- Validating the
backup
- Incremental
Backup
- Recovery
Catalog etc
- Multi Destination
Backups
- Archive log
mode is must to use RMAN
RMAN is faster because it takes block level
backups
RMAN is faster because we can initiate
parallel processes
RMAN will data block corruptions and repair it
for you
RMAN stores backup metadata information in the
database controlfile, you can also store the information into deferent database
on different server which is called as RECOVERY catalog
RMAN Incremental Recovery
-
Level 0 - Full database backup
-
Level 1 - Backup of only changed blocks taken by referring data block header
for updated SCN
- As per
recovery, we must only restore the database from level 0 and recovery the
Database using level 1. When you try to take level 1 backup, RMAN checks if
already you have level 0 backup or not. If not then it will take level 0 backup
automatically.
Recovery is of two parts:
-
Restore and Recover - We cannot perform recovery without restore. Hence Level 0
is used for restore and level 1 is used for recovery.
RMAN configuration Items
-
show all;
RMAN Backup Methodology:
========================
- Full Backup
-
Entire Database backup
-
Cannot apply Incremental backup on full backup
- Incremental
Backup
-
Level 0 - FULL Database backup
-
Level 1 - Backup changes only from last incremental backup
Connecting to RMAN utility:
========================
- RMAN utility comes
with Oracle Binaries
- No special
installation or license required for using RMAN
- At command
prompt just type rman
- It defaults
connects to database environmental variables defined
- RMAN utility
can be used only when your DB is in at least MOUNT stage
- RMAN is used while the DB
is up and running and have very little performance impact if the backup is
running
Components of RMAN:
===================
- RMAN Prompt
- Target
Database
- Recovery
Catalog
- Auxiliary
Database --- When cloning, Clone DB is called as auxiliary Database
- Media Management Layer ---
layer between RMAN 3rd part vendor backup tools netbackup, Veritos and Tape backups
- RMAN Channels
RMAN Configuration Parameter:
===========================
- RETENTION POLICY - tells
till what date our backup will be stored which we can use for recovery
-
Redundancy -- How many backups to be retained
-
Recover Window -- How many days backup to be retained
- Channels -- You
can define channel to take backup to disk or tape
- Controlfile
auto backup -- includes controlfile and spfile autobackup
- Parallelism --
Creates multiple processes to speed up backup
- Encryption --
to secure the backup
RMAN DB FULL back-up on Disk:
===========================
- rman target /
- set
controlfile autobackup on;
- backup
database plus archivelog format='/loc';
- Checking
backups
-
list backup;
-
list backup of database summary;
Database level 0 Incremental Backup on disk:
======================================
- rman target /
- backup
incremental level 0 database plus archivelog format='/loc';
- backup
incremental level 1 database plus archivelog format='/loc';
Recovering DB from Incremental Backup:
===================================
- rman target /
- restore
controlfile from '<backup loc>'
- sql 'alter
database mount';
- list backup of
database summery;
- restore
database from tag '<level 0 Tag>';
- recover
database from tag '<level 1 Tag>';
- sql 'alter
database open resetlogs';
Regards,
Mallik
