Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: January 20, 2002 - 11:39 am UTC

Last updated: September 28, 2007 - 5:39 pm UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

Hi,
We would like to implement our Backup and Recovery Plan for our new Oracle 9i database on UNIX using RMAN. Our current backup and recovery plan is based on OS scripts for our other 8i databases.

Can you outline a robust plan for us. We have done reading of the RMAN manuals... appears a bit dispersed. We are a 24x7 shop and have database in archive log mode. The size of the database is around 200 MB.

Requirements:

i) Full database backup eveynight( need help as to incremental or not)
ii) Backup of archive logs twice/thrice a day.
iii) Would like to backup directly to tapes, if possible and efficient. Otherwise we have to do Disk-Disk-Tape. If we do a D-D-T, do we have to remember the tape details in that case.

Regards..

Irshad.

and Tom said...

i) for a 200meg database (really -- just 200meg? my system tablespace is bigger then that), incremental wouldn't be necessary.

ii) fine

iii) fine as well

RMAN does all of that -- you just need to write the scripts or use OEM to do this in a "GUI" fashion.

I don't know what you mean by RMAN Manuals and "dispersed". There are only two manuals -- a users guide and a reference. The users guide has a "quick start" to get your feet wet, and much more detail on how it works. Then chapter 9 has examples for you "making whole database backups with RMAN" and "backup up archived redo logs with RMAN"



Rating

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Backups

Rahul, January 20, 2002 - 7:29 pm UTC

Tom,

How big is your database and what is your backup startegy.
Do you perform rman or physical backups.

Thanks



Tom Kyte
January 20, 2002 - 8:25 pm UTC

they range from tens to a couple hundred gigs (there is about 6 or 7 of them). We have a tape library and just do old fashioned scripted backups keeping the last 5 backups and archives available. Just never got around to upgrading to rman -- our backups have been going good enough since version 7.0 -- no need to break that which isn't broken. Although with some of the new 9i features -- like block level recovery, restartable backups/restores, and so on -- I'm very tempted...



very interesting

Eric Grancher, January 21, 2002 - 9:17 am UTC

