This discussion is archived
1 2 Previous Next 26 Replies Latest reply: May 25, 2012 11:40 AM by Gamblesk RSS

Hot backup getting delayed everyday

891135 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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
  • 2. Re: Hot backup getting delayed everyday
    KuljeetPalSingh Guru
    Currently Being Moderated
    use RMAN
  • 3. Re: Hot backup getting delayed everyday
    sybrand_b Guru
    Currently Being Moderated
    +1 to Kuljeet Pal Singh

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: Hot backup getting delayed everyday
    891135 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    You may use RMAN for cold backups too :-)
  • 9. Re: Hot backup getting delayed everyday
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 11. Re: Hot backup getting delayed everyday
    sybrand_b Guru
    Currently Being Moderated
    The reason is likely hot backup.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 12. Re: Hot backup getting delayed everyday
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points