Backup & Recovery

Oracle Database Backup and Recovery Reference

Types of backup:

  1. full (level 0)
  2. incremental (level 0)
  3. incremental (level 1, done after incremental level 0 backup or previous level 1 backup – in case of differential incremental backup)

Incremental backup can be differential and cumulative.

  • Differential incremental backup, backs up all blocks changed since the previous level 0 OR LEVEL 1 incremental backup.
  • Cumulative incremental backup, backs up all blocks changed (ONLY) since the previous level 0 incremental backup.

You have to use backup set if you are doing incremental backup. You can run incremental backup on datafiles, tablespaces and database, but not on control file, archived redo log file (this have to be backed up fully every night) and backup sets.

Modes of backup:

  1. Offline (cold, consistent)
  2. Online (hot, inconsistent)

Backup Storage options:

  1. Image copy – simple file copy, no compression
  2. Backup set – compressed, Oracle proprietary format (If yo are doing incremental backup you have to use backup set)

In order to use RMAN you have to be archiving you REDO log files. It is explained here LINK
what needs to be done. You can also do it using Oracle Enterprise Manager under Availability -> Recovery Settings

Duplexed Backupset

Producing more than one copy of BS

RMAN> backup device type disk copies 3
database format
'd:\backup\%U', 'e:\backup\%U', 'f:\backup\%U';

Oracle will allocate a channel automatically for each disk.

Multiplexed Backupset

It is possible with one channel to back up more than one file into the backupset. We determine this by specifying filesperset and maxopenfiles parameters.

filesperset is a parameter of the backup comand that determines the number of files in each backup set.

maxopenfiles is a parameter of the backup command that sets the number of files that can be simultaneously read into a single backup set piece

Multisection Backup

It means having more than one channel backing up large datafile in parallel. Datafile should be bigger than 1 GB. We do this for performance reasons when we want to lower the amount of time it takes us to back up our data.

RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup section size 700m datafile 3;

Specifically, this backup will create a single backup set that consists of 700MB sections of data.

This is called a “parallelized” backup. We can query the backup pieces by issuing the command:

SQL> select pieces, multi_section from v$backup_set;

We are relaying on RMAN to know hot to put those pieces back together when doing backup & recovery.

Archival Backups

It is used when we want to backup a backupset.

Consistent offline backup

calling RMAN script needs to be done with sys user, because he is the only one that can start and shutdown the DB (by default)

OS> rman target sys/syspass@orcl @rmanScriptName.rman


— database should be in the mounted state, but not open
shutdown immediate;
startup mount;

— channel is a thread between RMAN and instance. When we have set Flash Recovery Area,
— we do not need to point out the path to it here
allocate channel c1 type disk;

— by choosing property database we backup the entire DB
backup as backupSetName database;

format ‘E:\orabak\fullbak.bus’;
alter database open;


Setting Flash Recovery Area

To have a default area where database backups are being stored, we set the Flash Recovery Area by setting these two initialization parameters DB_RECOVERY_FILE_DEST_SIZE an DB_RECOVERY_FILE_DEST

To see the current value of those two parameters type in sql*plus:

show parameter db_recovery

To change the values type:

ALTER SYSTEM SET db_recovery_file_dest_size = 4294967296 SCOPE=BOTH
ALTER SYSTEM SET db_recovery_file_dest = "O:\app\oracle\backups" SCOPE=BOTH

or in OEM go to Availability -> (Backup/Recovery -> Setup) Recovery Settings
and under Flash Recovery area set Flash recovery Area Location and Flash Recovery Area Size

RMAN Open (hot) Backup

User managed solution

select file_name from dba_data_file
where tablespace_name = 'USERS';
alter tablespace users begin backup;

< copy df inside users ts >

alter tablspace users end backup;

or we can just use

rman target /
backup tablespace users;

Server managed solution

