xtivia-oracle-errors-ebook-link

Click to download full Oracle Error eBook

A very common Oracle database error that is basically telling you that you have run out of logical or physical space on the mount, disk, or your db_recovery_file_dest location that holds your archivelogs. When you get this error your database hangs and will not allow anyone besides admin level users to log into the database to perform maintenance to fix the problem.

The first step to fixing this issue would be to log into your server and see if you have run out of physical space on one of your disks or mounts. If you have run out of physical space you have a few options. You can either point your archivelogs to write to a new disk or mount with sufficient space, you can backup your archivelogs and delete input, or you can just delete the archivelogs if you won’t need them to restore your database. I would recommend using rman for either the backup or delete methods as this will keep your catalog of backups up to date and clean.

If there is plenty of physical space on the disk or mount that your archivelog files are being written to then you need to connect to the database and check for logical space. What you need to do is to run the following commands to find out where your archivelogs are being written to and how much space you have allocated for them to take up.

This command will show you where your archivelogs are being written to:


SQL> show parameter log_archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      /u01/archivelog/orcl/

If the ‘log_archive_dest’ parameter is empty then you are most likely using a ‘db_recovery_file_dest’ to store your archivelogs. You can run the below command to see that location.


SQL> show parameter recovery
  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/fast_recovery_area
db_recovery_file_dest_size           big integer 100G

You will see at least these two parameters if you’re on Oracle 10g, 11g, or 12c. The first parameter ‘db_recovery_file_dest’ is where your archivelogs will be written to and the second parameter is how much space you are allocating for not only those files, though also other files like backups, redo logs, controlfile snapshots, and a few other files that could be created here by default of you don’t specify a specific location.

Please comment below if this helped you fix your ORA-00257: archiver error. Connect internal only, until freed issue. If you require more help please content us to speak with a certified Oracle DBA support expert.

This post has been updated, learn more here.

Share This