Wednesday, January 13, 2010

Different types of backups in Oracle.

1. Cold backup:

If the database is in noarchive log mode,then we have take cold backup.for taking cold backup follow the following steps:

1. Shutdown the database

Sql>shutdown immediate;

2. Manually copy data files ,control files and redolog files to destination.

3. Start database


2.Hot backup:

If database is in archive log mode, then we can take hot backup without any database outage.

1. If you want to receive logs for hot backup in separate logfile, then execute the following command.

2. Start hot backup by executing following command.

Sql>alter database begin backup;

3. Manually copy data files, control files and redo log files without shutting down the database.

4. Stop hot backup by executing following command.

Sql>alter database end backup;

5. Switch log file after completing hot backup.

Sql>alter system switch logfile;

copy the archive files and it is used to recover the database.

issue this command to recover database :

sql>recover database using contolfile autobackup untill cancel;

3. Import – Export Backup:

Data guard is a new feature in Oracle 10g which helps to transfer data from one location to another location at high speed.

It consist of two utilities:

1. Export utility
2. Import Utility

1. Export utility: is used to store database objects in storage media such as disks outside the database.
2. Import Utility: is used to load the objects to database from storage media.

Export operation:
1. Create a directory

C:\>mkdir c:\backup

2. Connect to database and create a directory for backup

Sql>create directory backup as ‘c:\backup’;

Directory created.

3. Now move to host mode.

4. Go to the directory C:\oracle\product\10.2.0\db_1\BIN
C:\>cd C:\oracle\product\10.2.0\db_1\BIN

5. Execute export command.
C:\oracle\product\10.2.0\db_1\BIN> expdp sys/test directory=bkup dumpfile=bfull.dmp logfile=bfull.log full=y

For import operation execute the following command.

C:\oracle\product\10.2.0\db_1\BIN> impdp sys/test directory=bkup dumpfile=bfull.dmp logfile=bfull.log full=y

4 RMAN Backup:
Target database: database of which we are going to take backup.
Recovery catalog: is a destination which holds information about the target database.

1. RMAN Without recovery catalog:

Requirements for rman backup:

a. Data base must be in archivelog mode.

A. From operating system prompt issue the following command to connect to target database without recovery catalog. When connecting to a target or auxiliary database, you must have the SYSDBA privilege.

B. To take the complete backup of database issue following command.

1. RMAN>backup database;

2. To take the backup of tablespace use following command.

Rman>backup tablespace tablespace_name;

3. To take backup of datafile issue following command.

Rman>backup datafile ‘C:\oracle\product\10.2.0\oradata\orcl\datafile_name’;

By default these backups will be stored in flash_recovery_area.

Recovery operation using RMAN :

1. To view list of backups use this command.

Rman>list backupset;

2.It is not possible to recover data from all backup set. So we have to validate the backup set. To check is the backup set is valid or not, issue following command.

Rman>validate backupset list_number;

Shutdown dataabse and put in mount mode to recover database.

Rman>recover database;


  1. As you all know that backup is very important concept. The same concept is applied to Oracle. Oracle has different types of backups. The introduction of every backup is given here and that too with examples. The uses of these backups are also explained.
    sap upgrade challenges


  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle DBA TECHNOLOGY , kindly contact us
    MaxMunus Offer World Class Virtual Instructor-led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ pieces of training in India, USA, UK, Australia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Pratik Shekhar
    Ph:(0) +91 9066268701