run {
allocate channel c1 type orcl_tape;
allocate channel c2 type orcl_tape;
backup as compressed backupset filesperset 6 database;
backup as compressed archivelog all delete all input;

RMAN Hot Image Copy

RMAN> backup as copy database;

Syntax for backup command can be found HERE

Incremental backup

backup as backupset incremental level 0 database; -- full backup
backup as backupset incremental level 1 database; -- incremental backup
backup as backupset cumulative database; -- differential backup, backup of everything since last backup 0

Login to RMAN

From command line tool type:

C:/>rman target /

this logs as under currently logged OS credentials

if we have more DBs on one computer we can log using

C:/>rman target sys/password@orcl

this is OK to do only in a testing environment, otherwise we should log using


RMAN> connect target orcl

*** here you will be required to enter a password ***

target database Password:

Backup control file


This sets automatic backup of the control file
RMAN> configure controlfile autobackup format for device type disk to '0:\app\oracle\backups\controlfile_%F';

Backup spfile

RMAN> backup spfile;

it will be saved in a Flash Recovery Area

Backup entire DB

RMAN> backup database

Backup specific datafile only


Incremental backup

RMAN> backup incremental level 0 database;


RMAN> backup incremental level 1 database;

The Recovery Catalog

  • db schema that serves as a centralized repository of backup & recovery metadata
  • should be separate DB
  • optional unless you use Data Guard

Scripting RMAN

There are two ways to run RMAN scripts.

  1. OS Shell Scripts (written in any scripting language that supports work with OS. Specific for all of this scripts (.sh in Linux or .cmd/.bat in Windows) is that they all contain RMAN commands. We run those scripts with some of the OS task schedulers like Cron (Linux) or Task Scheduler(Windows).
  2. Stored Scripts – we can embed them inside of a Recovery Catalog. Scripts can be local (for 1 DB) or global (for more DBs). They can be run either like previous ones or through the OEM.

Creating stored script in RMAN

C:/>rman target / catalog rman@orcl
recovery catalog database Password: -- enter password

RMAN> CREATE SCRIPT recoveryscript {
4> }

Finding all existing scripts in RMAN

RMAN> list script names;

Showing some RMAN script

RMAN> print script scriptName;

and finally executing of some script

2> EXECUTE SCRIPT scriptName;
3> }

If we want to run a RMAN stored script from an OS script we should invoke the following line:

rman target / catalog=username/Pa$$w0rd@orcl script=scriptName

Automating DB Backups

We can do this in OEM when we go to Availability page -> Manage -> Scheduled Backup

Can be any type of backup:

  • online
  • offline
  • whole
  • partial
  • full
  • incremental

Schedule options are:

  • one time immediatelly
  • one time later
  • repeating (minutes, hours, days…)

Managing Backups/Reports

OEM -> Availability -> Backups Reports
OEM -> Availability -> Manage Current Backups

Flash Recovery Area:
OEM -> Availability -> Recovery Settings

RMAN> list backup;

Creating a user for a catalog

SQL> create tablespace catab;

SQL> create user rman identified by password;

SQL> alter user rman
temporary tablespace temp
default tablespace catab
quota unlimited on catab;

SQL> grant recovery_catalog_owner to rman;

SQL> exit;

C:\>rman target /

— rman is the user name and orcl is the DB SID —
RMAN> connect catalog rman@orcl
recovery catalog database Password: password

(it was also possible to write in one line both previous commands
C:\>rman target / catalog rman@orcl)

RMAN> create catalog;

RMAN> register database;

— check which datafiles in the catalog —
RMAN> report schema;


Standard precautions:

  • Multiplex the control file
  • Multiplex the ORLF (Online Redo log files)
  • Backup data files and control file
  • Trial restores to be done periodically
  • Config DB for ARCHIVELOG mode

Restore means to extract file from backup and place it where it is missing (data is out of sync)

Recover means to extract changes from redo log stream/undo area (tablespace) to sync data

Data Recovery Advisor
Diagnostic/repair framework for DB
It advices us on datafiles and control files and it does not on spfile and online rlf (redo log files).
It is based on ADR (Automatic Diagnostic Repository) and Health Monitor.

Recipe for a loss of a Control file

1. list all control files (select name from v$controlfile;)
2. shutdown
3. copy control files (to the place that your oracle alert log told you it is missing. It tells you when Oracle tries to go from nomount to mount mode). To find the missing control files, you have to check in diagnostics. You will get the location of your diagnostic folder by writing in sql*plus:

SQL> show parameter diagnostic

Location of the alert trace file:
valueAbove(diagnostic)/diag/rdbms/SIDname/SIDname/trace/alert_SIDname.log file
Check out what is written after the row: ALTER DATABASE MOUNT

it is possible than just to copy the good spfile to the location where it is missing, or if we have Flash Recovery Area we can log into rman, execute command:
RMAN> startup nomount;
and then
RMAN> restore controlfile from autobackup;

4. startup nomount
5. edit CONTROLFILES parameter
6. bounce DB

Recovering with OEM is pretty easy with advisors.

OEM -> Availability -> Perfrm Recovery -> Advise and Recover

Recipe for a loss of an Online Redo Log Member

— see if your redo logs are multiplexed and check their status report.
— If some has invalid status we have to fix them
1. select * from v$logfile;

— we have to run the command that deletes and recreates all members of that log file
— we are giving the ID of the datafile taken from the Data Dictionary
2. alter database clear logfile group 2;

— if there are problems one should also run
3. alter system switch logfile;
4. alter system checkpoint;
5. alter system archive log all;

Recipe for a loss of a Data File

There are two types of files that can be lost.

  • Noncritical (db opens, datafile is taken offline)
  1. USERS
  2. APP
  • Critical (DB aborts)
  2. UNDO

Steps to take:

  1. mount the DB
  2. take damaged files offline
  3. open the DB
  4. restore the damaged files
  5. recover them
  6. bring the damaged files online

Repairing critical error using RMAN

— get the list of failures
RMAN> list failure;

— see what to do about the failure
RMAN> advise failure;
RMAN> repair failure;

DB Blackouts

When doing backup of the DB it is good to make a blackout of the DB so that the backup actions do not go into general statistic of the DB (when online backup is made).

OEM -> Setup -> Blackouts


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: