1 2 Previous Next 26 Replies Latest reply on May 25, 2012 6:40 PM by Gamblesk Go to original post
      • 15. Re: Hot backup getting delayed everyday
        jgarry
        Do you have a cron job doing alter system switch logfile every 30 minutes?
        • 16. Re: Hot backup getting delayed everyday
          Jonathan Lewis
          jgarry wrote:
          Do you have a cron job doing alter system switch logfile every 30 minutes?
          What he didn't tell us, and what I asked about, was the log_checkpoint_timeout - which defaults to 30 minutes and would produce exactly this "incremental checkpoint" report every 30 minutes if log_checkpoints_to_alert was set to true.

          Regards
          Jonathan Lewis
          • 17. Re: Hot backup getting delayed everyday
            Jonathan Lewis
            888132 wrote:

            Here in our environment all the tablespaces go into hot backup mode at a time instead of one by one through a script.
            Not a good idea
            alter tablespace PROAIMIS01 begin backup
            Completed: alter tablespace PROAIMIS01 begin backup
            Mon Apr 19 01:02:47 2010
            alter tablespace SYSTEM begin backup
            Completed: alter tablespace SYSTEM begin backup
            ...
            Mon Apr 19 01:07:47 2010
            ***Completed checkpoint up to RBA [0x1ced.2.10], SCN: 10912956469916***
            ***Mon Apr 19 01:14:13 2010***
            ***Incremental checkpoint up to RBA [0x1ced.fd.0], current log tail at RBA [0x1ced.***
            ***10a.0]***
            ***Mon Apr 19 01:44:32 2010***
            ***Incremental checkpoint up to RBA [0x1ced.73f.0], current log tail at RBA [0x1ced***
            ***.89e.0]***Mon Apr 19 01:51:58 2010*alter tablespace CTPIS01 end backup*Mon Apr 19 01:51:58 2010
            Completed: alter tablespace CTPIS01 end backup
            Mon Apr 19 01:51:58 2010
            alter tablespace MAPDS01 end backup
            Completed: alter tablespace MAPDS01 end backup
            Mon Apr 19 01:51:58 2010
            alter tablespace SYSAUX end backup
            Completed: alter tablespace SYSAUX end backup
            Mon Apr 19 01:51:58 2010
            alter tablespace AUDT01 end backup
            Completed: alter tablespace AUDT01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace PROAIMIS01 end backup
            Completed: alter tablespace PROAIMIS01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace MAPIS01 end backup
            Completed: alter tablespace MAPIS01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace UNDO01 end backup
            Completed: alter tablespace UNDO01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace USER01 end backup
            Completed: alter tablespace USER01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace PROAIMDS01 end backup
            Completed: alter tablespace PROAIMDS01 end backup
            Mon Apr 19 01:51:59 2010
            alter tablespace PACESIS01 end backup
            Completed: alter tablespace PACESIS01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace XDB01 end backup
            Completed: alter tablespace XDB01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace ADMN01 end backup
            Completed: alter tablespace ADMN01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace SYMANTEC_I3_ORCL end backup
            Completed: alter tablespace SYMANTEC_I3_ORCL end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace CTPDM01 end backup
            Completed: alter tablespace CTPDM01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace PACESDS01 end backup
            Completed: alter tablespace PACESDS01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace SYSTEM end backup
            Completed: alter tablespace SYSTEM end backup
            Mon Apr 19 01:52:01 2010
            alter tablespace CTPDS01 end backup
            Completed: alter tablespace CTPDS01 end backup
            Mon Apr 19 01:52:01 2010
            alter tablespace PERF01 end backup
            Completed: alter tablespace PERF01 end backup
            Mon Apr 19 01:52:01 2010
            alter database backup controlfile to trace
            Completed: alter database backup controlfile to trace
            Mon Apr 19 01:52:01 2010
            alter database backup controlfile to '$CTLBKPDIR/$ORACLE_SID-$dt-$tm.ctl'
            Completed: alter database backup controlfile to '$CTLBKPDIR/$ORACLE_SID-$dt-$tm.
            ctl'
            Mon Apr 19 01:52:03 2010
            Beginning log switch checkpoint up to RBA [0x1cee.2.10], SCN: 10912957698129
            Thread 1 advanced to log sequence 7406
            Completed: alter tablespace PACESDS01 end backup
            Mon Apr 19 01:52:00 2010
            alter tablespace SYSTEM end backup
            Completed: alter tablespace SYSTEM end backup
            Mon Apr 19 01:52:01 2010
            alter tablespace CTPDS01 end backup
            Completed: alter tablespace CTPDS01 end backup
            Mon Apr 19 01:52:01 2010
            alter tablespace PERF01 end backup
            Completed: alter tablespace PERF01 end backup
            Mon Apr 19 01:52:01 2010
            alter database backup controlfile to trace
            Completed: alter database backup controlfile to trace
            This shows that you've switched tablespaces in and out of backup mode, in the space of about 47 minutes, in which time Oracle has done two time-based checkpoints. (log_checkpoint_timeout). We might reasonably guess that you ran your backup between 1:14 and 1:52 am


            >
            Incremental checkpoint up to RBA [0x396d.3129.0], current log tail at RBA [0x396
            d.366c.0]
            Tue May 22 03:14:23 2012
            Incremental checkpoint up to RBA [0x396d.147a8.0], current log tail at RBA [0x39
            6d.15ac8.0]
            Tue May 22 03:44:45 2012
            Incremental checkpoint up to RBA [0x396d.1c679.0], current log tail at RBA [0x39
            6d.1d177.0]
            Tue May 22 04:15:08 2012
            Incremental checkpoint up to RBA [0x396d.20387.0], current log tail at RBA [0x39
            6d.205dd.0]
            Tue May 22 04:45:30 2012
            Incremental checkpoint up to RBA [0x396d.20dcf.0], current log tail at RBA [0x39
            6d.2109e.0]
            Tue May 22 05:15:48 2012
            Incremental checkpoint up to RBA [0x396d.23af5.0], current log tail at RBA [0x39
            6d.23d6a.0]
            Tue May 22 05:46:05 2012
            Incremental checkpoint up to RBA [0x396d.245e3.0], current log tail at RBA [0x39
            6d.245ef.0]
            Tue May 22 05:47:08 2012
            This shows that a few days later from 3:14 to 5:47 Oracle was still doing time-based incremental checkpoints every 30 minutes - it tells us nothing about the time that your backup took place, or the amount of time it took. However, if you're trying to tell us that some days your backup takes much longer than usual, perhaps it's because you run it at a different time when more (or a different type of) work is going on.

            Regards
            Jonathan Lewis
            • 18. Re: Hot backup getting delayed everyday
              jgarry
              Yes, I did confuse interval and timeout too.

              To the OP: Please read about [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1133.htm#I1030106]V$INSTANCE_RECOVERY.
              • 19. Re: Hot backup getting delayed everyday
                891135
                Hi Jonathan,


                I will tell the enter scenario here. Normally this entire hot backup event is scheduled and it is scheduled to run everyday on 1:02 AM and end on 1:54 AM like below

                Sun Apr 4 01:02:25 2010
                alter tablespace PACESIS01 begin backup
                Completed: alter tablespace PACESIS01 begin backup
                Sun Apr 4 01:02:25 2010
                alter tablespace CTPDS01 begin backup
                Completed: alter tablespace CTPDS01 begin backup
                Sun Apr 4 01:16:34 2010
                Incremental checkpoint up to RBA [0x1c6a.2ef32.0], current log tail at RBA [0x1c
                6a.2ef79.0]
                Sun Apr 4 01:46:53 2010
                Incremental checkpoint up to RBA [0x1c6a.2f37d.0], current log tail at RBA [0x1c
                6a.2f3a1.0]


                Sun Apr 4 01:54:18 2010
                alter tablespace CTPIS01 end backup
                Sun Apr 4 01:54:19 2010
                Completed: alter tablespace CTPIS01 end backup
                Sun Apr 4 01:54:19 2010
                alter tablespace MAPDS01 end backup


                Now for the last few days the hot backup is getting started really late and in no scheduled time and it is also ending much later than usual taking a huge lot of time. For example today it has started at 2:28 AM and it has still not ended. It is 7:27 now and the hot backup has not ended till.

                Fri May 25 02:28:23 2012
                alter tablespace ADMN01 begin backup
                Completed: alter tablespace ADMN01 begin backup
                Fri May 25 02:28:23 2012
                alter tablespace PACESIS01 begin backup
                Completed: alter tablespace PACESIS01 begin backup
                Fri May 25 02:28:23 2012
                alter tablespace CTPDS01 begin backup

                In the meantime there has been a few incremental checkpoints as well. That is where we are trying to dig deep and seeking help to identify the cause.

                Also below are the values of the parameters asked for,

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                log_checkpoints_to_alert boolean TRUE

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                log_checkpoint_timeout integer 1800

                ------------------------------------ ----------- ------------------------------
                fast_start_mttr_target integer 300
                • 20. Re: Hot backup getting delayed everyday
                  Jonathan Lewis
                  888132 wrote:

                  I will tell the enter scenario here. Normally this entire hot backup event is scheduled and it is scheduled to run everyday on 1:02 AM and end on 1:54 AM like below

                  Now for the last few days the hot backup is getting started really late and in no scheduled time and it is also ending much later than usual taking a huge lot of time. For example today it has started at 2:28 AM and it has still not ended. It is 7:27 now and the hot backup has not ended till.

                  In the meantime there has been a few incremental checkpoints as well. That is where we are trying to dig deep and seeking help to identify the cause.
                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  log_checkpoint_timeout integer 1800
                  You are running hot backups.
                  The time it takes to do a hot backup is affected by the rate of change of data as the back is running because (a) the backup process is now competing for I/O resources and (b) the backup is causing an increase in I/O resource consumption in the database bcause the log writer now has to write (some) whole blocks to the log as the blocks change and (c) because of the extra waits for log writes, some batch processes may take longer which may result in more I/O being done to acquite undo blocks for read-consistency purposes.

                  If you check your alert log you will see an incremental checkpoint every half hour of every day. Incremental checkpoints are NOT making your backups slower, it's the slower backups that allow more incremental checkpoints (half hour intervals) to occur between the start and end of backup.

                  Schedule your backup properly, and don't backup more than one tablespace per backup device at a time.

                  Regards
                  Jonathan Lewis
                  • 21. Re: Hot backup getting delayed everyday
                    Gamblesk
                    Just a thought on the scheduling / late start problem.

                    I am not sure how you are scheduling your backups but I have in a previous life seen backups get all dumped in the backup scheduler (Commvault in this case) at one time. Then the backup software decides what to run when. This lead to something similar to what you are seeing. We had one Oracle Apps DB that most nights the backup would start just fine at 9:00 PM but periodically it would not start until 3, 4, 5 AM causing problems for the users if the backup was still running when they came in.

                    This really has nothing to do with Commvault it was just the way the parties responsible for backups decided to do things. Long arguments about scheduling backups at specific times etc didn't get anywhere until the business started complaining.

                    Like I said not sure how you are scheduling your backups but it might be worth looking into how that is done and what else might be running at the same time and what might be stopping your backup from running. Things like waiting for tape drives etc.
                    • 22. Re: Hot backup getting delayed everyday
                      891135
                      Thanks Jonathan for the clarification. But what is causing the delay in starting the backups ? Please throw some light on this.
                      • 23. Re: Hot backup getting delayed everyday
                        jgarry
                        Yes, please tell us how you are scheduling the backups. Is it simply in a queue waiting for something else to finish?
                        • 24. Re: Hot backup getting delayed everyday
                          Richard Harrison .
                          Hi,
                          Maybe i'm missing something here but what you are saying is:

                          1) something runs alter tablespace xxx being backup for each tablespace (you know you can do this in one statement with alter database begin backup?) - this command completes in fractions of a second - you can see that from the times between each tablespace.
                          2) the backup runs using 'autosys' - (never heard of this) this varies in time between 'normal' and a 'long time' - this must be some os utility
                          3) the end backup runs - again taking no time

                          The checkpoint messages have nothing to do with your backup running.

                          the autosys thing is the problem - sometimes it takes very little time - sometimes ages - you need to see how this is actually copying the files - is it straight to tape - is there a delay here - contention with tother session etc. Find the logs for this and then monitor ths when the process runs to see what it actually does.

                          And to repeat what everyone else is saying - use RMAN - even if its to disk and then the files are backed up by this autosys tool.

                          Regards,
                          Harry
                          • 25. Re: Hot backup getting delayed everyday
                            891135
                            The backup is scheduled through a tool called Autosys which is a scheduler tool.
                            • 26. Re: Hot backup getting delayed everyday
                              Gamblesk
                              Autosys must have some logs available for its scheduling activities.

                              It should not be hard to see that Autosys submitted the job at X time and it did not start running until sometime after that. Hopefully it would be really nice and tell you why it waited assuming that it is waiting.
                              1 2 Previous Next