How to delete Flashback logs from database in oracle 10g ?
Hi All,
In Oracle 10g flashback logs are generated in flash recovery area which can be used to go back in time in the database. This feature can be used to recover dropped table or even data from tables which might changing with time such as in OLTP applications.
These Flashback logs are designed in such a way that if there is space pressure in flash recovery area the flashback logs should be automatically deleted relieving the space.
But due to certain oracle 10g BUG flashback logs do not get automatically deleted even when space in flash recovery area is 100% full and hence to delete flashback logs the following method can be used.
1) shutdown the database using shutdown immediate
2) startup the database in mount stage
3) then fire the command
SQL> alter database flashback off;
4) Now if will see that all the flashback logs are being deleted .
If u dont need this feature u can be keep the flashback permanently off. But u need the faeture in mount stage itself you have to give
SQL> alter database flashback on;
In case of RAC to enable the flashback feature all the other instance except from the other instances from which the flashback should be enabled should be shutdown.
This will enable the flashback feature again.
To remove uncessary Flashback Logs, you need to do the following:
1) drop any Guaranteed Restore Points that you don’t need (e.g. drop restore point
2) set the init parameter DB_FLASHBACK_RETENTION_TARGET adequately
3) Log in as root and run the following statements:
ls -lrt …/flashback|tail -100
prepare delete statement for each file you want to remove
(e.g. rm -f
Thanks . A nice article.