Forum Stats

  • 3,770,334 Users
  • 2,253,095 Discussions
  • 7,875,407 Comments

Discussions

Databases that need to restored+recovered 'ASAP'

Steve_74
Steve_74 Member Posts: 102 Blue Ribbon
edited Jun 20, 2020 12:37AM in Recovery Manager (RMAN)

Oracle DB version: 19c

OS : Oracle Linux 7.8

I have two RAC DBs in production. One DB is 2TB in size and another one is 1.5 TB. Currently, it is being backed up to DataDomain backup appliance.

I had done test restore+recovery of these 2 DB and following are the time it took.

2TB DB ----> 2 Hours

1.5TB DB ---> 1.5 Hours

Currently, I use Cumulative incremental backup for these 2 DBs.

But, application team wants these 2 DBs to be restored+recovered (be back up and running) 'ASAP', in case of a disaster.

In this case, 'Incrementally Updating Backups' mentioned in the below link is the best option. Right ?

I just have create a new diskgroup for each DB to store the image backups. i.e. The new Diskgroup size should be same as the DB size.

https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/backing-up-database.html#GUID-AF130A6B-06D1-4D52-87AC-BB8F4B30C9F9

Message was edited by: Steve_74 Changed the title as some don't understand what RTO means

resistanceIsFruitfulKazuhiroSteve_74EdStevens