it is very interesting to have a point of view different from (aouah you are still using scripts, you'd better use RMAN). We are also in the situation where we use a number of well prepared scripts.

Reader

Reader, January 21, 2002 - 10:44 am UTC


I am using a HYBRID, conventional once a week ( 2 sets
retention), RMAN every day - before building confidence
with RMAN.

delete rman obsolete backups

A reader, June 11, 2004 - 6:13 am UTC

Hi

We are using RMAN 10g and configued a retention policy 7, so keepping 1 week copy basically.
We send the backup to disk then using a media manager we move from disk to tape.

My question is whenever I run delete obsolete backup RMAN looks in the disk the old backups and tries to delete them, obviously the old backups are not there, are in the tapes (we then reuse these tapes). Is there a way to delete from catalog so RMAN wont look in the disk?

Tom Kyte
June 11, 2004 - 3:54 pm UTC

why would you not have rman manage them? don't delete them yourself, ask rman to do so? less steps.

we do not manage tape with RMAN

A reader, June 12, 2004 - 5:41 am UTC

Hi

We dont use RMAN to manage the tapes so we backup to disk using RMAN then the media manager manages to move disk to tape. When RMAN wants to delete it fails because the files are in TAPE (or not even there anymore since we reuse the tapes). I saw this command called crosscheck, guess that is the only way to make RMAN not complain before we run delete obsolete?

Tom Kyte
June 12, 2004 - 9:49 am UTC

what are you going to do when you want to restore here?

what is the point of having them on tape and telling rman "forget about them, they are gone". How will you restore with them?

You need to let rman manage this metadata.

If you nuke it from the rman repository -- how will you restore them later? you just have to let rman think they are there. until you really remove them from tape and they really really are not there.

no no, you kissunderstand I guess

A reader, June 13, 2004 - 5:40 am UTC

Hi

What I do is configure the retention policy to 7, when I want to restore I first restore to tape. However the problem I am talking is not about restoring the backup. Whenever I issue delete obsolete (because of retention policy) it compalins about file existence, does it mean to delete obsolete backups shall I first retstore from tape to disk...? Doesnt sound right to me...

Tom Kyte
June 13, 2004 - 10:55 am UTC

given that you are not using RMAN to manage the stuff at all, why are you using a recovery catalog period??

You are doing *everything yourself*. In the event of a recovery YOU must go to tape. YOU must find files. YOU must restore files (to trick rman into thinking it had the files all along) and then -- and only then -- you can use rman as designed.


You have no need of a recovery catalog here. You have no *need* of the delete obsolete (why even bother? it is not like rman can clean up anything? you have moved it all by yourself).

I think you are misunderstanding. The features of rman you are using are totally 100% defeated by your approach.



because I have my scripts stored in the catalog

A reader, June 13, 2004 - 2:01 pm UTC

Hi

I use a catalog because I have the script stored in the catalog. But you are right since RMAN is not managing the tape I will use again our old database backup scripts and stop using RMAN

Cheers



Tom Kyte
June 13, 2004 - 5:05 pm UTC

oh no, keep using rman -- the payoff is that it actually checks the datafiles, just don't expect to use any of the rman features that rely on the catalog since you are moving every thing around (either that or include dbv as part of your backup).

what do you mean it checks for datafiles?

A reader, June 14, 2004 - 10:19 am UTC

Hi

How does RMAN check for datafiles? Do you mean all backups made by RMAN wont have corruption?

Tom Kyte
June 14, 2004 - 10:51 am UTC

RMAN reads and processes oracle blocks.

if the block is not "good", would cause a runtime error during a query, rman tells you right away. You do not discover this 5 months later like you might with "tar" or "dd" which just read "bits and bytes"

Suprised

daniel, June 15, 2004 - 12:51 pm UTC

Tom,

Are you still not using rman? I"m suprised that you are using old fashioned scripted backups.

Tom Kyte
June 15, 2004 - 5:32 pm UTC

we use rman to backup now. this was many releases ago when we first started this thread.

How??

ARU, April 26, 2005 - 11:03 pm UTC

Hi Tom,

Earlier in the thread you said:-

"You are doing *everything yourself*.In the event of a recovery YOU must go to tape. YOU must find files. YOU must restore files(to trick rman into thinking it had the files all along)and then--and only then--you can use rman as
designed."

I would be really interested to know how in this case can one TRICK rman into thinking it had the files all along ??

Thanks,
Regards,
Aru,




Tom Kyte
April 27, 2005 - 7:38 am UTC

by putting them back where rman put them in the first place. If you move rmans cheese (the files) you have to put them back before it can use them.

Is it really "plan" and requirements?

Edgar, April 28, 2005 - 7:20 am UTC

Just in case,

What for are You doing backups? - to recover sometime when crash happens.

What is the business requirements?
I ques, it is absolutely indifferent for business how often and using which method You are doing thats backups.

Things, which makes seance for business, are

- how fast can You recover from various failure situation
(disk crash, server crash, network and datacenter crash, human error..)

- what probability to happen have the above cases
(it can be reduced by redundant hardware, flashback ..)

- how much it costs to implement good enough backup/recovery strategy (additional hardware, software) to compare to the costs of downtime and, maybe even, some data loss.

24x7, 200MB (typing error?)
You can benefit from D-D-T at low cost.
And stabdby, RAC maybe is also an option for You and should be then part of the recovery plan.
It is worth
- to test recovery on regular basis;
- to have own-written short manuals to act quickly;

Dear Tom,
Could You, please, put references to Your favorite quides/templates for implementation of data contingency polices?

Sorry for my English,
Sincerely,


Tom Kyte
April 28, 2005 - 8:32 am UTC

Retention

A reader, April 28, 2005 - 8:47 am UTC

Hi Tom

What is retention and what the retention does? We used to take manual backups and through NT backup and on to tapes. One time, when we are trying to recover database, one of my colliegue has done the retention on the data tape (from which we need to retrive the data) unknowingly as media was not detected by the system. At that time, entire data was lost. This may be a silly question. Can you put some light on this.

Tom Kyte
April 28, 2005 - 8:55 am UTC

do you mean "retention" as in to "retain" (a retention policy tells us how long to keep backups for).

or do you mean the act of re-tentioning the tape to take slack out of it and they didn't do that and it snapped (the tape broke)?

<quote>
one of my colliegue has done the retention on the data tape (from
which we need to retrive the data) unknowingly as media was not detected by the
system.
</quote>
is not clear to me.

RMAN Backup Status

Paresh, April 28, 2005 - 9:18 am UTC

Hi Tom,

Many thanks for your efforts to share your knowledge and experience with Oracle professionals around the world.

We have a job that runs at 3am everyday. This job backups the whole database every morning using RMAN. We want to write some scripts to report the status of the backup (successfully completed, failed, etc). If it failed, what was backed up successfully until then. Which RMAN views should I be looking at to confirm the backup status. Please provide some scripts/queries to explain them.

Thanks once again,

Paresh


Tom Kyte
April 28, 2005 - 9:37 am UTC

don't you use a tool like OEM to manage jobs and such?

Sounds like a short retention period and tape was overwritten

Bill Schwartz, April 28, 2005 - 9:23 am UTC

Not sure about what tape system the poster is using, but it sounds to me like they specified too short a retention period, then reused the tape when the period expired and now they have incomplete data.

Tom Kyte
April 28, 2005 - 9:38 am UTC

thats why I sort of want a clarification -- I can read that two or three or four ways :)

