idle> select *from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production PL/SQL Release 22.214.171.124.0 - Production CORE 126.96.36.199.0 Production TNS for Linux: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production
Kunwar wrote:about how big is this database?
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?
Kunwar wrote: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.
@ 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
b. oracle documentation
But no luck so far :( . What do you guys suggest?
Edited by: Kunwar on Apr 26, 2013 5:45 AM
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.
SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
Kunwar wrote:And are you doing regular housekeeping of the archivelogs, or do you just let them build up forever?
@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.
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.
rman> backup archivelog delete all input;
@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.
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?
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?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.