1 2 Previous Next 26 Replies Latest reply on May 25, 2012 6:40 PM by Gamblesk

    Hot backup getting delayed everyday

    891135
      Hi All,
      Everyday our hot backup job is getting delayed by a significant time. Before the tablespaces are placed into begin backup mode we spotted the alert log flooded with the below message ,

      Mon May 21 00:23:46 2012
      Incremental checkpoint up to RBA [0x396b.3365.0], current log tail at RBA [0x396
      b.3386.0]
      Mon May 21 00:54:03 2012
      Incremental checkpoint up to RBA [0x396b.3b81.0], current log tail at RBA [0x396
      b.3b98.0]
      Mon May 21 01:24:21 2012
      Incremental checkpoint up to RBA [0x396b.495a.0], current log tail at RBA [0x396
      b.497a.0]
      Mon May 21 01:54:47 2012
      Incremental checkpoint up to RBA [0x396b.5109.0], current log tail at RBA [0x396
      b.5137.0]
      Mon May 21 02:25:13 2012
      Incremental checkpoint up to RBA [0x396b.6771.0], current log tail at RBA [0x396
      b.67b6.0]


      Also after the tablespaces are placed into begin backup mode before the end backup occurs the altert log is also flooded with the below messages,

      Incremental checkpoint up to RBA [0x396b.24107.0], current log tail at RBA [0x39
      6b.24afe.0]
      Mon May 21 05:58:39 2012
      Incremental checkpoint up to RBA [0x396b.2569e.0], current log tail at RBA [0x39
      6b.257f2.0]
      Mon May 21 06:28:56 2012
      Incremental checkpoint up to RBA [0x396b.2815a.0], current log tail at RBA [0x39
      6b.28925.0]
      Mon May 21 06:59:14 2012
      Incremental checkpoint up to .

      Then the tablespaces are placed into end backup mode resulting in significant delay.

      Please help how we can reduce the backup time and also please throw some light on these checkpoint related messages.
        • 1. Re: Hot backup getting delayed everyday
          anand prakash - oracle
          Hi,

          These messages in the alert log is as log_checkpoint_to_alert has been set true. Have you seen/maintained the tablespace size information for the day? Is there increase in the number of datafiles?

          What aren't you using RMAN?

          Anand
          • 3. Re: Hot backup getting delayed everyday
            +1 to Kuljeet Pal Singh

            ---------
            Sybrand Bakker
            Senior Oracle DBA
            • 4. Re: Hot backup getting delayed everyday
              891135
              We cannot use Rman as it is our client's policy to use user managed hot backup. Please suggest what is the reason behind the delay and how it can be tuned ?
              • 5. Re: Hot backup getting delayed everyday
                Aman....
                If the client has a wrong policy, its your job to enlighten him that its not a good thing in 2012 to not use RMAN for hot backups. As for your question, you haven't told us anything that can lead to any sort of meaningful reasoning to be given to you. What you are using to schedule this backup, dbms_job package?

                Aman....
                • 6. Re: Hot backup getting delayed everyday
                  891135
                  Hi Aman,

                  Our client will not listen to us no matter how many times we insist for hot backup..We use a tool called autosys where the backup time is scheduled but it is getting delayed everyday. Is more number of iincremental checkpoints responsible for this ? If yes how to get rid of this and what should be the reason ?
                  • 7. Re: Hot backup getting delayed everyday
                    Aman....
                    Incremental checkpointing is a continuous activity and I cant think of any ways that it can slow down a scheduled job. Now, as you mentioned that you are using a 3rd party tool to do the backup, did you check in the logs of that tool that what's going on underneath?

                    Aman....
                    • 8. Re: Hot backup getting delayed everyday
                      PK125XL
                      You may use RMAN for cold backups too :-)
                      • 9. Re: Hot backup getting delayed everyday
                        Mark Malakanov (user11181920)
                        what is 4-digit Oracle version and OS?
                        Then the tablespaces are placed into end backup mode resulting in significant delay.
                        which tablespace causing delay? for how long?
                        • 10. Re: Hot backup getting delayed everyday
                          jgarry
                          I believe what is happening is the incremental checkpoints allow Oracle to avoid updating the data file headers for those checkpoints. When you go into hot backup mode, you have to do a full checkpoint to update the data file headers. So see if it makes a difference doing an alter system checkpoint and waiting for it to complete before you go into hot backup mode. I expect it won't be any faster overall, and only the begin backup will be faster, but at least your customer will have his intransigence about rman pushed back into his face where it belongs.
                          1 person found this helpful
                          • 11. Re: Hot backup getting delayed everyday
                            The reason is likely hot backup.

                            -----------
                            Sybrand Bakker
                            Senior Oracle DBA
                            • 12. Re: Hot backup getting delayed everyday
                              Jonathan Lewis
                              888132 wrote:
                              Hi All,
                              Everyday our hot backup job is getting delayed by a significant time. Before the tablespaces are placed into begin backup mode we spotted the alert log flooded with the below message ,

                              Mon May 21 00:23:46 2012
                              Incremental checkpoint up to RBA [0x396b.3365.0], current log tail at RBA [0x396
                              b.3386.0]
                              Mon May 21 00:54:03 2012
                              Incremental checkpoint up to RBA [0x396b.3b81.0], current log tail at RBA [0x396
                              b.3b98.0]
                              Mon May 21 01:24:21 2012
                              Incremental checkpoint up to RBA [0x396b.495a.0], current log tail at RBA [0x396
                              b.497a.0]
                              Mon May 21 01:54:47 2012
                              Incremental checkpoint up to RBA [0x396b.5109.0], current log tail at RBA [0x396
                              b.5137.0]
                              Mon May 21 02:25:13 2012
                              Incremental checkpoint up to RBA [0x396b.6771.0], current log tail at RBA [0x396
                              b.67b6.0]
                              One message every half hour doesn't look like flooding. To get such regularity I'd guess that you've got the log_checkpoint_timeout set to 30 minutes. You might want to consider changing your configuration standards, because you might have done something to slow down the rate at which the database writer copies dirty blocks to disc - which could have an impact on your "begin backup".

                              When you issue "begin backup" Oracle issues a tablespace checkpoint and waits for it to complete. (And then the log writer will have to write complete database blocks to the log file (sometimes) as the data is modified by user sessions.) If you put many, or all, tablespaces into backup mode at the same time you could have a massive I/O overload and find your backup procedure waiting for a long time until all the checkpoints are complete. If you have to do manual backups, then one tablespace at a time is better - and a setting of fast_start_mttr_target that keeps the database writer a little more busy copy dirty blocks to disc at a steady rate may help.

                              Regards
                              Jonathan Lewis
                              • 13. Re: Hot backup getting delayed everyday
                                891135
                                The database version is 10.2.0.4 enterprise edition.

                                Edited by: 888132 on May 24, 2012 4:05 AM
                                • 14. Re: Hot backup getting delayed everyday
                                  891135
                                  Hi Jonathon ,

                                  Thanks for your valuable suggestion.

                                  Here in our environment all the tablespaces go into hot backup mode at a time instead of one by one through a script.
                                  Previously when the hot backup used to work fine the number of incremental checkpoints between begin backup and end backup were significantly less. It is also evident from the excerpt of the alert log file.

                                  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:02:47 2010
                                  alter tablespace MAPDS01 begin backup
                                  Completed: alter tablespace MAPDS01 begin backup
                                  Mon Apr 19 01:02:47 2010
                                  alter tablespace ADMN01 begin backup
                                  Completed: alter tablespace ADMN01 begin backup
                                  Mon Apr 19 01:02:47 2010
                                  alter tablespace PACESIS01 begin backup
                                  Completed: alter tablespace PACESIS01 begin backup
                                  Mon Apr 19 01:02:47 2010
                                  alter tablespace CTPDS01 begin backup
                                  Completed: alter tablespace CTPDS01 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
                                  alter tablespace ADMN01 end backup
                                  Completed: alter tablespace ADMN01 end backup
                                  Completed: alter tablespace CTPDS01 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




                                  But for the last couple of days the number of incremental checkpoints seem to be higher between begin backup and end backup like below,

                                  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



                                  Also the parameter log_checkpoint_interval is set to zero here.
                                  log_checkpoint_interval integer 0

                                  And the parameter fast_start_mttr_target is set to 300
                                  fast_start_mttr_target integer 300

                                  Please suggest ..
                                  1 2 Previous Next