Here are the steps to clear flashback logs in Oracle without causing downtime ¹:
- Check the size of the recovery area with the command "show parameter db_recovery"
- Check how much space is used and free with the command "select space_used/(1024_1024_1024),space_limit/(1024_1024_1024) from v$recovery_file_dest"
- Set the parameter "log_archive_min_succeed_dest" with the command "show parameter log_archive_min_succeed_dest"
- Set "log_archive_dest_1" to a different location with the command "alter system set log_archive_dest_1='LOCATION=/home/oracle/temp_archivelog' scope=memory"
- Defer the default "DB_RECOVERY_FILE_DESTINATION" with the command "alter system set log_archive_dest_state_10=defer scope=memory"
- Switch logfiles with the command "alter system switch logfile"
- Make "db_recovery_file_size" the same as the actual usage with the command "alter system set db_recovery_file_dest_size=55G scope=memory"
- Check for errors and manually remove archive log files from the OS and run "RMAN> crosscheck archivelog all" and "RMAN> delete expired"
- Put log_archive_destination back with the commands "alter system set log_archive_dest_1=' ' scope=memory" and "alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=memory"
- Make sure "flash_recovery_area" has enough space with the command "alter system set db_recovery_file_dest_size=50G scope=spemory"