RMAN Backup Status

Paresh, April 28, 2005 - 1:16 pm UTC

Sorry for not being clear.

We don't use OEM to manage jobs.

We have compiled an RMAN script that takes hot backup of the database (full backup). This RMAN script is scheduled to run at 3am using cron job. Well, the question is not about the scheduler. What is the easiest way of making sure that the backup taken overnight was successful. RMAN views, source database's v$ views or is there a better way of doing this. Please explain in detail. Excuse me for my knowledge on RMAN. I have only started to use RMAN.

Paresh

Tom Kyte
April 28, 2005 - 2:04 pm UTC

You'll want to fully familarize yourself with the rman catalog views -- i don't have a script for you personally. sorry. They are fully documented in the rman guides.

from RMAN Advanced Guide Docs....

pasko, April 28, 2005 - 3:55 pm UTC

Hi Paresh,

this is from RMAN Docs 10G , it could be of some use:
----------------------------------------------------------
Identifying RMAN Return Codes
----------------------------------------------------------
One way to determine whether RMAN encountered an error is to examine its return code or exit status. The RMAN client returns 0 to the shell from which it was invoked if no errors occurred, and a nonzero error value otherwise.
How you access this return code depends upon the environment from which you invoked the RMAN client. For example, if you are running UNIX with the C shell,then, when RMAN completes, the return code is placed in a shell variable called
$status. The method of returning exit status is a detail specific to the host operating system rather than the RMAN client.
-----------------------------------------------------------

Problem

ARU, April 28, 2005 - 5:55 pm UTC

Hi Tom,

You said :-
"by putting them back where rman put them in the first place. If you move rmans cheese (the files) you have to put them back before it can use them."

This backup which we have on tape is from around a month back.Business wants the database restored to that backup now. Redundancy is 14. But it is on the tape nevertheless. Now we put it back to the place where RMAN backs it disk, but now how do we tell RMAN to apply that particular backup. Also how to tell RMAN about the backup files ? Will RMAN detect it by itself or do we need to recatalog it or what else......?
Please advice.

Thanks lots as always,
Regards,
ARU.






Tom Kyte
April 28, 2005 - 6:40 pm UTC

are you using a recovery catalog or just control files

