Oracle Backup and Recovery-11


Backup and Recovery



  1. Physical backup – i. Cold backup ii. Hot backup & RMAN
  2. Logical backup – i. Export ii. Import
Cold Backup Hot Backup RMAN
Offline / consistent backup Online  / partial / inconsistent backup,  only a read consistent and no active transactions backup consistent
DB down Can Backup online. Can Backup online.
Both modes Database should be in archive mode Any Mode
All physical files Tablespace, control file All physical + logical


Cold Backup:

It’s an offline backup/ consistent backup.

Database needs to be down by – Shutdown immediate /normal

Oracle will run checkpoint and write a SCN number.

If you use, shutdown abort for cold backup, you cannot recover the database.

We can run this in both archive and no archive mode. But, there will be a different recovery for each.

Note: In No archive mode, Recovery needs all the files incase if the redo log files are not overwritten, we can only restore/recovery particular file.


What are the files we need to backup by cold backup?

Data file, Redo log file, Control file – Mandatary

Password file, Parameter file – Optional

$mkdir –p /backup/coldbackup/test1/11092015

Note the location of files:

Sql> select name from v$datafile;

Sql> select member from v$logfile;

Sql> select name from v$controlfile;

Optional:  Sql> Create pfile =’\opt\init.ora’ from spfile;

Sql> Shutdown immediate;

$cd /opt/oradata/

$ cp –r *.dbf  /backup/coldbackup/test1/11092015

$ cp –r *.log  /backup/coldbackup/test1/11092015

$ cp –r *.ctl  /backup/coldbackup/test1/11092015


Note: You can corrupt the data file by > Data file name

Sql> Startup

Note: If you take a backup in archive log mode, it should be recovered in archive mode only and no archive log — no archive mode.

Recovery scenario

In No archive mode vs Archive mode

No Archive mode Archive Mode
DB needs to  be down Recovery can be done in online
We need to restore all files We can only restore corrupted file
Data loss No data loss


We have a backup on Sunday 10PM — cold backup — one tablespace crashed on Wednedsday — emp table has 1500 rows.

No archive mode

Database needs to down, but oracle will try to  write SCN and it will fail for normal shutdown immediate, Since the file is tablespace is not available.

Sql> shutdown abort

$ cp –r *.dbf   /opt/oracle/oradata/test/

$ cp –r *.log  /opt/oracle/oradata/test

$ cp –r *.ctl  /opt/oracle/oradata/test/

Check the table, it only have 500 rows


Archive mode

Sql> Alter database datafile ‘/opt/oracle/oradata/test/tools.dbf’  offline drop;

$ cp tools.dbf  /opt/oracle/oradata/test/

Sql> Recover datafile ‘/opt/oracle/oradata/test/tools.dbf’;

Sql> Alter database datafile ‘/opt/oracle/oradata/test/tools.dbf’  online;


Check the table, it only have 1500 rows, since the oracle will take the data between dates from archive log backup.



Recovery Scenario Transaction & data file lost:

Backup taken in archive mode — committed data all comes from archive log, even if the backup is too. Old.

Shutdown Normal:

Case 1

No Archive log — commit 2+ un commit 2 rows  — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM — BB 2 AB 2 = 4 all committed data will come.

No Archive log — commit 2+ un commit 2 rows  — shutdown & Backup — Startup & commit 2 data leave 2 data —

  1. corrupt data fileflush all copy a data file from old backup (it shutdown automatically ORA-01110: data file 12: ‘/opt/oracle/oradata/test1/muthu01.dbf’)

It brings all committed data before & after backup.

  1. Drop offline data file & corrupt –flush all copy a data file from old backup

Case 2

Archive log — commit 2+ un commit 2 rows  — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM — BB 2 AB 2 = 4 all committed data will come.

Archive log — commit 2+ un commit 2 rows  — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM & delete datafile — restore a  tbs from old backup (It will say data file not available, so shutdown and restore it Error: ORA-01113: file 4 needs media recovery)

Recover database — check the row count: BB 2 AB 2 = 4 here media recovery only recover the committed data will come.

Log Mode Transaction Lost Data file lost
Archive all committed media recovery only recover the committed
No Archive all committed media recovery, will recovery the uncommitted


Hot Backup

It’s an online backup / partial backup / inconsistent backup,   only a read consistent and no active transactions can be backup.

Can backup online.

Database should be in archive mode.

What are the files we need to backup by hot backup?


Control files

We can take archive log backup in any mode and anytime. Since, it’s an offline copy of redo log.


Tablespace Backup

$ mkdir –p /backup/hotbackup/test/11092015

Sql> select * from v$tablespace;

