Skip to Content

Redo reorg in Oracle11g

Hello,

After migrating to 11g, we have noticed checkpoint is not complete during intensive batch jobs.

It basically means that Oracle 11g is gummier in redo size than 10g.

Notes 79341 specifies that, when the interval between log switch is less than 1 minute,

we should increase the redo size.

When the interval is higher than one minute, we should increase the redo groups number.

Could you explain me why?

I would be tempted to say that small redo size would force a checkpoint faster.

Therefore, with fast redo switch, it would be better to have a small redo size.

Thanks in advance for your answer.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Dec 06, 2011 at 04:15 PM

    Hello Benoît,

    do you really hit a "checkpoint is not complete" issue or do you only see some "Private strand flush not complete" message like that:

    Tue Dec 06 14:30:18 2011
    Thread 1 cannot allocate new log, sequence 61050
    Private strand flush not complete
      Current log# 4 seq# 61049 mem# 0: /oracle/<SID>/origlogB/log_g14m1.dbf
      Current log# 4 seq# 61049 mem# 1: /oracle/<SID>/mirrlogB/log_g14m2.dbf
    Beginning log switch checkpoint up to RBA [0xee7a.2.10], SCN: 4172998151
    Thread 1 advanced to log sequence 61050 (LGWR switch)
    

    If you only hit the "Private strand flush not complete" issue - it is a normal behavior - for details please check metalinknote #372557.1.

    Could you explain me why?

    Well basically said, if you have a real "checkpoint is not complete" issue - both solutions (increasing log file size or adding more redo log groups) have the same effect.

    Your database system has more time to bring your redo log group to status INACTIVE until it will be reused. If it is still ACTIVE and you want to reuse it, it is not possible because of the need of crash recovery.

    Checkpoints are a complex beast based on some algorithms like FAST_START_MTTR_TARGET for example.

    -> http://docs.oracle.com/cd/E11882_01/server.112/e17110/initparams087.htm#i1127412

    Jonathan Lewis replied in detail on some OTN topics:

    /thread/609453

    Regards

    Stefan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Stefan,

      thanks for that info.

      Actually I was aware of "incremental checkpoints" as a (well now not any more all too new)

      "new" feature, but I never looked up how these really work in detail :-). Thanks again.

      @Benoit: I looked up the messages as we had some of these again recently (because someone

      "downsized" my eight archivers to four again...) This actually wasn't a peak situation, but we had

      trouble with some SAN IO in addition, this is why the times are quite bad.

      :
      :
      Tue Nov 29 12:37:38 2011
      ALTER SYSTEM SET log_archive_max_processes=8 SCOPE=BOTH;       <<< yelling for help
      Tue Nov 29 12:37:39 2011
      Beginning log switch checkpoint up to RBA [0x169bb.2.10], SCN: 11863071730
      Thread 1 advanced to log sequence 92603 (LGWR switch)
        Current log# 2 seq# 92603 mem# 0: /oracle/PRD/origlogB/log_g12m1.dbf
        Current log# 2 seq# 92603 mem# 1: /oracle/PRD/mirrlogB/log_g12m2.dbf
      Tue Nov 29 12:39:51 2011
      
      ORACLE Instance PRD - Can not allocate log, archival required    <<< message to grep for
      Thread 1 cannot allocate new log, sequence 92604
      All online logs needed archiving                                 <<< message to grep for
      
        Current log# 2 seq# 92603 mem# 0: /oracle/PRD/origlogB/log_g12m1.dbf
        Current log# 2 seq# 92603 mem# 1: /oracle/PRD/mirrlogB/log_g12m2.dbf
      Tue Nov 29 12:40:08 2011
      Completed checkpoint up to RBA [0x169b6.2.10], SCN: 11862476358
      Tue Nov 29 12:41:09 2011
      Archived Log entry 92237 added for thread 1 sequence 92596 ID 0x20a4cb56 dest 1:
      ARC2: STARTING ARCH PROCESSES                 <<<<<<<<<< Reinforcements called
      Tue Nov 29 12:41:11 2011
      ARC4 started with pid=569, OS id=47186752     <<<<<<<<<< here they come 
      Tue Nov 29 12:41:11 2011
      ARC5 started with pid=628, OS id=12518526     <<<<<<<<<< and the next guy
      ARC4: Archival started     <<<<<<<<<< first knight starting to fight
      Tue Nov 29 12:41:12 2011
      Beginning log switch checkpoint up to RBA [0x169bc.2.10], SCN: 11863191874
      Thread 1 advanced to log sequence 92604 (LGWR switch)
        Current log# 1 seq# 92604 mem# 0: /oracle/PRD/origlogA/log_g11m1.dbf
        Current log# 1 seq# 92604 mem# 1: /oracle/PRD/mirrlogA/log_g11m2.dbf
      ARC5: Archival started
      :
      :
      

      Volker

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2011 at 02:33 PM

    Checkpoint not complete happen when all redo logs are still active. To render them inactive the dbwr process(es) have to write the data blocks changed by these transactions to the data files.

    dbwr is triggered by checkpoints (e.g. log switch checkpoints). So if you have frequent log checkpoints (< below 1min) and dbwr still cannot keep up increase the log size, or add more groups otherwise.

    many small log files -> early trigger of dbwr

    large log files -> plenty of space for transactions, more time for dbwr to write changes to data files

    In most cases the combination of both measures is the optimal solution. I just had a case with checkpoint not complete. There were 4 groups with 150mb each, i added two more groups and increase the log size to 300mb. It looks like this improved the situation considerably.

    And last but not least dbwr can also be tuned (very platform depending):

    - tune random io write performance on data files

    - configure dbwr_io_slaves or multiple dbwr processes

    Cheers Michael

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Benoît Schmid

      Yes but dbwr will also take more time to write dirty blocks if there are more to write.

      Therefore we could think that it takes twice the time to write two times more dirty blocks.

      Am I wrong?

      Basically you are right, but i believe that often the same blocks are changed several times and only the latest change has to be written to file. Thus larger logfiles (more changes on the same block within a checkpoint) can mean less writes for dbwr.

      But it would be better if you ask someone with more profound oracle knowledge than me...

      😊

  • Posted on Dec 06, 2011 at 02:37 PM

    Hi,

    Check below thread, it will clear your doubt:

    http://help.sap.com/saphelp_nwpi71/helpdata/en/c4/3a728b505211d189550000e829fbbd/content.htm

    Thanks

    Sunny

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      >

      > Check below thread, it will clear your doubt:

      >

      > http://help.sap.com/saphelp_nwpi71/helpdata/en/c4/3a728b505211d189550000e829fbbd/content.htm

      >

      > Thanks

      > Sunny

      Hello your post does not clear my doubts as in my case, the problem is not the logwr that writes too slow.

      The problem, as stated in the first answer is that the dbwr takes times to write the dirty blocks to disk.

      The redo can not be reused until all its dirty blocks have been written.

      Regards,

  • Posted on Dec 06, 2011 at 03:44 PM

    increase the size of redologs... also increase the number of redolog groups.

    investigate usage of brspace with option moredo

    refer to SAP Note 1259767 - Management of online redo log files ...

    Bonne chance!

    Add a comment
    10|10000 characters needed characters exceeded

    • increase the size of redologs... also increase the number of redolog groups.

      >

      > investigate usage of brspace with option moredo

      >

      > refer to SAP Note 1259767 - Management of online redo log files ...

      >

      > Bonne chance!

      Hello Eric,

      My problem is not performing the change as I have already performed it once on my systems.

      My question is understanding the best redo size/number combination.

      Regards,

  • Posted on Dec 09, 2011 at 07:45 PM

    Ask SAP to do amn EarlyWatch report for you.

    They will recommend what to do with size and number of redo logs.

    Last time I had an EarlyWatch report, they recommend 8 groups of 800MB each... That was for a 1TB database.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.