Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup.

Would this be a good practice ? What would be the advantages of using RMAN over this approach ?

share|improve this question
    
Database duplication is much easier using RMAN –  Sumnibot Jan 4 '11 at 21:08

3 Answers 3

up vote 6 down vote accepted

The advantage of RMAN is PITR - point in time recovery. You can take an RMAN backup of the DBFs and an RMAN backup of the archived redo logs and recover your database to any point in time up to the time of the most recent archived redo log backup. The disadvantage of this approach is that it is very coarse-grained - you can only recover at the level of the tablespace.

The advantage of exp/expdp is that you have a consistent copy of the database that you can just import into a freshly created blank database. However, you cannot roll it forward - it is at this point a completely separate, independent database with no logical relationship to its original. But it is easy to recover just a single table, or even a few rows from an export.

A good approach would be regular RMAN full and incremental datafile backups, continuous backups of archived redo logs (e.g. as soon as one archivelog backup completes, start the next one straight away so you are "streaming" the logs to tape) then educate your users in the use of exp/imp so they can perform their own "backups" (in the case of wanting an actual copy of the data) and flashback (so they can do their own recoveries in the case of DML going wrong).

Remember - a backup is intended for the DBA to recover from catastrophic failure of the hardware. It is not for the benefit of end users (or you will spend your whole time doing restores to a test system and copying a few rows back to the original!).

share|improve this answer

The advantages of RMAN are as follows:

  • Automatic backup retention policy implemented using notion of windows. You can specify what amount of days your backups should be kept and chose a policy to keep them: they are removed as soon as they are expired, or they are removed if there's no free space left for new backups
  • Using of channels to improve performance while writing to media or using several disk controllers
  • Easy restore — basically you just list the backups you have and specify from which you want to perform the database restore
  • RMAN can save its backup metadata in an independent table called the RMAN Repository, which can hold really large number of records of all backups you've performed, as opposed to control file
  • You can specify the default number of backup duplicates, moreover the latter can be saved on different physical disks (media)
share|improve this answer

Exp/Expdp as a backup solution is like saying the auto-parts store is your backup automobile. Technically it will get you back up and running, but it will cause you nothing but pain and suffering.

Exp or Expdp can be used as a secondary backup to either filesystem cold backups or rman hot or cold backups (other Oracle backup client software usually only runs the RMAN commands).

The typical methodology would be as follows:

1 cold backup weekly
1 hot backup daily

And if you really want to be prudent, periodically transport the archived redo logs off of the server (every hour works) or set a remote location for a second archive log destination.

-- RMAN *new --

My typical RMAN session:

rman target=/

backup as compressed backupset database plus archivelog delete input;

delete obsolete;

exit

"Backup as compressed backupset..." : You can also do an image, which is a byte for byte copy of the datafiles. This would be good as that weekly backup.

"...database..." : pretty obvious

"...plus archivelogs..." : gives us point in time recovery (and point in time cloning [duplicate command in rman])

"...delete input" : delete the archivelogs that have been backed up. You can also set this to delete ones that have been backed up at least twice, etc.

"delete obsolete" : when you have configured your rman retention policy (mine is 5 days), then this will delete the backups that are outside of that window. This doesn't mean we can only recover 5 days ago. You should still have your tape/off-server backups daily of the flash recovery area. It just means that online you will have 5 days of recovery and after that you will need to restore flash recovery area items from tape/off-server backup, then register them with rman in order to use them.

Here is an actual log from a session, modified slightly for path's, etc:

oracle@prodserver[PROD]$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 5 21:00:00 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PROD (DBID=randomnumber)

RMAN> backup
2> as compressed backupset
3> database
4> include current controlfile
5> plus archivelog delete input;
6> backup spfile;
7> delete obsolete;
8>

Starting backup at 05-JAN-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=291 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=743 recid=743 stamp=739623589
input archive log thread=1 sequence=744 recid=744 stamp=739623940
input archive log thread=1 sequence=745 recid=745 stamp=739624712
input archive log thread=1 sequence=746 recid=746 stamp=739625380
input archive log thread=1 sequence=747 recid=747 stamp=739659606
channel ORA_DISK_1: starting piece 1 at 05-JAN-11
channel ORA_DISK_1: finished piece 1 at 05-JAN-11
piece handle=/u03/oraflash/PROD/backupset/2011_01_05/o1_mf_annnn_TAG20110105T210008_6lb8kb4o_.bkp tag=TAG20110105T210008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_743_6l95ckxx_.arc recid=743 stamp=739623589
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_744_6l95plo2_.arc recid=744 stamp=739623940
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_745_6l96gpok_.arc recid=745 stamp=739624712
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_746_6l973l32_.arc recid=746 stamp=739625380
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_747_6lb8k57v_.arc recid=747 stamp=739659606
Finished backup at 05-JAN-11

