Backup & Restore
Ravip via DBMonster.com
If i have the backup of the database (RMAN incremental backup) for a
particular date, eg: till yesterday I have the backup. Due to some reason ,
if restore needs, from which time onwards the archive redolog needs to apply ?
16-09-2007 3:00 AM: Backup incremental level 1 - finished successfully
17-09-2007 11:00 AM: DB down to HDD controller failure.
Need to restore from the 16th backup - and how about the archive redolog
files ? Please clarify.
Thank You.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/oracle/200709/1
Cristian Cudizio
> Hi
>
> If i have the backup of the database (RMAN incremental backup) for a
> particular date, eg: till yesterday I have the backup. Due to some reason ,
> if restore needs, from which time onwards the archive redolog needs to apply ?
>
>
> 16-09-2007 3:00 AM: Backup incremental level 1 - finished successfully
> 17-09-2007 11:00 AM: DB down to HDD controller failure.
>
> Need to restore from the 16th backup - and how about the archive redolog
> files ? Please clarify.
>
>
> Thank You.
>
I'm not confortable with incremental backups, however i think you have to
restore first your level 0 backup, then all level 1 backups and then apply
redo logs from last backup op 16-09-2007. This is why i don't like incremental backups....
regards,
--
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com
vital...@gmail.com
wrote:
> I'm not confortable with incremental backups, however i think you have to
> restore first your level 0 backup, then all level 1 backups and then apply
> redo logs from last backup op 16-09-2007. This is why i don't like incremental backups....
These are the steps indeed, but you don't have to perform them
manually, RMAN will take care of them when you execute a single set of
commands like:
...
restore database;
recover database;
...
RMAN will choose the backup pieces automatically. Incremental backup
is nothing to be feared ;) (with currently supported Oracle versions
at least.)
Brian Peasland
> Ravip via DBMonster.com wrote:
>> Hi
>>
>> If i have the backup of the database (RMAN incremental backup) for a
>> particular date, eg: till yesterday I have the backup. Due to some
>> reason ,
>> if restore needs, from which time onwards the archive redolog needs to
>> apply ?
>>
>>
>> 16-09-2007 3:00 AM: Backup incremental level 1 - finished successfully
>> 17-09-2007 11:00 AM: DB down to HDD controller failure.
>>
>> Need to restore from the 16th backup - and how about the archive redolog
>> files ? Please clarify.
>>
>>
>> Thank You.
>>
>
> I'm not confortable with incremental backups, however i think you have to
> restore first your level 0 backup, then all level 1 backups and then apply
> redo logs from last backup op 16-09-2007. This is why i don't like
> incremental backups....
>
>
>
> regards,
>
>
Christian,
Have you looked at Incrementally Updated Backups in 10g? With IUB, you
only have to restore the level 0 and then *one* level 1 backup and the
redo logs.
There is more information in the RMAN docs.
Cheers,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
DA Morgan
> Ravip via DBMonster.com wrote:
>> Hi
>>
>> If i have the backup of the database (RMAN incremental backup) for a
>> particular date, eg: till yesterday I have the backup. Due to some
>> reason ,
>> if restore needs, from which time onwards the archive redolog needs to
>> apply ?
>>
>>
>> 16-09-2007 3:00 AM: Backup incremental level 1 - finished successfully
>> 17-09-2007 11:00 AM: DB down to HDD controller failure.
>>
>> Need to restore from the 16th backup - and how about the archive redolog
>> files ? Please clarify.
>>
>>
>> Thank You.
>>
>
> I'm not confortable with incremental backups, however i think you have to
> restore first your level 0 backup, then all level 1 backups and then apply
> redo logs from last backup op 16-09-2007. This is why i don't like
> incremental backups....
>
>
>
> regards,
What vitalisman said plus ... you can dislike level 1 backups all you
want if you have a very small database. Move into the tens of TB or
above and you will quickly discover level 0 is a luxury.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
vital...@gmail.com
> Have you looked at Incrementally Updated Backups in 10g? With IUB, you
> only have to restore the level 0 and then *one* level 1 backup and the
> redo logs.
Hi Brian,
I don't know if it was due to your phrasing but your explanation looks
strange to me.
Having to restore just 1 level-1 backup after 1 level-0 backup is
guaranteed with cumulative incremental backups, available with 9i.
I have never used IUB yet, but when you want to restore, you don't
have to restore any level-0 backup according to the doco, only one
level-1 backup.
I don't think IUB is a great thing because of the disk space needed.
And if the database is not that huge, why bothering with incremental
stuff?
Jerome
Cristian Cudizio
From documentation http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/editions.htm#CJACGHEB
i see that incremental backup and recovery are available with SE but there is a note thas say's:
"SE1/SE: no optimized incremental backup"
what does it means? Is about the optimization described here? http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1008.htm#sthref314
thanks,
Cristian Cudizio
Then if you have enough space on disk restore and recovery operation
are very fast.
Cristian Cudizio
> Cristian Cudizio wrote:
>> Ravip via DBMonster.com wrote:
>>> Hi
>>>
>>> If i have the backup of the database (RMAN incremental backup) for a
>>> particular date, eg: till yesterday I have the backup. Due to some
>>> reason ,
>>> if restore needs, from which time onwards the archive redolog needs
>>> to apply ?
>>>
>>>
>>> 16-09-2007 3:00 AM: Backup incremental level 1 - finished successfully
>>> 17-09-2007 11:00 AM: DB down to HDD controller failure.
>>>
>>> Need to restore from the 16th backup - and how about the archive redolog
>>> files ? Please clarify.
>>>
>>>
>>> Thank You.
>>>
>>
>> I'm not confortable with incremental backups, however i think you have to
>> restore first your level 0 backup, then all level 1 backups and then
>> apply
>> redo logs from last backup op 16-09-2007. This is why i don't like
>> incremental backups....
>>
>>
>>
>> regards,
>
> What vitalisman said plus ... you can dislike level 1 backups all you
> want if you have a very small database. Move into the tens of TB or
> above and you will quickly discover level 0 is a luxury.
there's no doubt, however the question is about time to make full backup, not space.
However you pay a price in the unpleasant situation you have to restore the backup
regards
DA Morgan
Backup every night ... hopefully restore never.
If you have implemented best practices ... lockdown the server, DDL
triggers that prevent dropping and truncating objects ... etc. the
likelihood of having to ever recover and restore is vanishingly small.
I'd focus on what I must do rather than on what I may never have to do.
Brian Peasland
Actually, IUB can save you more disk space. You won't have multiple
level 1 backups lying around. The level 0 is "updated" (hence the term
Incrementally Updated) with changes from the previous level 1. The
following is a good paper discussing IUB's.
http://www.nyoug.org/Presentations/SIG/DBA/10grman.pdf
The big difference between IUB's and cumulative incremental backups is
that IUB's update the level 0 copy and the only level 1 denotes the
changes since the level 0. When you run a new level 1, the level 0 gets
updated with the old level 1 info and a new level 1 is created.
The cumulative backups keep the level 0 the same. The level 1 just holds
all changes since the level 0. If every block changes since that level
0, the level 1 could be as large as the level 0 backup.
If your database is small, then incrementals may not be of any interest
to you. If your database is small, then why not just perform a full
backup every time? Incremental backups were not created for small
databases. They were created to reduce the backup window of large
databases.
Brian Peasland
I have no clue! I would say that the optimization described in your
second link is not the optimization they mean when they say "no
optimized incremental backup". But I'm not sure what that term means
either. I'm afraid I'd need my Oracle sales rep to sort that one out for
me. It has never applied to me in the past since I use EE, so I've never
had to ask the question....
I do find it odd that in the Oracle docs where it discusses incremental
backups
(http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1005.htm#sthref237),
it does not discuss the IUB type. Rather, they are discussed here
(http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup004.htm#sthref409).
Alberto Frosi
>
> - Show quoted text -
Hi Cristian,
the incremental backup is NECESSARY for every db of large dimension.
It's RMAN which manage the incremental and the full backup.
A full backup it's normally performed every sunday or saturday while
the incremental every day, but exist more solution or combination for
incremental and full solution.
I've a 9.2 Oracle EE server and i performed this solution, in 10g i
don't know.
The incremental backup it's only performed on the EE while in the
professional this feature don't exist.
Regards,
Alberto Frosi
Jerome Vitalis
> Actually, IUB can save you more disk space. You won't have multiple
> level 1 backups lying around. The level 0 is "updated" (hence the term
> Incrementally Updated) with changes from the previous level 1.
But it doesn't save any disk space in typical backup infrastructures
where backups are taken to tapes directly. IUB requires a level 0 on disk.
> If your database is small, then incrementals may not be of any interest
> to you. If your database is small, then why not just perform a full
> backup every time? Incremental backups were not created for small
> databases. They were created to reduce the backup window of large
> databases.
Exactly my opinion on that subject.
I hope the OP will read your second explanation which was really good. ;)
Jerome
Ravip via DBMonster.com
My backup strategy:
Initial Level 0 backup
daily level 2 backup
weekly level 1 backup
I have the clarifications below:
1. Should I take the archive log backups as part of daily and weekly backup ?
My intention is to take the archive log backup separately. Is this
recommended ?
2. During the rotation I feel I can remove the level 2 backup taken on 1st
week during the 5th week.
The incremental backup merge in 10g can be used in my weekly backup. So that
every week backup will be merged with Level 0 backup.
I'm in a little confusion of using incremental backup with merging. My DB
size is close 200GB and daily 7-8 archive log of each 500MB get generated.
What kind of backup strategy will you recommend.?
Please recommend your views.
Thank You.
Brian Peasland wrote:
>>> Hi
>>>
>[quoted text clipped - 18 lines]
>>
>> regards,
>
>Christian,
>
>Have you looked at Incrementally Updated Backups in 10g? With IUB, you
>only have to restore the level 0 and then *one* level 1 backup and the
>redo logs.
>
>There is more information in the RMAN docs.
>
>Cheers,
>Brian
>
--
Ravip via DBMonster.com
I'm using ASM in by DB.
Ravip wrote:
>Hi Brian
>
>My backup strategy:
>
>Initial Level 0 backup
>
>daily level 2 backup
>
>weekly level 1 backup
>
>I have the clarifications below:
>
>1. Should I take the archive log backups as part of daily and weekly backup ?
>My intention is to take the archive log backup separately. Is this
>recommended ?
>
>2. During the rotation I feel I can remove the level 2 backup taken on 1st
>week during the 5th week.
>
>The incremental backup merge in 10g can be used in my weekly backup. So that
>every week backup will be merged with Level 0 backup.
>
>I'm in a little confusion of using incremental backup with merging. My DB
>size is close 200GB and daily 7-8 archive log of each 500MB get generated.
>What kind of backup strategy will you recommend.?
>
>Please recommend your views.
>
>Thank You.
>
>>>> Hi
>>>>
>[quoted text clipped - 12 lines]
Ravi via DBMonster.com
In the case of big DB like 200GB, is it possible to have a full backup on
every week end. Already raised this clarification with Brian. You can also
give your input for this.
Thank You.
Alberto Frosi wrote:
>On Sep 18, 12:35 pm, Cristian Cudizio <cristian.cudi...@yahoo.it>
>wrote:
>> > Hi
>>
>[quoted text clipped - 22 lines]
>>
>> - Show quoted text -
>
>Hi Cristian,
>the incremental backup is NECESSARY for every db of large dimension.
>It's RMAN which manage the incremental and the full backup.
>A full backup it's normally performed every sunday or saturday while
>the incremental every day, but exist more solution or combination for
>incremental and full solution.
>I've a 9.2 Oracle EE server and i performed this solution, in 10g i
>don't know.
>The incremental backup it's only performed on the EE while in the
>professional this feature don't exist.
>Regards,
>
>Alberto Frosi
--
DA Morgan
Three things.
1. Do not top post. Scroll to the bottom to reply.
2. 200GB is small to medium ... not large.
3. Yes.
Brian Peasland
> Hi Brian
>
> My backup strategy:
>
> Initial Level 0 backup
>
> daily level 2 backup
>
> weekly level 1 backup
>
> I have the clarifications below:
>
> 1. Should I take the archive log backups as part of daily and weekly backup ?
> My intention is to take the archive log backup separately. Is this
> recommended ?
How much data can you afford to lose? Note that this is a business
decision since data is a corporate asset. That data has a value to the
company. And when you get closer and closer to losing less and less data
(or even zero data loss), the solution gets more expensive.
The reason I ask the above is that if you lose your database server, you
can go to your backup tape (hopefully you have a backup on tape) and
recover from your incremental backups. So that get's you to the point in
time up to the backup. Your archived redo logs will get you the rest of
the way. If you can only lose one hour's worth transactions, then you
should back up your archived redo logs to tape once an hour. If you can
lose a day's worth of transactions, then you should back up the archived
redo logs at least once a day. If you can only lose 5 mins of
transactions...or have a zero data loss requirement, then you should
probably be looking at Data Guard.
How often you backup your archived redo logs depends on your data loss
requirements. It does not matter if this is done seperately from your
incremental backups or not. A RMAN backup of your archived redo logs is
a RMAN backup no matter what else was backed up with it.
> 2. During the rotation I feel I can remove the level 2 backup taken on 1st
> week during the 5th week.
>
> The incremental backup merge in 10g can be used in my weekly backup. So that
> every week backup will be merged with Level 0 backup.
>
> I'm in a little confusion of using incremental backup with merging. My DB
> size is close 200GB and daily 7-8 archive log of each 500MB get generated.
> What kind of backup strategy will you recommend.?
To be honest, I cannot recommend a backup strategy because I do no know
enough of your requirements. I already discussed data loss above. Your
data loss requirement drives part of your backup strategy. And the one
thing that many people do not look at hard enough when it comes to
designing the backup strategy is the *recovery requirements*. What are
your recovery requirements? Are you required to be up an running in one
hour? 2 mins? 1 day? Are you required to be up and running at a remote
site? There are other questions that could be asked as well. You
mentioned the db size, and that is one requirement...you have to be able
to back up a 200GB database with approximately 4GB of daily archived
redo logs. But what about your recovery requirements? Those should be
heavily weighed and factored in to your backup strategy. For instance,
if you are required to restore all transactions within one hour of
failure and you only perform cold backups and have no archived redo
logs, then the backup strategy does not meet the recovery requirements.
HTH,
Ravip via DBMonster.com
Thanks for comprehensive detail. Obviously I'm not in a position to lose data
generated in last minute as well. Again as you said it depends on backup
strategy.
My clarification is once the incremental backup taken on yesterday night,
should I need to retain the
the backup of archive redolog backups taken prior to yesterday's backup.
Because the db incremental backups are available from yesterday and previous
days. As I mentioned earlier that the backup of DB and archive log files are
executed separately.
Thank You.
Brian Peasland wrote:
>> Hi Brian
>>
>[quoted text clipped - 11 lines]
>> My intention is to take the archive log backup separately. Is this
>> recommended ?
>
>How much data can you afford to lose? Note that this is a business
>decision since data is a corporate asset. That data has a value to the
>company. And when you get closer and closer to losing less and less data
>(or even zero data loss), the solution gets more expensive.
>
>The reason I ask the above is that if you lose your database server, you
>can go to your backup tape (hopefully you have a backup on tape) and
>recover from your incremental backups. So that get's you to the point in
>time up to the backup. Your archived redo logs will get you the rest of
>the way. If you can only lose one hour's worth transactions, then you
>should back up your archived redo logs to tape once an hour. If you can
>lose a day's worth of transactions, then you should back up the archived
>redo logs at least once a day. If you can only lose 5 mins of
>transactions...or have a zero data loss requirement, then you should
>probably be looking at Data Guard.
>
>How often you backup your archived redo logs depends on your data loss
>requirements. It does not matter if this is done seperately from your
>incremental backups or not. A RMAN backup of your archived redo logs is
>a RMAN backup no matter what else was backed up with it.
>
>> 2. During the rotation I feel I can remove the level 2 backup taken on 1st
>> week during the 5th week.
>[quoted text clipped - 5 lines]
>> size is close 200GB and daily 7-8 archive log of each 500MB get generated.
>> What kind of backup strategy will you recommend.?
>
>To be honest, I cannot recommend a backup strategy because I do no know
>enough of your requirements. I already discussed data loss above. Your
>data loss requirement drives part of your backup strategy. And the one
>thing that many people do not look at hard enough when it comes to
>designing the backup strategy is the *recovery requirements*. What are
>your recovery requirements? Are you required to be up an running in one
>hour? 2 mins? 1 day? Are you required to be up and running at a remote
>site? There are other questions that could be asked as well. You
>mentioned the db size, and that is one requirement...you have to be able
>to back up a 200GB database with approximately 4GB of daily archived
>redo logs. But what about your recovery requirements? Those should be
>heavily weighed and factored in to your backup strategy. For instance,
>if you are required to restore all transactions within one hour of
>failure and you only perform cold backups and have no archived redo
>logs, then the backup strategy does not meet the recovery requirements.
>
>HTH,
>Brian
>
--
Alberto Frosi
>
> - Show quoted text -
Hi,
To be honest, I cannot recommend a backup strategy because I do no
know
enough of your requirements.
However i've a 250GB database and i backup it every day with
incremental 0, and during a day backup a archive log every hour.
In my database the archive logs are generated frequently and i meet
this solution.
At week-end i do a full backup.
I don't know your situation but i do that for my site.
The incremental 0 backup start at 7 pm and finish to 7.45 every day.
Regards,
Alberto
Jerome Vitalis
> To be honest, I cannot recommend a backup strategy because I do no know
> enough of your requirements.
> However i've a 250GB database and i backup it every day with incremental
> 0, and during a day backup a archive log every hour. In my database the
> archive logs are generated frequently and i meet this solution.
> At week-end i do a full backup.
> I don't know your situation but i do that for my site. The incremental 0
> backup start at 7 pm and finish to 7.45 every day. Regards,
So you never take any level-1 incremental backup?
Cristian Cudizio
and so it is full backup every day that can be used also with
incremental level-1 if done.
Brian Peasland
> Hi Brian,
>
> Thanks for comprehensive detail. Obviously I'm not in a position to lose data
> generated in last minute as well. Again as you said it depends on backup
> strategy.
>
> My clarification is once the incremental backup taken on yesterday night,
> should I need to retain the
> the backup of archive redolog backups taken prior to yesterday's backup.
> Because the db incremental backups are available from yesterday and previous
> days. As I mentioned earlier that the backup of DB and archive log files are
> executed separately.
>
> Thank You.
You will not need the archived redo logs from prior to the incremental
backup. However, if the last incremental backup was unusable for some
reason, you would need the archvied redo logs since the previous
incremental backup. Just to be safe, I keep the archived redo logs since
the previous incremental level 0. And I keep at least 3 level 0 backups
on hand. So if I generate a level 0 once a week, I'll have 3 weeks worth
of backups on hand. To make sure that I can use any of those backups, I
keep the archived redo logs for the last 3 weeks.
Keeping more than one generation of your backups is an old practice. The
most recent backup is generation 0. The backup before that is generation
-1. You may have generation -2 and maybe generation -3, depending on
your retention policy.
The reason to keep multiple generations is that backups are often stored
on tape media and tape media can fail. If there is only one backup and
that tape fails, the safety net is lost. But by having more than one
generation, we ensure we can get access to at least some of the data.
The other reason to keep multiple generations around is that one may
discover a problem (i.e. bad data) and that problem is in the backup.
One may occasionally desire to restore before that data became a
problem. This is hard to do with only one generation. Multiple
generations do not guarantee success here either.
In your reply, you stated "Obviously I'm not in a position to lose data
generated in last minute as well.". This makes me ask...are you doing
some sort of log shipping? Are you ensuring that your logs switch and
get archived at least once a minute? If not, then you may be at risk of
losing more than one minute's worth of data. Have you looked at Data
Guard in your data protection strategy?
Alberto Frosi
<vitalismanN05...@gmail.com.invalid> wrote:
No i never take any level-1 incremental backup, but for a need i can
attach it.
Regards,
Alberto
joel garry
This and the previous post about requirements are an excellent
description. I only want to add a couple of things:
There are some strange situations that can be gotten into with respect
to whether older archived logs are needed. But rather than try to be
paranoid enough to figure them all out, periodic test restores will
highlight any issues, as well as validate written procedures and keep
people practiced in skills which are rarely used, but needed in
critical situations.
Redundancy of archived log backup redundancy is a good thing! :-)
jg
--
@home.com is bogus.
It don't take a weatherman to punch a bozo in the nose.
http://www.youtube.com/watch?v=szWFQpnY_Dc
(Actually he shoved the guy down the rocks)
vital...@gmail.com
> > So you never take any level-1 incremental backup?
>
> No i never take any level-1 incremental backup, but for a need i can
> attach it.
Some advanced previsional backup strategy then. ;-)
Cheers,
Jerome
Ravip via DBMonster.com
From your feedback I have arrived the following backup strategy.
Level 0 backup every week.
Level 1 backup daily.
Retains the backup for every 4 weeks and it will be a cycle.
As my intention is to not loose any data but with present setup I didn't
configured for log shipping or data guard.
Please have your feedback again on this.
Thank You.
Brian Peasland wrote:
>> Hi Brian,
>>
>[quoted text clipped - 10 lines]
--
DA Morgan
Good start.
Did you do the following?
alter database force logging;
alter database add supplemental log data;
alter database flashback on;
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880; -- or other value
If you have EE I would give serious consideration to using Physical
Data Guard in Maximum Availability mode.
Brian Peasland
> Hi Brian,
>
> From your feedback I have arrived the following backup strategy.
>
> Level 0 backup every week.
>
> Level 1 backup daily.
>
> Retains the backup for every 4 weeks and it will be a cycle.
>
> As my intention is to not loose any data but with present setup I didn't
> configured for log shipping or data guard.
>
> Please have your feedback again on this.
>
> Thank You.
>
The above looks good, but have you covered all of your backup & recovery
needs? The above might be a good starting point in which to arrive at a
sound backup and recovery strategy which meets your requirements.
Cheers,
Ravip via DBMonster.com
I have updated my strategies. Your comments are highly welcome and
appreciated.
Thank You.
Brian Peasland wrote:
>> Hi Brian,
>>
>[quoted text clipped - 10 lines]
>>
--