1 2 3 Previous Next 31 Replies Latest reply on Nov 11, 2013 5:19 AM by 975148

    Designing backup policy for Oracle 11g database

    975148

      We have Oracle 11g database and our database size is around 80 GB. The backup is scheduled using RMAN. I had the query that how much time should be taking to restore 80 GB of data?

       

      Also, we are facing space issues with storing RMAN backups. Our backup policy currently is-

       

      Sunday                                        - Full backup

      Monday, Wednesday, Friday      - Incremental level 0 backup

      Tuesday, Thursday, Saturday    - Incremental level 1 backup

       

      I was thinking if I could change the policy backup so that less space could be used. Could this be made to-

       

      Sunday                                                                                             - Full backup

      Monday, Tuesday, Wednesday, Thursday, Friday, Saturday         - Incremental level 1 backup.

       

      I guess by changing this, the only impact would be that in case a restoration is required, the time taken to complete the restoration would be more? Let’s take an example, say if the restoration is required on Thursday, then how would the process of RMAN be? Would it be like

       

      1) Apply the redo logs on the incremental 1 backup taken on Wednesday

      Or

      2) Take the full backup of Sunday, apply incremental backups from Monday to Wednesday, apply redo logs subsequently till Thursday.

       

      I hope, my question is clear.

       

      Please revert with the reply to my query.

       

      Regards

        • 1. Re: Designing backup policy for Oracle 11g database
          EdStevens

          975148 wrote:

           

          We have Oracle 11g database and our database size is around 80 GB. The backup is scheduled using RMAN. I had the query that how much time should be taking to restore 80 GB of data?

           

          Also, we are facing space issues with storing RMAN backups. Our backup policy currently is-

           

          Sunday                                        - Full backup

          Monday, Wednesday, Friday      - Incremental level 0 backup

          Tuesday, Thursday, Saturday    - Incremental level 1 backup

           

          I was thinking if I could change the policy backup so that less space could be used. Could this be made to-

           

          Sunday                                                                                             - Full backup

          Monday, Tuesday, Wednesday, Thursday, Friday, Saturday         - Incremental level 1 backup.

           

          I guess by changing this, the only impact would be that in case a restoration is required, the time taken to complete the restoration would be more? Let’s take an example, say if the restoration is required on Thursday, then how would the process of RMAN be? Would it be like

           

          1) Apply the redo logs on the incremental 1 backup taken on Wednesday

          Or

          2) Take the full backup of Sunday, apply incremental backups from Monday to Wednesday, apply redo logs subsequently till Thursday.

           

          I hope, my question is clear.

           

          Please revert with the reply to my query.

           

          Regards

          First, why are you taking a 'full' backup on Sunday, then an Incremental level 0 backup on Monday?  An inc-0 backup IS a full (complete) backup.  The difference is that your sunday 'full' backup cannot be used as part of an incremental backup restore strategy.  It pretty much stands on its own.

           

          When you take the inc-1 backups you have a choice of a cumulative or differential backup.

          Cumulative will include all db data blocks modified since the last inc-0 backup.  Thus each successive inc-1 backup will be larger than the previous.  On a highly active database, the inc-1 backups could easily and quickly be larger than the inc-0.

          Differential inc-1 will include all db data blocks modified since the last inc-1 backup.  Thus they will take less space, but will take more time to recover your db.

           

          A full recovery will always begin with a restore from the relevent inc-0 backup, then application of the relevent inc-1.  If your inc-1 backups are cumulative, it will only need to apply the latest one prior to the point in time to which you are recovering. If your inc-1 backups are incremental, each one will need to be applied.  Once the db backups are restored, the necessary redo will be applied.

           

          So, as in most design decisions, you have  a trade-off.  Storage space vs. time to recover.  And regardless of your strategy, no one can tell you how long a recovery is going to take.

          1 person found this helpful
          • 2. Re: Designing backup policy for Oracle 11g database
            Paul M.

            We have Oracle 11g database and our database size is around 80 GB.

            Surely not a very big database... I don't have a database of that size, the smallest one, closest to yours, is around 130G.

             

            I recently simulated a "disaster recovery" with this DB, and my backup strategy is as follows :


            - Sunday                  :  incremental level 0 backup    

            - Rest of the week  :  incremental level 1 (differential) backup

            - Every two hours   :  archivelogs backup (even more often for critical DBs)

             

            The restore has been done on Thursday at 11 AM, and it took around one hour and half. Of course the time will also depend on the speed of your machines and network.

            1 person found this helpful
            • 3. Re: Designing backup policy for Oracle 11g database
              975148

              Thanks a lot for your answer. But, I thing I did not get is that where to specify a incremental 1 differential backup as the option while creating a schedule only says about a cumulative backup.

               

              Request your revert.

              • 4. Re: Designing backup policy for Oracle 11g database
                ropgenoo

                Hi,

                 

                Also, we are facing space issues with storing RMAN backups. Our backup policy currently is-

                 

                I understood from your post, that you're also facing space issues. Do you use compressed backupsets?
                You can speed up the incremental backup process by using Block Change Tracking, because the RMAN then knows which blocks changed since the last backup.

                 

                Kind regards

                Richard

                • 5. Re: Designing backup policy for Oracle 11g database
                  TSharma-Oracle

                  Honestly if you take the full compressed backup daily, it should not take more than 25 GB of space and should be done in 30 minutes even on average system.  Why don't you just go with the full compressed backup daily? is there any specific reason? DISK is cheap

                  • 6. Re: Designing backup policy for Oracle 11g database
                    EdStevens

                    975148 wrote:

                     

                    Thanks a lot for your answer. But, I thing I did not get is that where to specify a incremental 1 differential backup as the option while creating a schedule only says about a cumulative backup.

                     

                    Request your revert.

                    Sounds like you are trying to create your backups by clicking pretty icons in OEM.

                    I write my own backup scripts and run them from the OS scheduler.  I've found out the hard way that the OEM repository can be rather brittle, so I don't depend on it for critical functions like backup and recovery.

                    • 7. Re: Designing backup policy for Oracle 11g database
                      Eduardo Legatti

                      Hi,

                       

                      I think that does not make sense to mix FULL backups and LEVEL 0 Incremental backups in the same backup policy. My backup RMAN policy is similar to others in this thread:

                       

                      - Sunday:  incremental level 0 backup (plus archivelog DELETE ALL INPUT)   

                      - Rest of the week:  incremental level 1 (differential) backup (plus archivelog)

                      - Staturday:  restore database validate check logical

                       

                      The only observation is that I don´t delete archivelogs during the week. It sounds like a mirroring of my incremental LEVEL1 backups.

                       

                      Cheers

                       

                      Legatti

                      • 8. Re: Designing backup policy for Oracle 11g database
                        975148

                        Thanks for your answer. Is there a way to define compressed backup sets while creating a schedule in Oracle 11g enterprise manager?

                        • 9. Re: Designing backup policy for Oracle 11g database
                          975148

                          Thanks for your answer. But my query was that where to specify a incremental 1 differential backup as the option while creating a schedule only says about a cumulative backup?

                           

                          • 10. Re: Designing backup policy for Oracle 11g database
                            975148

                            Thanks for your answer but does that mean that there is no way to specify a incremental 1 differential backup from EM?

                            • 11. Re: Designing backup policy for Oracle 11g database
                              975148

                              I have a query in this that as per your post, "once the db backups are restored, the necessary redo will be applied" would mean that that it could pick the redo either from the archive logs if the redo entries are archived or directly from the redo logs if the redo logs have not yet been archived?

                               

                              Request you to please revert with the clarification.

                              • 12. Re: Designing backup policy for Oracle 11g database
                                975148

                                Thanks for your reply, your post has raised one query in my mind that the archive logs would need to be manually deleted as the retention policy would be applicable only for the RMAN backups. Would the archive logs have to be backed up separately with the option of archive logs as there is an option to do that in customized backup or would they be included in say an incremental level 0 backup?

                                 

                                Request a revert on this.

                                • 13. Re: Designing backup policy for Oracle 11g database
                                  ropgenoo

                                  Thanks for your answer. Is there a way to define compressed backup sets while creating a schedule in Oracle 11g enterprise manager?

                                  In Enterprise Manger you've to go to "Backup Settings" -> "Device" -> "Disk Settings" -> "Disk Backup Type", here you can select "Compressed Backup Set".'

                                   

                                  But as already stated by EdStevens, don't rely on Enterprise Manager in regards to you backup. It's safer and more flexible to write your own backup scripts and

                                  schedule them by the OS scheduler, e.g. cron.

                                  • 14. Re: Designing backup policy for Oracle 11g database
                                    975148

                                    Thanks for your answer but it can you please throw some light on how % of space is saved using compressed backup set.

                                     

                                    I hope I have put the query correctly. Please let me know.

                                    1 2 3 Previous Next