This discussion is archived
10 Replies Latest reply: Jun 18, 2012 6:04 PM by mseberg RSS

FRA - Flashback logs usage varies on Primary against Standby Database

Sivaprasad S Newbie
Currently Being Moderated
Environment:
HP-UX B.11.31 U ia64
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Question:

Primary DB: The FRA on OraDB_P is set at 400 GB. It is showing 338 GB of flashback logs.

Physical Standby DB: The FRA on OraDB_R is set at 400 GB. It is showing 274 GB of flashback logs.

There is an inconsistency between these two figures. I would have thought that the standby would have at least the same volume of flashback logs.

Please let me know the reason, why the standby is using less FRA space on flashback logs?
  • 1. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    CKPT Guru
    Currently Being Moderated
    Sivaprasad S wrote:
    Environment:
    HP-UX B.11.31 U ia64
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

    Question:

    Primary DB: The FRA on OraDB_P is set at 400 GB. It is showing 338 GB of flashback logs.

    Physical Standby DB: The FRA on OraDB_R is set at 400 GB. It is showing 274 GB of flashback logs.

    There is an inconsistency between these two figures. I would have thought that the standby would have at least the same volume of flashback logs.

    Please let me know the reason, why the standby is using less FRA space on flashback logs?
    What is the Retention on both primary & standby?

    & post from both primary & standby
    SQL> select * from V$FLASHBACK_DATABASE_LOG;
  • 2. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    Sivaprasad S Newbie
    Currently Being Moderated
    Primary:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    -------------------- -------------------- ---------------- -------------- ------------------------
    7.8182E+10 30-APR-2012 01:11:49 *1440* 4.9564E+11 1.3030E+11

    Standby:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    -------------------- -------------------- ---------------- -------------- ------------------------
    7.8160E+10 29-APR-2012 12:10:49 *1080* 4.7680E+11 9.5807E+10
  • 3. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    mseberg Guru
    Currently Being Moderated
    So CKPT nailed it


    You Primary is 60 x 24 = 1440

    While your Standby is 60 X 18 = 1080

    Change you Standby if you want them to match.

    alter system set db_flashback_retention_target=1440;

    So your Standby flashback log are about 75% to 80% of the primary depending upon when you check. This matches the numbers in the first post very close.

    Best Regards

    mseberg

    Your query shows Primary 1440 and Standby 1080 which lines up with you first post.

    Primary:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    --------------------
    --------------
    7.8182E+10 30-APR-2012 01:11:49 1440 4.9564E+11 1.3030E+11
    
    Standby:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    --------------------
    --------------
    7.8160E+10 29-APR-2012 12:10:49 1080 4.7680E+11 9.5807E+10
    Edited by: mseberg on May 1, 2012 11:47 AM
  • 4. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    CKPT Guru
    Currently Being Moderated
    Sivaprasad S wrote:
    Primary:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    -------------------- -------------------- ---------------- -------------- ------------------------
    7.8182E+10 30-APR-2012 01:11:49 *1440* 4.9564E+11 1.3030E+11

    Standby:
    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    -------------------- -------------------- ---------------- -------------- ------------------------
    7.8160E+10 29-APR-2012 12:10:49 *1080* 4.7680E+11 9.5807E+10
    Dear,

    Depends on retention , So check above what is retention in primary & standby you have been configured.
    When configuration is same then ask the question again.

    A suggestion, further if you posting output put in a coded format, so that easy to understand.
    Thanks.
  • 5. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    Sivaprasad S Newbie
    Currently Being Moderated
    However in our case, the standby was holding less flashback logs than the primary.

    The standby said that it could flashback much farther than the primary.

    To my understanding, if the primary has more flashback logs then it should be able to flashback farther than the standby. The primary said that it could only flashback about 8 hours while the standby could flashback 24 hours. It seems that it should be opposite of this.
  • 6. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    CKPT Guru
    Currently Being Moderated
    Hi,

    Here Along with Retention period you have to look at another few things.
     Primary:
     OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
     -------------------- -------------------- ---------------- -------------- ------------------------
               7.8182E+10 30-APR-2012 01:11:49             *1440*     4.9564E+11               1.3030E+11
     
     Standby:
     OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
     -------------------- -------------------- ---------------- -------------- ------------------------
               7.8160E+10 29-APR-2012 12:10:49             *1080*     4.7680E+11               9.5807E+10
    Well, it purely depends on Retention,

    Again, Is primary & standby is in SYNC? i doubt because fo FLASHBACK SCN change,

    Always remember that Oracle doesn’t guarantee that you can flashback your database to the flashback retention target. If Oracle is running low on free space in the flash recovery area for newly arriving archived redo log files, it will remove some flashback logs to make room.
  • 7. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    Sivaprasad S Newbie
    Currently Being Moderated
    Primary and Standby are in sync.
     
        Thread Last Sequence Received Last Sequence Applied Difference
    ---------- ---------------------- --------------------- ----------
             1                  28100                 28100          0
             2                  15447                 15447          0
  • 8. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    mseberg Guru
    Currently Being Moderated
    So you are saying the Standby has an older time 4-29-2012 than the Primary but its 25 percent smaller. How can that be?

    I have a feeling we could dig for a while but I would consider this :

    Only Oracle manages Flashback logs.

    If there’s space pressure in the Flash Recovery Area flashback logs may be deleted to make room for other things, like backups.

    So if you were doing backup on the Primary this might explain it. Now you might come back and say no I'm not doing backups on the Primary. All I can say the Primary FRA should be more active than the Standby FRA. Because of this whatever secret method Oracle uses to free up FRA kicks in more on a Primary. There's a chance you can find somebody who can answer this, but most likely not.

    This is the best answer I can come up with and it makes sense to me. Good question.

    Best Regards

    mseberg
  • 9. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    SivaOra Newbie
    Currently Being Moderated
    I would appreciate other views on this.

    This is not yet fully answered.
  • 10. Re: FRA - Flashback logs usage varies on Primary against Standby Database
    mseberg Guru
    Currently Being Moderated
    On "not yet fully answered"

    It should be. There are things you control and things Oracle controls. You don't control this ( once you decide to use the feature ). Oracle controls this.

    Worry about things you can control I say. This isn't one of them.

    Flashback trivia is a waste of time.

    That said Chapter 3 - "Using the Flash Recovery Area" in RMAN Recipes for Oracle Database 11g is a good read.

    Best Regards

    mseberg

Legend

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