Starting backup at 05-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/PROD/system01.dbf
input datafile fno=00005 name=/u02/oradata/PROD/software.dbf
input datafile fno=00003 name=/u02/oradata/PROD/sysaux01dbf
input datafile fno=00002 name=/u02/oradata/PROD/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/PROD/users.dbf
channel ORA_DISK_1: starting piece 1 at 05-JAN-11
channel ORA_DISK_1: finished piece 1 at 05-JAN-11
piece handle=/u03/oraflash/PROD/backupset/2011_01_05/o1_mf_nnndf_TAG20110105T210135_6lb8n0y4_.bkp tag=TAG20110105T210135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 05-JAN-11
channel ORA_DISK_1: finished piece 1 at 05-JAN-11
piece handle=/u03/oraflash/PROD/backupset/2011_01_05/o1_mf_ncsnf_TAG20110105T210135_6lb8qblm_.bkp tag=TAG20110105T210135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-JAN-11

Starting backup at 05-JAN-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=748 recid=748 stamp=739659803
channel ORA_DISK_1: starting piece 1 at 05-JAN-11
channel ORA_DISK_1: finished piece 1 at 05-JAN-11
piece handle=/u03/oraflash/PROD/backupset/2011_01_05/o1_mf_annnn_TAG20110105T210323_6lb8qf3l_.bkp tag=TAG20110105T210323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u03/oraflash/PROD/archivelog/2011_01_05/o1_mf_1_748_6lb8qcrr_.arc recid=748 stamp=739659803
Finished backup at 05-JAN-11

Starting backup at 05-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 05-JAN-11
channel ORA_DISK_1: finished piece 1 at 05-JAN-11
piece handle=/u03/oraflash/PROD/backupset/2011_01_05/o1_mf_nnsnf_TAG20110105T210329_6lb8qlnm_.bkp tag=TAG20110105T210329 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-JAN-11

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 5 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           55     30-DEC-10        
  Backup Piece       55     30-DEC-10          /u03/oraflash/PROD/backupset/2010_12_30/o1_mf_nnndf_TAG20101230T210026_6ktg9v8w_.bkp
Backup Set           56     30-DEC-10        
  Backup Piece       56     30-DEC-10          /u03/oraflash/PROD/backupset/2010_12_30/o1_mf_ncsnf_TAG20101230T210026_6ktgdvnt_.bkp
Backup Set           57     30-DEC-10        
  Backup Piece       57     30-DEC-10          /u03/oraflash/PROD/backupset/2010_12_30/o1_mf_annnn_TAG20101230T210204_6ktgdy5j_.bkp
Backup Set           58     30-DEC-10        
  Backup Piece       58     30-DEC-10          /u03/oraflash/PROD/backupset/2010_12_30/o1_mf_nnsnf_TAG20101230T210210_6ktgf3pz_.bkp
Backup Set           59     31-DEC-10        
  Backup Piece       59     31-DEC-10          /u03/oraflash/PROD/backupset/2010_12_31/o1_mf_annnn_TAG20101231T210008_6kx2ob5r_.bkp
deleted backup piece
backup piece handle=/u03/oraflash/PROD/backupset/2010_12_30/o1_mf_nnndf_TAG20101230T210026_6ktg9v8w_.bkp recid=55 stamp=739141227
deleted backup piece
backup piece handle=/u03/oraflash/PROD/backupset/2010_12_30/o1_mf_ncsnf_TAG20101230T210026_6ktgdvnt_.bkp recid=56 stamp=739141323
deleted backup piece
backup piece handle=/u03/oraflash/PROD/backupset/2010_12_30/o1_mf_annnn_TAG20101230T210204_6ktgdy5j_.bkp recid=57 stamp=739141326
deleted backup piece
backup piece handle=/u03/oraflash/PROD/backupset/2010_12_30/o1_mf_nnsnf_TAG20101230T210210_6ktgf3pz_.bkp recid=58 stamp=739141331
deleted backup piece
backup piece handle=/u03/oraflash/PROD/backupset/2010_12_31/o1_mf_annnn_TAG20101231T210008_6kx2ob5r_.bkp recid=59 stamp=739227610
Deleted 5 objects


Recovery Manager complete.
share|improve this answer
1  
There is really no need in this day and age to even think about cold backups. And what is the point of a hot backup if you aren't also backing up the archived redo logs!? –  Gaius Jan 4 '11 at 20:46
    
If further detail is needed then I would be happy to elaborate, otherwise most articles on rman discuss archived redo log backup as well. It is the difference between: "backup database" and "backup database plus archivelogs", with of course all of the additional variations in between. –  REW Jan 4 '11 at 21:05
    
Please do come forward with your answer! –  Sathya Jan 6 '11 at 5:53
    
Thanks @REW for the detailed answer, wish I could +1 you again –  Sathya Jan 11 '11 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.