Answers

  • Kazuhiro
    Kazuhiro Member Posts: 20 Blue Ribbon
    edited Jun 19, 2020 11:58AM

    Hi,

    Regarding "Incrementally Updating Backups", it will reduce RTO but do require full database restore for PITR, as same as in "Cumulative incremental backups", which usually takes some time.

    >But, application team wants these 2 DBs to be restored+recovered (be back up and running) 'ASAP', in case of a disaster.

    It is difficult for me to understand what ASAP really means in a context of backup and recovery. I think it should be described by terms like RTO/RPO...  Anyway, suppose you want to minimize RTO/RPO in case of physical disaster such as earthquake, floods or whatever, and the data center will be no longer available, then a disaster recovery solution such as Dataguard will be best suited for that situation.

    On the other hand, if your (application teams) "disaster" means data corruption by application failure or bugs and want to revert the whole database to a certain point of near time, then flashback database will be usually better suited than RMAN restore/recovery because it doesn't require full database restore.

    I'm using flashback database for my test/qa environment for restoring test data over and over, I found it is very useful and effective to speed up application testing with an automation platform.

    I hope I'm answering your question.

    Best regards,

    Kazuhiro

    resistanceIsFruitfulSteve_74Steve_74
  • EdStevens
    EdStevens Member Posts: 28,533 Gold Crown
    edited Jun 19, 2020 3:21PM

    The term ASAP (As Soon As Possible) has no measurable meaning.  Therefor, it's use in a specification can also logically mean "as slow as I can get away with".

    Steve_74
  • Dude!
    Dude! Member Posts: 22,826 Black Diamond
    edited Jun 19, 2020 11:23PM

    Designing an RMAN backup and recovery strategy to accomplish the quickest possible disaster option alone is not the best idea. Backup and recovery includes many more failure scenarios than disaster recovery alone.

    "Incrementally updating" eliminates the need for RMAN restore. You can use the "switch" command to quickly switch to datafile copies, and only have to apply the remaining archivelogs for data consistency to be able to open the database. However, while this provides a quick recovery option, it pretty much eliminates all the other restore and recovery options that you usually rely on when using RMAN.

    You cannot rely on backups any more than you can rely on your database. What happens, for example, if the last backup went bad, the backup media or hardware is bad? What happens when you loose your current archivelogs? What will you do if you need to recover some logical error and require older backups? You cannot use "Incrementally Updating Backups" for that purpose.

    I just have create a new diskgroup for each DB to store the image backups. i.e. The new Diskgroup size should be same as the DB size.

    That's hardly going to work. You need storage for incremental level backups as well as archivelogs too. Not to mention backups you wish you had for any other kind of failure scenarios, and archiving requirements you may have to comply with. If more disk space is not an option, use tape storage.

    If you need to plan for disaster you may also need to consider the loss of your server and storage due to fire, water, lightning, whatever. I think 2 hours for a complete restore and recovery of a 2 TB database is pretty good. If it isn't good enough, I suggest to look for alternatives, such as RAC, DataGuard, etc. You can go RMAN all the way, but you are going to need more backup storage.

    Kazuhiro
  • Steve_74
    Steve_74 Member Posts: 102 Blue Ribbon
    edited Jun 19, 2020 11:26PM

    Thanks Kahuzhiro, EdStevens, Dude

    Dude,

    You said:

    You need storage for incremental level backups as well as archivelogs too

    Thank you for pointing this out. Storage space is needed only for 1 days incremental backup. Right ?

    Every day, RMAN will take an L1 differential incremental backup then apply it to the image copies of the datafiles.

    For example: On Monday, RMAN will take an L1 differential incremental backup then apply it to the image copies of the datafiles.

    On Tuesday, L1 incremental backup on Monday will be made obsolete and a new L1 differential incremental backup will taken which will applied to the image copies. The process continues ....

    How can I get RMAN to automatically delete Monday's L1 backup which is obsolet ? How about placing the line shown in red below. Will that help?

    RUN

    {

      RECOVER COPY OF DATABASE

        WITH TAG 'incr_update';

      BACKUP

        INCREMENTAL LEVEL 1

        FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;

      delete noprompt obsolete device type disk;

    }

  • Dude!
    Dude! Member Posts: 22,826 Black Diamond
    edited Jun 20, 2020 12:17AM

    pastedImage_0.png

    That must explain why I had to reinstall MS Windows numerous times.

    As far as backup and disaster recovery concerns, however, simplicity is probably the best advice.

    RMAN> backup as compressed backupset incremental level 1 database plus archivelog delete input;

    .. is probably all you really need, unless you want to go fancy, but then you better know what you're doing.

    Steve_74Steve_74
  • Dude!
    Dude! Member Posts: 22,826 Black Diamond
    edited Jun 20, 2020 12:12AM
    Thank you for pointing this out. Storage space is needed only for 1 days incremental backup. Right ? Every day, RMAN will take an L1 differential incremental backup then apply it to the image copies of the datafiles.

    You need space for storing the copy of database files, as well as incremental and archvielog backups. You will also need to be able to switch the database to the datafile copies in case of a disaster. If you use the same storage for backup and the production database, what will you do if you loose the storage system? If this is really important, you will also need a backup for the backup as well. Keep in mind that a backup often runs in the background, and doesn't get much attention until you realize you're unable to restore you database.

    Do you have limited space for backups? What you can rely on is that you are going to need more storage space for your backups than initially anticipated. You don't want to jeopardize your ability to have a reasonable strategy due to lack of storage space. If that's the case, you're saving at the wrong end and will pay for it later, often loosing the database in the process.

    Steve_74EdStevens
  • Dude!
    Dude! Member Posts: 22,826 Black Diamond
    edited Jun 20, 2020 12:37AM
    RUN{  RECOVER COPY OF DATABASE     WITH TAG 'incr_update';  BACKUP     INCREMENTAL LEVEL 1    FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;  delete noprompt obsolete device type disk;}

    That's the part to recover the image copy of your database and creating a new incremental level 1 backup for the purpose of image recovery. You still need the space to store the copy (image backup) of the database, and archivelogs to do the final recovery. You cannot use image copy and incremental level backups alone to recover the database, unless the database can be shutdown during backup.

    Regarding delete obsolete, yes you can add that to your script. It might help to save some disk space, but then again, your plan will only work when everything goes as planned. What happens, for example, if your last backup could not be completed due to some unexpected data volume or some failure. You should not rely on the previous backup to be removed to to have room for a new backup - it's a recipe for disaster.

    Steve_74