(and why haven't you practiced this 1,000 times already before having to do it)

actually -- big suggestion: please contact support, they'll collect the needed information and guide you through this. You are not testing, this is real...

Problem

aru, April 28, 2005 - 8:06 pm UTC

Hi Tom,
Yes we are using a catalog. Does that make things better?
This is actually a practice for "IF the scenerio ever arises" and will definitely test 1001 times. But we're stuck now.Help please.

Thanks,
Aru.

Tom Kyte
April 28, 2005 - 8:18 pm UTC

restore the files back to where they came from and rman will never know they were gone (unless someone did a resync)

Problem

Aru, April 28, 2005 - 9:57 pm UTC

Hi Tom,

But isen't a resync done at each backup database command? If so, then about 30 resyncs have happened since.
Regards,
Aru.



Tom Kyte
April 29, 2005 - 8:00 am UTC

put the files back, if rman doesn't know about them, resync your catalog.

seeking help

RD, May 03, 2005 - 8:50 pm UTC

Hi Tom,

We are also facing a similar problem as Aru above,

Scenerio is :-

Backupset 1,2 are more than 8 days old.
Backupsets are removed from disk and copied to tape next day. Every day a 'delete expired backup completed before 'sysdate-7'' is done. So now backupsets 1,2 are deleted from the recovery catalog. Right?

I want to now use the backupsets 1,2(still on tape) to restore the database to a point in time 8 days back.
I put the sets back on disk but now how will I get RMAN to recognize the backupsets that are not in the catalog anymore?? We are using oracle8i and 9i and using recovery catalog.
Thanks in advance,
Regards,
RD.

Tom Kyte
May 04, 2005 - 8:52 am UTC

see Note 174623.1 (i've not done this myself)

Posting question for 1st time

Shabbir Vora, May 12, 2005 - 11:28 am UTC

Hi Tom

1 quick question.

plz let me know if you this message reaches you ?

Will truly appreciate it !!

Rgds

Shabs


Tom Kyte
May 12, 2005 - 1:22 pm UTC

yes it did - but 1 is a number, and plz is a german postal code :)



Recover using backup control file

jp, May 24, 2005 - 8:42 am UTC

Dear Tom,
When we are doing a recover, and we need to use a backup of the controlfile, what its the difference using
RECOVER DATABASE USING BACKUP CONTROLFILE or only
RECOVER DATABASE

thanks



Tom Kyte
May 24, 2005 - 10:23 am UTC

recover data uses the current control file which knows the "exact state of the database" and much can be automated.

using a backup controlfile, it might be missing a newly added datafile, it doesn't know how far the database could be recovered and so on.

it is 'more complex' with the backup control file.

Just create a new controlfile

Robert, May 24, 2005 - 12:15 pm UTC

Tom,

If possible, and all other things being equal, wouldn't it usually be easiest just to recreate a new controlfile and do a simple 'recover database'

In other words, wouldn't we usually only want to use the 'backup controlfile' if there was no other possible way?

Thanks,

Robert.

Tom Kyte
May 24, 2005 - 1:41 pm UTC

many times you use a backup control file to create a controlfile to trace, yes. to get a starter controlfile back (one you can edit to "fix up" if need be)

A reader, May 24, 2005 - 1:52 pm UTC

Hi Tom,

We have a situation like as follows:

1. We have setup an oracle 10g database server which has two drives, one in which the datafiles are actually created and the other drive we are planning to use for backups. Consider our situation where we have the drives as 'D' and 'E'. If we have currently setup the archive log destination as 'D', should we also duplex the archive logs to 'E'. What should the settings be for that?
2. In a situation where there is a disk failure in 'D' and we want the database to be up and running by using the datafiles from the backup which are in 'E' what should we do? Should we something before taking the backup such that this will happen?
3. If we want to take a backup of all the datafiles to disk E should we use the OCOPY mechanism or is there anything else?

Please let us know.

Thanks.

Tom Kyte
May 24, 2005 - 3:37 pm UTC

two drives? not much opportunity there is there.

one drive for database is going to "be slow", what is this application, how many users

(two disks??)

A reader, May 25, 2005 - 10:50 am UTC

Yes there are two disks. At any time the number of users who will be connected will be around 50-100. The thing is we were using tapes for daily and weekly backups and we want to go away from using tapes. So we went for an additional RAID disk which can be used for backups. Why do you think this will slow down the database?

Thanks.

Tom Kyte
May 25, 2005 - 1:30 pm UTC

two disks or two mount points with dozens of disks?

two disks is tiny, i have that many for my laptop, won't perform very well with 50-100 users.

A reader, May 25, 2005 - 1:36 pm UTC

Two arrays of disks.

Tom Kyte
May 25, 2005 - 3:30 pm UTC

ok, that makes more sense.

what tool do you use to backup with?

A reader, May 25, 2005 - 3:46 pm UTC

Hi Tom,

We were using Veritas for backup with tapes. For disk hot backups we were planning to use commands like :

alter tablespace <tablespacename> begin backup;
ocopy .....
alter tablespace <tablespacename> end backup;

Is there some other tool we can use for disk backups?

Thanks.

Tom Kyte
May 25, 2005 - 7:44 pm UTC

rman, no need to put things into backup mode at all (it understands the database and can do the right thing)

OCOPY

Deepak, July 01, 2005 - 6:57 am UTC

Hi Tom,

You always suggest not to use COPY or OCOPY for copying data files during Hot Backup. Right? And insists on using RMAN instead. I am just curious to know that RMAN being a new feature from Oracle 8.0 onwards, how the old 7.x and earlier databases were being backed up. Was hot backups not being used in critical database systems?

Isn't Oracle working on it's OCOPY utility for improvements?


Tom Kyte
July 01, 2005 - 10:18 am UTC

ocopy is still there, you can still use it. no improvments need be made to something whose sole job is to simply copy a file

(and when 7.3 was out there, was anyone running 'critical database systems' on windows :)

Pravesh Karthik from India

Pravesh Karthik, December 06, 2005 - 8:01 am UTC

Tom,

Please let me know how to achieve the below requirment


* All incremental level 0 oracle backups (taken every saturday) to be kept for 1 month only, except for the first weekly backup each month, which is to be kept for 1 year


I can have a script for level 0 daily as below.

run {
allocate channel ch1 type 'SBT_TAPE';
allocate channel ch2 type 'SBT_TAPE';
allocate channel ch3 type 'SBT_TAPE';
allocate channel ch4 type 'SBT_TAPE';
backup
incremental level 0 keep until time 'sysdate+30'
filesperset 8
format '%d.L0_bkup_%s.%p'
(database);
}

please let me know, how to have for first weekly backup to be kept for 1 year in the above script

Thanks,
Pravesh Karthik

Repository Configuration Assistant

A reader, December 24, 2006 - 4:25 pm UTC

Hi Tom

How do I verify if the repository has been installed on any existing database(Oracle 10g Rel 1).

Is there a check for it ? or if you can direct me to some documents.

Will really appreciate your response.

Thanks and Merry Christmas !!

SVS

RMAN Retention policy

Sanji, June 28, 2007 - 5:02 pm UTC

Tom,

Env. Oralce 9.2.0.7.0, HP-UX 11i

The management wants the following backup policy to be implemented

1> Daily backups to be retained for 2 weeks
2> Monthly backups to be retained for a year.
3> Yearly backups to be retained for 10years.

Now, the daily backup is not an issue as i have set the retention policy to recovery window of 14 days.
Issue is with the monthly backups. If i change the retention to 1 year of those backups, all existing (starting monthly backup schedule) and future archives would be retained for a year. I see this as a limitation with RMAN on 9i.

We have a local dataguard instance (physical standby). The only alternative that i could think of is to do a clean shutdown of the physical standby database and then back it up to tape, either through OS or through RMAN.

If through RMAN, i can set the retention of these backup pieces to 1 year and simultaneously the media management server policy to retain the tapes for a year.

If through OS, i can set the media library policy to retain the tapes for a year.

Is there any other method where in we can retain a hot backup of the database for a year (without retaining the archives forever.. since the monthly backups would be repetetive)

Thanks
Sanji

Tom Kyte
July 02, 2007 - 10:49 am UTC

seems the monthly and yearly should be warehoused offsite doesn't it? meaning, it would be sensible to

a) restore the database to some machine once a month
b) recover it and open it
c) shut it down normal