Sql> select file_name from dba_data_file where tablespace_name =’tools’;

Note down all the data file info, which are belongs to the tablespace.

Sql> Alter tablespace <tablespace> begin backup;

Sql> select * from v$backup;



File # Stauts
1 not active
2 active
3 cannot read header


Not active – Default state.

Active – Waiting for hot backup

Cannot read header – File corrupted

$ cd /opt/oracle/oradata/test

$ cp –r toolso1.dbf   /backup/hotbackup/test/11092015

Sql> Alter tablespace <tablespace> end backup;

Now, check the Sql> select * from v$backup;


Note: Without begin backup if you copy a file, it will be corrupted, in case any transaction is running on the time. If no transaction is running at the time, there will be no problem.


What will happen internally, after you started the begin backup:

Checkpoint will run and update the SCN in all the data files except the backup tablespace.

Backup tablespace header will be freezed, this is the reason it is called inconsistent backup.

All the new entries will not written, until the backup ends. It will be written in redo and archive log files. Archive log may generate more.

When backup ends, it will refer the other data file and update the SCN.

Same Recovery scenario:  Power failure between begin and end backup

Database will be stopped at mount stage, since it’s not consistent backup and SCN will differ.

Sql> startup mount

Sql> alter database end backup;

Sql> alter database open;

Sql> alter tablespace tools offline;

$ cd backup location

$ cp –r toolso1.dbf  /opt/oracle/oradata/test

Sql> recover tablespace tools;

All data taken from the archive log file.

Sql> alter tablespace tools online;


Data file recovery – Same as what we have done last course

Conculsion:  If the tablespace have more data files, then tablespace recovery will be good, if it has only one file data file recovery will be good.

System tablespace corruption and recovery

We can take online backup, but the recovery should be done in DB down state.

Sql> shutdown abort – Since SCN not updated

$ cd backup location

$ cp –r system01.dbf  /opt/oracle/oradata/test

Sql> startup mount

Sql> recover database;

Sql> alter database open resetlogs;/ noresetlogs

Noresetlog – default


It will start the LSN from the scratch 0,1…

It is like a new database and all the archive log backups are absolute, cannot be use in the recovery purpose, Since the LSN resets.

Noresetlog – It will read the redo log and take the next LSN.


Control file backup and recovery

Two types of backup 1. Trace file 2. Binary file

Trace file method:

First check the udump location

Sql> show parameter user_dump_dest;

Sql> Alter database backup controlfile to  trace; — Default location

Sql> Alter database backup controlfile to  trace as ‘\opt\control01.txt’; — non default location


(Note the system time and open a file from udump location)

$cat filename

Copy the script from ‘create control file …to 😉 and save it to a notepad.

Recovery from trace file:

Sql> startup nomount;

Sql> create controlfile…;

Sql> alter database open…;


Binary file method:

Sql> Alter database backup controlfile to  trace as ‘\opt\control01.ctl’;

Sql> create pfile from spfile;

Note: Whenever we backup control file using binary mode backup pfile also.


Scenario: We have a backup of control file on Sunday and Wednesday it got corrupted.


Replace the pfile control file location on pfile.

$vi /opt/inittest.ora

SQL> Startup pfile =’ /opt/inittest.ora’ nomount

SQL> Alter database mount;

SQL> Recover database using backup controlfile

The Recover DB will take a all missing info and update it to the old control files.

SQL> Alter database open resetlogs; – We always use, this for binary control file recovery

SQL> Create spfile from  pfile= ’ /opt/inittest.ora’;

Best Practice: Trace file backup method, since the binary abosolute the backups.


Witout Backup and recovery

We have condition for this.

  1. Database should be in archive log mode
  2. We can only recover non-system tablespaces

We can recover without data loss.


A tablespace corrupted and size is 2GB.


Sql> Alter database datafile ‘/opt/oracle/Muthu/tools01.dbf’ offline drop;

Sql> shutdown immediate


Copy the corrupted file from original to somewhere. So that we can use and point the same file as new file with data.

$ cd /opt/oracle/Muthu

$ cp tools01.dbf /data/oracle/oradata

Sql> startup mount

Sql> alter database create datafile ‘/opt/oracle/Muthu/tools.dbf’ as ‘/data/oracle/oradata/tools.dbf’;

It will create a same file size as a old file.

Sql> alter database datafile ‘/data/oracle/oradata/tools.dbf’ online;

It will go and get the data from all archive log files.

Sql> recover database;
auto – Oracel will take a data from archive logs.

Sql> alter database open resetlogs – optional;

Sql> alter database open;


Note: We need to do this in online state, Since, the data is getting from archive logs.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

5 + 2 =