Forum Stats

  • 3,824,753 Users
  • 2,260,414 Discussions
  • 7,896,306 Comments

Discussions

incremental level 1 backup

Robeen
Robeen Member Posts: 2,179 Silver Badge
edited Jan 20, 2020 9:23PM in Recovery Manager (RMAN)

Oracle DB 12.1.0.2

Hi,

is it necessary to have archivelog backups just after incremental level 1 backup completes?

My current L1 backup script is:

run {

setlimit channel ch1 maxopenfiles 2;

setlimit channel ch2 maxopenfiles 2;

backup incremental level = 1  filesperset = 8 format='<CVJOBID>_%d_%U' database  ; <<=========== should I include archivelog?

}

exit;

Regards,

Joe

Best Answer

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Jan 16, 2020 4:05AM Answer ✓

    Without archivelogs you are not able to get a valid, consistent restore and recovery with your backup (lvl 0 + lvl 1, You always need lvl 0 + lvl 1 + Archivelogs to be consistent (to allow an open (resetlogs)))..

    As I typically say, what is your SLA and what is your strategy for restore and recover and with that in mind what does this means for the backup strategy.

    If you already do some archivelog backups every hour or something like that, you don't need to backup the archivelog files also with the incremental backup lvl 1 - if you allow a loss of data in a recovery scenario.

    If you only run a lvl 0 backup once a week and a lvl 1 backup every day and no addtiional archive log backups, you are lost if your archivelog destination folder is destroyed or unaccessible.

    But that really depends on your SLAs.

    Most customers of our company have started to do hourly backups of archivelogs as separate jobs as they are really necessary for recovery.

«1

Answers

  • Sunny kichloo
    Sunny kichloo Member Posts: 2,459 Gold Trophy
    edited Jan 16, 2020 1:00AM

    It is better to include archive log in your script.

    Also you should have policy of deletion of obsolete backup through script only.

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Jan 16, 2020 1:15AM

    RMAN incremental level and archivelogs are both part of the recovery process. It makes sense to include "plus archivelog" with your backup, which includes archiving the current redo log. You may also include "delete input" so archivelogs are automatically removed after a successful backup. However, it isn't necessary, and depends on your backup strategy and requirements, which are unknown.

    Robeen
  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Jan 16, 2020 4:05AM Answer ✓

    Without archivelogs you are not able to get a valid, consistent restore and recovery with your backup (lvl 0 + lvl 1, You always need lvl 0 + lvl 1 + Archivelogs to be consistent (to allow an open (resetlogs)))..

    As I typically say, what is your SLA and what is your strategy for restore and recover and with that in mind what does this means for the backup strategy.

    If you already do some archivelog backups every hour or something like that, you don't need to backup the archivelog files also with the incremental backup lvl 1 - if you allow a loss of data in a recovery scenario.

    If you only run a lvl 0 backup once a week and a lvl 1 backup every day and no addtiional archive log backups, you are lost if your archivelog destination folder is destroyed or unaccessible.

    But that really depends on your SLAs.

    Most customers of our company have started to do hourly backups of archivelogs as separate jobs as they are really necessary for recovery.

  • Robeen
    Robeen Member Posts: 2,179 Silver Badge
    edited Jan 16, 2020 5:05AM

    My SLA is I will need to backup the archivelogs every 2 hours. My environment is a datawarehouse with downstream configuration of OGG. Around 350G archivelogs are generated everyday.

    I am getting same issue as from 2011174.1

    Do you advise me the purge archivelogs as described in the doc (once a day)? Will DB be recoverable after restore?

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Jan 16, 2020 5:37AM

    Your SLA says it is ok if 2 hours of data is lost when the database crashes and the primary archive log location is also corrupted/lost.

    There is a difference between having archive logs in your primary archive log destination (could be more than one) and having archive logs in backups.

    If you have backed up your archivelogs you can delete them from the primary location(s), because the database (it's recorded at the controlfile) does know what is needed to restore and recover the database to a specific point in time.

    If you have a lvl0 and a lvl1 and 2hourly archive log backups you can restore the database at least until then.

    If you have the archivelogs which have been created since the last backup and "Chrash-Time" also somewhere (on a disk, in a manual backup, as snapshot) then you are able to restore the database until you reach the "Crash-Time", so no data is lost.

    There are some kind of rman "report/list" commands which can help you to find out, which file(s) rman would use in case of a restore and recover and so on.

    Doing that on our backup-recovery workshop in detail with the attendees.

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Jan 16, 2020 5:50AM
    Without archivelogs you are not able to get a valid, consistent restore and recovery with your backup (lvl 0 + lvl 1, You always need lvl 0 + lvl 1 + Archivelogs to be consistent (to allow an open (resetlogs))).. 

    Sorry but your assessment is not quite correct. RESETLOGS only applies to an incomplete recovery. For example, when restoring the controlfile from backup, when performing a previous point in time recovery, or when the redo logs are lost.

    You can certainly do the following:

    RMAN> startup force mount

    RMAN> restore database;

    RMAN> recover database;

    RMAN> alter database open resetlogs;

    RMAN-03002: failure of sql statement command at 01/16/2020 13:31:02

    ORA-01139: RESETLOGS option only valid after an incomplete database recovery

    The above does not require RESTLOGS because it performed a full recovery.

    You also do not always require archivelogs, for example, when  restoring from cold backups.

    RMAN> shutdown immediate

    RMAN> startup mount

    RMAN> backup incremental level 1 database;

    RMAN> restore database;

    RMAN> recover database;

    channel ORA_DISK_1: starting incremental datafile backup set restore

    starting media recovery

    media recovery complete, elapsed time: 00:00:00

    RMAN> startup

    database is already started

    database opened

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Jan 16, 2020 5:52AM

    I know Dude, I am giving backup and recovery courses. That's why I have put (resetlogs) in brackets. It depends how and until when you need to restore and recover and...

    I can't put the whole course in there.

    In a production environment (and I think the OP means NOT a cold backup) I only see cold backups e.g. before upgrades, transfers to new servers, ...

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Jan 16, 2020 6:09AM
    My SLA is I will need to backup the archivelogs every 2 hours. My environment is a datawarehouse with downstream configuration of OGG. Around 350G archivelogs are generated everyday.

    I don't think this makes sense. Most backups including archivelogs reside on the same physical media. You aren't going to save any disk space if you use FRA, nor will you improve your recovery options. You are only going to slow down the database for no good reason. Instead I suggest to backup the database and archivelogs including cleanup during off-peek hours. If you need to offload archivelogs during normal operation, you need to increase your disk space and FRA allocation. If you need to squeeze your regular backups to fit on the storage media, you need to invest in more or different storage media. If you need to make sure not to lose archivelogs, I suggest to use RAID or multiplex archivelogs among different disks.

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 597 Bronze Trophy
    edited Jan 16, 2020 6:16AM

    None of my customer keep the backups and the archivelogs at the same physical media - all customers have some kind of backup agent/backup pool who is storing backups "somewhere else". They don't even use the same storages and most of them don't use a storage at the same datacenter.

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Jan 16, 2020 6:28AM

    I was talking about backups and backups of archivelogs. I don't know any setup where these backups go to different storage media. In case of FRA, archivelogs and backups must be in the same location in order for Oracle FRA space management to function. To backup the archivelogs without deleting them will occupy twice the space, plus there is no advantage to restore archvielogs from and to the same location.