and warehouse a full cold backup of that database.

and just clean out the ones you don't want each month.



planning for a backup

Srikanth, September 28, 2007 - 6:14 am UTC

hi friend,
when you want to take up a backup(cold or hot) what are the main things which you need to consider?
what do you mean by "planning for a backup" in real world environment?
how you plan for backup in your case(in your workplace)?
Tom Kyte
September 28, 2007 - 5:39 pm UTC

planning for backup

o what is your mean time to repair - if you backup once a week, and suffer a media failure, you might have to wait a while on Saturday if you backup on Sundays to recover.

o what is your unit of recovery, do you want to be able to repair blocks, files, tablespaces or everything every time.

o what sort of storage do you have to store the backups on, can you have disk based recovery, or are you penny wise, pound foolish and using a single DAT tape that you rewind and write over manually or what.

You know, all of the things you think about when you devise how you will backup your database - based on your requirements for availability and reliability.

Alexander, December 01, 2008 - 5:30 pm UTC

Hello Tom,

I'm trying to keep a week of archivelogs and backups on disk, then remove them.

This is what I have configured

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oin04t/ora_backups/rman_backups/%F.bkp';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oin04t/ora_backups/rman_backups/%d_%U.bkp' MAXPIECESIZE 10 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/9.2.0/dbs/snapcf_OIN04T.f'; # default

What I don't understand is RMAN is not removing old backups and I don't understand why.

RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           3682   21-NOV-08
  Backup Piece       3682   21-NOV-08          /oin04t/ora_backups/rman_backups/OIN04T_k6k09n7a_1_1.bkp
Backup Set           3683   21-NOV-08
  Backup Piece       3683   21-NOV-08          /oin04t/ora_backups/rman_backups/OIN04T_k7k09n9v_1_1.bkp
Backup Set           3684   21-NOV-08
  Backup Piece       3684   21-NOV-08          /oin04t/ora_backups/rman_backups/OIN04T_k5k09n7a_1_1.bkp
Backup Set           3685   21-NOV-08
  Backup Piece       3685   21-NOV-08          /oin04t/ora_backups/rman_backups/OIN04T_k8k09nde_1_1.bkp

RMAN> delete obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=51 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=16 devtype=DISK
no obsolete backups found



retention policy

Ari, June 13, 2011 - 3:28 pm UTC

Hi Tom,

I am going to ask a very basic query to you:

Suppose I have taken a incremental full level 0 backup on Monday and incremental level 1 cumulative backup on other days (Tuesday ->Sunday). I have set my retention policy as (a) "CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;" (b) "CONFIGURE RETENTION POLICY TO REDUNDANCY 3;"

Now my question is: In the both cases based on the retention policies will the incremental level 0 backup be omitted by RMAN? If not then what will be omitted by RMAN?

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.