Log Archive Destination Full How to Handle a Hung Database due to FRA Being Full

YouTube video

Introduction

In this article, we will discuss one of the most commonly asked interview questions related to the Oracle database. Many people often face the issue of a hung database when the log archive destination is full, resulting in the inability to generate or switch archive logs. We will explore different solutions to handle this situation and fix the issue.

Key Points

  1. Scenario: When the log archive destination is full, it can lead to a hung database and prevent any transactions or cloud switches from occurring.

  2. Common Solutions:

  3. Delete older archives if they are not needed.
  4. Increase the size of the archive destination.
  5. Change the archive destination to a mount point with sufficient space.
  6. Temporarily move the archives to a different location.

  7. Deleting Older Archives:

  8. Connect to the admin console and delete unnecessary archive logs.
  9. This will release space from the Fast Recovery Area (FRA) and allow the database to function properly.

  10. Increasing Archive Destination Size:

  11. If there is still available space in the mount point, increase the size of the archive destination.
  12. By doing this, the database will continue to generate archive logs without any issues.

  13. Changing Archive Destination:

  14. If the archive destination itself is full, change it to a different mount point with sufficient space.
  15. This will resolve the issue and allow the database to function normally.

  16. Moving Archives Temporarily:

  17. If none of the above solutions work, move the archives temporarily using the OS command.
  18. Once the space issue is resolved, the archives can be moved back to their original location.

Step-by-Step Process

  1. Check Database Status: Connect to the Oracle database and check the status of the database.
  2. Use the command to connect to the database: connect <username>/<password>@<database>
  3. Check the status of the database: select status from v$instance;

  4. Identify the Issue: Determine if the log archive destination is full by checking the alert log.

  5. Locate the alert log file: cd $ORACLE_BASE/diag/rdbms/<database_name>/<instance_name>/trace
  6. Open the alert log file: less alert_<instance_name>.log
  7. Look for error messages related to the log archive destination being full.

  8. Solution 1: Delete Older Archives:

  9. Connect to the admin console: rman target /
  10. Run the following command to delete old archive logs: crosscheck archivelog all; delete expired archivelog all;

  11. Solution 2: Increase Archive Destination Size:

  12. Determine the current usage of the recovery area: select * from v$recovery_file_dest;
  13. Increase the size of the recovery area: alter system set db_recovery_file_dest_size=<size>g scope=both;

  14. Solution 3: Change Archive Destination:

  15. Identify a mount point with sufficient space.
  16. Modify the archive destination: alter system set log_archive_dest_1='<new_archive_dest>' scope=both;

  17. Solution 4: Moving Archives Temporarily:

  18. Use the OS command to move the archives to a temporary location.
  19. Fix the space issue in the original location.
  20. Move the archives back to the original location: alter system set log_archive_dest_1='<original_archive_dest>' scope=both;

Conclusion

In this article, we have discussed the common issue of a hung database due to a full log archive destination. We have explored various solutions to handle this situation and provided step-by-step instructions for each solution. By following these guidelines, you can effectively resolve the issue and ensure the smooth functioning of your Oracle database. Remember to regularly monitor and manage the log archive destination to avoid similar issues in the future.