This discussion is archived
14 Replies Latest reply: Apr 26, 2013 11:38 PM by Kunwar RSS

Minimum size for db_recovery_file_dest_size

Kunwar Newbie
Currently Being Moderated
Hi Friends,
i am trying to enable flashback on in my database and i am getting the below error.

idle> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file


my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".
Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing?
Or is the size of the DB which impacts the db_recovery_file_dest_size also?

Please suggest.
idle> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
  • 1. Re: Minimum size for db_recovery_file_dest_size
    sb92075 Guru
    Currently Being Moderated
    Kunwar wrote:
    Hi Friends,
    i am trying to enable flashback on in my database and i am getting the below error.

    idle> alter database flashback on;
    alter database flashback on
    *
    ERROR at line 1:
    ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
    ORA-38708: not enough space for first flashback database log file


    my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".
    Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing?
    Or is the size of the DB which impacts the db_recovery_file_dest_size also?

    Please suggest.
    about how big is this database?
  • 2. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    @ sb92075      
    The size of the DB is 122,718 MB excluding the temp files.
  • 3. Re: Minimum size for db_recovery_file_dest_size
    Vishnusivathej Newbie
    Currently Being Moderated
    This is one way to calculate the recommended size, if you place the backups in the FRA.
    Disk Quota =
    Size of a copy of database +
    Size of an incremental backup +
    Size of (n+1) days of archived redo logs +
    Size of (y+1) days of foreign archived redo logs (for logical standby) +
    Size of control file +
    Size of an online redo log member * number of log groups +
    Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value)
  • 4. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    @ Vishnu . I did not understand your suggestion. I don't place my backups in FRA.
    What i want to do is try  out flashback database command.
    What doing so i get "ORA-38708:". To avoid that i want to know what should be the minimum size of db_recovery_file_dest_size required.
    How can i do the calculation for any given DB in general? Are there any formulae/guidenlines i need to follow:

    I searched below
    a. google
    b. oracle documentation
    c. metalink/MOS.
    But no luck so far :( . What do you guys suggest?

    Edited by: Kunwar on Apr 26, 2013 5:45 AM
  • 5. Re: Minimum size for db_recovery_file_dest_size
    316993 Pro
    Currently Being Moderated
    Kunwar wrote:
    @ Vishnu . I did not understand your suggestion. I don't place my backups in FRA.
    What i want to do is try  out flashback database command.
    What doing so i get "ORA-38708:". To avoid that i want to know what should be the minimum size of db_recovery_file_dest_size required.
    How can i do the calculation for any given DB in general? Are there any formulae/guidenlines i need to follow:

    I searched below
    a. google
    b. oracle documentation
    c. metalink/MOS.
    But no luck so far :( . What do you guys suggest?

    Edited by: Kunwar on Apr 26, 2013 5:45 AM
    There is no fixed calculation for sizing flashback logfile generation , it can vary considerably depending on specific workload of yours database activity.You can measure the size estimation for flashback log during couple of peak load hours , oracle deemed determined that 1G is not enough for yours flashback logs due to capturing peak hours database activity , this could be determined by V$FLASHBACK_DATABASE_LOG view , it let you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query.
    SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
    Possibly you can get some estimation before enabling flashback log by executing above query to set FRA size on top of these flashback log files (not sure), flashback log files estimation are direct proportional to yours database activity and flashback retention target , the more you want to house keep the flashback logs (define using flashback retention target parameter) and the more yours DML activity perform the more estimation size for flaashback logs will be revealed from above query result.



    Khurram

    Edited by: orawarebyte on Apr 25, 2013 7:55 PM
  • 6. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    @ orawarebyte      Thanks for the clarification. i am getting what you are saying.

    Now one question which is making me think (Please let me know if i need to open a different thread for this)

    Few days ago i had enabled flashback on the same database with db_recovery_file_dest_size =1g;

    After doing a few rounds of testing, shutting ,starting up the db, enabling,disabling flashback when i want to try something this week i tried to enable with db_recovery_file_dest_size =1g ,it fails with "ORA-38708:"

    The only thing that has changed is i have created 4 tables of 125MB each. Thats all. No other change in the database(size-wise). What do you think that oracle is not accepting value of 1g and accepting value of 20g for "alter database flashback on;"?
  • 7. Re: Minimum size for db_recovery_file_dest_size
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    What was the size of files which were there in db_recovery_file_dest when you set it to 1G and then tried enabling flashback? Read through the following from Tom Kyte which can give you hint about current used space in db_recovery_file_dest may hinder you enabling the flashback until you increase the value of this parameter

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:390358600346220623#873169900346998332

    Salman
  • 8. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    @Salman Qureshi      My db_recovery_file_dest is used for archive log files as well. But i dont recall what were the size of the archive files in that then.
  • 9. Re: Minimum size for db_recovery_file_dest_size
    EdStevens Guru
    Currently Being Moderated
    Kunwar wrote:
    @Salman Qureshi      My db_recovery_file_dest is used for archive log files as well. But i dont recall what were the size of the archive files in that then.
    And are you doing regular housekeeping of the archivelogs, or do you just let them build up forever?
    rman> backup archivelog delete all input;
    Bottom line for sizing the FRA, asking what the 'minimum' size is like asking 'how long is a string?' The minimum size is whatever you need in your environment to quit getting errors about it not being big enough.
  • 10. Re: Minimum size for db_recovery_file_dest_size
    SalmanQureshi Expert
    Currently Being Moderated
    hi,
    @Salman Qureshi My db_recovery_file_dest is used for archive log files as well. But i dont recall what were the size of the archive files in that then.
    My suspicion is that your db_recovery_file_dest had a lot of archive redo log files and/or backup sets which did not allow you to enable flashback because it would have checked "available space " in your db_recovery_file_dest and returned you error message if it did not file enough space. You might check you alert log file and I think you should have some message there also with the reason of the error becuase crunching db_recovery_file_dest during flashback enabled, puts message in alert log file.

    Salman
  • 11. Re: Minimum size for db_recovery_file_dest_size
    864103 Newbie
    Currently Being Moderated
    you can solve it by increase the parameter db_recover_file_dest_size
    or just edit the retention policy and then delete obsolete .
  • 12. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    @Salman Qureshi ,861100,Edstevens

    I moved the archived log file destination elsewhere and cleared up the archive space using commands given by Edstevens. Now i can set db_recovery_file_dest_size to lesser value i.e. 1g and can "alter database flashback on"


    Vishnu's comments of
    :
    This is one way to calculate the recommended size, if you place the backups in the FRA. 
    Disk Quota =
    Size of a copy of database +
    Size of an incremental backup +
    Size of (n+1) days of archived redo logs +
    Size of (y+1) days of foreign archived redo logs (for logical standby) +
    Size of control file +
    Size of an online redo log member * number of log groups +
    Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value) 
    One clarification: If i have enabled flashback database, do i still need the complete database backup. Because as it is i will be doing "flashback database to scn <scn number>"?? i think i will save the space of one backup?
  • 13. Re: Minimum size for db_recovery_file_dest_size
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    One clarification: If i have enabled flashback database, do i still need the complete database backup. Because as it is i will be doing "flashback database to scn <scn number>"?? i think i will save the space of one backup?
    Think, what happens if any or all of your datafiles get corrupted? Can you flashback recover your corrupted datafiles? So you need to have a backup strategy in place. Flashback only helps you to go back in time (by saving time for time based incomplete recovery) very fast, but id does not save you against media failure.

    Salman
  • 14. Re: Minimum size for db_recovery_file_dest_size
    Kunwar Newbie
    Currently Being Moderated
    Thanks Salman.
    I understood it now. Thanks.

Legend

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